Join searches and make a calculation

[balash1979]

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

    <search A>  | stats count  max(size) AS Users_Waiting | join [search <search B> | stats count as Daily_Users | streamstats sum(Daily_Users) as Cumulative_Users | timechart span=1d Cumulative_Users-Users_Waiting

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 ?

[richgalloway · Accepted solution]

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.

<search A>  
| bin span=1d _time
| stats count max(size) AS Users_Waiting by _time
| append [search <search B> 
  | bin span=1d _time
  | stats count as Daily_Users by _time
  | streamstats sum(Daily_Users) as Cumulative_Users ]
| stats values(*) as * by _time
| eval Users_Not_Waiting = Cumulative_Users - Users_Waiting
| timechart span=1d Users_Not_Waiting

[balash1979]

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.

[ITWhisperer]

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

[ITWhisperer]

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.