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