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!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...