Overview

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

Workflow

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

Software Updates – Updates required but not deployed

Simply running the report gives you an indication of what variables are required to run this report. When fed into this 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" #Include your report path here - this is a sample only

#Create Array Of Data To Display in Report.
$OutputArrays = @()
$ProviderMachineName = "sccmsqlrserver.corp.corporation.com" #enter your sccm sql server here
$Sitecode = "DGM" #enter your site code here

Set-Location $Sitecode":"

#Array1
$inputParams = @{
    "CollID"="DGM00084"; #These are sample values
    "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

}