Analyze evt log files with Log Parser Lizard - NetApp auditing

What:

Log Parser Lizard installed on Windows machine

Problem:

NetApp cifs auditing is producing a large amount of data stored in evt files, which should be analysed.

Solution:

Log Parser Lizard is a front end to Microsoft's Log Parser utility, which allows to write SQL queries against text files.

With Log Parser you can analyze IIS log files, XML or CVS files, Windows Event Log files and many more.

I am focusing on evt files saved by NetApp filer. I will present few examples of how to extract some useful data from there.

I keep all my evt files in F:\Auditing_Logs\

Most accessed files
====================================================

SELECT Extract_Token(Extract_Token(Message,4,':'),0,'Handle') AS [Folder], count(Folder) AS Hits FROM 'F:\Auditing_Logs\*.evt'
WHERE EventID IN(560)
GROUP BY Folder
ORDER BY Hits desc

Most active users
====================================================

SELECT RESOLVE_SID(SID) as Username, COUNT(*) as hits FROM 'F:\Auditing_Logs\*.evt' GROUP BY username order by hits desc

 Top 10 most active users
====================================================

SELECT TOP 10 RESOLVE_SID(SID) as Username, COUNT(*) as hits FROM 'F:\Auditing_Logs\*.evt' GROUP BY username order by hits desc

Number of operations (read, write, delete) by selected user
====================================================

SELECT
Extract_Token(Extract_Token(Message,14,':'),1,' ') AS [Operation],
Count(Operation) AS Hits
FROM 'F:\Auditing_Logs\*.evt'
WHERE EventID IN(560) AND resolve_sid(SID) in('DOMAIN\<username>')
GROUP by Operation
Order by Hits desc

 Folders and files accessed by selected user
====================================================

SELECT
resolve_sid(SID) AS Username,
TimeGenerated,
Extract_Token(Extract_Token(Message,14,':'),1,' ') AS [Operation],
Extract_Token(Extract_Token(Message,4,':'),0,'Handle') AS [Folder or Filename]
FROM 'F:\Auditing_Logs\*.evt'
WHERE EventID IN(560) AND Operation IN('ReadData') AND resolve_sid(SID) IN ('DOMAIN\<username>')
Order by TimeGenerated desc

 Files modified by selected user
====================================================

SELECT
resolve_sid(SID) AS Username,
TimeGenerated,
Extract_Token(Extract_Token(Message,14,':'),1,' ') AS [Operation],
Extract_Token(Extract_Token(Message,4,':'),0,'Handle') AS [Folder or Filename]
FROM 'F:\Auditing_Logs\*.evt'
WHERE EventID IN(560) AND Operation IN('WriteData') AND resolve_sid(SID) IN ('DOMAIN\<username>')
Order by TimeGenerated desc