I have transaction records that are pretty clear.
OperationType=singon Client=abc IsSuccess=1
OperationType=changePassword Client=xyz IsSuccess=0
...
Based on these records I want to see the success % rate by OperationType and Client
sourcetype=mydata | stats count as Total, sum(IsSuccess) as Succeeded by OperationType, Client | eval SuccessRate=(Succeeded/Total)*100 | fields - Total, Succeeded
That outputs:
OperationType | Client | SuccessRate
signon | abc | 95
signon | xyz | 97
changepassword| abc | 89
changepassword| xyz | 93
This creates a lot of rows, one for every Client, OperationType combination. Instead, I'd like the chart to have a column for every client and a row for each OperationType like this:
OperationType | abc | xyz
signon | 95 | 97
changepassword| 89 | 93
Any idea how to approach this?
Like this:
sourcetype=mydata | stats count AS Total sum(IsSuccess) AS Succeeded BY OperationType Client | eval SuccessRate=(Succeeded/Total)*100 | fields - Total, Succeeded | chart Avg(SuccessRate) AS SuccessRate BY OperationType Client
Like this:
sourcetype=mydata | stats count AS Total sum(IsSuccess) AS Succeeded BY OperationType Client | eval SuccessRate=(Succeeded/Total)*100 | fields - Total, Succeeded | chart Avg(SuccessRate) AS SuccessRate BY OperationType Client
Thanks. Worked like a charm. I guess I need to spend more time reading up on stats vs chart.
It seems strange and confusing to do a chart on the end here instead of xyseries. the chart command here is calculating average successrate for each unique combination of OperationType and Client. However the stats command before it guarantees that there will be only one such row for each such combination. So it takes the average of 1 record. Which ends up working out OK, but it's very confusing. It seems a less confusing way to get the same end result is to do this:
sourcetype=mydata | stats count AS Total sum(IsSuccess) AS Succeeded BY OperationType Client | eval SuccessRate=(Succeeded/Total)*100 | xyseries OperationType Client SuccessRate
You are quite correct EXCEPT if the data in the file is not as unique as it appears that it should be. If we know it is 1-to-1 then xyseries
will be slightly more direct/efficient.
coming out of the stats command, the BY OperationType Client
in the stats command will guarantee that no matter what the events look like, there will only one row coming out per combination of OperationType and Client. Therefore each row coming into the other chart command will have precisely one row per unique combination of OperationType and Client. That's what I meant.
Yes, in that case, you are absolutely correct.