Ivanti DSM Export APM Patch Management Data into Excel

script.ps1

#Install-Module ImportExcel
#Connection Options
param
(
    [string]$argServer = '',
    #localhost:8090

    [string]$argUser = '',
    #domain\username

    [string]$argPassword = '',
    #Password123

    [string]$context = "emdb:\rootDSE\Managed Users & Computers\*"
)

$subRoutineFlag = 0; #Default = 0 (Change only for debugging)

#Prepare PS to Use HEAT DSM
import-module psx7 -DisableNameChecking

#Create global Authentification
$Server = "\\$argServer";
$Username = $argUser;
$global:path = $context
$password = $argPassword | ConvertTo-SecureString -asPlainText -Force
$credential = New-Object System.Management.Automation.PSCredential($Username, $password)

Write-Host "Using context: " + $context

#Connect to HEAT DSM
new-psdrive -name emdb -root $Server -scope script -psprovider blsemdb -Credential $credential
emdb:

$data = @"
Computer,Patch,Compliance,FoundDate,FixDate
"@

$contextTokens = $context.Split('\\')

$token = $contextTokens[$contextTokens.Length-2].Replace('&','').Replace(' ','_').Replace('(','').Replace(')','').Replace('.','')

Write-Host $token

$fileName = "C:\temp\" + (Get-Date).Year.ToString() + "_" + (Get-Date).Month.ToString() + "_" + (Get-Date).Day.ToString() + "_"  + (Get-Date).Hour.ToString()+ (Get-Date).Minute.ToString() + (Get-Date).Second.ToString() + "_" + $token +".xlsx"

Write-Host $fileName

if ($subRoutineFlag -eq 0)
{
    $computers = Get-EmdbComputer $context -Recurse
    #$computers.GetType()

    foreach ($machine in $computers)
    {
        $issues = Get-EmdbComputer $context -Name $machine.Name -Recurse | ForEach-Object { $_.GetAssociations("ComputerMissingPatch") } | Add-EmdbRelatedItem -PassThru

        foreach ($secIssue in $issues)
        {
            Write-Host $secIssue.GetTargetObject().Name " - " $secIssue.Status
            $data += [System.Environment]::NewLine + $machine.Name + "," + $secIssue.GetTargetObject().Name.Replace(',',' ') + "," + $secIssue.Status + "," + $secIssue.DetectDate + "," + $secIssue.FixDate

        }
    }
    $dataList = $data | ConvertFrom-CSV

    $dataList | Export-Excel $fileName -Show -AutoSize -IncludePivotTable -PivotRows Compliance -PivotData @{ Computer = "count" } -IncludePivotChart -ChartType PieExploded3D

    $subRoutineFlag = 1;
}