Splunk Search

Summary of stats from multiple events for each identifier based on specific columns by combining

pavanml
Explorer

Hi.. I have a dataset with each identifier having multiple events. Each event can have a TransactionType which can have one of the two values (Solution or Applied). And each event will have a Status with one of the 3 values (Success/Failure/Exception). I need to generate a summary statistics with one row for each identifier. And counts of Success/Failure/Exception for each of the two transactiontype values and this has to come in the columns as shown in the Image.

alt text

Tags (2)
0 Karma
1 Solution

manjunathmeti
SplunkTrust
SplunkTrust

hi @pavanml,

Count events using stats command and use xyseries command to format results. Try this query:

index = <index_name>
| stats count by Identifier, TransactionType, Status 
| eval TransactionType = TransactionType." (".Status.")" 
| xyseries Identifier, TransactionType, count 
| fillnull value=0

Sample query:

| makeresults 
| eval _raw="Identifier, TransactionType, Status
 1234, Solution, Success
 1234, Solution, Success
 1234, Applied, Fail
 4567, Solution, Fail
 4567, Solution, Excepetion" 
| multikv forceheader=1 
| stats count by Identifier, TransactionType, Status 
| eval TransactionType = TransactionType." (".Status.")" 
| xyseries Identifier, TransactionType, count 
| fillnull value=0

View solution in original post

manjunathmeti
SplunkTrust
SplunkTrust

hi @pavanml,

Count events using stats command and use xyseries command to format results. Try this query:

index = <index_name>
| stats count by Identifier, TransactionType, Status 
| eval TransactionType = TransactionType." (".Status.")" 
| xyseries Identifier, TransactionType, count 
| fillnull value=0

Sample query:

| makeresults 
| eval _raw="Identifier, TransactionType, Status
 1234, Solution, Success
 1234, Solution, Success
 1234, Applied, Fail
 4567, Solution, Fail
 4567, Solution, Excepetion" 
| multikv forceheader=1 
| stats count by Identifier, TransactionType, Status 
| eval TransactionType = TransactionType." (".Status.")" 
| xyseries Identifier, TransactionType, count 
| fillnull value=0

pavanml
Explorer

Thanks for the response. Also I have an additional challenge along with this which I have posted at below link. If possible please clarify:
https://answers.splunk.com/answers/813163/how-to-combine-an-xyseries-output-with-other-aggre.html?mi...

0 Karma

richgalloway
SplunkTrust
SplunkTrust

This was a good challenge. To make sure all columns are present in the results I created a lookup table called "AllColumns.csv" that consists of all combinations of TransactionType and Status like so:

Identifier TransactionType Status
0000    solution    success
0000    solution    failure
0000    solution    exception
0000    applied success
0000    applied failure
0000    applied exception

This dummy data is filtered out near the end of the query.

<your search for data> 
`comment("Read in dummy data to make sure all columns are displayed"`
| inputlookup append=true allColumns.csv 
`comment("Merge the TransactionType and Status fields")`
| strcat TransactionType "(" Status ")" transStat 
`comment("Count the results")`
| stats count by Identifier,transStat 
`comment("Convert the stats output into a table")`
| xyseries Identifier transStat count 
`comment("Fill in missing values")`
| fillnull value=0 
`comment("Remove the dummy data")`
| where identifier!="0000" 
`comment("Display the results in the desired order")`
| table identifier "Solution(Success)", "Solution(Failure)", "Solution(Exception)", "Applied(Success)", "Applied(Failure)", "Applied(Exception)"
---
If this reply helps you, Karma would be appreciated.

pavanml
Explorer

Thanks for clarifying. Interesting to know the combination of lookup and xyseries !

0 Karma

rmmiller
Contributor

One of the best examples of using xyseries I've ever seen! Bravo!

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