Trouble parsing through a lot of logs using wildcards

Question

I want to create a script that will wrap logparser into Powershell and automatically analyze a set of logs. I will create a function for a lot of common analysis tasks. The issue I am having is I have a separate copy script that will go to a lot of servers, and modify the names dumping the files into one location. I append the log folder name and server name to the file. So if I have two sites on one server rather than u_ex160415.log I would have NYVPRDWEB2_W3SVC1_u_ex160415.log and NYVPRDWEB2_W3SVC2_u_ex160415.log. I end up having a folder filled with maybe 50 logs to parse through, and as you can imagine the file names vary a lot.

When I run a logparser query against the logs with a wildcard I always receive an error

logparser "SELECT * FROM C:\Logs\*.log WHERE sc-status <> 200"

The error returned is:

logparser : Error: WHERE clause: Syntax Error: unknown field 'sc-status'

From past experience I knew it was the wildcard, so instead of *.log I did

logparser "SELECT * FROM C:\Logs\NYVPRDWEB2_W3SVC1_u_ex160415.log,C:\Logs\NYVPRDWEB2_W3SVC2_u_ex160415.log WHERE sc-status <> 200"

This worked great so I figued I will just script it out to append all file names in a string that I will pass into the logparser query. I tried this:

function Get-IISErrors([Parameter(Mandatory=$true)]$LogPath,[Parameter(Mandatory=$true)]$OutputDestination,[datetime]$StartDate,[datetime]$EndDate,$DaysBack)
{
#Validate Parameters
if ($LogPath -eq '')
    {
        Write-Host -ForegroundColor Red "Log Path is a required parameter"
    }
if ($OutputDestination -eq '')
    {
        Write-Host -ForegroundColor Red "Output Destination is a required parameter"
    }

#Get names of Files that will be analyzed
$LogName = (Get-ChildItem $LogPath).Name

#Create String of comma separated files to be analyzed
$LogFileString = ""
foreach($x in $LogName)
    {
        $LogFileString = $LogFileString + "'" + $LogPath + $x + "'" + "," 
    }
$LastIndex = $LogFileString.Length -1
$LogFileString = $LogFileString.Substring(0,$LastIndex)

logparser "SELECT * INTO $OutputDestination FROM $LogFileString WHERE sc-status <> 200"

}

But when I run that against a large amount of files I get

'LogParser.exe' failed to run: The filename or extension is too long

I would rather figure this out using Long Parser wild cards, has anyone had luck just using *.log ? I end up using a third party tool LogLizard a lot of the time, so I am a little inexperienced when it comes to Syntax in LogParser.

have you tried `logparser -i:IISW3C "SELECT * FROM C:\Logs\*.log WHERE sc-status <> 200"` — wmz, Mar 24 '17 at 23:02
wmz is right - the reason you get "unknown field 'sc-status'" is because you're letting LogParser guess the fields based on the logs it sees in the FROM clause. If you explicitly specify the input format - e.g. with -i:IISW3C - that error should go away. You should then be able to use wildcards. Also make sure your wildcards are not picking up extraneous logs, try to be more specific, e.g. "*_u_ex*.log". — Gabriele Giuseppini, Mar 26 '17 at 09:07

Arani · Answer 1 · 2020-01-27T10:13:51.080

I use the below query to find all event id = 4771 from all .evtx files on a drive with powershell:

PS C:\ Get-ChildItem -Recurse | where {$_.name -like "*.evtx"} | foreach{ cd $_.DirectoryName; pwd; & 'C:\Program Files(x86)\Log Parser 2.2\LogParser.exe'
-stats:OFF -i:EVT -q:ON "select * from $_. where eventid='4771'" >> out.csv}

It works great, I think if I change some part of code it can use for your issue:

PS C:\ Get-ChildItem -Recurse | where {$_.name -like "*.log"} | foreach{ cd $_.DirectoryName; pwd; & 'C:\Program Files(x86)\Log Parser 2.2\LogParser.exe'
-stats:OFF -i:IISW3C -q:ON "select * from $_. where sc-status <> '200'" >> out.csv}

Finally, you can open the .csv file with Microsoft Excel and use the text to column function with pipe delimiter (|) to have a readable format.

Trouble parsing through a lot of logs using wildcards

1 Answers1