How do I use the difference between multiple field values to create a new field in Splunk?

[russell120]

I have an example lookup file below:

Week           Site_Visits                   
Week1Oct          500
Week2Oct          1300
Week3Oct          400
Week4Oct          2100
Week1Sep          1400

How do I create a new field to show the differences between each Site_Visit value against the first one ( 500)? Each week I will be adding a new row of data, so it should be scalable. It should look like this:

Week           Site_Visits         Progress           
Week1Oct          500                 0     
Week2Oct         1300                800 
Week3Oct          400               -100
Week4Oct         2100               1700
Week1Sep         1400               1100

[somesoni2]

You would use Splunk delta command for this. Read this for more information
http://docs.splunk.com/Documentation/Splunk/7.2.0/SearchReference/Delta

Usage:

your current search with fields Week and Site_Visits
| delta Site_Visits as Progress

[russell120]

|delta Site_Visits as Progress returned the difference between each Site_Visits value and the Site_Visits value before directly before it. It did not return the difference between each Site_Visits value and the very first Site_Visits values, which in this case is 500. I need a field that returns 1300-500, 400-500, 2100-500, etc.

[493669 · Accepted solution]

Hi @russell120,
try this :-

|eventstats first(Site_Visits) as first|eval progress=Site_Visits-first|fields - first

try this run anywhere example as per your sample data-

| makeresults |eval week="Week1Oct", Site_Visits =500
|append[| makeresults |eval week="Week2Oct", Site_Visits =1300]
|append[| makeresults |eval week="Week3Oct", Site_Visits =400]
|append[| makeresults |eval week="Week4Oct", Site_Visits =2100]
|append[| makeresults |eval week="Week5Oct", Site_Visits =1400]
|eventstats first(Site_Visits) as first|eval progress=Site_Visits-first|fields - first

[russell120]

EDIT: |eventstats first(Site_Visits) as first|eval progress=Site_Visits-first|fields - first

This is the line that made it work. Thanks!

[493669]

@russell120 , did you try this? are you getting expected output?

[russell120]

@493669 I did, and I don't think its compatible with my query. My query is here: https://answers.splunk.com/answers/692485/how-to-populate-a-new-field-with-the-differences-o.html

It creates a table that looks the same as my question above but with different field names.