PowerShell: Downloading SCCM 2012 ReportServer Report CSV's

The SCCM 2012 Reports interface is cumbersome for quickly pulling information together.  Instead, I updated my SCCM 2007 SQL server web-reporting technique.  I found it worked great and now can download reports as CSV's and use them in PowerShell scripts.

Here's an example:

invoke-webrequest "http://sccmserver/ReportServer?%2fConfigMgr_NR1%2fAsset+Intelligence%2fSoftware+02E+-+Installed+software+on+a+specific+computer&Name=$ComputerName&rs:Command=Render&rs:Format=CSV&rc:Toolbar=False" -credential $creds -outfile $destination 

This will download a report about all of the software loaded on one computer and place it into a CSV file.  All you'd have to do is $software = import-csv $destination 

Another example reports about all the software that starts when the computer is powered on:

iwr "http://sccmserver/ReportServer?%2fConfigMgr_NR1%2fAsset+Intelligence%2fSoftware+04C+-+Software+configured+to+automatically+run+on+a+specific+computer&Name=$ComputerName&rs:Command=Render&rs:Format=CSV&rc:Toolbar=False" -credential $creds -outfile $autodestination

A third example reports about what computers and IPs are currently on a subnet:

iwr -credential $creds "http://sccmserver/ReportServer?%2fConfigMgr_NR1%2fNetwork%2fIP+-+Computers+in+a+specific+subnet&variable=192.168.0.0&rs:Command=Render&rc:toolbar=false&rs:Format=CSV" -outfile $oursubnet

Here I get the network card information for a computer:

iwr -credential $creds "http://sccmserver/ReportServer?%2fConfigMgr_NR1%2fHardware+-+Network+Adapter%2fNetwork+adapter+information+for+a+specific+computer&variable=$ComputerName&rs:Command=Render&rc:toolbar=false&rs:Format=CSV&quo t; -outfile $NICDestination

A report about the users of a certain computer:

iwr -credential $creds "http://sccmserver/ReportServer?%2fConfigMgr_NR1%2fAsset+Intelligence%2fHardware+05A+-+Console+users+on+a+specific+computer&Name=$ComputerName&rs:Comm and=Render&rc:toolbar=false&rs:Format=CSV" -outfile $cache

A computer hardware report about a certain computer:

iwr -credential $creds "http://sccmserver/ReportServer?%2fConfigMgr_NR1%2fHardware +-+General%2fComputer+information+for+a+specific+computer&variable=$ComputerName&rs:Command=Render&rc:toolbar=false&rs:Format=CSV" -outfile $HWDestination

Disk information report about a certain computer:

iwr -credential $creds "http://sccmserver/ReportServer?%2fConfigMgr_NR1%2fHardware +-+Disk%2fDisk+information+for+a+specific+computer+-+Physical+disks&variable=$ComputerName&rs:Command=Render&rc:toolbar=false&rs:Format=CSV" -outfile $diskdestination

PowerShell: Netstat Established Connections List

This is a script which you can adapt to your needs.  I wanted to find the "Established" foreign computers and the programs which are talking to them.  PowerShell doesn't have a native tool (not that I'm aware of) so most use NetStat.  Here is the script I made that finds the remote host FQDN and converts the Process ID into a program name.  When finished, it displays a table but you can easily out-gridview if you'd like.

$Netstats = netstat -aof|select-string "active connections","Proto","\[::","Can not obtain" -notmatch|select-string "establish"
$process = get-process|select id,name

foreach($n in $Netstats){
$temp = $n.line.split(' ',[System.StringSplitOptions]::RemoveEmptyEntries)
$p = ($process|?{$_.id -like $temp[4]}).Name
$outcsv += write ($temp[2] + "," + $p + "`n")}
$Final = $outcsv|convertfrom-csv -header RemotePort,Program
$Final|ft -autosize

I'm sure you'll find it doesn't fit all your needs but should give you some insight on how to manipulate NetStat's output.

Reference:
Netstat
Get-Networkstatistics

PowerShell: Find Current Remote User Mapped Drives

I thought it would be simple to find someone's current drive mappings using PowerShell.  This wasn't the case.  When I used my credentials to remotely access the computer, it looked for the mapped drives of the credentialed user, not the current user.  After much research and testing, I found a solution that you can customize to your environment.  This script was tested in a Windows 7 Enterprise Active Directory environment using PowerShell 4.0 on the script side and PowerShell 2.0 on the client side.  Here is the script with explanations:

$computer = "remotecomputername"
$creds = get-credential

