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
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
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 🙂
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
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
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
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
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