Help with Log Parser 2.2 script.

[blurredvision]

I'm in need of a good script to parse event logs for warnings and errors, and found that Microsoft's Log Parser does the trick quite nicely. However, I'm running into an issue. As a system administrator, I parse the logs of many, many servers once per month. I've got the following script written to parse the system logs:

logparser "SELECT DISTINCT EventTypeName, EventID, SourceName, Message, Count(*) AS Entries FROM System WHERE EventTypeName = 'Warning event' OR EventTypeName = 'Error event' OR EventTypeName = 'Critical event' GROUP BY EventID, Message, SourceName, EventTypeName ORDER BY EventTypeName,Entries DESC" -i:EVT -o:DATAGRID -rtp:-1

This brings back a datagrid with just warnings and errors, groups duplicates and gives me a count of how many there are of each. This is exactly what I'm looking for, however I still need to be able to specify to bring back only the records that are within the past 30 days since I do this monthly. I don't want everything that's on the system every time I run it. I know that I can pull the TimeGenerated field and use that to specify to report back a certain period, but if I pull that field, then it breaks my DISTINCT command, which is basically the crowning jewel of what I'm ultimately trying to do here (sorting through dozens/hundreds of repeated entries).

Any thoughts?

 

[ronbo1964]

I'm in need of a good script to parse event logs for warnings and errors, and found that Microsoft's Log Parser does the trick quite nicely. However, I'm running into an issue. As a system administrator, I parse the logs of many, many servers once per month. I've got the following script written to parse the system logs:

logparser "SELECT DISTINCT EventTypeName, EventID, SourceName, Message, Count(*) AS Entries FROM System WHERE EventTypeName = 'Warning event' OR EventTypeName = 'Error event' OR EventTypeName = 'Critical event' GROUP BY EventID, Message, SourceName, EventTypeName ORDER BY EventTypeName,Entries DESC" -i:EVT -o:DATAGRID -rtp:-1

This brings back a datagrid with just warnings and errors, groups duplicates and gives me a count of how many there are of each. This is exactly what I'm looking for, however I still need to be able to specify to bring back only the records that are within the past 30 days since I do this monthly. I don't want everything that's on the system every time I run it. I know that I can pull the TimeGenerated field and use that to specify to report back a certain period, but if I pull that field, then it breaks my DISTINCT command, which is basically the crowning jewel of what I'm ultimately trying to do here (sorting through dozens/hundreds of repeated entries).

Any thoughts?

Any number of ways to check the date, but here's one way ... Change your WHERE clause to:

WHERE EventTypeName IN ('Warning event'; 'Error event'; 'Critical event') AND TO_STRING(TimeGenerated, 'yyyy-MM') = '2011-06'

To pull June events ...

 

[blurredvision]

Thanks a lot rombo, I'll play around with that revised query when I get a chance in a couple of days. Much appreciated!

 

[blurredvision]

After some testing, I've come up with the following script:

logparser "SELECT DISTINCT EventTypeName, EventID, SourceName, Message, Count(*) AS Entries FROM Application WHERE EventTypeName IN ('Warning event'; 'Error event'; 'Success event') AND TimeWritten > TO_LOCALTIME( SUB( SYSTEM_TIMESTAMP(), TIMESTAMP('0000-02-01', 'yyyy-MM-dd') ) ) GROUP BY EventID, Message, SourceName, EventTypeName ORDER BY EventTypeName, Entries DESC" -i:EVT -o:DATAGRID -rtp:-1

This allows me to bring back the last 31 days of results. However, I'm in need of another tweak to my script. I'm using the DISTINCT command to combine all duplicates, however I'm running into the problem where some programs that write many, many events to the logs have slightly different messages. For example, I have a server where Symantec is encountering many scan errors and logs them under the same event ID, however the message has a timestamp in it that is throwing off the DISTINCT check. As far as I'm concerned, they're all the same, but my parser doesn't see it that way.

Is there a way I can use the DISTINCT query for just the EventID field but still be able to return the rest of the fields for display? I want it to check for duplicates on the Event ID only, not every single field I'm getting from the event logs.