SCCM Script: Returning SQL Reports as Arrays

Overview

This script will allow you to take a standard SCCM SQL Report and output the report, automatically, to either a CSV or an array for use in a pipeline, etc.

Sample Workflow

This standard SCCM SQL report can be run which would allow us to see all of the software updates that are required but not deployed via WSUS.

Software Updates – B Deployment Management/Management 2 – Updates required but not deployed

Simply running the report gives us an indication of what variables are required to run this report. When fed into my function, the report will run automatically with these values chosen and the results can be either output to the pipeline as an array or to a CSV file for easy emailing.


Import-Module \\scriptserver\scripts\DMGSCCM\Get-DMGSCCMSQLReport\Get-DMGSCCMSQLReport.psm1 -Force


#Set Universal Parameters for this Report
$ReportServerUrl="http://sccmsqlrserver/ReportServer"
$ReportPath="/ConfigMgr_DGM/Software Updates - B Deployment Management/Management 2 - Updates required but not deployed"

#Create Array Of Data To Display in Report.
$OutputArrays = @()
$ProviderMachineName = "sccmsqlrserver.corp.corporation.com"
$Sitecode = "DGM"

Set-Location $Sitecode":"

#Array1
$inputParams = @{
    "CollID"="DGM00084";
    "UpdateClass"="Security Updates";
    "Vendor"="Microsoft";
}

$array = Get-DMGSCCMSQLReport -inputParams $inputParams `
                               -ReportServerUrl $ReportServerUrl `
                               -ReportPath $ReportPath `
                               -ProviderMachineName $ProviderMachineName `
                               -Sitecode $Sitecode
Set-Location $Sitecode":"  

