Splunk Search

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

msalaverry
New Member

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,

0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

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

View solution in original post

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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
0 Karma

msalaverry
New Member

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

0 Karma

skoelpin
SplunkTrust
SplunkTrust

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)
0 Karma

msalaverry
New Member

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

0 Karma

sbbadri
Motivator

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

0 Karma

msalaverry
New Member

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

0 Karma

skoelpin
SplunkTrust
SplunkTrust

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

0 Karma

skoelpin
SplunkTrust
SplunkTrust

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
0 Karma

msalaverry
New Member

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

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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

---
If this reply helps you, Karma would be appreciated.
0 Karma

msalaverry
New Member

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.

0 Karma
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

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

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...