topic Re: How to find the difference between the results of two different searches in one search to display in a table panel? in Splunk Search

How to find the difference between the results of two different searches in one search to display in a table panel?

msalaverry — Mon, 10 Aug 2015 18:24:39 GMT

Hi,

I hope you can help me with this,
I have 2 search results and I want to get the difference between both in the same search to display it in a table panel.

So..
search events 1:

New apps retrieved | stats values(Count) as Apps_retrieved | Table _time, Apps_retrieved

search events 2:

Apps_Assignment: apps generated in  | stats values(Count) as Apps_generated | Table _time, Apps_generated

So, basically what I need is to get:

{(search events 1) - (search events 2)} | timechart span=1h count

or some way to expose this difference in 1h intervals.

Thanks in advance,

Re: How to find the difference between the results of two different searches in one search to display in a table panel?

richgalloway — Mon, 10 Aug 2015 19:07:31 GMT

If you can provide more detail about your base searches, it may be possible to combine them so you have a single query.

Re: How to find the difference between the results of two different searches in one search to display in a table panel?

skoelpin — Mon, 10 Aug 2015 19:18:53 GMT

If I understand this correctly, you want to find the difference between timestamps which will show you how long an event took to process?

You first need to see what the events have in common, usually they have a unique identifier tied to each request/response pair. Then you can pipe it into a transaction or stats command which will then group them. Then you will pipe it into a timechart

If it doesn't have a unique identifier and is in the same index, you can then use startswith="start" and endswith="ends"

index=whatever | transaction startswith="start" endswith="end" | timechart avg(duration)

Re: How to find the difference between the results of two different searches in one search to display in a table panel?

msalaverry — Tue, 29 Sep 2020 06:54:16 GMT

Hi richgalloway,
Well the log statements I'm looking for are:
- Apps_Assignment: New apps retrieved. Count={}
- Apps_Assignment: apps generated in {} millis. Count={}

This process will shown the first log at the begining, and the second one at the end. And I want to get difference between the initial value of count and the final. This process run once every hour.

Re: How to find the difference between the results of two different searches in one search to display in a table panel?

msalaverry — Tue, 29 Sep 2020 06:54:19 GMT

Maybe I didn't explained correctly.
As I said to richgalloway:

the log statements I'm looking for are:
- Apps_Assignment: New apps retrieved. Count={}
- Apps_Assignment: apps generated in {} millis. Count={}

This process will shown the first log at the begining, and the second one at the end. And I want to get difference between the initial value of count and the final. This process run once every hour.*

Re: How to find the difference between the results of two different searches in one search to display in a table panel?

skoelpin — Mon, 10 Aug 2015 19:40:50 GMT

Try this and let me know if it works.. It may need some tweeking as its untested

"New apps retreived" OR "New apps generated" | stats count values(Apps_retrieved) values(Apps_generated)  | eval Diff = Apps_retrieved - Apps_generated | timechart count(Diff) span=1h

Re: How to find the difference between the results of two different searches in one search to display in a table panel?

sbbadri — Tue, 29 Sep 2020 06:56:41 GMT

Hi,

Below is the sample query,

index=whatever | transaction statrtswith="Apps_Assignment: New apps retrieved" maxspan=1h | stats values(Count) as Apps_Assignment | stats first(Apps_Assignment ) as Initial_Apps_Assignment | eval apps_assignment_time = _time | Table apps_assignment_time , Initial_Apps_Assignment | transaction startswith="Apps_Assignment: apps generated" maxspan=1h | stats values(Count) as Assignment_app | stats last(Assignment_app ) as final_Assignment_app | eval Assignment_app_time = _time | Table Assignment_app_time , final_Assignment_app

Hope this will help you

Regards,
Badri Srinivas B

Re: How to find the difference between the results of two different searches in one search to display in a table panel?

msalaverry — Mon, 10 Aug 2015 20:27:05 GMT

transaction startswith="Apps_Assignment: New apps retrieved" doesn't return anything. Even, I don't know what's this command. 😞

Re: How to find the difference between the results of two different searches in one search to display in a table panel?

msalaverry — Tue, 29 Sep 2020 06:54:24 GMT

"New apps retreived" OR "New apps generated" | stats count values(Apps_retrieved) values(Apps_generated)

returns:
count | Apps_retrieved | Apps_generated
88 | |

Looks like "count" contains the sum of retrieved and generated. But we're not getting them separately.

Re: How to find the difference between the results of two different searches in one search to display in a table panel?

skoelpin — Mon, 10 Aug 2015 20:39:41 GMT

The transaction command only groups independent events together.. So if you have 2 events, 1 is a request and the other is a response.

Event 1 has the words "request" and event 2 has "response", you can then jon those 2 events into 1 event by doing this

index=whatever | transaction starswith="request" endswith="response"

Once you have 1 event, you can then easily find the duration between then 2 events..

I don't think this applies to what the original question stated as it wasn't clearly defined. It looks like you want to take 2 searches and combine them together, then do an | eval and subtract those fields and plot the results vs time, is this correct?

If so, then give me some sample data and I'll fix your search

Re: How to find the difference between the results of two different searches in one search to display in a table panel?

somesoni2 — Mon, 10 Aug 2015 20:50:31 GMT

Try something like this

     New apps retrieved | timechart span=1h values(Count) as Apps_retrieved | appendcols [search Apps_Assignment: apps generated in  | timechart span=1h values(Count) as Apps_generated ] | eval Difference=Apps_retrieved-Apps_generated
| table _time, Difference

Re: How to find the difference between the results of two different searches in one search to display in a table panel?

msalaverry — Mon, 10 Aug 2015 21:16:14 GMT

Awesome!. That's exactly what I need... Thanks somesoni2 ...
Also thanks to everyone else. You guys rock!.