How to create dynamic columns based on calculated data?

[FeatureCreeep]

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?

[woodcock · Accepted solution]

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

[FeatureCreeep]

Thanks. Worked like a charm. I guess I need to spend more time reading up on stats vs chart.

[sideview]

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

[woodcock]

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.

[sideview]

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.

[woodcock]

Yes, in that case, you are absolutely correct.