I have a base search query which is quite complicated and I want to use that to calculate multiple stats and display all of them on a single chart. I am having a hard time to solve this problem in Splunk. When searched, found multiple options to do it but nothing really works.
1. Use append and sub search - need the output from base search. not a neat way to copy the big and complicated search again and again
2. Cannot just refer to base search again in sub searches
High level overview of the problem I am trying to solve:
base search returns
Time --- #Action --------- #id1 ---------- #id2 ------------- #id3
_time, event A send, sessionAid
_time, event B send, -------------- sessionBid
_time, event A recv, sessionAid
_time, event B recv, --------------- sessionBid
_time, event C send, ---------------------------------- sessionCid
_time, event C recv, ---------------------------------- sessionCid
I need to calculate the duration between each event's send and receive. I am using stats command for that.
base search results| eval eventA_send_time = ### | eval eventA_recv_time = ###| stats values(eventA_send_time) as send_time values(eventA_recv_time) as recv_time by sessionAid| mvexpand recv_time | eval A_time = recv_time - send_time
Similarly for Event B and C. This works fine if run individually but I need to display durations of event A, B and C on a chart.
Can someone explain how this can be achieved. Really appreciate your help!!
You have not explained how you use stats to obtain each event's send and receive time. Judging from your high level data representation, what you really want is session duration (sessioId1, sessionId2, ...) grouped by event type (A, B, C, ...). If that is the case, indeed stats provides a cheap method. For simplicity, I further assume that the session IDs are in the same series, i.e., the same ID will not appear in more than one event types. There are ways to workaround this limitation, but it is just nuance.
You also have not explained what kind of chart you are looking for. Here is an example of time chart for average session duration for each type.
base search | rex "event (?<eventType>\w+)"
| stats min(_time) as _time max(_time) as end by sessionId eventType
| eval duration = end - _time
| timechart avg(duration) by eventType
(The above also assume that "event A", "event B", etc., appears literally like that so a regex can extract event type. You'll have to find your own way to extract this if that's not the case.) If there is a chance that session IDs overlap between event types, the simplest way to deal with it is to redefine session ID by sticking event type to session ID to form a new ID that is unique, e.g., | eval sessionId = eventType.sessionId.
I am using eval eventA_send_time = if(action = "event A send",_time,null()) to obtain time for each event send and receive. Same goes for others.
Also not sure how this "| eval sessionId = eventType.sessionId" will work since sessionId is not a single column. I have three different columns for ids.