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

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

russell120
Communicator

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

0 Karma
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...