nedelja, 22. november 2015

Powershell save SQL data into Excel


When talking which queries are used  a lof of them are checcking statistics, execution as a job and saving data in table. After test is completed all this data is then converted in:
 - html
 - excel
 - svn.

HTML is very usefull, easy to open, but do not give you a possibility to sort data. .svn pattern gives you a posibility to open file in kibana and sort, compare data, etc. Excel great is after testing is completed. It is to sort data compare with different version of application, creating graphs,etc. In this section will be described export to excel.


In our case will be used statistics of most expensive stored procedures. There a over 100 queries to analyze statistics, latches, blocking processes, etc. SQL:

    SELECT TOP 1000 d.object_id
         ,OBJECT_NAME(object_id, database_id) 
    FROM sys.dm_exec_procedure_stats AS d
    WHERE db_Name(d.database_id)='yourdatabase'

This query search for all executed stored procedures in the test. Most important  is information is average elapsed time, which identify, which tables are not optimized for searching, inserting, deleting
Additional information about returned data from query will not be explained.

Connect to SQL

 Powershell is script language which has a lot common with .NET framework. On internet there is a lot help available how to connect to Mssql and execute query. For connection to MSSQL server is used connection string. Used will be used SQLClient class in the System.Data namespace. First will be instantiate a new SqlConnection and SqlCommand object.:

    $connectionString = "Server=$databaseServer;uid=$user; pwd=$pwd;Database=$database;Integrated Security=False;"
    $connection = New-Object System.Data.SqlClient.SqlConnection
    $connection.ConnectionString = $connectionString

   $command = $connection.CreateCommand()   
    $command.CommandText  = "SELECT TOP 1000 d.object_id
        , OBJECT_NAME(object_id, database_id) 
    FROM sys.dm_exec_procedure_stats AS d
    Where db_Name(d.database_id)='Database'
    ORDER BY  d.last_elapsed_time DESC ; "


In some cases SQL execution takes more than expected, default timeout is 30 seconds to extend timeout use next command:
    $command.CommandTimeout = 300         

To create an in-memory table that can easily be manipulated is used SqlDataAdapte. The SqlDataAdapter will fill a variable $DataSet with the data from given sql statement from SQL.     
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $command
    $DataSet = New-Object System.Data.DataSet


