Splunk Search

How to find a difference of a column field by date

bhava2704
New Member
Namepercdate
xxx9028-Dec-23
yyy9128-Dec-23
zzz9228-Dec-23
xxx9629-Dec-23
yyy9729-Dec-23
zzz9829-Dec-23

 

i want to calculate the difference between perc column value based on date,

 

for example, xxx have 90 in perc column for 28 dec 2023 and 96 for 29 dec 2023.  96-90= 6 will be the output .can you please help me with solution for my query.

additional query is i want to subtract the current date perc with yesterday date perc value. please assist me on this

Labels (2)
0 Karma

tscroggins
Influencer

Hi @bhava2704,

Given your sample data:

| makeresults format=csv data="Name,perc,date
xxx,90,28-Dec-23
yyy,91,28-Dec-23
zzz,92,28-Dec-23
xxx,96,29-Dec-23
yyy,97,29-Dec-23
zzz,98,29-Dec-23"
| eval _time=strptime(date, "%d-%b-%y")

you can use streamstats, timechart and autoregress, timechart and timewrap, etc. The timewrap command depends on the search earliest and latest times, so I've set them to 2023-12-28 and 2023-12-29, respectively.

When using streamstats, be mindful of the event order. In the example, your results are sorted by Date/_time ascending. In a normal event search, your results will be sorted by _time descending, and you'll need to adjust streamstats etc. arguments accordingly.

| streamstats global=f window=2 first(perc) as perc_p1 by Name
| eval delta_perc=perc-perc_p1

or

| timechart fixedrange=f span=1d values(perc) by Name
| autoregress xxx p=1
| autoregress yyy p=1
| autoregress zzz p=1
| eval delta_xxx=xxx-xxx_p1, delta_yyy=yyy-yyy_p1, delta_zzz=zzz-zzz_p1

or

| timechart fixedrange=f span=1d values(perc) by Name
| timewrap 1d
| eval delta_xxx=xxx_latest_day-xxx_1day_before, delta_yyy=yyy_latest_day-yyy_1day_before, delta_zzz=zzz_latest_day-zzz_1day_before

 

ITWhisperer
SplunkTrust
SplunkTrust

Do you mean something like this?

| stats range(perc) as range by Name
0 Karma

tscroggins
Influencer

--with the caveat that range() values are always positive, i.e. abs(x-y).

0 Karma

marnall
Builder

The delta command seems like it goes in the right direction, but the only problem is that it can't be told to do separate deltas based on the values of other fields. If you don't have too many different Name fields, you could separate the perc fields into differently named fields and then do deltas on each one. This also requires you to sort by Name and then sort back to whichever your preferred sorting order is after the delta operation.

| sort Name
| eval perc_xxx = if(Name="xxx",perc,perc_xxx)
| eval perc_yyy = if(Name="yyy",perc,perc_yyy)
| eval perc_zzz = if(Name="zzz",perc,perc_zzz)
| delta perc_xxx as delta_perc
| delta perc_yyy as delta_perc
| delta perc_zzz as delta_perc
| fields - perc_*
| sort date

 

Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...