How to track state changes for RDP sessions and generate a report show durations with start and end times?

[sergiupopescu]

So I am looking for help, I guess I just found something I can't do with Splunk... and I know I'm wrong, only I can't prove it!
It may be related to me (as I am somehow new to Splunk), so please help. Here goes:

Basically, I have events like:

Time     | SessionId | State
01:00:00 | 7         | Active
01:01:00 | 7         | Disconnected
01:02:00 | 7         | Disconnected
01:03:00 | 7         | Active

What I would like is to generate a report to show actual session durations (with start time and end time), something like this:

Session 7 | State=Active        | Start 01:00:00 | End 01:01:00
Session 7 | State=Disconnected  | Start 01:01:00 | End 01:03:00
Session 7 | State=Active        | Start 01:03:00 | End ....

I tried with streamstats, but no go, and unfortunately I am in a lack of ideas, so any help would be highly appreciated!

[sundareshr]

Try something like this (this is untested, so you will have to tweak it to get it to work)

your_search | reverse | eval ct=if(match(_raw, "Active"), 1, 0) | streamstats window=1 count(ct) as uid min(_time) as start max(_time) as end by SessionId | table _time SessionId start end State

[sergiupopescu]

Thanks, but ... it won't work.
Let's take the following scenario: Session ID 6 is Active from 01:00:00 to 01:01:00 (1 minute)
It goes in Disconnect from 01:01:00 to 01:03:00 (2 minutes)
It goes back to active from 01:03:00 to 01:10:00 (7 minutes).
What I need is to identify the State change and somehow to measure earliest and latest time for the events until state change, and then go on.... with the rest.
P.S. I already got to a similar result using streamstats, but I tried nevertheless your proposed answer, with no result ...

[sundareshr]

I didn't think about the start/end for disconnected state. This should do it.

| sort sid | streamstats window=2 earliest(time) as start latest(time) as end by sid | table time sid state start end | eval end=if(end=start, strftime(now(), "%m/%d/%Y %H:%M"), end)