Splunk Search

chart sums by month, then difference current month from preceding month

swangertyler
Path Finder

I need to make a table where I have four columns, the group, the current month, the previous month, and the difference between the two. I have the first three columns, however,I cannot figure out how to calculate the difference.


| eval impactduration = floor((impact_stop-impact_start)/60) | bucket _time span=1mon
| stats sum(impactduration) as "Impact duration in Minutes" by product_group, _time
|eval month=strftime(_time, "%b")
| maketable product_group month "Impact duration in Minutes"

The desired result is something like
|product_group|Oct |Sept|difference|
|X | 100 | 90 | -10|
|Y|60 |100|40|

Tags (2)
0 Karma
1 Solution

renjith_nair
SplunkTrust
SplunkTrust

@swangertyler ,

Try

| eval impactduration = floor((impact_stop-impact_start)/60) | bucket _time span=1mon 
| stats sum(impactduration) as impactduration by product_group, _time 
| eval month=strftime(_time, "%b")
| streamstats current=f window=1 last(impactduration) as prev by product_group|eval Difference=prev-impactduration
| maketable product_group,month,impactduration,Difference

You may rename the fields as necessary

Happy Splunking!

View solution in original post

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

@swangertyler ,

Try

| eval impactduration = floor((impact_stop-impact_start)/60) | bucket _time span=1mon 
| stats sum(impactduration) as impactduration by product_group, _time 
| eval month=strftime(_time, "%b")
| streamstats current=f window=1 last(impactduration) as prev by product_group|eval Difference=prev-impactduration
| maketable product_group,month,impactduration,Difference

You may rename the fields as necessary

Happy Splunking!
0 Karma

swangertyler
Path Finder

This works. Thank you @renjith.nar

Now I am trying to figure out how to account for "blanks". I have product_group values that exist in the current month, but not the previous month. Because of this, I have empty values that I would like to fill with zeros. However, I cannot identify where a fillnull fixes my problem.

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

@swangertyler , try using timechart instead of stats which will resolve most of the complications

Just give this a try and compare the results

 | eval impactduration = floor((impact_stop-impact_start)/60) 
 | timechart span=1mon  sum(impactduration) as impactduration by product_group
 |untable _time,product_group,impactduration |sort product_group
 | eval month=strftime(_time, "%b")
 | streamstats current=f window=1 last(impactduration) as prev by product_group
 |eval Difference=prev-impactduration
 | maketable product_group,month,impactduration,Difference
Happy Splunking!
0 Karma

swangertyler
Path Finder

with a little massaging of timechart and add a fillnull, I got it to do what I needed. Thank you for the help. I very much appreciate it.

| eval impactduration = floor((impact_stop-impact_start)/60)
| timechart span=1mon sum(impactduration) as impactduration by product_group useother=f limit=20|fillnull value=0
|untable _time,product_group,impactduration |sort product_group
| eval month=strftime(_time, "%b")
| streamstats current=f window=1 last(impactduration) as prev by product_group
|eval Difference=prev-impactduration
| maketable product_group,month,impactduration,Difference

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 ...