Home Cireson Uploads

Software Asset Reconciliation

IT MonkeyIT Monkey O.G.
edited May 2016 in Cireson Uploads
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:
Sign In or Register to comment.