topic Re: Join searches and make a calculation in Splunk Search

Join searches and make a calculation

balash1979 — Fri, 26 Mar 2021 14:28:14 GMT

I would like to run 2 searches and calculate the difference between 2 fields and plot the result using timechart

I have tested both these searches independently and it works fine.

I am trying this out

So basically, I want to take the count of first search which is Users_Waiting

Take the count of 2nd search which is Cumulative_Users

Draw a timechart with (Cumulative_Users - Users_Waiting)

Is my approach correct ?

Re: Join searches and make a calculation

ITWhisperer — Fri, 26 Mar 2021 14:41:35 GMT

The join will attempt to join with common fields - there are no common fields between your two queries.

The timechart needs a time element to work with - both queries remove this in their stats commands.

It is not clear where your second query finishes as there is no closing square bracket.

Re: Join searches and make a calculation

balash1979 — Fri, 26 Mar 2021 14:51:32 GMT

Sorry if i wasnt very clear.

This is search1
<search A> | stats count max(size) AS Users_Waiting

This is search 2
join [search <search B> | stats count as Daily_Users | streamstats sum(Daily_Users) as Cumulative_Users

Is there a way to do Cumulative_Users-Users_Waiting (basically difference between these 2 numbers) and then timechart the difference ?
Yes, I dont have any common fields between the 2 searches.

Re: Join searches and make a calculation

richgalloway — Fri, 26 Mar 2021 14:54:57 GMT

The join command works best when the two searches have common fields. This example has no common fields. What's more, join is inefficient and not needed in this case. Also, the timechart command requires the _time field, but it is stripped out by stats. Additionally, the timechart command cannot calculate the difference between two fields.

Try combining the searches with append then merging the results by time. Use eval to compute difference before invoking timechart.

Re: Join searches and make a calculation

ITWhisperer — Fri, 26 Mar 2021 14:56:41 GMT

Firstly, you should add a time element to the stats e.g. 1h buckets or whatever you choose so long as they are the same for the two queries. This will give you a field to join on i.e. _time