topic Re: How do I join two searches that both include rex field extractions? in Splunk Search

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

IRHM73 — Tue, 12 Jan 2016 09:26:58 GMT

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

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

javiergn — Tue, 12 Jan 2016 09:47:52 GMT

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

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

renjith_nair — Tue, 12 Jan 2016 09:51:14 GMT

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 🙂

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

IRHM73 — Tue, 12 Jan 2016 09:52:20 GMT

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

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

javiergn — Tue, 12 Jan 2016 09:56:25 GMT

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

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

IRHM73 — Tue, 12 Jan 2016 09:57:47 GMT

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

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

IRHM73 — Tue, 12 Jan 2016 10:00:21 GMT

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