Software Asset Reconciliation

IT Monkey

Reconciles Assets by exporting to separate CSV files using powershell and Cireson Asset Management.

import-module smlets

$itemsWithAssets = @()
$itemsWithoutAssets = @()
$outputFilePath = "C:\temp\"
$outputFileName_WithoutAssets = "itemsWithoutAssets.csv"
$outputFileName_WithAssets = "itemsWithAssets.csv"
$saveAsXLSX = $true

$relatedCiresonAssetDisplayName = @()
$relatedCiresonAssetVersion = @()
$relatedCiresonAssetSoftwarePattern = @()

Function fn-output-as-xml
{
    Param(
        $inputCSV
        )

    $newXLSX = $inputCSV.Split(".")[0]

    $xl = new-object -comobject excel.application
    $xl.visible = $false
    $Workbook = $xl.workbooks.open($inputCSV)
    $Worksheets = $Workbooks.worksheets

    $Workbook.SaveAs($newXLSX,51)
    $Workbook.Saved = $True

    $xl.Quit()

    $inputCSV = $null
    $xl =  $null
}


$allSoftwareItemsFromSCCM = get-scsmobject -class (Get-SCsmclass -name "System.SoftwareItem")

$relatedSoftwareAssetRelationship_guid = "78d316fc-5f12-c4a0-fefa-973759594e87"
$relatedSoftwareAssetRelationship_obj = Get-SCSMRelationshipClass -ID $relatedSoftwareAssetRelationship_guid

#Just for reference - how to see what objects are related to an object
#Get-SCSMRelationshipObject -ByTarget <software item object that we *know* is associated so we can see the relationship>

foreach ($softwareItem in $allSoftwareItemsFromSCCM)
{
    $relatedCiresonAsset = Get-SCSMRelationshipObject -targetobject $softwareItem -TargetRelationship $relatedSoftwareAssetRelationship_obj

    if ($relatedCiresonAsset.count -ge 1)
    {
        foreach ($asset in $relatedCiresonAsset)
        {
                    $relatedCiresonAssetDisplayName += $relatedCiresonAsset.SourceObject.Displayname
                    $relatedCiresonAssetVersion += ($relatedCiresonAsset.sourceobject.values | ?{$_.type -like "Version"}).value
                    $relatedCiresonAssetSoftwarePattern += ($relatedCiresonAsset.sourceobject.values | ?{$_.type -like "SoftwarePattern"}).value
        }

        $relatedCiresonAssetDisplayName = ($relatedCiresonAssetDisplayName | select -Unique) -join ", "
        $relatedCiresonAssetVersion = ($relatedCiresonAssetVersion | select -Unique) -join ", "
        $relatedCiresonAssetSoftwarePattern = ($relatedCiresonAssetSoftwarePattern | select -Unique) -join ", "

        Add-Member -InputObject $softwareItem -NotePropertyName "RelatedCiresonSoftwareAssetDisplayName" -NotePropertyValue $relatedCiresonAssetDisplayName -Force
        Add-Member -InputObject $softwareItem -NotePropertyName "RelatedCiresonSoftwareAssetVersion" -NotePropertyValue $relatedCiresonAssetVersion -Force
        Add-Member -InputObject $softwareItem -NotePropertyName "RelatedCiresonSoftwareAssetSoftwarePattern" -NotePropertyValue $relatedCiresonAssetSoftwarePattern -Force

        $itemsWithAssets += $softwareItem
    }
    elseif ($relatedCiresonAsset.count -eq 0)
    {
        $itemsWithoutAssets += $softwareItem
    }

    $relatedCiresonAssetDisplayName = @()
    $relatedCiresonAssetVersion = @()
    $relatedCiresonAssetSoftwarePattern = @()

}

if (!$outputFilePath)
{
    mkdir $outputFilePath
}

$itemsWithoutAssets | select displayname, publisher, versionstring  | Export-Csv -Path ($outputFilePath + $outputFileName_WithoutAssets) -Encoding ascii -NoTypeInformation
$itemsWithAssets | select displayname, publisher, versionstring, RelatedCiresonSoftwareAssetDisplayName, RelatedCiresonSoftwareAssetVersion, RelatedCiresonSoftwareAssetSoftwarePattern  | Export-Csv -Path ($outputFilePath + $outputFileName_WithAssets) -Encoding ascii -NoTypeInformation

remove-module smlets

if ($saveAsXLSX)
{
    fn-output-as-xml -inputCSV ($outputFilePath + $outputFileName_WithoutAssets)
    fn-output-as-xml -inputCSV ($outputFilePath + $outputFileName_WithAssets)
}

Download the attached .zip file below: