Log Parser and CRM
My apologies for the lack of posts in this blog lately. For the past year I got roped into the Ebola outbreak crisis (and then Zika), working at the World Health Organisation in Geneva and moving away from CRM for the duration.
One of the tools I set up over the past year however did strike me as a potential useful little gadget for a CRM administrator, it’s a lightweight method using open-source components for: –
- Interrogating anything held in windows logs.
- Prepare an email detailing the logs of interest.
- Automatically sending that email once day.
The actual business need was to monitor the number of times PDFs were downloaded from a website and send summary statistics to the web site administrator. So although I am querying the Windows IIS (Internet Information System) logs in this example, the principle is the same for any other log types and could therefore be extended to CRM On-Premises server (not online).
There’s details on this post explaining how Log Parser studio is used with CRM: –
But you will need to ensure that CRM is producing the logs you want by configuring it correctly as per: –
The solution using the following open-source components: –
- LogParser 2.2 – an open-source tool that allows SQL-like queries to be run against windows logs and results output to a console window or a CSV file. I believe that this tool started life as a small handy development add-on within Microsoft and proved so useful that it was made generally available.
- LogParserStudio – an open-source tool that provides a graphical user interface for LogParser and is particularly useful for quickly prototyping, testing and validating the queries.
- PowerShell – Windows native command language, used to dispatch an email with the LogParser results file attached.
- Windows Schedules Task – Window’s native scheduler used to run a .BAT file which executes the LogParser command and calls PowerShell to email the results file as an attachment.
And the rest of this article is a walk-through of how to install, set up and use them.
Log Parser Installation
Here’s how to install the two open-source solution components ‘ Log Parser 2.2’ and ‘Log Parser Studio 2.0’: –
- The LogParser 2.2 open-source tool downloaded from https://www.microsoft.com/en-us/download/details.aspx?id=24659 and the installation executable can be double-clicked to run it.
- After installation of LogParser 2.2, it must be added to the %PATH% variable for the machine on which you wish to interrogate logs so that “logparser” becomes a recognised instruction when executed from the command line. The %PATH% variable is modified by going to “My Computer” > “Properties” > “Advanced” > “Environment Variables” > “Path” and editing this variable to add the location to which Log Parser 2.2 has been installed – for example “C:\Program Files (x86)\Log Parser 2.2”
Figure 1: Modifying the Window %PATH% Value
- You can test that the %PATH% variable has been successfully modified by opening a Command Prompt and typing « logparser ». The window should return a description of the tool and options for executing it.
Figure 2: Log Parser Command Line
- Optionally install the LogParserStudio 2.0 graphical tool from http://blogs.technet.com/b/exchange/archive/2013/06/17/log-parser-studio-2-2-is-now-available.aspx to have a graphical interface for working with LogParser.
- Resources for working with Log Parser Studio can be found at http://blogs.technet.com/b/karywa/archive/2013/04/21/getting-started-with-log-parser-studio.aspx.
The tool includes the ability to select which log files the tool will work with, enter and run a SQL-like query against those files to extract any data that is logged into a tabular format and produce a graph/chart of the results.
Results can be output to the tool or exported to a file.
Figure 3: Log Parser Studio
Log Parser Studio is a convenient tool for building and testing your SQL query.
Log Automation and Notification
The full solution that I had to build required that: –
- Once a day, statistics from the event log are to be output to a CSV file.
- That the file is emailed to recipients.
LogParser provides the interrogation of the IIS event log as follows: –
- The logparser command required to query the IIS log and save results to a CSV file is below and this command was placed in a .bat file created on the target machine: –
logparser “SELECT TOP 20 cs-uri-stem, COUNT(*) AS Total, MAX(time-taken) AS MaxTime, AVG(time-taken) AS AvgTime, AVG(sc-bytes) AS BytesSent INTO ‘D:\LogParserStudioLOGS\Top20PDFs.CSV’ FROM D:\LOGS\W3SVC1\*.log WHERE cs-uri-stem LIKE ‘%%pdf’ GROUP BY cs-uri-stem ORDER BY Total DESC” -i:iisw3c -rtp:-1
- This command comprises: –
- The SELECT statement of the data to extract. It works against the IIS log so all the fields listed at https://www.microsoft.com/technet/prodtechnol/WindowsServer2003/Library/IIS/676400bc-8969-4aa7-851a-9319490a9bbb.mspx?mfr=true can be interrogated. Note that not all fields are enabled for logging by Windows by default and you may need to use IIS Manager to turn on additional fields if you want to query them.
- An INTO statement specifying the path and file name to be created/overwritten.
- A FROM statement which must point to the directory that IIS uses for logging. IIS writes one log file per calendar day and the path to these is set within IIS Manager.
- The same .bat file contains a command to execute a PowerShell file containing the script to email the CSV file output by LogParser. You will need access to an SMTP server to control the email dispatch. The command to run the PowerShell file is: –
powershell -noexit D:\LogParserStudioLOGS\Top20PDFs.ps1
- The code within the PowerShell file to send an email is as follows: –
$From = “firstname.lastname@example.org”
[string]$to = “email@example.com”, “firstname.lastname@example.org”
$Subject = “Website PDF Request Statistics”
$Body = “Statistics are attached for the top 20 PDFs downloaded from the website.”
$AttachFile = “D:\LogParserStudioLOGS\Top20PDFs.CSV”
$SMTP = “smtpserver.details.com”
Send-MailMessage -From $from -To $to -Subject $Subject -Body $body -Attachments $AttachFile -SMTPServer $SMTP
- This command comprises: –
- Variables for the FROM, TO, SUBJECT and BODY of the email.
- FROM is an array of strings for handling multiple recipients.
- The path and file name to add to the email as an attachment.
- The SMTP server to use for dispatch.
- The Send-MailMessage command which uses the variables to dispatch an email via the given SMTP server.
- Note that values are hard-coded within the current solution but could be read from a configuration file and/or provided to the PowerShell file as parameters for a more flexible and generic solution.
- At this point running the .bat file will execute LogParser, output the results to a file and dispatch an email so the only element missing is automation.
- Automation is achieved by configuring the Windows Scheduled Task to add a new task that, daily at 2am, executes the .bat
Figure 4: Windows Task Scheduler
Putting It All Together
Apologies that this blog entry is a bit of a “brain-dump” of the work performed but I hope it gives you a sense that: –
- You can use Log Parser to write SQL-like queries against the verbose Windows logs to quicly find and extract what you need.
- That Log Parser Studio is the best place to play around with this, prototype and refine your queries.
- That Log Parser outputs what you want CSV format.
- That once ready, you can transfer the instructions for executing the query and outputting CSV to a .bat file and then use some PowerShell to act on that file – for example emailing it out to interested recipients.
- That once the files are set up you can use Windows Scheduled Tasks to kick off the program on a timetable of your choice.