sql query analyzer against cisco call manager DB

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

sql query analyzer against cisco call manager DB

Author

Topic

igotregistered
Starting Member

7 Posts

Posted - 2006-11-24 : 15:49:57

Greetings all. New user here. I'm just getting started with using sql query's with our cisco voIP call manager database. Although cisco has a utility "CDR" which runs pre-defined reports; it's not enough. So far there have been several request for specific data which "CDR" cannot compile, in which we have to use analyzer. Unfortunately I'm not versed and need a little help. It's been asked I pull a report from call manager which shows all "forced authorization Codes" cisco calls these client matter codes. Here is the query I tried using. It pulls all user data but the client matter code column is empty. I tried with * and *.* inside brackets as well.

select * from CallDetailRecord where clientmattercode=''

Can anybody help so I can get the records of who has what particular in use client matter code?

thanks to all in advance, I hope to learn as much as possible from the experts.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-24 : 15:53:24

Which result do you get from
SELECT DISTINCT ClientMatterCode FROM CallDetailRecord

Peter Larsson
Helsingborg, Sweden

igotregistered
Starting Member

7 Posts

Posted - 2006-11-24 : 15:56:18

Many thanks for your reply. I ran this and it returns a single empty cell with header.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-24 : 15:58:21

try your orignal query and replace empty space with null instead

select * from CallDetailRecord where clientmattercode is null

or try this

select * from CallDetailRecord where isnull(clientmattercode, '') = ''

Peter Larsson
Helsingborg, Sweden

igotregistered
Starting Member

7 Posts

Posted - 2006-11-24 : 16:06:31

The first new query gives me headers and empty data
The second new query gives me same results as the query I posted.

Your help is greatly appreciated.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-24 : 16:17:51

What does sp_help CallDetailRecord give?
Look at the line for column clientmattercode. What data type is this? Does it allow null?

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-24 : 16:22:09

What does select count(*) from CallDetailRecord give?

Peter Larsson
Helsingborg, Sweden

igotregistered
Starting Member

7 Posts

Posted - 2006-11-24 : 16:24:25

What does select count(*) from CallDetailRecord give?
This gives

no column name
184105

igotregistered
Starting Member

7 Posts

Posted - 2006-11-24 : 16:26:27

sp_help CallDetailRecord

This one gives

Name owner type date create time
CalldetailRecord dbo usertable 2006-08-09 11:19:56.903

then below shows the database details

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-24 : 16:29:14

Ok, so there are 184 thousand 105 records in the table.
And with SELECT DISCTINCT ClientMatterCode FROM CallDetailRecord you only got one record back.
This means all records in the database has the same ClientMatterCode, so there is really nothing to filter out by using ClientMatterCode.

Have you got the right information? Maybe there is some other column to filter by?

This query below can help you in many instances

SELECT    ClientMatterCode, -- Any column 
          COUNT(*)
FROM      CallDetailRecord
GROUP BY  ClientMatterCode -- Same column as in the SELECT
ORDER BY  ClientMatterCode -- Same column as in the SELECT

This query groups and count every record in the table with the column you provide.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-24 : 16:30:30

quote:
Originally posted by igotregistered

then below shows the database details

Exactly! What information is given for ClientMatterCode column?
Which datatype?

Peter Larsson
Helsingborg, Sweden

igotregistered
Starting Member

7 Posts

Posted - 2006-11-24 : 16:36:22

Client matter code does not show up in any of the tables provided with that query. The client matter codes are needed in order for the users to make Long distance calls. It's a three digit unique number associated with each unique user. No code is the same, they start @ 100 and go to 500. Perhaps something is not right on the db where as I am not able to get this information? Sorry I must run to catch the train. I truly do appreciate your help and you are very thorough. Perhaps I can re-visit this on Monday with you? Thank you so much.

igotregistered
Starting Member

7 Posts

Posted - 2006-11-29 : 10:21:22

After messing with it and getting myself acclimated with the databases here is the statement
select * from FACInfo where AuthorizationLevel is null

Got the data

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-29 : 16:27:28

Good for you!

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources