Add to Technorati Favorites
Welcome to ThePowerShellGuy.com Sign in | Join | Help

Hey PowerShell Guy !, how can I use get-content to analyze sql server logfile ?

I got a nice question the the comments, how can I use get-content to analyze sql server logfile ?

Ran has a SQL logfile like this : 

2008-10-21 09:51:51.40 spid3 "ERROR:" 
2008-10-22 09:51:52.04 spid3 "OK" 
2008-10-22 01:51:52.04 spid5 "ERROR:" 
2008-10-22 09:51:52.04 spid3 "OK" 
2008-10-23 01:51:52.04 spid5 "ERROR:"
2008-10-23 09:51:52.04 spid5 "ERROR:"  
2008-10-23 09:51:52.04 spid3 "OK" 
2008-10-24 09:51:52.04 spid5 "ERROR:" 
2008-10-24 09:51:52.04 spid5 "ERROR:" 
2008-10-24 09:51:52.04 spid3 "OK" 
2008-10-25 09:51:52.04 spid5 "ERROR:" 
2008-10-25 09:51:52.04 spid5 "OK" 
2008-10-25 09:51:52.04 spid5 "ERROR:"

and wanted want to be be able to search for errors ( "ERROR:" )only when the date is yesterday or today

the Quick and Dirty answer (as it is late early ) I made interactive in the PowerShell console  :

(Get-Content C:\powershell\sample_ERRORLOG.txt) |? {[void]($_ -match '((.*)\d\d spid)');(get-date -date $matches[2]) -gt (get-date).AddDays(-2)} |? {$_ -match 'ERROR'}

the Result :

image

You can see it takes a literal 2 day's back filter up till second level, hence you can see 1 error record of 2009-10-13

As I do filtering on converted datetime objects al in this single one-liner it is a bit  cryptic and the target calls for a bit more clear and robust solution for repeated usage.

So in a next post, I will follow up on this one and take this one-liner into it's parts to do some more explaining and  build a small script to do this task.

Enjoy,

Greetings /\/\o\/\/

Published Friday, October 24, 2008 9:04 PM by MoW
Filed under: ,

Comments

# re: Hey PowerShell Guy !, how can I use get-content to analyze sql server logfile ?

Why did you put parentheses around the first statement?  Is there a reason why you wanted to defer processing until the file was done being read?

Friday, October 24, 2008 9:16 PM by tojo2000

# re: Hey PowerShell Guy !, how can I use get-content to analyze sql server logfile ?

@tojo,

nope there is no good reason for the parentheses in the final one-liner

actualy this was a residu from working interactive on in the console.

First steps ( where it was stil needed for the -Match operator :

Get-Content C:\powershell\sample_ERRORLOG.txt -match '((.*)spid)'

(Get-Content C:\powershell\sample_ERRORLOG.txt) -match '((.*)spid)'

after that I got to this, where I would have been able to remove those paretheses again (and should have) but left them in (by using ArrowUp )

(Get-Content C:\powershell\sample_ERRORLOG.txt) |% {$_ -match '((.*)spid)';$matches[1]}

(Get-Content C:\powershell\sample_ERRORLOG.txt) |% {[void]$_ -match '((.*)spid)';$matches[1]}

As said, it was a Q&D Job ;-)

Greetings /\/\o\/\/

Saturday, October 25, 2008 6:57 AM by MoW

# Interesting Finds: October 25, 2008

Saturday, October 25, 2008 12:01 PM by Jason Haley

# re: Hey PowerShell Guy !, How can I use a PSCustomObject as an extension object to an XslTransform?

/\/\o\/\/,

I've been beating my head against this one; I'm tempted to dust off the IActiveScript interfaces I started for PS just so I can do this with msxsl:script.

I tried the following:

### SNIP ###

$xml = New-Object System.Xml.XPath.XpathDocument($xml_input_file_name)

$xslt = New-Object System.Xml.Xsl.XslTransform

$xslt.Load($xsl_file_name)

$xsl_args = New-Object System.Xml.Xsl.XsltArgumentList

$xsl_ext = New-Object PSObject |

 Add-Member -PassThru -MemberType ScriptMethod -Name GetGUID -Value { [Guid]::NewGuid().ToString().ToUpper() }

$xsl_args.AddExtensionObject('urn:extension', $xsl_ext)

$xsl_out = New-Object System.Xml.XmlTextWriter($xml_output_file_name, $null)

$xslt.Transform($xml, $xsl_args, $xsl_out)        

### SNIP ###

Of course, when I try to call GetGUID from within the XSLT, I get "'GetGUID()' is an unknown XSLT function."  About the only member I CAN access is ToString.

Any ideas on how I might go about doing this in PowerShell v. 1.0?

In the meantime, I guess I'll bite the bullet and build an assembly of extension methods.

Thanks,

--

Thomas S. Trias

Senior Developer

Artizan Internet Services

http://www.artizan.com/

Tuesday, December 02, 2008 4:03 PM by tomtrias

# re: Hey PowerShell Guy !, how can I use get-content to analyze Terminal Server Users

I have a VB script that i have used for years to monitor Terminal servers.  I want to port this to Powershell.  The script is based on the microft query.exe application.  I don't see the hooks in the Terminal Services Session get-counter information from the performance monitoring method.  

I do see the :

get-counter  '\Terminal Services\Total Sessions','\Terminal Services Session(*)\% User Time'

and

get-counter  '\Terminal Services\Total Sessions'

But there has to be an easy way to get the username from the list of total sessions.

Thanks.

Monday, April 13, 2009 12:46 PM by john.m.monroe
Anonymous comments are disabled