Search code examples
logparser

Generating output by time interval where there are no values in source


I have a message log file that contains one line per message sent, with a timestamp rounded to the minute. I can use Logparser to generate a report on the message frequency per minute and output a chart showing the same. Here's the query I'm using to produce the report:

SELECT  
    QUANTIZE (Date, 60) AS M, 
    COUNT(*) AS Total 
FROM '[LOGFILEPATH]' 
GROUP BY M 
ORDER BY M

This is great for showing the message traffic rate for those minutes when traffic has been generated.

However, I would like to output a chart that also shows the minute intervals where the message count is 0 - in a bar chart, the bar would be at 0 for that minute. This will show us intervals when there has been no message traffic.

Naturally, this isn't in the source data, because it's not logging "no message". Any ideas how to do this? I actually don't care if it's Logparser - Powershell is something else I'm happy to use. Or even Excel, although I highly prefer a solution that can be scripted without manual intervention.

We're a Windows shop with limited access to dev tools, so no Python, bash, etc solutions, please.

Here's some sample (real, obfuscated) data that contanis a nearly half-hour interval where there is no traffic being logged.

Date,MID,Host,Sender,Recipient,Subject,Last State
2017-10-26 01:56,1078941,smtp3 (10.0.0.156),[email protected],[email protected],Notification has been created successfully,Message 1078941 to [email protected] received remote SMTP response 'ok'
2017-10-26 01:56,1078938,smtp3 (10.0.0.156),[email protected],[email protected],Notification has been created successfully,Message 1078940 to [email protected] received remote SMTP response '2.0.0'
2017-10-26 01:55,4571113,smtp1 (10.0.1.59),[email protected],[email protected],Notification has been created successfully,Message 4571113 to [email protected] received remote SMTP response '2.0.0'
2017-10-26 01:55,1078936,smtp3 (10.0.0.156),[email protected],[email protected],Notification has been created successfully,Message 1078936 to [email protected] received remote SMTP response 'ok'
2017-10-26 01:54,4571083,smtp1 (10.0.1.59),[email protected],[email protected],Notification has been created successfully,Message 4571083 to [email protected] received remote SMTP response 'Mail accepted'.
2017-10-26 01:53,1078927,smtp3 (10.0.0.156),[email protected],[email protected],Notification has been created successfully,Message 1078927 to [email protected] received remote SMTP response '2.0.0'
2017-10-26 01:52,4571051,smtp1 (10.0.1.59),[email protected],[email protected],Notification has been created successfully,Message 4571051 to [email protected] received remote SMTP response 'ok'
2017-10-26 01:23,4570598,smtp1 (10.0.1.59),[email protected],[email protected],Notification has been created successfully,Message 4570598 to [email protected] received remote SMTP response '2.0.0'
2017-10-26 01:23,4570594,smtp1 (10.0.1.59),[email protected],[email protected],Notification has been created successfully,Message 4570594 to [email protected] received remote SMTP response 'ok'
2017-10-26 01:22,4570579,smtp1 (10.0.1.59),[email protected],[email protected],Notification has been created successfully,Message 4570579 to [email protected] received remote SMTP response '2.0.0'
2017-10-26 01:22,4570577,smtp1 (10.0.1.59),[email protected],[email protected],Notification has been created successfully,Message 4570577 to [email protected] received remote SMTP response '2.0.0'
2017-10-26 01:22,4570575,smtp1 (10.0.1.59),[email protected],[email protected],Notification has been created successfully,Message 4570575 to [email protected] received remote SMTP response '2.0.0'
2017-10-26 01:21,4570571,smtp1 (10.0.1.59),[email protected],[email protected],Notification has been created successfully,Message 4570571 to [email protected] received remote SMTP response 'ok'
2017-10-26 01:21,4570557,smtp1 (10.0.1.59),[email protected],[email protected],Notification has been created successfully,Message 4570557 to [email protected] received remote SMTP response 'OK'
2017-10-26 01:21,4570549,smtp1 (10.0.1.59),[email protected],[email protected],Notification has been created successfully,Message 4570550 to [email protected] received remote SMTP response '2.0.0'
2017-10-26 01:20,1078803,smtp3 (10.0.0.156),[email protected],[email protected],Notification has been created successfully,Message 1078803 to [email protected] received remote SMTP response '2.0.0'
2017-10-26 01:20,1078802,smtp3 (10.0.0.156),[email protected],[email protected],New Account,Message 1078802 to [email protected] received remote SMTP response '2.0.0'
2017-10-26 01:20,4570539,smtp1 (10.0.1.59),[email protected],[email protected],Notification has been created successfully,Message 4570539 to [email protected] received remote SMTP response 'OK'
2017-10-26 01:19,1078794,smtp3 (10.0.0.156),[email protected],[email protected],Notification has been created successfully,Message 1078794 to [email protected] received remote SMTP response 'ok'

Solution

  • As mentioned in the comments it would be a multi step process starting with getting the dates covered in your log file:

    SELECT MIN(Date) AS MinDate, MAX(Date) as MaxDate 
    INTO theDates.csv 
    FROM yourlog.log
    

    The reading in the dates contained in theDates.csv using say a Powershell program to generate a log file that matched your log format and contained exactly 1 record per minute.

    Then you can run your original query with a slight tweak:

    SELECT 
       QUANTIZE(Date, 60) as M,
       SUB(COUNT(*), 1) as total
    FROM yourlog.log, yourfakelogfile.log
    ORDER BY M
    GROUP BY M
    

    The slight tweaking being the count is being decremented by 1 so that you get zeros in the time period where there was no activity.

    Put all three of these steps in say a powershell script and you have an automated, repeatable process.