Dashboards & Visualizations

Difference of two columns with dynamic names

rajashekar_s
Path Finder

I am looking for a way to find the difference between two columns which are dynamic in values(time based).
Here is how the query looks like-
index=_internal
| eval weeknum=strftime(time_field,"%Y-%V")
| chart count over sourcetype by weeknum
| addtotals 2*
| sort- Total
| head 4

Sample table
sourcetype 2018-03 2018-04 2018-05 2018-06 Total
a 2 3 4 5 14
b 2 1 3 5 11
c 0 0 4 5 9
d 1 1 2 3 7

I am looking to calculate the difference between column 2 (2018-03) and column 5 (2018-06).
As the time picker can be given with any input, the weeknum is dynamic and I need the way to calculate difference with the dynamic names of the column.

0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

Try this alternate implementation (Try to add one line at a time to your search to understand query functionality)

index=_internal
| eval weeknum=strftime(time_field,"%Y-%V")
| stats count by sourcetype weeknum
| streamstats count as sno by sourcetype
| appendpipe [| where sno=1 OR sno=4| chart values(count) over sourcetype by sno | eval count='1'-'4' | eval weeknum="Change" | table sourcetype weeknum count ]
| xyseries sourcetype weeknum count
| addtotals 2*

View solution in original post

somesoni2
SplunkTrust
SplunkTrust

Try this alternate implementation (Try to add one line at a time to your search to understand query functionality)

index=_internal
| eval weeknum=strftime(time_field,"%Y-%V")
| stats count by sourcetype weeknum
| streamstats count as sno by sourcetype
| appendpipe [| where sno=1 OR sno=4| chart values(count) over sourcetype by sno | eval count='1'-'4' | eval weeknum="Change" | table sourcetype weeknum count ]
| xyseries sourcetype weeknum count
| addtotals 2*

rajashekar_s
Path Finder

This technically works for me and is what I was looking for. But the time period is actually not a standard 4 weeks duration. The end user can change it to any duration.
Is it possible to incorporate dynamic weeknum too?

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Assuming you always want difference of first weeknum and last (recent) weeknum, so try this variation:

index=_internal
 | eval weeknum=strftime(time_field,"%Y-%V")
 | stats count by sourcetype weeknum
 | streamstats count as sno by sourcetype
 | eventstats max(sno) as max by sourcetype
 | appendpipe [| where sno=1 OR sno=max | eval sno=if(sno=1,"first","recent") | chart values(count) over sourcetype by sno | eval count='first'-'recent' | eval weeknum="Change" | table sourcetype weeknum count ]
 | xyseries sourcetype weeknum count
 | addtotals 2*
0 Karma

rajashekar_s
Path Finder

That worked. Thanks a lot

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...