SAP PI ESR Simple Query Documentation using Powershell

This post demonstrate a way to semi-auto to extract ESR objects into one big XML document, then programmatically parse the xml and create ESR object listing documentation in excel format. This is an easy way to have good overview of all relevant ESR objects in one excel sheet.
 
The steps:
1. Go to http://<<hostname:>><<port>>/rep/support/SimpleQuery
Choose “all software components”, tick “Show request XML” and “Show response XML”. Response XML will be used as source to generate excel documentation.
 
2. Select all result attributes. The script coded in such way that only work properly if all fields is selected.

 
3. Choose “Operation Mapping”. This simple query will query all other ESR objects that have relationship with the Operation mapping listed. Click “Refresh depended values”.

 
4. An huge HTML table will generated with many table and columns. However, all the data in html table is actually come from below “Response XML” at the bottom of response html page. Copy all response XML, saved to a file. Example “ESR.xml”.

5. Execute below Powershell code in Powershell editor. Change the “ESR.xml” to the path you desired. Default is “C:\TEST\ESR.xml”.

cls
$xml = Get-Content "C:\TEST\ESR.xml"
$xdoc = new-object System.Xml.XmlDocument
$xdoc.LoadXml($xml)
$tblItem = @()
foreach($r in $xdoc.queryResult.matrix.r)
{
    $SoftwareComponentVersion = $r.c[6].qref.ref.vc.Attributes["caption"].'#text'
    $Namespace = $r.c[10].simple.strg
    $OperationMapping = $r.c[9].simple.strg
    $subItem_Count = $r.c[18].array.ChildNodes.Count
    for($i = 0 ; $i -lt $subItem_Count ; $i++)
    {
        $ObjectType = $r.c[19].array.simple[$i].strg
        $ObjectSWCV = $r.c[18].array.ref[$i].vc.Attributes["caption"].'#text'
        $ObjectNamespace = $r.c[18].array.ref[$i].key.elem[1]
        $ObjectName = $r.c[18].array.ref[$i].key.elem[0]
        $ObjectSequence = $r.c[20].array.simple[$i].int
        if($ObjectType -eq "OUTBOUND_IF")
        {
            $ObjectType = "Source Interface"
        }
        elseif($ObjectType -eq "INBOUND_IF")
        {
            $ObjectType = "Target Interface"
        }
        elseif($ObjectType -eq "REQUEST_OUT_MES")
        {
            $ObjectType = "Source Message Type"
        }
        elseif($ObjectType -eq "REQUEST_IN_MES")
        {
            $ObjectType = "Target Message Type"
        }
        elseif($ObjectType -eq "REQUEST_TRAFO")
        {
            $ObjectType = "Message Mapping"
        }
        else
        {
            continue
        }
        $item = New-Object System.Object
        $item | Add-Member -MemberType NoteProperty -Name "Interface SWCV" -Value $SoftwareComponentVersion
        $item | Add-Member -MemberType NoteProperty -Name "Interface Namespace" -Value $Namespace
        $item | Add-Member -MemberType NoteProperty -Name "Interface Operation Mapping" -Value $OperationMapping
        $item | Add-Member -MemberType NoteProperty -Name "Object Type" -Value $ObjectType
        $item | Add-Member -MemberType NoteProperty -Name "Object SWCV" -Value $ObjectSWCV
        $item | Add-Member -MemberType NoteProperty -Name "Object Namespace" -Value $ObjectNamespace
        $item | Add-Member -MemberType NoteProperty -Name "Object Name" -Value $ObjectName
        $item | Add-Member -MemberType NoteProperty -Name "Object Sequence" -Value $ObjectSequence
        $tblItem += $item
    }
}
$tblItem | Sort-Object "Interface SWCV", "Interface Namespace", "Interface Operation Mapping", "Object Type", "Object Sequence" | Export-CSV "C:\TEST\Output.csv"

 
6. The output of script is CSV file. Open the csv file using excel, create pivot table based on all columns and arrange it the way you like it. The end result is an excel documentation contain all ESR object relevant to each Operation Mapping.
Keep on learning! 🙂