Splunk Search

Calculate and display count variance between same field across unique hosts by another field

New Member

I created a table showing a mv field1 count for the same transactions as they passed through sequential hosts A, B, C, D, E, F. I'm trying to create a similar table, but show the variances between the counts of hosts B and A, hosts C and B, hosts D and C, hosts E and D, hosts F and E, but haven't had any success

Table I have: | chart count(Field1) over host by Field2
host 11/16 11/17 11/18 11/19 11/20
A 1028 112 4765 257 1046
B 1028 112 4765 257 1046
C 1028 112 4765 257 866
D 1028 112 4765 257 866
E 1027 112 4765 257 866
F 1027 112 4765 257 866

Table I want: tried counts+evals, delta, diff
host 11/16 11/17 11/18 11/19 11/20
A 0 0 0 0 0
B 0 0 0 0 0
C 0 0 0 0 180
D 0 0 0 0 0
E 1 0 0 0 0
F 0 0 0 0 0

Any assistance would be much appreciated!

Tags (1)
1 Solution
SplunkTrust

Hi rmercy,

using your provided information, I created a CSV containing this:

host,count,day
A,1028,1
A,112,2
A,4765,3
A,257,4
A,1046,5
B,1028,1
B,112,2
B,4765,3
B,257,4
B,1046,5
C,1028,1
C,112,2
C,4765,3
C,257,4
C,866,5
D,1028,1
D,112,2
D,4765,3
D,257,4
D,866,5
E,1027,1
E,112,2
E,4765,3
E,257,4
E,866,5
F,1027,1
F,112,2
F,4765,3
F,257,4
F,866,5

based on this CSV I was able to use this search and get the delta of each of the count files:

base search to search for the above file sourcetype="csv"
| stats values(count) AS count by day extracted_host
| streamstats current=f last(count) as previouscount by day
| eval delta=previouscount-count
| chart values(count) AS count values(delta) AS delta over extracted_host by day

The result will look like this:

Hope this helps ...

cheers, MuS

New Member

Thanks for the quick response, MuS! I created a summary index (which I needed anyway) that included fields consistent with your csv, then used the search text you provided to generate the variance table. Much appreciated!

SplunkTrust

Hi rmercy,

using your provided information, I created a CSV containing this:

host,count,day
A,1028,1
A,112,2
A,4765,3
A,257,4
A,1046,5
B,1028,1
B,112,2
B,4765,3
B,257,4
B,1046,5
C,1028,1
C,112,2
C,4765,3
C,257,4
C,866,5
D,1028,1
D,112,2
D,4765,3
D,257,4
D,866,5
E,1027,1
E,112,2
E,4765,3
E,257,4
E,866,5
F,1027,1
F,112,2
F,4765,3
F,257,4
F,866,5

based on this CSV I was able to use this search and get the delta of each of the count files:

base search to search for the above file sourcetype="csv"
| stats values(count) AS count by day extracted_host
| streamstats current=f last(count) as previouscount by day
| eval delta=previouscount-count
| chart values(count) AS count values(delta) AS delta over extracted_host by day

The result will look like this: