Read, Filter,Save to SQL Table or XML…Sushi..Anyway FUN with SQL Server Profiler Trace Files in POWERSHELL


 

Technet Scripting Guys Link

Excuse me for English, is by google.

Requires Powershell V2.0

It was an annoying proceeding to be done. I had my trace files separated by folder, but as I check Enable File Rollover and it was raised many divisions of the same trace.

There were three shifts of DBAs. Each placed the need to trace your problem. Sometimes I also look to bring together various files needed to trace the events and different columns in the same. It was really hard to do this

Some of our servers were very busy and put the trace file for SQL Server table was negated by the overhead generated, so I’ve had to import to SQL Server later. A simple procedure for fn_trace_gettable to one file, but when you have multiple files becomes complicated to generate a uniform and repetitive script. And worse if I had to join the trace files in a single file ..

It was then that I decided to use powershell.

One of the parameters when I began to think of the solution was to be generated tables to the SQL Server  ($filetotable) There was no problem in setting this parameter .

But I wanted to centralize all the files in a table and as I said, these files could come with different events and columns.

This became a problem when I began to see as many lines of code and conditions that would have this function. Actually I was thinking procedural, as I did my functions in Visual Basic.

I forgot one of the main features of Powershell: Consistency.

Why would I do all this code, if the output of a cmdlet is an object and can be the input of the next cmdlet?

So..lets go the code and examples :

First the parameters. The functions works with 4 parameters being one (the first) mandatory.

    $TraceFileName = MANDATORY String Full SQL SERVER Trace File Path "C:\Temp\Profiler.trc" or "C:\temp\*.trc"

This parameter tells the trace file to be read. If you want more than one file, which as I said may be with different events and columns between them, specify that all *. trc files with the TRC will be read in path. If not only one file.

Ex C:\TEMP\TSQL.TRC or C:\TEMP\TEMP\ *.TRC

    $FileToTable = OPTIONAL Boolean Flag to insert all data into SQL tables, divided by .trc file

This parameter specifies if the files read will be sent to tables in SQL Server. Be set up different tables for each file, with the name "POWERSHELLTRACETABLE_" followed by the name of the trace. We will see below that this parameter is set to true, we need to pass the name of the Server and Database for these files. If not informed the default values will be used

    $ServerName = OPTIONAL Server Name String – If not especified and $FileToTable = true default server will be used

    $DatabaseName = OPTIONAL Database Name String – If not especified and $FileToTable = true TEMPDB will be used

 

