ResponsePoint SP1 CDR Reporting
http://cid-a491dfcb38b5df18.skydrive.live.com/self.aspx/pub-vids/RP%7C_CDR%7C_Analysis.wmv
today, I spent a little time working on a couple of scripts for 'adding value' to the ResponsePoint SP1 release. We have several Syspine's deployed at client sites, as well as our own. This exercise has renewed my 'scripting' bug and got me thinking about the opportunities that we pass up daily, weekly, monthly, and overall regularly. Back in the days of *nix administration I got very used to scripting EVERYTHING and placing it into cron. Likewise I spent lots of time creating windows batch files and vbs files for managing a variety of tasks in windows.
So the question came up recently in the RP Yahoo group about automated backup of the RP system. And while the ideal solution would be to have the RP dev team develop an app, even if it took cmd line options, I thought, huh, why can't I script this out? So today I set out to do just exactly that.
The first thing we should do is examine what the script should do, and then how it should be done. Now, for automating a backup remotely, one might do one of several things…login to a remote server using RDP or RWW, and execute the script, mostly the point of scripting this would be to save some clicks, or increase efficiency, or make it part of a process. Additionally one might be able to add it to an RDP file whereby a user at a remote service provider might have a pre-configured rdp file, they simply launch it, it auto-starts the script upon login, and maybe zips up the results and returns to the operator via email or file-copy or ftp or something…good idea, but we can do better…
So the next 'level' of this might be to have a VPN connection ready in waiting on a remote administrator's desktop. Have a script initiate the VPN, then launch the RP Admin screen, then tab through and pass the proper key strokes to the RP Admin screens, until the backup is complete…once we do that, maybe have it email a notice to some sort of ticketing system, maybe Shockey Monkey, to create a ticket, and setup documentation, of the service provider's work. Now that would be cool…extend it a bit further to be included in some sort of tickler system, maybe Outlook or SharePoint calendar, or a scheduled task or something…
Pretty cool, huh? Yea it is!
But wait, SP1 adds Call Detail Records to its feature set… So that got me to thinking, what about that. Abbie and I take great pride in our excel skills. So here's the thought…leverage the work I just did to create a backup script, but now, grab a set of Call Detail Records, and create a nice excel pivot table and pivot chart. So the video above shows that work in progress…
Option Explicit
Dim objShell, Racey, intCount
Set objShell = CreateObject("WScript.Shell")
wscript.echo "VPN into GP"
objShell.Run "rasdial GP administrator *******"
Wscript.Sleep 4500
wscript.echo "Connecting to RP"
objShell.Run "Administrator.exe 192.168.1.120"
Wscript.Sleep 9000
Wscript.Sleep 4500
objShell.SendKeys "{ENTER}"
Wscript.Sleep 9000
Wscript.Sleep 4500
wscript.echo "Logging into RP"
objShell.SendKeys "*******"
objShell.SendKeys "%C"
Wscript.Sleep 4500
Wscript.Sleep 4500
Wscript.Sleep 4500
Wscript.Sleep 4500
wscript.echo "Base Unit"
objShell.SendKeys "{TAB}"
Wscript.Sleep 500
wscript.echo "Call Routing"
objShell.SendKeys "{TAB}"
Wscript.Sleep 500
wscript.echo "Syspine Module"
objShell.SendKeys "{TAB}"
Wscript.Sleep 500
wscript.echo "About"
objShell.SendKeys "{TAB}"
Wscript.Sleep 500
wscript.echo "Help"
objShell.SendKeys "{TAB}"
Wscript.Sleep 500
wscript.echo "Add Phone"
objShell.SendKeys "{TAB}"
Wscript.Sleep 500
wscript.echo "Edit Phone"
objShell.SendKeys "{TAB}"
Wscript.Sleep 500
wscript.echo "Remove Phone"
objShell.SendKeys "{TAB}"
Wscript.Sleep 500
wscript.echo "Add User"
objShell.SendKeys "{TAB}"
Wscript.Sleep 500
wscript.echo "Edit User"
objShell.SendKeys "{TAB}"
Wscript.Sleep 500
wscript.echo "Remove User"
objShell.SendKeys "{TAB}"
Wscript.Sleep 500
wscript.echo "Add User to Phone"
objShell.SendKeys "{TAB}"
Wscript.Sleep 500
wscript.echo "Add Voice Service"
objShell.SendKeys "{TAB}"
Wscript.Sleep 500
wscript.echo "Edit Voice Service"
objShell.SendKeys "{TAB}"
Wscript.Sleep 500
wscript.echo "Remove Voice Service"
objShell.SendKeys "{TAB}"
Wscript.Sleep 500
wscript.echo "View Call History"
objShell.SendKeys "{TAB}"
Wscript.Sleep 500
objShell.SendKeys "{ENTER}"
Wscript.Sleep 9000
Wscript.Sleep 4500
wscript.echo "Changing to field"
objShell.SendKeys "%I"
'objShell.SendKeys "{TAB}"
'objShell.SendKeys "{TAB}"
wscript.echo "Changing to T"
objShell.SendKeys "T"
Wscript.Sleep 9000
wscript.echo "First L"
objShell.SendKeys "L"
Wscript.Sleep 9000
wscript.echo "Last L"
objShell.SendKeys "L"
wscript.echo "getting records"
Wscript.Sleep 9000
Wscript.Sleep 9000
wscript.echo "saving records"
objShell.SendKeys "%S"
Wscript.Sleep 4500
objShell.SendKeys "c:\users\bwatters\documents\rp_cdr_export.csv"
wscript.echo "Enter"
objShell.SendKeys "{ENTER}"
Wscript.Sleep 9000
Wscript.Sleep 4500
wscript.echo "Yes"
objShell.SendKeys "%Y"
Wscript.Sleep 4500
wscript.echo "records saved"
objShell.SendKeys "{TAB}"
Wscript.Sleep 1500
wscript.echo "leaving CDR Screen"
objShell.SendKeys "{ENTER}"
Wscript.Sleep 1500
wscript.echo "Closing out RP Administrator"
objShell.SendKeys "%{F4}"
Wscript.Sleep 1500
wscript.echo "Closing out VPN"
objShell.Run "rasdial GP /disconnect"
Wscript.Sleep 1500
wscript.echo "Opening Excel"
objShell.Run "excel c:\users\bwatters\documents\rp_cdr_export.csv"
Wscript.Sleep 9000
objShell.SendKeys "{ENTER}"
Wscript.Sleep 500
wscript.echo "Alt-N"
objShell.SendKeys "%N"
Wscript.Sleep 500
wscript.echo "V"
objShell.SendKeys "V"
Wscript.Sleep 500
wscript.echo "T"
objShell.SendKeys "T"
Wscript.Sleep 500
wscript.echo "Enter"
objShell.SendKeys "{ENTER}"
Wscript.Sleep 500
wscript.echo "Alt-N"
objShell.SendKeys "%N"
Wscript.Sleep 500
wscript.echo "C"
objShell.SendKeys "C"
wscript.echo "Enter"
objShell.SendKeys "{ENTER}"
So I use a lot of commenting and it depends on a lot of timings…It is also dependent on having created or staged a VPN connection. It is being openned by rasdial on line 5 and then closed again by rasdial further down the line. Additioanlly, make sure that you put in the correct password on line 15. Given that the password is being stored inside this file, BE SURE TO SECURE THIS FILE PROPERLY. As a best practice, your RP administrator SHOULD NOT BE THE SAME AS YOUR DOMAIN ADMIN PASSWORD!!!
This process assumes a secure environment in the first place. If that does not exist, then don't even bother with this. As a service provider, you should already have a certain level of security in place…Notice I am placing the export in my documents folder…again, maybe a best practice might be to place the file on a network share that Is backed up. Another thought might be to use ADO to place the records up in an Access or SQL database to create a data warehouse, and use something like SQL Reporting services or Crystal Reports to create and publish reports. With SQL reporting services, one could integrate into sharepoint and provide a client portal effect…The mind runs rampant with ideas!!!