topic Re: How to edit my search to find the sum of delta for multiple field variations? in Splunk Search

How to edit my search to find the sum of delta for multiple field variations?

rhfiberlight — Wed, 15 Feb 2017 17:22:03 GMT

I have a search that works correctly when specific field values are entered but would like to create a report for the same search for all of the field variations. This would be similar to different hosts however it is using fields that are populated by an autolookup. Here is the search syntax that works correctly:

tag=TagA field1=A field2=B Field3=C | eval flt=if(ds_name=="X", ds_value, NULL) , flr=if(ds_name=="Y", ds_value, NULL)| sort 0 _time | delta flt as flt_delta | delta flr as flr_delta | eval min=0 | eval fltd_corrected=if(flt_delta<0,NULL,flt_delta) | eval flrd_corrected=if(flr_delta<0,NULL,flr_delta) | stats sum(fltd_corrected) as fltd_total sum(flrd_corrected) as flrd_total | eval fls=(fltd_total-flrd_total) | eval flp=(flrd_total/fltd_total)*100

This outputs fltd_total flrd_total flp fls

I would like to change this to something close to: (this does not work because the delta does not know what specific events to use.

tag=TagA  | eval flt=if(ds_name=="X", ds_value, NULL) , flr=if(ds_name=="Y", ds_value, NULL)| sort 0 _time | delta flt as flt_delta | delta flr as flr_delta | eval min=0 | eval fltd_corrected=if(flt_delta<0,NULL,flt_delta) | eval flrd_corrected=if(flr_delta<0,NULL,flr_delta) | stats sum(fltd_corrected) as fltd_total sum(flrd_corrected) as flrd_total by field1 field2 Field3 | eval fls=(fltd_total-flrd_total) | eval flp=(flrd_total/fltd_total)*100

I know there are similar questions for multiple hosts but cannot wrap my head around it, thank you so much for any help!

Re: How to edit my search to find the sum of delta for multiple field variations?

somesoni2 — Wed, 15 Feb 2017 20:04:11 GMT

Give this a try

tag=TagA  | eval flt=if(ds_name=="X", ds_value, NULL) , flr=if(ds_name=="Y", ds_value, NULL)| sort 0 field1 field2 Field3 _time | streamstats current=f window=1 values(flt) as flt_delta values(flr) as flr_delta by field1 field2 Field3 | eval flt_delta=flt-flt_delta |eval flr_delta=flr-flr_delta | eval min=0 | eval fltd_corrected=if(flt_delta<0,NULL,flt_delta) | eval flrd_corrected=if(flr_delta<0,NULL,flr_delta) | stats sum(fltd_corrected) as fltd_total sum(flrd_corrected) as flrd_total by field1 field2 Field3 | eval fls=(fltd_total-flrd_total) | eval flp=(flrd_total/fltd_total)*100

Re: How to edit my search to find the sum of delta for multiple field variations?

rhfiberlight — Tue, 29 Sep 2020 12:52:22 GMT

This still did not work for me. I left out an important piece of information, the ds_name=X does not come in the same event that ds_name=Y comes in as. Here is an example of the data coming into Splunk:

Field1="A" Field2="B" Field3="C" t="1487193653" ds_name="X" ds_value="1"
Field1="A" Field2="B" Field3="C" t="1487193653" ds_name="Y" ds_value="1"
Field1="A" Field2="B" Field3="C" t="1487193653" ds_name="X" ds_value="2"
Field1="A" Field2="B" Field3="C" t="1487193653" ds_name="Y" ds_value="2"

Re: How to edit my search to find the sum of delta for multiple field variations?

DalJeanis — Thu, 16 Feb 2017 00:36:19 GMT

This generates some test data

| makeresults 1 | eval tag="TagA" | eval field1="A" | eval field2="B" | eval field3="C" 
| eval ds_name= "X X X Y Y X X Y Y Y X X X X Y Y" | makemv ds_name| mvexpand ds_name 
| streamstats count as ds_value | eval _time = _time + ds_value

This calculates the deltas

| eval flt=if(ds_name=="X", ds_value, NULL)
| eval flr=if(ds_name=="Y", ds_value, NULL)
| sort 0 _time 
| streamstats last(flt) as newflt, last(flr) as newflr
| delta newflr as flr_delta
| delta newflt as flt_delta
| eval fltd_corrected = case(isnull(flt), NULL, isnull(flt_delta), flt, true(), flt_delta)
| eval flrd_corrected = case(isnull(flr), NULL, isnull(flr_delta), flr, true(), flr_delta)
| table _time tag field1 field2 field3 ds_name ds_value flt fltd_corrected flr flrd_corrected

Generating results that look like this

_time                             tag     field1  field2  field3  ds_name ds_value  flt  fltd flr  flrd 
2017-02-16T00:30:33.000+0000      TagA    A       B       C       X       1         1    1              
2017-02-16T00:30:34.000+0000      TagA    A       B       C       X       2         2    1              
2017-02-16T00:30:35.000+0000      TagA    A       B       C       X       3         3    1              
2017-02-16T00:30:36.000+0000      TagA    A       B       C       Y       4                   4    4    
2017-02-16T00:30:37.000+0000      TagA    A       B       C       Y       5                   5    1    
2017-02-16T00:30:38.000+0000      TagA    A       B       C       X       6         6    3              
2017-02-16T00:30:39.000+0000      TagA    A       B       C       X       7         7    1              
2017-02-16T00:30:40.000+0000      TagA    A       B       C       Y       8                   8    3    
2017-02-16T00:30:41.000+0000      TagA    A       B       C       Y       9                   9    1    
2017-02-16T00:30:42.000+0000      TagA    A       B       C       Y       10                  10   1    
2017-02-16T00:30:43.000+0000      TagA    A       B       C       X       11        11   4              
2017-02-16T00:30:44.000+0000      TagA    A       B       C       X       12        12   1              
2017-02-16T00:30:45.000+0000      TagA    A       B       C       X       13        13   1              
2017-02-16T00:30:46.000+0000      TagA    A       B       C       X       14        14   1              
2017-02-16T00:30:47.000+0000      TagA    A       B       C       Y       15                  15   5    
2017-02-16T00:30:48.000+0000      TagA    A       B       C       Y       16                  16   1

As you can see, the corrected deltas fltd/flrd are correct to the data. I made the assumption that the first delta of each kind would use the first flr/flt value.

I'm operating on the assumption that you were trying to get rid of negative deltas because they did not represent valid data, being an artifact of the search and/or the delta command.

Now, I couldn't make out what the thinking was behind the rest of the query that you attempted to code. I understand that you are attempting to compare the sums of the flr and flt deltas, but I don't understand what they actually represent, so I'm at a loss to write sensible code. The sum of the deltas, based on the above scenario, is just the final value.

By the way, when you say "all of the field variations", if you are trying to generate the statistics for each separate combination of field1, field2, and field3 values, then there are some tweaks that need to get made to the sort and streamstats commands.