Desperately need help with sql analyzer

[rossporubski]

Hi all, I've scoured the internet looking for any example statements I might be able to use to run reports from cmm db. It's been asked to keep back up records of in use DN's, abandoned DN's, Forced Auth Codes etc. I don't know how to write statements and can't seem to find anything associated with cisco's sql query's. I've gone to sql forums but not many people are familiar with ccm. Please help in any way. Cisco only provides a brief summary for writing a statement to pull 911 data. Thanks to all in advance :)

[pradeepde]

Try this link

http://www.cisco.com/en/US/products/sw/voicesw/ps556/products_tech_note09186a00801e7ddf.shtml

[gajolly]

hi,

i may be wrong but i believe if you are looking for SQL queries then it will be something like mentioned below.

for example

Select * from 'TableName'where DN=''

now tablename can be any tablename from SQL database and instead of DN u can search with any other value..

[rossporubski]

I've tried this on CDR CCM0301 and ART, all of them return invalid column name on DN and Directory Number. The only way I can get the name and number is by querying, CCM0301:

select * from device where name is Not Null

This returns all data then I have to export the csv into excel and run the delimited option to split the name and dn into seperate columns.

[gajolly]

Okay what exactly u wanted to query i will do the same on my CM n will update u with the right query...

[dennisbehrens · Accepted solution]

Why don't you use a route plan report to get the used DN's and abandoned DN's?

For the Forced Auth Codes, use this query on the CCM030X database:

select Code, AuthorizationLevel, Description from FACInfo;