topic Re: Summary of stats from multiple events for each identifier based on specific columns by combining in Splunk Search

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

pavanml — Thu, 26 Mar 2020 14:02:03 GMT

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.

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

richgalloway — Thu, 26 Mar 2020 15:57:31 GMT

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)"

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

rmmiller — Thu, 26 Mar 2020 16:07:53 GMT

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

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

manjunathmeti — Thu, 26 Mar 2020 16:15:29 GMT

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

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

pavanml — Fri, 27 Mar 2020 13:53:09 GMT

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?minQuestionBodyLength=80

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

pavanml — Fri, 27 Mar 2020 13:54:59 GMT

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