Getting Data In

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

russell120
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

somesoni2
Revered Legend

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

somesoni2
Revered Legend

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
0 Karma

russell120
Communicator

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

0 Karma
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 ...