topic Re: How to create dynamic columns based on calculated data? in Splunk Search

How to create dynamic columns based on calculated data?

FeatureCreeep — Mon, 20 Mar 2017 01:09:13 GMT

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?

Re: How to create dynamic columns based on calculated data?

woodcock — Mon, 20 Mar 2017 01:19:01 GMT

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

Re: How to create dynamic columns based on calculated data?

FeatureCreeep — Mon, 20 Mar 2017 01:44:47 GMT

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

Re: How to create dynamic columns based on calculated data?

sideview — Mon, 20 Mar 2017 18:18:58 GMT

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

Re: How to create dynamic columns based on calculated data?

woodcock — Mon, 20 Mar 2017 20:27:06 GMT

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.

Re: How to create dynamic columns based on calculated data?

sideview — Mon, 20 Mar 2017 22:37:30 GMT

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.

Re: How to create dynamic columns based on calculated data?

woodcock — Mon, 20 Mar 2017 23:17:43 GMT

Yes, in that case, you are absolutely correct.