I ran across a useful post today as I was roaming through Google Analytics.
Using PowerShell, LogParser and PowerGadgets to get Exchange 2003 storage information – Part 1
Wes Stahler uses Log Parser and PowerShell to report on the free space in an Exchange Database.
This is a task I have done in the past. I will add this script to my toolkit.
Regards,
Dave
Welcome!
One day I was pondering how I might use log parser to map visitors to a website by state. I am aware this is easily done with tools like Google Analytics, but I was interested in using existing logs for the info.
Using the PowerShell and Log Parser functions from the library listed in a previouse post. Log parser can easily get the visitors by IP Address from an IIS log.
|
1 2 3 4 5 6 7 8 9 |
# Get unique ip addresses to working file $query = @" SELECT Distinct c-ip As IPAddress INTO D:Unique_IP_Addresses.txt From C:Logsex080310.log "@ $LPInputFormat = Get-LPInputFormat "w3c" $LPOutputFormat = Get-LPOutputFormat "csv" Invoke-LPExecuteBatch -query $query -inputtype $LPInputFormat -outputtype $LPOutputFormat | Out-Null |
The next task is to get the location of the IP addresses. The tool I chose for this task was the free GeoLite City from MaxMind http://www.maxmind.com/app/geolitecity. Here is an example:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
# Get location for IP addresses from GEOIP DB $geodbpath = "C:GeoLiteCity" $visitors = Import-Csv -Path "D:Unique_IP_Addresses.txt" $ipinfo = New-Object -ComObject "GeoIPCOMEx.GeoIPEx" $ipinfo.set_db_path($geodbpath) | Out-Null Add-Content -Path "D:IP_Locations.txt" "Location" foreach($visitor in $visitors){ $ipinfo.find_by_addr($visitor.ipaddress) | Out-Null if($ipinfo.Region){ Add-Content -Path "D:IP_Locations.txt" $ipinfo.Region } else { Add-Content -Path "D:IP_Locations.txt" "Unknown" } } # Get State counts $query = @" SELECT Location, Count(Location) As Visitors INTO D:VisitorsByState.txt From D:IP_Locations.txt Group By Location "@ $LPInputFormat = Get-LPInputFormat "csv" $LPOutputFormat = Get-LPOutputFormat "csv" Invoke-LPExecuteBatch -query $query -inputtype $LPInputFormat -outputtype $LPOutputFormat | Out-Null |
There are a couple of ways to use the MaxMind GeoIP database. It can be used in it’s native binary format or it can be imported into SQL from csv files. MaxMind recommends using the binary format, which is what I chose to do. MaxMind also provides API’s for use with a variety of platform’s. I chose to use the COM version.
After the location is determined the counts are calculated. This brings us to the point where we need to chart the results. The tool I chose for this operation is PowerGadgets. This is a charting tool made for use with PowerShell, it can be handy. Here is an example:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
# Create PowerGadgets Map $charttitle = "Daily visitors by state" $visits = Import-Csv -Path D:VisitorsByState.txt $visits | Out-Map -mapsource "USUSA-StatesAbrev" ` -label location ` -values visitors ` -palette "highcontrast" ` -output $chartout ` -size 600,400 ` -BackColor White ` -Titles_0_BackColor White ` -Titles_0_Font "Arial, 14" ` -Titles_0_Text $charttitle ` -Titles_0_TextColor Black ` -conditionalattributes_0_condition_from 1 ` -conditionalattributes_0_condition_to 10 ` -conditionalattributes_0_color Red ` -conditionalattributes_0_text "1 to 10" ` -conditionalattributes_1_condition_from 11 ` -conditionalattributes_1_condition_to 25 ` -conditionalattributes_1_color Blue ` -conditionalattributes_1_text "11 to 25" ` -conditionalattributes_2_condition_from 26 ` -conditionalattributes_2_condition_to 50 ` -conditionalattributes_2_color Green ` -conditionalattributes_2_text "26 to 50" ` -conditionalattributes_3_condition_from 51 ` -conditionalattributes_3_condition_to 100 ` -conditionalattributes_3_color Yellow ` -conditionalattributes_3_text "51 to 100" ` -conditionalattributes_4_condition_from 101 ` -conditionalattributes_4_condition_to 1000 ` -conditionalattributes_4_color Violet ` -conditionalattributes_4_text "101 to 1000" |
And here is our final Chart.
This works pretty well the only drawback to this solution is that PowerGadgets is a pay tool, but since I own a copy it suites my needs.
Regards,
Dave
Welcome back!
Last post I talked about using the Log Parser executable from PowerShell. I also briefly mentioned the Log Parser COM component. In this post I will go into more depth on using the COM component from PowerShell.
The COM component exposes a simple object model consisting of only three main objects.
There are also objects containing the input and output formats. There are several of them and they are well documented in the Log Parser documentation. I will give examples as we progress.
The first step on my task to integrate with the Log Parser COM object was to put together a function library of the basic building blocks.
The library consists of the following functions.
With these functions we can support almost all of the Log Parser functionality in PowerShell. I did not build in support for the custom COM input type or the NAT and Datagrid output types. The NAT and Datagrid output types can be handled in a different way in PowerShell. The COM input format is a challenge I left for another day.
Here is the function library.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 |
# --------------------------------------------------------------------------- ### <library name="Muegge_LogParser_Lib.ps1"> ### <author>David Muegge</author> ### <createdate>20081108</createdate> ### <modifieddate>20081121</modifieddate> ### <description> ### Log Parser function library ### </description> ### <dependencies> ### Log Parser 2.2 COM component ### </dependencies> ### <usage> ### Dot source from calling script ### </usage> ### </library> # --------------------------------------------------------------------------- # --------------------------------------------------------------------------- ### <function name="Get-LPInputFormat"> ### <description> ### Returns Log Parser Input Format object based on passed string ### </description> ### <usage> ### Get-LPInputFormat -InputType <string> ### </string></usage> ### </function> # --------------------------------------------------------------------------- function Get-LPInputFormat{ param([String]$InputType) switch($InputType.ToLower()){ "ads"{$inputobj = New-Object -comObject MSUtil.LogQuery.ADSInputFormat} "bin"{$inputobj = New-Object -comObject MSUtil.LogQuery.IISBINInputFormat} "csv"{$inputobj = New-Object -comObject MSUtil.LogQuery.CSVInputFormat} "etw"{$inputobj = New-Object -comObject MSUtil.LogQuery.ETWInputFormat} "evt"{$inputobj = New-Object -comObject MSUtil.LogQuery.EventLogInputFormat} "fs"{$inputobj = New-Object -comObject MSUtil.LogQuery.FileSystemInputFormat} "httperr"{$inputobj = New-Object -comObject MSUtil.LogQuery.HttpErrorInputFormat} "iis"{$inputobj = New-Object -comObject MSUtil.LogQuery.IISIISInputFormat} "iisodbc"{$inputobj = New-Object -comObject MSUtil.LogQuery.IISODBCInputFormat} "ncsa"{$inputobj = New-Object -comObject MSUtil.LogQuery.IISNCSAInputFormat} "netmon"{$inputobj = New-Object -comObject MSUtil.LogQuery.NetMonInputFormat} "reg"{$inputobj = New-Object -comObject MSUtil.LogQuery.RegistryInputFormat} "textline"{$inputobj = New-Object -comObject MSUtil.LogQuery.TextLineInputFormat} "textword"{$inputobj = New-Object -comObject MSUtil.LogQuery.TextWordInputFormat} "tsv"{$inputobj = New-Object -comObject MSUtil.LogQuery.TSVInputFormat} "urlscan"{$inputobj = New-Object -comObject MSUtil.LogQuery.URLScanLogInputFormat} "w3c"{$inputobj = New-Object -comObject MSUtil.LogQuery.W3CInputFormat} "xml"{$inputobj = New-Object -comObject MSUtil.LogQuery.XMLInputFormat} } return $inputobj } # --------------------------------------------------------------------------- ### <function name="Get-LPOutputFormat"> ### <description> ### Returns Log Parser Output Format object based on passed string ### </description> ### <usage> ### Get-LPOutputFormat -OutputType <string> ### </string></usage> ### </function> # --------------------------------------------------------------------------- function Get-LPOutputFormat{ param([String]$OutputType) switch($OutputType.ToLower()){ "csv"{$outputobj = New-Object -comObject MSUtil.LogQuery.CSVOutputFormat} "chart"{$outputobj = New-Object -comObject MSUtil.LogQuery.ChartOutputFormat} "iis"{$outputobj = New-Object -comObject MSUtil.LogQuery.IISOutputFormat} "sql"{$outputobj = New-Object -comObject MSUtil.LogQuery.SQLOutputFormat} "syslog"{$outputobj = New-Object -comObject MSUtil.LogQuery.SYSLOGOutputFormat} "tsv"{$outputobj = New-Object -comObject MSUtil.LogQuery.TSVOutputFormat} "w3c"{$outputobj = New-Object -comObject MSUtil.LogQuery.W3COutputFormat} "tpl"{$outputobj = New-Object -comObject MSUtil.LogQuery.TemplateOutputFormat} } return $outputobj } # --------------------------------------------------------------------------- ### <function name="Invoke-LPExecute"> ### <description> ### Executes a Log Parser Query and returns a recordset ### </description> ### <usage> ### Invoke-LPExecute -query <string> ### </string></usage> ### </function> # --------------------------------------------------------------------------- function Invoke-LPExecute{ param([string] $query, $inputtype) $LPQuery = new-object -com MSUtil.LogQuery if($inputtype){ $LPRecordSet = $LPQuery.Execute($query, $inputtype) } else { $LPRecordSet = $LPQuery.Execute($query) } return $LPRecordSet } # --------------------------------------------------------------------------- ### <function name="Invoke-LPExecuteBatch"> ### <description> ### Executes Log Parser batch query with passed input and output types ### </description> ### <usage> ### Invoke-LPExecuteBatch -query <string> -inputtype <logparserinputformat> -outputtype <logparseroutputformat> ### </logparseroutputformat></logparserinputformat></string></usage> ### </function> # --------------------------------------------------------------------------- function Invoke-LPExecuteBatch{ param([string]$query, $inputtype, $outputtype) $LPQuery = new-object -com MSUtil.LogQuery $result = $LPQuery.ExecuteBatch($query, $inputtype, $outputtype) return $result } # --------------------------------------------------------------------------- ### <function name="Get-LPRecord"> ### <description> ### Returns PowerShell custom object from Log Parser recordset for current record ### </description> ### <usage> ### Get-LPRecord -rs <recordset> ### </recordset></usage> ### </function> # --------------------------------------------------------------------------- function Get-LPRecord{ param($LPRecordSet) $LPRecord = new-Object System.Management.Automation.PSObject if( -not $LPRecordSet.atEnd()) { $Record = $LPRecordSet.getRecord() for($i = 0; $i -lt $LPRecordSet.getColumnCount();$i++) { $LPRecord | add-member NoteProperty $LPRecordSet.getColumnName($i) -value $Record.getValue($i) } } return $LPRecord } # --------------------------------------------------------------------------- ### <function name="Get-LPRecordSet"> ### <description> ### Executes a Log Parser Query and returns a LogRecordSet as a custom powershell object ### </description> ### <usage> ### Get-LPRecordSet -query <string> ### </string></usage> ### </function> # --------------------------------------------------------------------------- function Get-LPRecordSet{ param([string]$query) # Execute Query $LPRecordSet = Invoke-LPExecute $query $LPRecords = new-object System.Management.Automation.PSObject[] 0 for(; -not $LPRecordSet.atEnd(); $LPRecordSet.moveNext()) { # Add record $LPRecord = Get-LPRecord($LPRecordSet) $LPRecords += new-Object System.Management.Automation.PSObject $RecordCount = $LPQueryResult.length-1 $LPRecords[$RecordCount] = $LPRecord } $LPRecordSet.Close(); return $LPRecords } |
This library provides the basic functionality needed for Log Parser. We can use it in two basic scenarios.
One – We want to execute a Log Parser batch. This mode works exactly as the Log Parser command line tool works it queries an input file of a given type and writes the results to an output file of a given type.
Two – We want the results of a Log Parser query returned to a PowerShell object. This will allow us to further process the results using PowerShell or simply utilize the output facilities to display the results.
Example of Scenario One:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
. .Muegge_LogParser_Lib.ps1 $infile = "D:TempLPTest.log" $outfile = "D:TempLPTest.gif" # Set input format and parameters $inputformat = Get-LPInputFormat "w3c" # Set output format and parameters $outputformat = Get-LPOutputFormat "chart" $outputformat.chartTitle = "Top 10 Pages" $outputformat.chartType = "BarStacked" $outputformat.groupSize = "600x400" $query = @" SELECT Top 10 cs-uri-stem, Count(*) INTO $outfile FROM $infile Group By cs-uri-stem Order by cs-uri-stem desc "@ # Execute batch Invoke-LPExecuteBatch $query $inputformat $outputformat | Out-Null |
The code above provides a good way to create scheduled reports. The syntax is easier to follow than the command line switches at least for me.
Here is the sample output chart.
Yes, I took this log from a box with nothing but hacker traffic J
Now let’s look at the second scenario. We will return the results of a Log Parser query in a PSObject.
Here we are querying for application hang events in the application event log. We will use a Log Parser query to retrieve just the events we want. Then we will use PowerShell to filter out just the events for IE. The we can easily display the output any way we like.
|
1 2 3 4 5 6 7 8 9 10 11 |
. .Muegge_LogParser_Lib.ps1 $query = @" SELECT * FROM Application WHERE EventID = 1002 "@ $inputformat = Get-LPInputFormat "evt" $records = Get-LPRecordSet $query $inputformat $records | where {$_.Strings -like "*iexplore.exe*"} | ft -property TimeGenerated,SourceName,Strings |
Here is the output.
|
1 2 3 4 5 6 7 8 9 10 11 |
TimeGenerated SourceName Strings ------------- ---------- ------- 11/1/2008 11:21:02 PM Application Hang iexplore.exe|7.0.6000.16735|hungapp|0.0... 11/2/2008 6:25:35 PM Application Hang iexplore.exe|7.0.6000.16735|hungapp|0.0... 11/4/2008 7:32:35 AM Application Hang iexplore.exe|7.0.6000.16735|hungapp|0.0... 11/6/2008 4:21:22 PM Application Hang iexplore.exe|7.0.6000.16735|hungapp|0.0... 11/8/2008 7:05:20 PM Application Hang iexplore.exe|7.0.6000.16735|hungapp|0.0... 11/10/2008 3:01:45 PM Application Hang iexplore.exe|7.0.6000.16735|hungapp|0.0... 11/11/2008 5:10:40 PM Application Hang iexplore.exe|7.0.6000.16735|hungapp|0.0... 11/14/2008 12:13:06 PM Application Hang iexplore.exe|7.0.6000.16735|hungapp|0.0... 11/17/2008 7:34:29 AM Application Hang iexplore.exe|7.0.6000.16735|hungapp|0.0... |
To me this really seems like the best of both worlds utilizing each tool for its strength.
Now I just have to figure out why IE hangs J
Best Regards,
Dave
Welcome!
Log Parser and PowerShell are both great tools and they work well when used together. Yes, you can do pretty much everything Log Parser does with PowerShell alone, but part of PowerShell’s mission is to better leverage current tools. I believe this is an excellent example. Also it has been my experience Log Parser performs better at the task. Steve Schofield also blogged about the performance of Log Parser and PowerShell for querying logs here.
There are two ways to interact with Log Parser from PowerShell. The first and the easiest to get started with is the command line version logparser.exe.
Here is the same command line query example from the last post but with PowerShell.
& ./logparser.exe “SELECT Top 10 cs-uri-stem, Count(*) FROM D:Logsex081110.log Group By cs-uri-stem Order by cs-uri-stem desc” –i:w3c
Big difference isn’t it. J
Here is a simple powershell script using Log Parser. This is a little easier to follow than the batch file example in the last post.
|
1 2 3 4 5 6 7 8 9 10 |
$infile = "D:TempLPTest.log" $query = @" SELECT Top 10 cs-uri-stem, Count(*) FROM $infile Group By cs-uri-stem Order by cs-uri-stem desc "@ $result = & C:LogParserlogparser.exe $query -i:w3c |
The drawback to these examples is we are not really gaining the full benefit from PowerShell. We could write some functions in PowerShell and convert the text output from Log Parser into PowerShell objects, but there is an easier way to do this.
OK, now for the second way to interact with Log Parser from PowerShell, the Log Parser COM component (logparser.dll). This component installs with Log Parser and should be registered and ready to use if you have installed Log Parser.
The COM component exposes a simple object model consisting of only three main objects. The LogQuery, LogRecorset, and LogRecord objects. There are also a series of objects for the input and output formats.
The COM component is more difficult to use, but is an advantage because data can be returned in object form. We will look at this in detail starting in Log Parser and PowerShell – Part II
Best Regards,
Dave
Welcome!
Log Parser is a great tool for analyzing many types of text data. It is a free downloadable tool from Microsoft it installs a command line version and a COM component. It is a powerful tool for using the SQL language to query most types of fixed or delimited text data.
I recommend this article as an excellent place to begin learning to use log parser. It was written by the author of Log Parser, Gabriele Giuseppini and is a good overview. After reading it a great next step is to download Log Parser 2.2 here install it and take a look at the compiled help file.
The help file for the product is pretty good. The first two sections have enough information to get a good start. General use of Log Parser is covered there and other places so I will only give a brief overview here.
I believe most people probably start using log parser directly on the command line.
Example: c:LogParserlogparser.exe “SELECT Top 10 cs-uri-stem, Count(*) FROM D:Logsex081110.log Group By cs-uri-stem Order by cs-uri-stem desc” –i:w3c
Another good way to experiment with Log Parser is to use a batch file and a SQL file.
Example – Top 10 web Pages:
Batch File
This batch file prompts for the input, output, and sql query files and executes Log Parser to query a w3c log file. Log Parser allows the use of variables inside the SQL query. The example below inserts the values for the input and output files entered at the prompts into the query.
|
1 2 3 4 5 6 7 |
Set /p INPUTFILE=Enter input file Path: Set /p OUTPUTFILE=Enter output file Path: Set /p SQLFILE=Enter SQL file Path: "C:Program FilesLog Parser 2.2LogParser.exe" file:%SQLFILE%?infile=%INPUTFILE%+outfile=%OUTPUTFILE% -i:w3c -o:w3c |
Log Parser SQL File
This SQL file gets the top ten pages by hits from a w3c log file.
|
1 2 3 4 5 6 7 8 9 |
Select Top 10 cs-uri-stem As Page, Count(*) As Hits INTO %outfile% From %infile% Group By Page Order By Hits DESC |
These are basic examples of how Log Parser works. Next time I will talk about using Log Parser from PowerShell.
Best Regards,
Dave