How do I edit my search to find how many sessions are missing a certain log entry over time?

[Clutchplate]

I am logging events of my application by session. i.e whenever the app is started, I generate a new SessionId and then generate events. Two of the events generated are "Startup" and "Shutdown". Each event has a StartTime field, too.

I am trying to write a search that will look at all sessions and show me how many sessions did not have a Shutdown entry over time. Essentially letting me see how many crashes I'm getting over time.

I've come close with this:

index=foo source=barDB  | WHERE EventName="Shutdown" OR EventName="Startup" | BUCKET SessionId |STATS count BY SessionID

this gives me a list with a sessionID column and a count column containing either 1 or a 2. I now want to chart the ones that have a 1 over time.

Any ideas?

[gcato]

Hi Clutchplate,

Not quite sure what you mean by chart in your question (a graph or a table). If you are trying to count the still active sessions (count=1) over time then something like the following will work...

index=foo source=barDB  EventName="Shutdown" OR EventName="Startup" 
 | transaction SessionID | where eventcount=1
 | reverse |streamstats count as tally | timechart values(tally) as "count session still active"

Maybe you could clarify what you're trying to visualize.

[Clutchplate]

Yes, active sessions over time is correct. Your search yields completely different results to Renjith's answer below, though. Trying to understand the two approaches....

[renjith_nair · Accepted solution]

Try something similar

index="foo" sourcetype="barDB"|where EventName="Shutdown" OR EventName="StartUp"|stats first(EventName) AS recent_event first(_time) AS _time by  SessionId|search recent_event="StartUp"|timechart count

the bucket (bin) on SessionId might divide that into a range.

Try executing only below part if you want to know the result

    index="foo" sourcetype="barDB"|where EventName="Shutdown" OR EventName="StartUp"|stats first(EventName) AS recent_event first(_time) AS _time by  SessionId

[renjith_nair]

Did it work for your requirement?

[Clutchplate]

Yes, this worked great.