Jmeter - Creating word reports in Powershell

Introduction

When test are started in Jmeter is nice to have automatically generated report from all performance counters from different servers, some SQL data, Jmeter graphs in one word document.  Most of these functionlities are described in this blog.
Described will be steps how to create word document with from performance results artifacts.

Performance report artiacts

Each test  produce a own folder by date and time with next values for each involved sever. With PAL needs to be generated collected performance counter of each server under test ( search in this blog), and Jmter pictures are generated ( also find in this blog) and other artifacts needed for reporting.

Word Template document

Word template is basic document it described how will document look likes. Where picture will be inserted, text will be replaced, tables will be inserted, and some things need to be manually added and document overview
Table of content of document for Jmeter:



Details in word document for Jmeter are inserted pictures for (picture below):


Table of content of performance counters of application servers:


Detailed document  for application server or database server but with different performance counters :

XML Document for replacing text with artifacts in word document 

XML defines which text in the word document will be replaced text, pictures, added tables.
XML for replacing text with Jmter Graph:
        <jmeter>              
            <bookmark picturename="TransactionsPerSecond.png" sizepercent="100"/>      
            <bookmark picturename="HitsPerSecond.png" sizepercent="100"/>
            <bookmark picturename="ResponseTimesOverTime.png" sizepercent="100"/>
            <bookmark picturename="ResponseCodesPerSecond.png" sizepercent="100"/>
            <bookmark picturename="TransactionsPerSecond_EWS_Bet.png" sizepercent="100"/>
            <bookmark picturename="ResponseTimesOverTime_EWS.png" sizepercent="100"/>      
            <bookmark picturename="ResponseCodesPerSecond_EWS.png" sizepercent="100"/>
            <bookmark picturename="TransactionsPerSecond_PP.png" sizepercent="100"/>
            <bookmark picturename="TransactionsPerSecond_PP_Login.png" sizepercent="100"/>
            <bookmark picturename="TransactionsPerSecond_PP_Registration.png" sizepercent="100"/>          
            <bookmark picturename="ResponseTimesOverTime_PP.png" sizepercent="100"/>
            <bookmark picturename="ResponseTimesOverTime_PP_Login.png" sizepercent="100"/>
            <bookmark picturename="ResponseTimesOverTime_PP_Registration.png" sizepercent="100"/>
            <bookmark picturename="TransactionsPerSecond_BO.png" sizepercent="100"/>                      
            <bookmark picturename="ResponseTimesOverTime_BO.png" sizepercent="100"/>                                  
        </jmeter>

 This all pictures are saved under these names in performance result folder. Each test produce each own folder. Attribute 'picturname' string in word document and replaced with picture on defined path of test.

Replacing  values of specific variables put into word document:
        <variables>
            <variable id="$nasFileFolder" replace="[ReportFolder]"/>
            <variable id="$JMeterMaxThread" replace="[JmeterThrads]"/>
            <variable id="$JmeterTestDuration" replace="[JmeterTestDuration]"/>
            <variable id="$JMeterAddNoThreads" replace="[JMeterAddNoThreads]"/>
            <variable id="$JMeterAddThreadsEverySeconds" replace="[JMeterAddThreadsEverySeconds]"/>
            <variable id="$JMeterHoldLoadInSeconds" replace="[JmeterLoad]"/>
            <variable id="$JMeterFinalyStopNoThreads" replace="[JmeterRumpdownPlayers]"/>           
            <variable id="$JMeterFinalyStopThreadsEverySeconds" replace="[JmeterEverySecondDown]"/>          
        </variables>    

In word document are powershell variables mapped with value in attribute 'replace' in word template.

XML show how is mapping done in application, database servers. Searched test with serverid+picturename are seached in word document and replaced with picture in specific folder of app server.
<server id="perfcoreApp1">
            <bookmark palfolder="PALDotNet.xml" picturename="Process_ID_Process0.png" sizepercent="100" />          
            <bookmark palfolder="PALDotNet.xml" picturename="NET_CLR_Memory_Num_Bytes_in_all_Heaps0.png" sizepercent="100"/>
            <bookmark palfolder="PALDotNet.xml" picturename="NET_CLR_Memory_Num_Bytes_in_all_Heaps1.png" sizepercent="100"/>
            <bookmark palfolder="PALDotNet.xml" picturename="Process_Thread_Count0.png" sizepercent="100"/>
            <bookmark palfolder="PALDotNet.xml" picturename="Process_Percent_Processor_Time0.png" sizepercent="100"/>
            <bookmark palfolder="PALDotNet.xml" picturename="Processor_Percent_Processor_Time0.png" sizepercent="100"/>
            <bookmark palfolder="PALSystemOverview.xml" picturename="Network_Interface_Bytes_Total_sec0.png|Network_Interface_Bytes_Total__ms_0.png" sizepercent="100"/>
            <bookmark palfolder="PALSystemOverview.xml" picturename="PhysicalDisk_Avg_Disk_sec_Write0.png|PhysicalDisk_Avg_Disk__ms_Write0.png" sizepercent="100"/>
            <bookmark palfolder="PALIIS.xml" picturename="ASPNET_v4030319_Request_Execution_Time0.png" sizepercent="100"/>
        </server>

