Splunk Search

How do I join two searches that both include rex field extractions?

IRHM73
Motivator

Hi,

I wonder whether someone may be able to help me please.

I have the following two searches:

index=main auditSource="agent-f" auditType=ServiceSentResponse detail.referrer="*deletion*"  "detail.Location"="/agent/verification-list" 
|rex field="tags.X-Session-ID" "session\-(?<SessionID>[\S]+)"
|stats count(SessionID) as "Number of Clients Deleted" by SessionID

And

index=main auditSource="agent-p" auditType=MetricGetClientListTotal 
| rex field="tags.X-Session-ID" "session\-(?<SessionID>[\S]+)"
| stats count by SessionID detail.agent-code

What I'm trying to do is join them using the SessionID, and then create a table at the end which displays the fields detail.agent-code and Number of Clients Deleted.

I have used join before, but never where regex events have had to be written i.e using 'explicit' rather than 'implicit' events.

I just wonder whether someone may be able to look at this please and offer some guidance on how I may be able to do this.

Many thanks and kind regards

Chris

0 Karma
1 Solution

javiergn
SplunkTrust
SplunkTrust

Could you use something like this instead?
Whenever possible try to avoid using join (performance, limits, etc)

(index=main auditSource="agent-f" auditType=ServiceSentResponse detail.referrer="*deletion*"  "detail.Location"="/agent/verification-list") OR (index=main auditSource="agent-p" auditType=MetricGetClientListTotal) 
 |rex field="tags.X-Session-ID" "session\-(?<SessionID>[\S]+)"
 | yourstatsquery

View solution in original post

renjith_nair
SplunkTrust
SplunkTrust

Hello Chris,

A simple join should work here unless you have some other hidden complexities.

 index=main auditSource="agent-f" auditType=ServiceSentResponse detail.referrer="*deletion*"  "detail.Location"="/agent/verification-list" 
 |rex field="tags.X-Session-ID" "session\-(?<SessionID>[\S]+)"
 |stats count(SessionID) as "Number of Clients Deleted" by SessionID
|join SessionID  [search  index=main auditSource="agent-p" auditType=MetricGetClientListTotal 
 | rex field="tags.X-Session-ID" "session\-(?<SessionID>[\S]+)"
 | stats count by SessionID detail.agent-code]|table "Number of Clients Deleted" detail.agent-code

However, somehow you should be able to combine these searches instead of join. Just a suggestion 🙂

Happy Splunking!
0 Karma

IRHM73
Motivator

Hi @renjit.nair, thank you for taking the time to reply to my post. It certainly helps with boosting my knowledge of the 'Join' function. As you will see @javiergn has combined the queries for me.

Many thanks and kind regards

Chris

0 Karma

javiergn
SplunkTrust
SplunkTrust

Could you use something like this instead?
Whenever possible try to avoid using join (performance, limits, etc)

(index=main auditSource="agent-f" auditType=ServiceSentResponse detail.referrer="*deletion*"  "detail.Location"="/agent/verification-list") OR (index=main auditSource="agent-p" auditType=MetricGetClientListTotal) 
 |rex field="tags.X-Session-ID" "session\-(?<SessionID>[\S]+)"
 | yourstatsquery

IRHM73
Motivator

Hi @javiergn, I have to admit I initially thought that this wouldn't work because of the 'OR' statement. But this works great.

Many thanks and kind regards

Chris

0 Karma

javiergn
SplunkTrust
SplunkTrust

The OR will return events from both audit sources and the rex will extract the field you want.
If you then use stats to count by SessionID, you'll get the totals the way you want because it's present in both event types. Assuming your regex works fine of course.

Let me know if that works

0 Karma

IRHM73
Motivator

Sincere thanks for the confirmation. You will see, probably while you were writing, that I tried the code and amended my response.

Many thanks and kind regards

Chris

0 Karma
Get Updates on the Splunk Community!

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

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...