#Generate Array with All Production Servers with Maintenance Window (Security Updates): Required But Not Deployed                                        
$arrayresult = $array | %{Get-CMSoftwareUpdate -ArticleId $_.Details_Table0_Title -Fast| ?{$_.nummissing -ge 1 -and $_.IsExpired -eq $FALSE -and $_.isSuperseded -eq $FALSE -and $_.LocalizedDisplayName -notlike "*Security Only*"}} | `
                               Select ArticleID,LocalizedDisplayName,NumMissing,NumPresent,IsSuperseded,IsExpired -Unique | Sort-Object -Descending -Property NumMissing

As you can see in the example, we take the results from a standard SQL report, transform them into an array as $array, and simultaneously pipe them to Get-CMSoftwareUpdate for further processing as $arrayresult.

Logging Results

This function has standard capabilities to output its status a log file which is fully compatible with CMTrace.exe

PowerShell Function


<#
.SYNOPSIS
  Generates an array of a SCCM SQL Report
.NOTES
  Version:        1.0
  Author:         David Maiolo
  Creation Date:  2018-01-11
  Purpose/Change: Initial script development

#>

#---------------------------------------------------------[Initialisations]--------------------------------------------------------
 function Get-DMGSCCMSQLReport
 {
    param(
        [Parameter(Position=0,Mandatory=$true,ValueFromPipeline=$true)]
        $inputParams,
        [Parameter(Position=1,Mandatory=$true,ValueFromPipeline=$true)]
        $ReportServerUrl,
        [Parameter(Position=2,Mandatory=$true,ValueFromPipeline=$true)]
        $ReportPath,
        [Parameter(Position=3,Mandatory=$true,ValueFromPipeline=$true)]
        [String]$ProviderMachineName,
        [Parameter(Position=4,Mandatory=$true,ValueFromPipeline=$true)]
        [String]$Sitecode
    )


    #Set Logging Varibales
    $invocation = (Get-Variable MyInvocation -Scope 1).Value
    $ScriptDirectory = Split-Path $invocation.MyCommand.Path
    $ScriptName = ($MyInvocation.MyCommand.Name)+".psm1"
    $LogName = ($MyInvocation.MyCommand.Name)+".log"
    $LogFile = Join-Path $ScriptDirectory $LogName
    $ScriptFile = Join-Path $ScriptDirectory $ScriptName
    $ReportDate = Get-Date 

    #Set CSV Output Variables
    $CSVOutputName = ($MyInvocation.MyCommand.Name)+".csv"
    $CSVOutputFile = Join-Path $ScriptDirectory $CSVOutputName

    #Log Start of Function
    New-DMGCMTraceLog -message ("Starting Logging for $ScriptName") -component "Main()" -type 1 -ScriptName $ScriptName -LogFile $LogFile -ScriptFile $ScriptFile

    #Connect to SCCM
    # Import the ConfigurationManager.psd1 module
    $module = "$($ENV:SMS_ADMIN_UI_PATH)\..\ConfigurationManager.psd1"
    if((Get-Module ConfigurationManager) -eq $null) {
        Write-Host Importing $module ...
        Import-Module $module -Force
    }

    # Connect to the site's drive if it is not already present
    if((Get-PSDrive -Name $SiteCode -PSProvider CMSite -ErrorAction SilentlyContinue) -eq $null) {
        New-PSDrive -Name $SiteCode -PSProvider CMSite -Root $ProviderMachineName @initParams
    }

    # Set the current location to be the site code.
    Set-Location "$($SiteCode):\"


    # add assembly 
    Add-Type -AssemblyName "Microsoft.ReportViewer.WinForms, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"

    # if the path exists, will error silently and continue 
    New-Item -ItemType Directory -Path $baseFolder -ErrorAction SilentlyContinue | Out-Null

    $rv = New-Object Microsoft.Reporting.WinForms.ReportViewer

    # report Server Properties 
    $rv.ServerReport.ReportServerUrl = $ReportServerUrl
    $rv.ServerReport.ReportPath = $ReportPath
    $rv.ProcessingMode = "Remote"

    # set up report parameters 
    $params = $null

    #create an array based on how many incoming parameters 
    $params = New-Object 'Microsoft.Reporting.WinForms.ReportParameter[]' $inputParams.Count

    $i = 0 
    foreach ($p in $inputParams.GetEnumerator()) 
    { 
        $params[$i] = New-Object Microsoft.Reporting.WinForms.ReportParameter($p.Name, $p.Value, $true) 
        $i++ 
    } 

    # set the parameters 
     Write-Host "Setting Parameters..."
    $rv.ServerReport.SetParameters($params) 
    $rv.ShowParameterPrompts = $false 
    $rv.RefreshReport() 
    $rv.ServerReport.Refresh()

    Write-Host "The Parameters Were Applied..."

    # set rendering parameters 
    $mimeType = $null 
    $encoding = $null 
    $extension = $null 
    $streamids = $null 
    $warnings = $null

    # render the SSRS report in CSV 
    $bytes = $null 
    $bytes = $rv.ServerReport.Render("CSV", 
    $null, 
    [ref] $mimeType, 
    [ref] $encoding, 
    [ref] $extension, 
    [ref] $streamids, 
    [ref] $warnings)

    Set-Location C:\

    # save the report to a file
    $fileStream = New-Object System.IO.FileStream($CSVOutputFile, [System.IO.FileMode]::OpenOrCreate) 
    $fileStream.Write($bytes, 0, $bytes.Length) 
    $fileStream.Close()

    New-DMGCMTraceLog -message ("File Exported`: $CSVOutputFile") -component "Main()" -type 1 -ScriptName $ScriptName -LogFile $LogFile -ScriptFile $ScriptFile

    # Re-import file and remove first three lines

    get-content -LiteralPath $CSVOutputFile|
        select -Skip 3 |
        set-content "$CSVOutputFile-temp"
    move "$CSVOutputFile-temp" $CSVOutputFile -Force
    #Log Sent Email
    New-DMGCMTraceLog -message ("File Imported (First 3 Lines Removed)`: $CSVOutputFile") -component "Main()" -type 1 -ScriptName $ScriptName -LogFile $LogFile -ScriptFile $ScriptFile

    $Finalvalues = Import-CSV -LiteralPath $CSVOutputFile

    #Log End Of Function
    New-DMGCMTraceLog -message ("End Logging for $ScriptName") -component "Main()" -type 1 -ScriptName $ScriptName -LogFile $LogFile -ScriptFile $ScriptFile

    return $Finalvalues

}

Leave a Comment

Your email address will not be published.