Thursday, July 25, 2013

PowerShell: Downloading CSV's from SCCM-SMS 2007 Reporting Websites

I needed to bundle the SCCM/SMS 2007 ConfigMgr web reporting results into PowerShell or Excel.  The SMS Web Reporting has a great export-to-CSV tool on each page but runs Java in the background and requires a confirmation click and a subsequent filename before being able to download the CSV file.  Once downloaded, it quickly becomes a PowerShell object via import-csv.  After lots of searching, I compiled a method to automatically download and save the CSV file so it can be used by PowerShell.


#Set the variables

$xhttp = $nul
$stream = $nul
$username = "domain\username"
$password = "password"
$url = 'http://sccmserver/SMSReporting_bob/Report.asp?ReportID=386&Name=MyPcName'
$destination = "c:\users\myname\desktop\MyPcName.csv"

#Start XML

$xhttp = new-object -com msxml2.xmlhttp

$xhttp.open("Post",$url,$false,$username,$password)

$xhttp.setrequestheader("Content-Type","application/x-www-form-urlencoded")

#this sends the request to perform a CSV export

$xhttp.send("export=yes")

#This line's not needed but I use it to get confirmation
#that the download is queued correctly.  If not OK then
#I know to check the above part of this script

$xhttp.statustext

#Now grab the file in the buffer and save it to disk

$stream = new-object -com ADODB.Stream

$stream.open()

$stream.type = 1

#Connect the buffer to the downloaded file

$stream.write($xhttp.responsebody)

$stream.savetofile($destination,2)

$stream.close()

$stream = $nul

References:
http://myitforum.com/cs2/blogs/jnelson/archive/2008/04/02/114527.aspx
http://www.greyhathacker.net/?p=500
http://scriptolog.blogspot.com/2007/08/query-http-status-codes-and-headers.html

1 comment:

Unknown said...

Thanks for all those awesome PowerShell code samples. Helps me a lot! I owe you a beer.