Splunk Search

Relative reference to columns

aaron_sakovich
Path Finder

There's something I'm just not getting today...

I've got a chart command that generates results from a series of searches, evals, and other processes. The net result is a nice little chart with results that looks like this:

Location     2019    2020    Delta
Main          980   1268    29.39 %    

The 2019 and 2020 are indeed years. My issue is that Delta is calculated based on those 2 columns as

 eval Delta=(('2020'-'2019')/'2019'*100)

This is fine for this year, but of course it means we'd have to edit this dashboard again next year.

How do I reference the relative column positions rather than the column names, or otherwise glean the column names from the dynamic data, in order to crunch the Delta value automagically?

0 Karma
1 Solution

somesoni2
Revered Legend

Try something like this

Your current chart command giving Location data for Year i.e. fields Location, <last year>,<current year>
| eval Delta=null()
| foreach 2* [| eval Delta=if(isnull(Delta),'<<FIELD>>' ,(('<<FIELD>>'-'Delta')*100/'Delta' ]

Assuming your output contains fields Location, 2019, 2020 (in the very same order, order is important here). Then the foreach will run 2 passes- one for 2019 and one for 2020. When it runs for first column '2019', the Delta is null, so '2019' is assigned to Delta. On 2nd pass for '2020', Delta is not null, so it'll calculate the % Delta based on '2020' as compared to current Delta which is '2019' value.

View solution in original post

0 Karma

somesoni2
Revered Legend

Try something like this

Your current chart command giving Location data for Year i.e. fields Location, <last year>,<current year>
| eval Delta=null()
| foreach 2* [| eval Delta=if(isnull(Delta),'<<FIELD>>' ,(('<<FIELD>>'-'Delta')*100/'Delta' ]

Assuming your output contains fields Location, 2019, 2020 (in the very same order, order is important here). Then the foreach will run 2 passes- one for 2019 and one for 2020. When it runs for first column '2019', the Delta is null, so '2019' is assigned to Delta. On 2nd pass for '2020', Delta is not null, so it'll calculate the % Delta based on '2020' as compared to current Delta which is '2019' value.

0 Karma

aaron_sakovich
Path Finder

So very close -- only thing missing was a couple closing parens!

 | eval Delta=null()
 | foreach 2* [| eval Delta=if(isnull(Delta),'<<FIELD>>' ,(('<<FIELD>>'-'Delta')*100/'Delta')) ]

TYVM!!! I'm happy for another 979 years!

0 Karma
Get Updates on the Splunk Community!

Take Your Breath Away with Splunk Risk-Based Alerting (RBA)

WATCH NOW!The Splunk Guide to Risk-Based Alerting is here to empower your SOC like never before. Join Haylee ...

SignalFlow: What? Why? How?

What is SignalFlow? Splunk Observability Cloud’s analytics engine, SignalFlow, opens up a world of in-depth ...

Federated Search for Amazon S3 | Key Use Cases to Streamline Compliance Workflows

Modern business operations are supported by data compliance. As regulations evolve, organizations must ...