Splunk Search

Dividingof result from two Hosts

SplunkBaby
Explorer

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?

0 Karma

somesoni2
Revered Legend

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) 

kristian_kolb
Ultra Champion

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

kristian_kolb
Ultra Champion

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.

0 Karma

SplunkBaby
Explorer

Only X_sum is showing in my result.total_sum is blank

0 Karma

SplunkBaby
Explorer

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)

0 Karma

kristian_kolb
Ultra Champion

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

0 Karma

SplunkBaby
Explorer

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?

0 Karma

MuS
Legend

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

0 Karma

MuS
Legend

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

0 Karma

SplunkBaby
Explorer

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

kristian_kolb
Ultra Champion

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

/k

0 Karma
Get Updates on the Splunk Community!

Optimize Cloud Monitoring

  TECH TALKS Optimize Cloud Monitoring Tuesday, August 13, 2024  |  11:00AM–12:00PM PST   Register to ...

What's New in Splunk Cloud Platform 9.2.2403?

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

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...