I have Windows Kerberos logs that I have turned into a summary table by day by user. I am trying find the way to determine the number of continuous days when the person doesn't work, but this is kind of hard to do because if they didn't work....there is no record. My initial thought was:
index=summary source=daily_kerberos_summary success=T | bucket _time span=1d | stats count by _time username | eval worked=if(count!=0,1,0) | streamstats count by username worked reset_on_change=true
This sort of identifies groupings of them working (although even that isn't actually working really), but doesn't begin to answer when they aren't working. Ideas?
How about this? This will chart the number of consecutive of days off based on my testing.
index=summary source=daily_kerberos_summary success=T | fields _time username | timechart span=1d limit=100 dc(username) by username | untable _time username count | eval off = if(count>0, 0, 1) | table _time username off | sort -username +_time | streamstats current=t global=f reset_after="("off=0")" sum(off) as daysoff by username | eval daysoff = if(off=0, 0, daysoff) | timechart span=1d max(daysoff) as daysoff by username
Give this a try. This should give you times when the username didn't work for at least 1 day. My need some tweak if you want to list the dates when user was absent.
index=summary source=daily_kerberos_summary success=T | bucket _time span=1d | stats count by username _time | streamstats current=f window=1 values(_time) as prev_time by username | eval daysNotWorked=((_time-prev_time)/86400) -1 | where daysNotWorked>0 | eval lastWorkedOn=strftime(prev_time,"%F %T")