Splunk Search

How to create dynamic columns based on calculated data?

FeatureCreeep
Path Finder

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?

1 Solution

woodcock
Esteemed Legend

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

View solution in original post

woodcock
Esteemed Legend

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

View solution in original post

FeatureCreeep
Path Finder

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

0 Karma

sideview
SplunkTrust
SplunkTrust

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
0 Karma

woodcock
Esteemed Legend

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.

0 Karma

sideview
SplunkTrust
SplunkTrust

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.

0 Karma

woodcock
Esteemed Legend

Yes, in that case, you are absolutely correct.

0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!