A great friend of ours and client and partner has grand plans. He's a big thinker, and a big do-er. We are working on a project for them right now, helping them update their infrastructure. He has big, loud, wonderful, audacious plans, but he realized he needed a stable platform to launch from. We helped him a couple of years ago move into his current location, and at the time, he had big plans. Over the time hence, he has met most of the goals he set and then some. Like any small business they squeaked by on mac and cheese a couple of times. But they have survived and are looking to take that next step.
Fortunately for us, with all of our missteps, he still believes in us, and our ability to help him deliver of those dreams. So we are helping him with some of the core things we do…
Search :: Portals and Collaboration :: Unified Communications
Small Business Server :: Essential Business Server
Business Desktop Deployment
He's built his business on the back of our standard solutions: Small Business Server, and standardized desktops. Now we are moving up the chain.
Business Desktop Deployment: Windows XP SP3 on new workstations, and mobile workstations, Office 2007 Enterprise
Search: Windows Desktop Search 3.x, Microsoft Search Server (as part of MOSS), Competitive Search and Alerts from Google, Live, and MOSS
Portals and Collaboration: Microsoft Office SharePoint Server 2007, Exchange Server 2003/7, Groove, InfoPath
Unified Communications: Office Communications Server 2007, Communicator 2007, Exchange 2003/7, SBS Desktop Fax, Network Scanning, Integrated PBX
Mobility: Remote Web Workplace, Outlook Anywhere, Mobile Communicator, Live Messenger
We are building on the existing foundation of Small Business Server 2003 R2 Premium, now extending his Information Worker resources. He has realized that for his business to grow, he needs tools that empower, not limit his people. By empowering his people with the right data and right tools at the right time, in the right way, he is enabling his company to become THE PREMIER, BOUTIQUE COMPANY in his line of business.
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!!!
Wow, this summer has been a whirlwind! I am the primary care giver most days for our kids. Being in that role, while trying to also grow another kid, my business, it is difficult to find time to write a blog, or publish a podcast. We are also trying, but failing miserably to do a video show/podcast on Mogulus. Couple that with the usual summertime distractions, and it has led to a lack of content publishing. Not that I have been without inspiration or even creation. I have several video podcasts that I seem to need to re-encode. Not exactly sure why, but Soapbox won't take them, and therefore, Community Clips from office live won't take them either…very frustrating, you use tools as they are described, but it just won't do right…oh well.
I took my daughter a local park to watch our town's fireworks display last night. It was really fun!
Getting ready for the show!
The Grand Finale!
Playing in the sprinkler after the show!
For a suburban fireworks display, it was pretty good! Probably the highlight was just as the fireworks were coming to an end (about 30 minutes of display) the sprinklers turned on and there was a mad dash to cars!!! Except me, I was already sitting over at my car, on the hood. But the best part of this, was how my daughter, just stood there, soaking up the sprinkler!!! Great a soaking ride home!
We had a blast, and a huge thank you to EVERYONE who has given their lives or put themselves in harm's way to protect the freedoms we enjoy EVERYDAY. And a special THANK YOU to the families of the people serving us everyday!