Now we have extracted data from sql saved in the array table, it's time to prepare excel file. If folder does not exists then should be created:
    $fileXls ="$global:nasDepot\SQLReports\SPExpensiveQueries.xls"
    if (-Not (Test-Path "
$global:nasDepot\SQLReports") )
        New-Item "$ReportFileFolder\SqlReports" -ItemType directory > $null

   To create an excel file is used object
OfficeOpenXml.ExcelPackage with excel filename. If file does not exists then header in table is created starting with row in excel 1. New worksheet ExpensiveSP is created with method $ExcelPackage.Workbook.Worksheets.Add("ExpensiveSP").
Updating cell in the sheet is used method Cells with parameters: "$column$row". Header can be populated with different approach.

    $intRow = 1
    $ExcelPackage = New-Object OfficeOpenXml.ExcelPackage  $fileXls
    if (-Not (Test-Path $fileXls) )
        $Worksheet = $ExcelPackage.Workbook.Worksheets.Add("ExpensiveSP")
        $TableStyle = [OfficeOpenXml.Table.TableStyles]::Medium1
        $Worksheet.Cells["A$intRow"].Value="INSTANCE NAME:"
        $Worksheet.Cells["B$intRow"].Value= $connectionString
        $intRow ++
        $Worksheet.Cells["A$intRow"].Value="Server Name:"
        $Worksheet.Cells["B$intRow"].Value= $dataSource
        $intRow ++
        $intRow ++
        $Worksheet.Cells["A$intRow"].Value = "ObjectId"
        $Worksheet.Cells["B$intRow"].Value = "DatabaseId"
        $Worksheet.Cells["C$intRow"].Value = "Object Name"
        $Worksheet.Cells["D$intRow"].Value = "DateTime"
        $Worksheet.Cells["E$intRow"].Value = "Averega response time (microsecond)"
        $Worksheet.Cells["F$intRow"].Value = "Execution count"
        $Worksheet.Cells["G$intRow"].Value = "TestName"
        $Worksheet.Cells["H$intRow"].Value = "Version"
        $Worksheet.Cells["I$intRow"].Value = "Comment"
        $intRow ++
        $Worksheet = $ExcelPackage.Workbook.Worksheets.Item("ExpensiveSP")
        $dimAddress = $Worksheet.Dimension.Address

When header was completed now data from variable $DataSet  saved in excel. First should be identified end row to continue adding data from $DataSet that values in excel will not be updated. 
    for($i=0;$i -le $DataSet.Tables[0].Rows.Count-1;$i++)
        $a = $($DataSet.Tables[0].Rows[$i][0])  
            #write-host "value is : $i $a $($DataSet.Tables[0].Rows[$i][1])$($DataSet.Tables[0].Rows[$i][2])"
            $Worksheet.Cells["A$intRow"].Value = $($DataSet.Tables[0].Rows[$i][0])
            $Worksheet.Cells["B$intRow"].Value = "$($DataSet.Tables[0].Rows[$i][1])"
            $Worksheet.Cells["C$intRow"].Value = $($DataSet.Tables[0].Rows[$i][2])
            $Worksheet.Cells["D$intRow"].Value = "$($DataSet.Tables[0].Rows[$i][3])"
            $Worksheet.Cells["E$intRow"].Value =$($DataSet.Tables[0].Rows[$i][4])
            $Worksheet.Cells["F$intRow"].Value = $($DataSet.Tables[0].Rows[$i][5])
            $Worksheet.Cells["G$intRow"].Value = "$global:TestName $global:TestCaseName $global:TestScenario"
            $Worksheet.Cells["H$intRow"].Value = $global:Version
            $Worksheet.Cells["I$intRow"].Value = "$global:additional_comment"


After excel worksheet is populated with data from sql statement now table should be formatted and saved.  



Powershell is gives a great opportunity to process data and show them in reports. This blog show how data is retrieved from SQL and saved into Excel. 


sreda, 11. november 2015

Powershell reading XML

Xml document

Content of XML file (part of the XML file) is used in examples below:
<!-- file.xml -->
             <authentication login="no">
        <excelpathdll>e:\aps\EPPlus 4.0.4\EPPlus.dll</excelpathdll>
        <server login="true">performanceapp1</server>
        <server login="true">performanceapp2</server>
        <server login="true">performanceapp3</server>
        <server login="false">client001</server>
        <server login="false">client002</server>
        <server login="false">client003</server>
        <server login="true">performancebo1</server>
        <server login="true">performancebo2</server>
        <server login="true">performancedb1</server>
        <server login="true">performancedb2</server>
            <performanceapp1>cmlt General Counter</performanceapp1>
            <performanceapp2>cmlt General Counter</performanceapp2>
            <performanceapp3>cmlt General Counter</performanceapp3>
            <performancebo1>cmlt General Counter</performancebo1>
            <performancebo2>cmlt General Counter</performancebo2>
            <client001>cmlt General Counter</client001>
            <client002>cmlt General Counter</client002>
            <client003>cmlt General Counter</client00>

            <server id="
performanceapp1" run="local">
                <counter threshold="DotNet.xml">cmlt General Counter</counter>     

        <server id="
performanceapp2" run="local">
            <counter threshold="DotNet.xml">cmlt General Counter</counter>        </server>


Read XML

For creating script is used Powershell ISE, because it uses Intelisense, which is not good as in visual studio, but very helpful to find searched method .

XML file is saved in current folder. Load complete XML file into one variable:

$configpath = "."
$configfile = [xml] (Get-Content $configpath\config.xml)


Read value of one element from variable $configfile with the Xpath "config/settings/depot/depotDirectory". To get text of the element is used method ".InnerText":

$depotDirectory  = $configfile.SelectSingleNode("config/settings/depot/depotDirectory").InnerText


To read attribute of specific element is used method "GetAtribute". First element is read with method 'SelectSingleNode' and saved in variable '$authenticationnode'. Vairable '$authenticationnode' is used methon 'GetAttribute' to get value of the attribute:

$authenticationnode  = $configfile.SelectSingleNode("/config/settings/depot/authentication")
$global:login = $authenticationnode.GetAttribute("login")


Sometimes we need to read a repetitive elements in XML. In out case we are going to read remote computes on which we will clean log, make IIS reset, to get version of application under test. To read we use next command:

$arrayNodes = @($configfile.config.clean.server.InnerText)

We get array of child elements of parent element server. Iteration is done with 'foreach' element in node, as it is shown below:
foreach($xmlnode in $xmlnodes)
     $login = $xmlnode.GetAttribute("login").ToLower()

Or you can use:

Select-Xml -Xml $xdoc -Xpath "//compile/server[@id=`'$hostForCompile`']/counter" `
| foreach { $thresHolds  += $_.node.threshold ; $counterNames += $_.node.InnerXML}


 Using XML document in powershell is very easy. Once you reach how to read XML file it make sense to drive complete powershell automation with XML. In my project XML is used for:
- powershell Test runner script is driven by XML
- to connect to remote computers
- defines whch perfomance countesd must be started on remote computes
- copy file from one location to another
- to create PAL reports from performance counters
- to create a word document
- read file and seved it in to variable

With powershell nothing is imposibile.