topic Re: Dividingof result from two Hosts in Splunk Search

Dividingof result from two Hosts

SplunkBaby — Tue, 11 Feb 2014 08:07:35 GMT

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?

Re: Dividingof result from two Hosts

MuS — Tue, 11 Feb 2014 08:27:24 GMT

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

Re: Dividingof result from two Hosts

kristian_kolb — Tue, 11 Feb 2014 08:41:10 GMT

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

Re: Dividingof result from two Hosts

kristian_kolb — Tue, 11 Feb 2014 08:43:18 GMT

Oops, you're faster (again), but I'll leave my answer as it describes a different solution.

Re: Dividingof result from two Hosts

SplunkBaby — Tue, 11 Feb 2014 10:18:30 GMT

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?

Re: Dividingof result from two Hosts

SplunkBaby — Tue, 11 Feb 2014 10:25:08 GMT

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

Re: Dividingof result from two Hosts

MuS — Tue, 11 Feb 2014 12:27:27 GMT

okay, based on /k second example which you used to get it working a multisearch would look like this:

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

Re: Dividingof result from two Hosts

kristian_kolb — Tue, 11 Feb 2014 12:38:58 GMT

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

Re: Dividingof result from two Hosts

SplunkBaby — Mon, 28 Sep 2020 15:51:42 GMT

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)

Re: Dividingof result from two Hosts

SplunkBaby — Mon, 28 Sep 2020 15:51:45 GMT

Only X_sum is showing in my result.total_sum is blank

Re: Dividingof result from two Hosts

kristian_kolb — Tue, 11 Feb 2014 15:16:55 GMT

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.

Re: Dividingof result from two Hosts

somesoni2 — Tue, 11 Feb 2014 15:44:47 GMT

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)