Getting Data In

How to populate a new field with the differences of multiple CSVs against a single CSV?

Communicator

The SPL below returns a count from one field in multiple CSVs. At the end, a delta is calculated, comparing each count to the count before it. That difference (or delta) is then displayed as a line chart overlay:

|inputlookup inventory20180815.csv
|stats count(computer_id) as 20180815
|appendcols [|inputlookup inventory20180822.csv | stats count(computer_id) as 20180822]
|appendcols [|inputlookup inventory20180830.csv | stats count(computer_id) as 20180830]
|appendcols [|inputlookup inventory20180904.csv | stats count(computer_id) as 20180904]
|appendcols [|inputlookup inventory20180910.csv | stats count(computer_id) as 20180910]
|appendcols [|inputlookup inventory20180917.csv | stats count(computer_id) as 20180917]
|appendcols [|inputlookup inventory20180924.csv | stats count(computer_id) as 20180924]
|appendcols [|inputlookup inventory20181001.csv | stats count(computer_id) as 20181001]
|appendcols [|inputlookup inventory20181015.csv | stats count(computer_id) as 20181015]
|transpose
|delta "row 1" as "Change over Time"
|rename "row 1" as Dates column as "Weekly Computer Count"

Statistics Table:
alt text

Column Chart w/ Chart Overlay:
alt text

My question is how do I populate a new field so that it takes each count and compares it to the very first count ( |stats count(computer_id) as 20180815)? For example, if I had |stats counts that returned 5, 10, and 20, the new field should return 0, 5, 15 because it calculates 5-5, 10-5, and 20-5. My intent is to use this new field and turn it into a line chart overlay (thus replacing the line chart overlay I currently have).

0 Karma
1 Solution

SplunkTrust
SplunkTrust

Give this a try

|inputlookup inventory20180815.csv
 |stats count(computer_id) as 20180815
 |appendcols [|inputlookup inventory20180822.csv | stats count(computer_id) as 20180822]
 |appendcols [|inputlookup inventory20180830.csv | stats count(computer_id) as 20180830]
 |appendcols [|inputlookup inventory20180904.csv | stats count(computer_id) as 20180904]
 |appendcols [|inputlookup inventory20180910.csv | stats count(computer_id) as 20180910]
 |appendcols [|inputlookup inventory20180917.csv | stats count(computer_id) as 20180917]
 |appendcols [|inputlookup inventory20180924.csv | stats count(computer_id) as 20180924]
 |appendcols [|inputlookup inventory20181001.csv | stats count(computer_id) as 20181001]
 |appendcols [|inputlookup inventory20181015.csv | stats count(computer_id) as 20181015]
 |transpose
 |rename "row 1" as Dates column as "Weekly Computer Count"
| eventstats first(Dates) as first 
| eval "Change over time"=Dates-first | fields - first

View solution in original post

0 Karma

SplunkTrust
SplunkTrust

Give this a try

|inputlookup inventory20180815.csv
 |stats count(computer_id) as 20180815
 |appendcols [|inputlookup inventory20180822.csv | stats count(computer_id) as 20180822]
 |appendcols [|inputlookup inventory20180830.csv | stats count(computer_id) as 20180830]
 |appendcols [|inputlookup inventory20180904.csv | stats count(computer_id) as 20180904]
 |appendcols [|inputlookup inventory20180910.csv | stats count(computer_id) as 20180910]
 |appendcols [|inputlookup inventory20180917.csv | stats count(computer_id) as 20180917]
 |appendcols [|inputlookup inventory20180924.csv | stats count(computer_id) as 20180924]
 |appendcols [|inputlookup inventory20181001.csv | stats count(computer_id) as 20181001]
 |appendcols [|inputlookup inventory20181015.csv | stats count(computer_id) as 20181015]
 |transpose
 |rename "row 1" as Dates column as "Weekly Computer Count"
| eventstats first(Dates) as first 
| eval "Change over time"=Dates-first | fields - first

View solution in original post

0 Karma

Communicator

This does exactly what I asked to have done. Thank you!

0 Karma