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