How to edit my search to find the number of continuous days a user did not work if this is not recorded in the logs?

[troyward]

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?

Thanks,

Troy

[somesoni2 · Accepted solution]

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")

[rjthibod]

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