Get-SQLProfiler Script Code

  1: Function Get-SQLProfiler () 
  2: <#
  3: ----------------------------------------------------------
  4: Load SQL SERVER Profiler Traces Files. (.trc)
  5: ----------------------------------------------------------
  6: Version 1.0
  7: Laerte Poltronieri Junior
  8: www.laertejuniordba.spaces.live.com
  9: 
 10: $TraceFileName   = MANDATORY String Full SQL SERVER Trace File Path  "C:\Temp\Profiler.trc" or "C:\temp\*.trc"   
 11: $FileToTable = OPTIONAL Boolean Flag to insert all data into SQL tables, divided by .trc file
 12: $ServerName = OPTIONAL Server Name String - If not especified and  $FileToTable = true default server will be used
 13: $DatabaseNe = OPTIONAL Database Name String - If not especified and  $FileToTable = true TEMPDB will be used
 14: 
 15: #>
 16: 
 17: 
 18: {
 19: 
 20:  [CmdletBinding()]
 21:  
 22:  PARAM(
 23:    [Parameter(Position=1,Mandatory=$true, ValueFromPipelineByPropertyName=$true,HelpMessage="SQL Server Profiler Trace File")]
 24:    [Alias("FullName")]
 25:    [ValidateScript({$_ -match ".TRC"})]
 26:    [String] $TraceFileName,
 27:    
 28:    [Parameter(Position=2,Mandatory=$false, ValueFromPipelineByPropertyName=$true,HelpMessage="Flag to insert into SQL Table. Default False")]
 29:    [Alias("InsertFile")]
 30:    [switch] $FileToTable = $false,
 31: 
 32: 
 33:    [Parameter(Position=4,Mandatory=$false, ValueFromPipelineByPropertyName=$true,HelpMessage="Server Name Default Localhost")]
 34:    [Alias("SvrName")]
 35:    [String] $ServerName = $env:COMPUTERNAME,
 36: 
 37:    [Parameter(Position=5,Mandatory=$false, ValueFromPipelineByPropertyName=$true,HelpMessage="Database Name Default TEMPDB")]
 38:    [Alias("DbName")]
 39:    [String] $DatabaseName = "TEMPDB"
 40: 
 41:    
 42:   ) 
 43:   
 44:  
 45:  begin
 46:     {
 47:  
 48:    
 49:   $verbosePreference="continue" 
 50:   if ($fileToTable -AND $servername -eq $env:COMPUTERNAME -and $DatabaseName -eq  "TEMPDB" ) { 
 51:    $msg = "Server and Database parameters are not informed default values will be used : Server " + $env:COMPUTERNAME + " Database : TEMPDB"
 52:    write-warning $msg
 53:   }  
 54: 
 55:   
 56:   [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfoExtended") | out-null     
 57:   [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | out-null     
 58:   [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null 
 59:  }
 60:  process 
 61:  {
 62: 
 63:   try 
 64:   {
 65:    
 66:    # Verify if was passed multples .trc
 67:    $MultipleFiles = ($TraceFileName.substring($TraceFileName.length  - 5, 5) -eq "*.trc")
 68:    
 69:    #Setup Final Result and line number
 70:    $LineNumber = 1
 71:    $FinalResult = @()
 72:    
 73:      
 74:    # Get All .trc files (one or various)
 75:    foreach ($TraceFilePath in Get-ChildItem $TraceFileName -ErrorAction Stop ) {
 76:    
 77:     try 
 78:     {
 79:    
 80:   
 81:      #get trace name to create table
 82:      $TraceFileNameTRC = ($TraceFilePath.PSChildName).trim()
 83:      $TraceFileNameTRC = $TraceFileNameTRC.Trim()
 84: 
 85:      [String] $TraceFilePathString = $TraceFilePath
 86: 
 87:      $TableName = "PowershellTraceTable_" + $TraceFileNameTRC.substring(0,$TraceFileNameTRC.length -4)
 88:      
 89:      $TraceFileReader = New-Object Microsoft.SqlServer.Management.Trace.TraceFile
 90:      $TraceFileReader.InitializeAsReader($TraceFilePathString) 
 91:      
 92:      if ($TraceFileReader.Read()-eq $true) 
 93:      {
 94:      
 95:       while ($TraceFileReader.Read())
 96:       {
 97:        
 98:       
 99:        $ObjectTrace = New-Object PSObject
100:        
101:       
102:        $ObjectTrace | add-member Noteproperty LineNumber   $LineNumber   
103:        $ObjectTrace | add-member Noteproperty TraceFile   $TraceFileNameTRC  
104: 
105:        
106:        $TotalFields = ($TraceFileReader.FieldCount) -1
107: 
108:        for($Count = 0;$Count -le $TotalFields;$Count++)
109:        {
110:         $FieldName = $TraceFileReader.GetName($Count)
111:         $FieldValue = $TraceFileReader.GetValue($TraceFileReader.GetOrdinal($FieldName))
112:         if ($FieldValue -eq $Null){ $FieldValue = ""}
113:          
114:         $ObjectTrace | add-member Noteproperty  $FieldName  $FieldValue
115:        }
116:        
117:        $FinalResult += $ObjectTrace
118:        $LineNumber ++ 
119:       
120:       }
121:       if ($FileToTable)
122:       {
123:       
124:        try {
125:         $SQLConnection = New-Object Microsoft.SqlServer.Management.Common.SqlConnectionInfo
126:         $SQLConnection.ServerName = $ServerName
127:         $SQLConnection.DatabaseName = $DatabaseName
128:         
129:         
130:         $TraceFileWriter = New-Object Microsoft.SqlServer.Management.Trace.TraceTable
131:         
132:                
133:         $TraceFileReader.InitializeAsReader($TraceFilePathString)
134:         $TraceFileWriter.InitializeAsWriter($TraceFileReader,$SQLConnection,$TableName) 
135:        
136:         while ( $TraceFileWriter.Write()) {}
137:        } 
138:        Catch {
139:          $msg = $error[0]
140:          write-warning $msg
141:        }  
142:        Finally {
143:         $TraceFileWriter.close()
144:        } 
145:        
146: 
147:       }
148: 
149:      } 
150:      
151:      
152:     } Catch {
153:         $msg = $error[0]
154:         write-warning $msg  
155:       } Finally {
156:         $TraceFileReader.close() 
157:     }
158:   
159:    } 
160: 
161:    Write-Output $FinalResult   
162:    
163:    
164:   } Catch {
165:      $msg = $error[0]
166:      write-warning $msg  
167:   } Finally {
168:       $TraceFileReader.Dispose 
169:       $TraceFileWriter.Dispose
170:       $SQLConnection.Dispose
171:   }
172:  }  
173:  
174: }
175: 
176: 

 

Download script : i

 

Let´s do some tests :

I created a function module with only this function and import it in my powershell profile. Only for our test be better !!!!

At powershell profiler type :

Import-Module -Name C:\temp\Get-SQLProfiler.psm1 -WarningAction SilentlyContinue  -ErrorAction Stop 

 

 

   Reading One Trace File and not Insert into SQL table.

We have this SQL Server Profiler trace C:\Temp\ProfilerTraces\TSQL.trc

2 copy

This File contains the events and columns of the SQL Server Profiler template TSQL.

Execute :

Get-SQLProfiler "C:\TEMP\ProfilerFiles\TSQL.TRC"

and the result :

3

The properties LineNumber and TraceFile will be in any resulting object of this function.

LineNumber is the line number (increment) and TraceFile is the name of the trace.

Remember, these properties match the columns in the trace file. Even if I’m merging files with different columns and events.

Let´s see only the properties LineNumber, TraceFile ,EventClass and TextData, filtering the lines smaller than or equal to 5

Get-SQLProfiler "C:\TEMP\ProfilerFiles\TSQL.TRC"  | whereobject {$_.LineNumber -le 5} | select LineNumber, TraceFile ,EventClass ,TextData | format-table

 

4

   Reading One Trace File and Insert into SQL table.

With the previous example we have the same result with $FiletoTable parameter $true, but means now we have a SQL Server Table.

As not informed as the server and database, a warning message was displayed that would be used default values :

6 copy

  1: Get-SQLProfiler "C:\TEMP\ProfilerFiles\TSQL.TRC" $true | where-object {$_.LineNumber -le 5} | select LineNumber, TraceFile ,EventClass ,TextData | format-table

And the SQL Server table  :

7

   Reading Multiple Trace Files and Insert into SQL table.

Let´s say we have a SQL Server Profiler trace file rollover. If you want to merge result simply enter the path that are the trace files.

  1: Get-SQLProfiler "C:\TEMP\ProfilerFiles\Rollover\*.TRC" $true |  select LineNumber, TraceFile ,EventClass ,TextData | format-table

The figure below shows when the file changes :

8

Will be created one SQL table for each trace file. If you want all trace files are placed in one table, save the result of the cmdlet in XML (or txt) to import soon after the invoke-sqlcmd. Below I have one example.

   Reading Multiple Trace Files With Different Events and Columns

We can also merge two files of traces with different columns. In the example I merge a file TSQL_SP with TSQL_Locks.

9

Well Laerte, but if I merge multiples files with different set up, how can I now the properties from Get-SQLProfiler ?

Simple, put a filter to one row or linenumber = 1

LINENUMBER and TRACEFILE Always be at Get-SQLProfiler.

  1: Get-SQLProfiler "C:\TEMP\ProfilerFiles\Merge\*.TRC" | where-object {$_.LineNumber -le 1} | format-table

10

Now just get the list, choose the fields and put in | select.

Remember that for multiple files with the option  $FiletoTable true, it creates several tables, one for each file.
Ha Laerte but I wanted to centralize everything in one. I confess that at first I wanted to do this, through parameter. But as I said, the powershell can  resolve this situation with two more lines of code and not have to "stifle" my function.

First I Create a table with XML colum. For tests I Put at tempdb :

USE [tempdb]
GO

/****** Object:  Table [dbo].[PowershellProfileTable_XML]    Script Date: 11/18/2009 02:27:39 ******/
If  EXISTS (Select * FROM sys.objects WHERE Object_id = Object_ID(N'[dbo].[PowershellProfileTable_XML]’) And type In (N’U’))
DROP TABLE [dbo].[PowershellProfileTable_XML]
GO

USE [tempdb]
GO

/****** Object:  Table [dbo].[PowershellProfileTable_XML]    Script Date: 11/18/2009 02:27:42 ******/
Set ANSI_NULLS On
GO

Set QUOTED_IDENTIFIER On
GO

CREATE TABLE [dbo].[PowershellProfileTable_XML](
[XML] [xml] NULL
) On [PRIMARY]

GO

 

and let´s wait Powershell to work. We just save  to XML …and we can choose de properties too!!!

(Get-SQLProfiler "C:\TEMP\*.TRC" | Select LIneNumber,EventClass,TextData,NTUserName,LoginName | ConvertTo-Xml -NoTypeInformation).save("c:\temp\teste.xml")
$XML= "insert into PowershellProfileTable_XML values (‘" + ((Get-Content "C:\Temp\teste.xml") -replace "’", "”")  + "’)" 
invoke-sqlcmd -ServerInstance $env:COMPUTERNAME -database "master" -query $xml

 

And with a simple Xquery we list  all data into XML .Let’s See ?

Select t2.Col1.value(‘(Property[@Name="LineNumber"]/text())[1]’, ‘int’) LineNumber,
    t2.Col1.value(‘(Property[@Name="EventClass"]/text())[1]’, ‘nvarchar(255)’) EventClass,
    t2.Col1.value(‘(Property[@Name="TextData"]/text())[1]’, ‘nvarchar(max)’) TextData,
    t2.Col1.value(‘(Property[@Name="NTUserName"]/text())[1]’, ‘nvarchar(255)’) NtUserName,
    t2.Col1.value(‘(Property[@Name="LoginName"]/text())[1]’, ‘nvarchar(255)’) LoginName
FROM dbo.testeXML
CROSS APPLY xml.nodes(‘/Objects/Object‘) As t2(Col1)
where t2.Col1.value(‘(Property[@Name="LoginName"]/text())[1]’, ‘nvarchar(255)’) Like ‘%laerte%’

 

 

 

11

I do not need to put more parameters to add a table…etc. The consistency characteristic of powershell does it for me.

I just need to pipe and save. We can Read, save to XML, filter by any property, import to SQL Table…and Sushi.

Thanks to my friend Shay Levy as always has time to share his great knowledge. Jeffrey Hicks too, with a lot of tips to mandatory parameters in functions ,my SQL SERVER DAY fellow Felipe Ferreira in XQuery and obvius Chad Miller, the master in powershell with SQL Server

opinions to optimize this code are welcome!

Well  It’s 2 o’clock in the morning and I hear a sound very good (tip from Thomas – doctordns) …

POWERSHELL ROCKS !!!!!

“Hey, comin’ out around the world be ready for a brand new beat
Oh, summer’s here and the time is right, oh, for dancing in thestreet
Dancing in Chicago (dancing in the street)
Down in New Orleans (dancing in the street)
In New York City (dancing in the street)

All we need is music, sweet music (sweet, sweet music)
They’ll be music everywhere (everywhere)
They’ll be laughing, singing, music swinging and dancing in thestreet

It doesn’t matter what you wear just as long as you are there
They’re dancing (dancing in the street) oh oh

This is an invitation across the nation the chance for folks tomeet
There’ll be swinging, swaying, music playing and dancing in thestreet

Philadelphia, PA now (dancing in the street)
Baltimore and D.C. (dancing in the street)
Can’t forget them other cities (dancing in the street)”

Dancing In The Street

Grateful Dead

  • New blog post: http://tinyurl.com/yhbzuzd – Read, Filter,Save SQL Table, XML.. Sushi.Finally FUN SQL Server Profiler Traces in #POWERSHELL

Follow me on Twitter

About Laerte Junior

Laerte Junior Laerte Junior is a SQL Server specialist and an active member of WW SQL Server and the Windows PowerShell community. He also is a huge Star Wars fan (yes, he has the Darth Vader´s Helmet with the voice changer). He has a passion for DC comics and living the simple life. "May The Force be with all of us"
This entry was posted in Powershell. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s