Splunk Search

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

amaralt808
Path Finder

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!!!

Labels (4)
Tags (1)
0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

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.

---
If this reply helps you, Karma would be appreciated.

View solution in original post

richgalloway
SplunkTrust
SplunkTrust

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.

---
If this reply helps you, Karma would be appreciated.

amaralt808
Path Finder

@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
SplunkTrust
SplunkTrust

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.

---
If this reply helps you, Karma would be appreciated.

amaralt808
Path Finder

@richgalloway 

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

0 Karma
Get Updates on the Splunk Community!

Brains, Bytes, and Boston: Learn from the Best at .conf25

When you think of Boston, you might picture colonial charm, world-class universities, or even the crack of a ...

Splunk AppDynamics Agents Webinar Series

Mark your calendars! On June 24th at 12PM PST, we’re going live with the second session of our Splunk ...

SplunkTrust Application Period is Officially OPEN!

It's that time, folks! The application/nomination period for the 2025 SplunkTrust is officially open! If you ...