How to create multisearch with different transactions times and find a distinct average time for each transaction?

[amaralt808]

Hello Friends,

I have an interesting query that I would like help on.

I have three transactions that we are tracking and I would like to create a graph that has the three transaction time categories and their averages.

I am able to graph the three graphs together, and I can do their average individually, but I need help combining them together.

My code to show all of the different graphs are:

|multisearch
[search
(
index="a" addinventory InboundInventoryChangeElement
)
| eval addTime = if(actionelementname=="AddInventory",strftime(strptime(length,"%H:%M:%S.%f"),"%S.%f") ,length)
|where addTime>0
]
[search
(
index="a" SWAPinventory InboundInventoryChangeElement
)
| eval swapTime = if(actionelementname=="SwapInventory",strftime(strptime(length,"%H:%M:%S.%f"),"%S.%f") ,length)
|where swapTime>0
]
[search
(
index="a" removeinventory InboundInventoryChangeElement
)
| eval removeTime = if(actionelementname=="RemoveInventory",strftime(strptime(length,"%H:%M:%S.%f"),"%S.%f") ,length)
|where removeTime>0
]
|table _time, addTime, swapTime, removeTime

And here is my search for the averages.

index="a" addinventory InboundInventoryChangeElement | eval addTime = strftime(strptime( length,"%H:%M:%S.%f"),"%S.%f")
|where addTime>0| table _time, addTime
| join [ search index="a" addinventory InboundInventoryChangeElement
| eval addTime = strftime(strptime( length,"%H:%M:%S.%f"),"%S.%f")
|where addTime>0
|stats avg(addTime) as AverageAddTime]

The other two searches are the exact same except it the variables are different for the add, swap, and remove.

Any help would be greatly appreciated! 

Also, if there is an easier way rather than joins and multisearches, please let me know!

Thank you!!!

[richgalloway · Accepted solution]

I think you can do that in a single query.  No need for multisearch.

index="a" (addinventory OR SWAPinventory OR removeinventory ) InboundInventoryChangeElement
| eval addTime = if(actionelementname=="AddInventory",strftime(strptime(length,"%H:%M:%S.%f"),"%S.%f"), null())
| eval swapTime = if(actionelementname=="SwapInventory",strftime(strptime(length,"%H:%M:%S.%f"),"%S.%f"), null())
| eval removeTime = if(actionelementname=="RemoveInventory",strftime(strptime(length,"%H:%M:%S.%f"),"%S.%f"), null())
| where (isnotnull(addTime) OR isnotnull(swapTime) OR isnotnull(removeTime))
| fields _time, addTime, swapTime, removeTime
| stats avg(addTime) as AverageAddTime, avg(swapTime) as AverageSwapTime, avg(removeTime) as AverageRemoveTime

I used nulls rather than zeroes for missing values so they're not included in the averages.

[amaralt808]

@richgalloway 

Thank you so much!!

I was able to implement your solution as a join. This is a problem that I ran into with splunk previously where if you do a stats avg then it collapses your original field into nothing and only shows the average. I was able to do a join and it works now!

For reference here is the solution that works:

index="a" (addinventory OR SWAPinventory OR removeinventory ) InboundInventoryChangeElement
| eval addTime = if(actionelementname=="AddInventory",strftime(strptime(length,"%H:%M:%S.%f"),"%S.%f"), null())
| eval swapTime = if(actionelementname=="SwapInventory",strftime(strptime(length,"%H:%M:%S.%f"),"%S.%f"), null())
| eval removeTime = if(actionelementname=="RemoveInventory",strftime(strptime(length,"%H:%M:%S.%f"),"%S.%f"), null())
| where (isnotnull(addTime) OR isnotnull(swapTime) OR isnotnull(removeTime))
| table _time, addTime, swapTime, removeTime
| join[ search index="a" (addinventory OR SWAPinventory OR removeinventory ) InboundInventoryChangeElement
| eval addTime = if(actionelementname=="AddInventory",strftime(strptime(length,"%H:%M:%S.%f"),"%S.%f"), null())
| eval swapTime = if(actionelementname=="SwapInventory",strftime(strptime(length,"%H:%M:%S.%f"),"%S.%f"), null())
| eval removeTime = if(actionelementname=="RemoveInventory",strftime(strptime(length,"%H:%M:%S.%f"),"%S.%f"), null())
| stats avg(addTime) as AverageAddTime, avg(swapTime) as AverageSwapTime, avg(removeTime) as AverageRemoveTime
]

[richgalloway]

If you need to preserve other fields then use eventstats instead of stats.  Try to avoid joins because they don't perform well and have limits.

[amaralt808]

@richgalloway 

I had no idea! When i used Eventstats it worked beautifully without the join. Thank you from the bottom of my heart!