Splunk Search

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

rmercy
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)
0 Karma
1 Solution

MuS
SplunkTrust
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:

alt text

Adopt the search to your needs.

Hope this helps ...

cheers, MuS

View solution in original post

0 Karma

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

0 Karma

MuS
SplunkTrust
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:

alt text

Adopt the search to your needs.

Hope this helps ...

cheers, MuS

View solution in original post

0 Karma
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!