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.
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
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
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...
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)"
Thanks for clarifying. Interesting to know the combination of lookup and xyseries !
One of the best examples of using xyseries I've ever seen! Bravo!