Hi
I would like to divide the values from 2 hosts.Is it possible.
Example:
Say i have HostA and HostB.
HostA contains a field which can be used for calculating total.
HostB which contails value to divide.
Ie host=HostA|stats sum(filed) will give 1000.
host=HostB|stats sum(filed) will give 3.
I want to divide 1000/3 in single search query .Is it possible?
Try these
Options 1
host=hostA | stats sum(field) as FieldTotA | appendcols [search host=hostB | stats sum(field) as FieldTotB] | eval division=round(FieldTotA/FieldTotB,2)
Option 2
| multisearch [search host=hostA | eval fieldA=field | table fieldA] [search host=hostB | eval fieldB=field | table fieldB] | stats sum(fieldA) as FieldTotA, sum(fieldB) as fieldTotB | eval division=round(FieldTotA/FieldTotB,2)
If the field name is the same you can do it like so (Different sourcetypes rather than hosts in the example below);
index=_internal sourcetype=splunkd_access OR sourcetype=splunkd | stats sum(timeendpos) as summy by sourcetype | stats values(summy) as summy2| eval foo=mvindex(summy2,0) | eval bar=mvindex(summy2,1) | eval result=foo/bar
If the field names are different, it's even easier (bytes
in splunkd_access and workers
in splunkd);
index=_internal sourcetype=splunkd_access OR sourcetype=splunkd | stats sum(bytes) as sumb sum(workers) as sumw | eval result = sumb/sumw
/k
Oops, my bad. you need to make sure that the total_sum survives through the stats operation;
host=bob | eventstats sum(field_A) as total_sum | search field_B=X | stats sum(field_A) as X_sum first(total_sum) as total_sum | table X_sum total_sum
NB. Since the eventstats
only calculates one value, it does not matter much which stats
function (first, last, max etc) you use to preserve it.
Only X_sum is showing in my result.total_sum is blank
Say I have a field Visit my host.
I want to find the total of Visit first.Then i want to filter host with some condition and again want to find the sum for Visit.
I did the query like this
host=ABC|eventstats sum(Visits) as total_sum | search "EMP Code"=BJX |stats sum(Visits) as X_sum.I am getting only second sum ie (X_sum)
Maybe I don't fully understand your question, but if you want make like this (in a pseudo-search-query);
find all relevant events
| sum(field_1) as total_sum
| filter to only keep events where user=bob
| sum(field_1) as bob_sum
Then you can use eventstats
, which puts the statistic in all events, without altering them/removing information;
host=A | eventstats sum(field) as total_sum | search field1=X | stats sum(field) as X_sum
/K
Thanks I work with second solution(fields different) and it helped me to solve.
For my understanding I have another doubt also.
In the same example if i want to take sum of a field in hostA before and after filtering data how can i proceed.
Ex:
host="hostA" |sum(field) as Beforefilter|field1=A|sum(field)as Afterfilter
Is the above possible?
Hi SplunkBaby,
a quick way could be the following multisearch:
| multisearch [ search host=HostA | stats sum(filed) AS fieldA ] [ search host=HostB | stats sum(filed) AS fieldB ] | eval division=fieldA/fieldB | table host filed fieldA fieldB division
I'm pretty sure there are better and more efficient ways to do it, but this will get you started ...
cheers, MuS
okay, based on /k second example which you used to get it working a multisearch
would look like this:
| multisearch [ search index=_internal sourcetype=splunkd_access | eval foo=bytes ] [ search index=_internal sourcetype=splunkd | eval bar=workers ] | stats sum(foo) as sumb sum(bar) as sumw | eval result = sumb/sumw
but this is really not necessary in your use case since you use fields with different names and different sourcetypes. A multisearch is used for other use cases......
When i tried this i got below exception.
Error in 'multisearch' command: Multisearch subsearches may only contain purely streaming operations (subsearch 1 contains a non-streaming command.)
Oops, you're faster (again), but I'll leave my answer as it describes a different solution.
/k