#Find the remote computer's current user and cut off the 
#domain part of the username
$user = (gwmi win32_computersystem -computer $computer -credential $creds).username.split('\')[-1]
#Query Active Directory using $user to find the users SID

$sid = (get-aduser $user).sid.value

#Run a remote script while inputting the user SID (variable at bottom of script)
invoke-command -computer $computer -credential $creds -scriptblock {
#Remote HKCU & HKLM are only loaded.  
#We're adding the HKEY_USERS key so we can query against it

set-location registry::\HKEY_USERS
New-PSDrive HKU Registry HKEY_USERS
Set-Location HKU:

#Find all mapped drive letters in the \network key
$drives = (gci -Path Microsoft.PowerShell.Core\Registry::HKEY_USERS\$($args[0])\Network -recurse)
#Read the RemotePath key from each mapped drive
$driveresults = foreach ($d in $drives){$q = ("Microsoft.PowerShell.Core\Registry::HKEY_USERS\$($args[0])\Network\" + $d.pschildname);get-itemproperty -Path $q;}
#You can manipulate the output to your needs and assign this whole invoke-command script to a variable for further formatting
$driveresults|Format-Table PSChildName,RemotePath -autosize -hidetableheaders} -argumentlist $sid

CMD: Install Windows 7 IP-based Printers via batch file

IT Departments debate over Server-based and IP-based direct printing.  I use IP-based printing as a way to lower WAN traffic, negate at least two points of failure, and speedup user response/print time.  Microsoft has developed some fine printer scripts they introduced in Windows XP.  They further polished these scripts in Windows 7 and placed them into their own printing_admin_scripts subfolder.  I'm sharing my sample script and giving a quick explanation of each command.  For more details about the Microsoft VB script switches, visit Microsoft's TechNet site or use Notepad to read the syntax notes placed inside each VBS file.

REMARK                   --- Start of Script ---

REMARK Added a script title so I know what printer I'm installing

echo *** Installing Canon iPF720 Plotter ***

REMARK Remove old printer presence via PowerShell registry search

PowerShell "if (test-path 'hklm:\software\microsoft\windows NT\currentversion\Print\Printers\HP DesignJet 1050C Plotter'){cscript c:\windows\system32\printing_admin_scripts\en-us\prnmngr.vbs -d -p 'HP DesignJet 1050C Plotter'}"

REMARK Checking for processor type then installing appropriate driver
REMARK AMD64 works with all typical Windows useable 64-bit processors

if %processor_architecture% equ AMD64 (cscript c:\windows\system32\printing_admin_scripts\en-us\prndrvr.vbs -a -m "Canon iPF720" -h "\\server\share\Printers\Drivers\Canon iPF720\64-bit\Driver" -i "\\server\share\Printers\Drivers\Canon iPF720\64-bit\Driver\6WJF02M.INF") else (cscript c:\windows\system32\printing_admin_scripts\en-us\prndrvr.vbs -a -m "Canon iPF720" -h "\\server\share\printers\Drivers\Canon iPF720\Driver" -i "\\server\share\printers\Drivers\Canon iPF720\Driver\2WJF02M.INF")

REMARK Adding the IP port the new printer will use

cscript c:\windows\system32\printing_admin_scripts\en-us\prnport.vbs -a -r "CanoniPF720" -h 192.168.1.100 -o raw -n 9100

REMARK Putting the Friendly name, driver, and port together

cscript c:\windows\system32\printing_admin_scripts\en-us\prnmngr.vbs -a -p "Canon iPF720 Plotter" -m "Canon iPF720" -r "CanoniPF720" 

REMARK Added a 10 second pause so I can chain install scripts together yet still monitor each installation progress

timeout /t 10

REMARK                   --- End of Script ---

I also thought about adding an audible prompt when things go right or wrong.  This would lessen the need to physically watch the install but rather allow multitasking by listening out for the correct sounds after each install while performing other duties.

PowerShell: Invoke-RestMethod Decibel.Net Sample

I learned how to insert Headers into an Inoke-RestMethod request in order to pull music information from an online database.  Afterwards, I can insert the received objects into MP3 ID3 tags using methods mentioned in my previous posts (including album covers).  I recently started using Decibel for grabbing music information (even though indications are that they're going to a pay-only API).  As of now, they have a free version and two paid versions which allow more information about each query.  When you create a free account, you'll be requested to make an application.  I called my application "PowerShell."  The system generated an Application ID & Key.  New to me was having to pass this Application ID, Application Key, and Date/Time via a header request when making my PowerShell queries.  After many web searches and trying different script methods, I'm sharing my successful script:

#Replace the x's with your Applications information
#Using an artist like "Fine Young Cannibals" will search for artists
#that have any or all of those three words

$DAppID = "xxxxxxxx"
$DAppKey = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxx"
[string]$DTimeStamp = get-date -format "yyyyMMdd HH:mm:ss"
$Artist = "Metallica"

#Decibel documention

#I requested all albums that have Metallica in the artists name field

$query = invoke-restmethod http://api.decibel.net/v1/albums?artist=$Artist `
-headers @{"DecibelAppID"=$DAppID;"DecibelAppKey"=$DAppKey;"DecibelTimestamp"=$DTimeStamp;} `
-method get

$result = $query.AlbumQueryResult.ResultSet.album

# I filtered for only Metallica album names

foreach ($r in $result){$r.Name|where-object{$r.Artists -eq $Artist}}