I am trying to find out details of a remote session. Although the events are the same, they are separate by action (added to a session and removed from a session). I am trying to search the added session then append a search to find a matching session ID with the removed action. I do not want to use a join as I think they are not very efficient. If I manually type in a session for both searches the query works (except it produces 2 rows which I am trying to convert to 1). I have been beating my head with this issue, any help is appreciated!
sourcetype=uag user=bigrichie90 action=added | eval timeAdded=_time | eval sessionAdded=session | head 1 | eval DateAdded=strftime(_time, "%m/%d/%Y %H:%M:%S") | append [search sourcetype=uag user=bigrichie90 action=removed | eval sessionRemoved=session | eval DateRemoved=strftime(_time, "%m/%d/%Y %H:%M:%S") | eval timeRemoved=_time |head 1 ] | where sessionAdded==sessionRemoved <--(where I am trying to match sessions, but is not working)** | eval ts=coalesce(timeAdded,timeRemoved) | eventstats max(ts) as t1 | eventstats min(ts) as t2 | eval timeDiff=tostring(t1-t2,"duration") <--(used to find time duration of session)
| rename stuff and throw into table
sourcetype=uag user=bigrichie90 (action=added OR action=removed) | transaction session
Or if you really don't need anything other than the duration, just
sourcetype=uag user=bigrichie90 (action=added OR action=removed) | stats range(_time) by session
It is not returning any results. I do need other information such as the time they started the session, ended the session, duration, user, etc. I have to use both "added" and "removed" session events to create one row of information.
If you're not getting results, your base search (I simply used yours) is wrong.
Based on the info in your comment, this is how you could retrieve the information you're after:
sourcetype=uag user=bigrichie90 (action=added OR action=removed) | stats earliest(_time) as starttime, latest(_time) as endtime, range(_time) as duration by user,session
That query seemed to work, thanks! Do you know why a field would not be showing up? I have a field called "srcip" in both events and "removalreason" in a removed event. They both will not show values in the search. If I search in verbose mode, the values show up.