Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Splunk Search

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Community
- :
- Splunk Answers
- :
- Using Splunk
- :
- Splunk Search
- :
- How to edit my search to find the sum of delta for...

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark Topic
- Subscribe to Topic
- Mute Topic
- Printer Friendly Page

- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

rhfiberlight

Engager

02-15-2017
09:22 AM

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!

- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

DalJeanis

SplunkTrust

02-15-2017
04:36 PM

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.

- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

somesoni2

Revered Legend

02-15-2017
12:04 PM

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

- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

rhfiberlight

Engager

02-15-2017
01:27 PM

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"

State of Splunk Careers

Find out what your skills are worth!

Read the report >