Getting information from PAL XML files with PowerShell

Hi All,

Marc Dudok here again with a quick little post before the long weekend is upon us.

I had a whole heap of PAL (https://pal.codeplex.com/) XML threshold files that I needed to share details of. In other words I needed to get them in more human readable format. (Mind you, I have a few colleagues that can read XML faster than I can read The Very Hungry Caterpillar...) If you haven't discovered PAL yet, go and check it out - but make sure you come back...

This post should help you if you need to create some documentation on what counters you are monitoring (you are monitoring performance, right?), or if you have other XML documents which you need to read and output to CSV.

You can see below what a PAL XML file looks like. Not so nice to read. However, there is some really interesting data in there. I can see the name and source of the counter, a description and the threshold.

In order to turn get out the data I needed, I turned to PowerShell. Now, this probably isn't necessarily the "Best" way to do this in PowerShell - but it works. With the Get-Content cmdlet I can read in some XML and PowerShell will store it for me in an object with every node in the XML becoming a property.

What the script is doing is, one by one reading all of the XMLs files contained within a folder. It creates a hashtable for each in which to store the data, enters the data into the hashtable and then spits it out a CSV into the same folder.

This script can be easily expanded to include other items you want to include from an XML document.
 

$files = get-childitem "C:\Lync Server 2013 PAL Threshold Files v1.0"

ForEach($item in $path){

    $output = $item.Directory.FullName + "\" + $item.BaseName + ".csv"

    $content = [xml](Get-Content -Path $item.FullName)

    $arr1 = @{}
    $arr1["Counter"] = @{}
    $arr1["Counter"]["Name"] = @{}
    $arr1["Counter"]["Threshold"] = @{}
    $arr1["Counter"]["Description"] = @{}

        $content.PAL.Analysis | ForEach-Object{
        $arr1["Counter"]["Name"] = $_.NAME
        $arr1["Counter"]["Threshold"] = $_.Threshold.Name
        $arr1["Counter"]["Description"] = $_.DESCRIPTION.'#cdata-section'

        $arr1.getEnumerator() | ForEach-Object{New-Object -TypeName PSObject -Property $_.Value} | Select-Object -Property Name,Description,Threshold | Export-Csv $output -Append

        }
}

 and...Ta-da!! Here is my converted XML looking nice as pretty in Excel with the relevant headers. Our resident Excel PFE would be proud.

Until next time.

Marc 'I don't want to have to copy and paste all that data' Dudok.

Premier Field Engineer - Microsoft