Number2 Blog - myITforum

THE PROBLEM

You want to automatically run a set of SMS Web reports, but you don't have something like SQL Server Reporting Services installed (which would give you the ability to have people subscribe to reports and have them delivered a bunch of ways).  So, you need the ability to run SMS Web reports unattended and save the results somewhere.  

THE SOLUTION (Get the full code here--> ExportSMSReport.txt <-- rename to .VBS)
(UPDATED 3/24/2008  Made OpenTextFile support unicode so special characters will write to text file properly)
(UPDATED 4/01/2008  Using responseBody instead of ResponseText and saving using ADODB.Stream)

The answer is in the object "Microsoft.XMLHTTP".  We can use that little bugger to simulate an HTTP POST command to the web reporting server and fake it into thinking we've just clicked EXPORT on an SMS web report.  We then capture the CSV that gets returned and save it to a file.  This file could later be emailed or just opened directly and reviewed.

Here's a quick example of how to use "Microsoft.XmlHttp"

    Set WshShell = WScript.CreateObject("WScript.Shell")

    strURL = "http://SMSSERVER/SMSReporting_XXX/Report.asp?ReportID=382&param1=abc"
    strOutFile = "c:\output.csv"

    'setup an HTTP POST to the report URL
    Set http = CreateObject("Microsoft.XmlHttp")
    http.open "Post", strURL, False  
    http.setRequestHeader "Content-Type", "application/x-www-form-urlencoded" 'required header

    'fake SMS into thinking EXPORT has been clicked. The following line initiates the POST
    http.send "export=yes"    
   
    'save binary response using ADODB.Stream
    Set oStream = createobject("ADODB.Stream")
    oStream.type = 1 'adTypeBinary
    oStream.open
    oStream.write http.responseBody 'write the binary response from our POST above to the stream
    oStream.savetofile strOutputFile, 2 '2=adSaveCreateOverwrite...save http stream to a file
    oStream.Close

    Set WshShell = Nothing   

 So, if you want to automate this process, I'd do the following:

1. Save the attached ExportSMSReport.vbs to a spot on the computer you want to use as the report scheduler
2. Create a Scheduled Tasks entry for this script (make sure you pass the reportURL and the name of the output CSV file)
   The command-line for that task should look something like this:
   cscript c:\ExportSMSReport.vbs "http://reportURL/report.asp?ReportID=384" "c:\Reports\Report384.csv"
3. Schedule this task as a user who has rights to run the report you want exported

At this point whenever the scheduled task gets run, you should get a CSV file with the exported data from the report URL that you specified (make a new scheduled task for each new report you want exported)

(Get the full code here--> ExportSMSReport.txt <-- rename to .VBS)

Syntax:

 cscript c:\ExportSMSReport.vbs "http://ReportServerURL/report.asp?ReportID=384" "c:\Reports\Report384.csv"

I hope you find this useful!

Number2 (John Nelson)
MyITForum - Forum Posts
MyITForum - Blog