Powershell to create a word document with graph, and other artifacts from collected data

Powershell is a great  scripting language with no limits. It is easy to use and a lot of support can be found on google.

At first we need to load word document:
$msWord = New-Object -Com Word.Application
$wordDoc = $msWord.Documents.Open("$worddocumentTemplate")
$wordDoc.Activate()
Populate variable with file context defined in XML

Read XML file with mapping with word  template:
 $configpath = "."
#end if
$configfile = [xml] (Get-Content $configpath\config.xml)

Read a file and replace with searched text in template:

$files  = $configfile.SelectNodes("config/document/files/file")
$hlog.WritePSInfo($files)
$hlog.WritePSInfo("START Populate fiels ")          
foreach($file in $files)
{
    $filename = $file.GetAttribute("file")
    $replace = $file.GetAttribute("replace")
    If (Test-Path $nasFileFolder$filename){
        $fileContent = get-Content $nasFileFolder$filename | Out-String
        $msWord.Selection.Find.Execute($replace, $False, $True, $False, $False, $False, $True,  1, $False, $fileContent, 2 )                          
    }
    else
    {
    
    }
}


This par of powershell code describes how to read XML and search for the text in word template and replace with picture:

$bookmarks  = $configfile.SelectNodes("config/document/jmeter/bookmark")
foreach($bookmark in $bookmarks)    {
            try
           {
            $hlog.WritePSInfo("Bookmark exists")   
            $picturenameelement = $bookmark.GetAttribute("picturename")
           
            $PictureFile = $Dir | where {$_.Name -eq $picturenameelement}
                ForEach ($file in $PictureFile) {
                    $insertfile = $file.FullName
                 }
               
            if ($msWord.Selection.Find.Text = $picturenameelement)
            {
                 if ($msWord.Selection.Find.Execute())
                 {            
                    $objSelection = $msWord.selection    
                    $objShape = $objSelection.InlineShapes.AddPicture($insertfile)                               
                
                }
            }
        }
        catch [system.exception]
        {
        
        }           
      }

This part of the code replace value from powershell variable and put it into word template:

variables = $configfile.SelectNodes("config/document/variables/variable")
foreach($variable in $variables)
{
     $id = $variable.GetAttribute("id")
     $replace = $variable.GetAttribute("replace")
          
    try
    {               
       $msWord.Selection.Find.Execute($replace, $False, $True, $False, $False, $False, $True,  1, $False, $ExecutionContext.InvokeCommand.ExpandString($id), 2 )
    }
     catch [system.exception]
    {
     
    }
}

Section of powershell code replace application servers performance counters pictures:
$xmlnodes = $configfile.SelectNodes("config/document/server")

foreach($xmlnode in $xmlnodes)
{
    $hostname = $($xmlnode.id)    
    $bookmarks = $xmlnode.ChildNodes
    $Dir = get-childitem $folder -recurse
    $hlog.WritePSInfo("START Populate pictures for $hostname")
   
    foreach($bookmark in $bookmarks)
      {        
           $palfolderelement = $bookmark.GetAttribute("palfolder").ToLower()
           $picturenameelement = $bookmark.GetAttribute("picturename").ToLower()
       
           $insertfile =" "
            $i=0
           $picturenameelement.split("|") | ForEach {
               $pictureelem = $_
               if ($i -eq 0 )
               {
                $searchpicture = $_
               }
               $PictureFile = $Dir | where {$_.Name -eq $pictureelem}
               ForEach ($file in $PictureFile) {
                    $insertfile = $file.FullName
                    break
                 }
                $i++
            }
            if($insertfile -ne " "){
            if ($msWord.Selection.Find.Text = "$($xmlnode.id)$searchpicture")
            {
                 if ($msWord.Selection.Find.Execute())
                   {                   
                    $objSelection = $msWord.selection
                    try
                    {
                    $objShape = $objSelection.InlineShapes.AddPicture($insertfile)                               
                    }
                    catch [system.exception]
                    {
                    }

                }
            }
            }
      }   
}

Conclusion

After performance framework prepare environment to run tests, execute test an collect all artifacts need for reporting then is word document generated. It's a simple way to create great document after each test, to get result faster and efficient. IF you check also other blogs you get a picture how is performance powershell framework working. It is a simple but powerfull.
Generated document is attached.