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

[pavanml]

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.

[manjunathmeti · Accepted solution]

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]

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

[richgalloway]

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

[pavanml]

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

[rmmiller]

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