How to check UNUSED ADMIN-SCRIPTS through SQL Query..??

[Khurshid Ali]

Can someone help me with the SQL query, which would give result of total unused ADMIN SCRIPTs for past few months?? (not the routing scripts)

Thanks in advance for your help n concern..!!

[Senthil Kumar Sankar]

Hi,

Run a query to list all the Scripts from the AW Database. Take the Output in a Excel file

Run another query against the Route_Call_Detail for past few months. and use the ScriptID field to find the Scripts. Take this output in another Excel file and use Vlookup and find the unused Scripts.

Regards,

Senthil

[Senthil Kumar Sankar]

You can youse the below queries

Step 1:

Query to pull the used scripts between 17/12/2011 to 18/01/2012

select MasterScriptID,EnterpriseName from Master_Script where MasterScriptID in (

select MasterScriptID from Script where ScriptID in(

select distinct(ScriptID) from Route_Call_Detail

where DateTime between '2011-12-17 00:00:00' and '2012-01-18 23:59:59'))

Step 2:

Query to pull all the current configured Scripts 

select MasterScriptID,EnterpriseName from Master_Script

Step 3:

Once we get both the data, we can use Excel’s VLOOKUP functionality to find the unused Scripts

Regards,

Senthil

[Khurshid Ali]

Thanks Senthil for your time and concern.

I have run that query, but it only gives report for ROUTING SCRIPTs and NOT ADMIN-SCRIPTs(It shows that, NO Admin scripts has got call ever).

So, that is not working. Any other way??

[Senthil Kumar Sankar]

Hi Khurshid,

My bad. I think i did not noticed the Admin Scripts you have mentioned.

I am not aware of any table storing the Admin Scripts or the Schedules,of the Admin Scripts.

But how many Admin Scripts do you have, In your script explorer under the Administrative Manager should show all the Scripts, If the scripts are mentioned Everyday or Every week or whatever period the scripts are scheduled.

Regards,

Senthil

[Khurshid Ali]

You are right. There are around three hundred(approx)Admin Scripts. And all are scheduled as mostly Everyday.

[Senthil Kumar Sankar]

I quickly gone thru the Data Schema Guide. I do a see a table named as "t_Admin_Script_Schedule_Map"

http://www.cisco.com/en/US/docs/voice_ip_comm/cust_contact/contact_center/icm_enterprise/icm_enterprise_8_5/reference/guide/icm85schema.pdf

That Table consists of EndDay,EndDate etc. You could export the data in this Table and if you identify that these Scripts doesn't Expire and they are for Everyday. The All the Scripts will run as the schedule.

[Khurshid Ali]

Fine Senthil. I will check that out...

Thanks for your help.

Regards,

Khurshid Ali