How to count days?
Last post Nov 09, 2018 04:10 AM by NyyNeely
Oct 28, 2014 08:35 AM|felix158|LINK
I want to use logparser to do something that I think should be very simple, but I've never used the program and I have no idea how to use it.
Firstly, I managed to know if the event "4624" appears at least once every day:
SELECT DISTINCT QUANTIZE( TimeGenerated, 86400 ) AS Timee,
EXTRACT_TOKEN(Strings,5,'|') AS USER
FROM 'c:\archive-*.evtx' WHERE user like 'dcse1401' AND EventID=4624
GROUP BY Timee,user
HAVING to_string(timee,'yyyy-MM-dd HH:mm:ss') like '2014-10%'
2014-10-06 00:00:00 dcse1401
2014-10-07 00:00:00 dcse1401
2014-10-08 00:00:00 dcse1401
2014-10-09 00:00:00 dcse1401
2014-10-13 00:00:00 dcse1401
2014-10-14 00:00:00 dcse1401
2014-10-15 00:00:00 dcse1401
2014-10-16 00:00:00 dcse1401
2014-10-20 00:00:00 dcse1401
2014-10-21 00:00:00 dcse1401
2014-10-22 00:00:00 dcse1401
2014-10-23 00:00:00 dcse1401
2014-10-27 00:00:00 dcse1401
Now I want to count the number of days that the user "dcse1401" appears in the above grid, that would be 13. In other words:
I´ve Proved the following query:
EXTRACT_TOKEN(Strings,5,'|') AS USER,
count(*) AS Number_of_Logons
FROM 'c:\archive-*.evtx' WHERE user like 'dcse1401%' and EventID='4624'
AND to_string(timegenerated,'yyyy-MM-dd HH:mm:ss') like '2014-10%'
GROUP BY USER
but this query displays all records that have produced in this moth, in this case 157
¿Does anyone Know how to get such a result?
Thanks in advance.
Oct 28, 2014 09:58 AM|terridonahue|LINK
Since logparser uses SQL queries, you should be able to sue the BETWEEN operator like shown on this
Oct 28, 2014 10:46 AM|felix158|LINK
Thanks for answering me, terridonahue, but I´ve been proving some examples with BETWEEN, but I’m not capable of sorting out the problem. I’ve proved with:
BETWEEN TIMESTAMP('10/14 00:00:00', 'MM/dd hh:mm:ss')
AND TIMESTAMP('10/13 01:00:00', 'MM/dd hh:mm:ss')
But It desn’t work
Could you write some examples where I can see the use BETWEEN, please? Centered in what I want to achive.
Thanks in advances.
Oct 28, 2014 11:15 AM|terridonahue|LINK
Try the following where statement:
where Date < '2014-10-27' and Date > '2014-10-06'
Nov 09, 2018 04:10 AM|NyyNeely|LINK
I have done as your guide