SQL Query Analyzer for Forced Auth Codes

[cdiross]

I would like to create a report of all in-use “forced authorization codes” from call manager, utilizing sql analyzer. Here is the statement I’ve created:

FROM CDR
select * from calldetailrecord where forcedauthorizationcode= ' '

I get an error message “forcedauthorizationcode not found”. I have over 400 user specific auth codes in use. I want a complete listing of who is associated with which one. CDR does not offer this. It only gives you three options to choose from to create a “clientmattercode” report. None of which I can use.

Does anybody know how to run this statement against the db and if I’m even close with my statement?

Any help is greatly appreciated. Thanks to all in advance

 

[VoIPGuyNSC]

You'll probably need to run your report on the ART table.

John Lever
Telecommunications
Richland School District Two
Columbia, SC

 

[cdiross]

Naa, nothing seems to work. You think it's my statement? I am by no means advanced with this. I'm just getting started but the problem is, I have no prior experience and cisco doesn't offer any type of examples of statements. Kind of like here is call manager have fun. It's probably simple as pie. However, it's driving me bonkers.

 

[VoIPGuyNSC]

Hehe. I can certainly feel that way at times. Shoot, I've been doing CM for two years now and I still learn new stuff every day. To be honest, I'm not quite sure what you are looking to return. But I think your key here is authCodeDescription in the Tbl_Billing_Data table. You will need to have the Analysis and Reporting Tool installed and running on CM in order to populate the ART database, though.

What I created some time ago was an Access Database with SQL linked tables to CallDetailRecord, NumPlan, Tbl_Billing_Data and Tbl_DialPlan,. I did this so as to quickly create reports when the bosses needed them. Those four tables hold a wealth of information. For example, the following SQL select in conjunction with Access Forms and Reports generate a nice site-based Billing Report for our schools. It could be rather easily modified to sort on FAC, rather than Directory Number, so as to give you an idea of those being used in the time period that you specified.

SELECT dbo_Tbl_Billing_Data.Duration AS Seconds, dbo_Tbl_Billing_Data.authCodeDescription AS AuthorizationCode, dbo_Tbl_Billing_Data.Orig_Date AS CallDate, Right([Orig_Called_Party_Number],10) AS Called, dbo_Tbl_Billing_Data.Orig_Party_Number AS Extension, dbo_Tbl_Billing_Data.Charge AS Cost, Left([Orig_Party_Number],2) AS SchoolDN, Schools.SchoolName
FROM dbo_Tbl_Billing_Data, Schools
WHERE (((dbo_Tbl_Billing_Data.Orig_Date)>=[Forms]![Report Generator]![cal_Start] And (dbo_Tbl_Billing_Data.Orig_Date)<=[Forms]![Report Generator]![cal_End]) AND ((Left([Orig_Party_Number],2))=[Schools.DNPrefix]) AND ((Len([Orig_Party_Number]))=5) AND ((Schools.SchoolID)=[Forms]![Report Generator]![cmbo_SchoolName]) AND ((dbo_Tbl_Billing_Data.Call_Classification) In ('L','LD')))
ORDER BY dbo_Tbl_Billing_Data.Orig_Party_Number, dbo_Tbl_Billing_Data.Orig_Date;

Hope this helps or at least gets you going in the right direction.


John Lever
Telecommunications
Richland School District Two
Columbia, SC

 

[cdiross]

Awesome. I'll try it. I'm actually pretty good with access. I'll let you know how I make out. Thanks for your expertise. Much appreciated

 

[cdiross]

Good morning @voIPGuyNSC:

I've never linked sql tables with an Access DB, I thought it would be easy, but apparently not. I'm working on creating the ODBC connection but am not sure if this is the right location for the tables:

Z:\Program Files\Microsoft SQL Server\MSSQL\Data

There are several .mdf and .ldf files within this directory on the ccm publisher. Is this where I want to be?

Thank you for your help. It's greatly appreciated.

 

[VoIPGuyNSC]

Open your ODBC Control Panel (by default, it won't show up in Windows XP), so go to START, RUN, odbccp32.cpl

Its there that you will add a System DSN using the SQL Driver. You'll have to give it the address and authentication information for your SQL server. Its best to create two sources here, one for the CCM0300 database and one for the ART database.

In Access, when you go to create the linked tables, select ODBC Sources from the selection type dropdown box and you will see the two ODBC sources you created earlier.

John Lever
Telecommunications
Richland School District Two
Columbia, SC

 

[cdiross]

Well looks like I finally figured out the db's and tables within the analyzer panel. I was able to run a simple statement

select * from <dbo> where <tbl> is Not Null

After perusing the dbo's hundreds of times, it all started to click. Ran the query's and got the reports.

Thanks for your help voIPGuyNSC. I'll eventually use your suggestion, I think that's the best.