Join Search Hitting Row Limit - Stats Alternative

[IRHM73]

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

I'm trying to put together a query which looks for two specific patterns of auditSource and auditType for given "SessionID".

I've put together the query below but it is slow to run and suffers from the data row limit, so is not returning all of the events.

index=main auditSource=auth-frontend auditType=Login
    | rename tags.X-Session-ID as sessionID
    | join sessionID [search index=main auditSource=per-frontend auditType=RequestReceived
    | rename tags.X-Session-ID as sessionID
    | table sessionID]
    | stats dc(detail.User)

In know from help already received and form research I've undertaken that you can use the 'Stats' command in the place of Join, but despite spending quite sometime on this now, I'm unable to get this to work.

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

Many thanks and kind regards

Chris

[IRHM73]

Hi @chimell, the field is actually called "detail.IdaUser", but a value of this field is:

22d277dd872cd09475a309c42356081fd367cef8211de523dabe2121cbf3054e

Many thanks and kind regards

Chris

[chimell]

Hi IRHM73
Just test this search i think that it will help you

| set union [search index=main auditSource=auth-frontend auditType=Login | fields tags.X-Session-ID] [search  index=main auditSource=per-frontend auditType=RequestReceived  | fields tags.X-Session-ID]| rename tags.X-Session-ID as sessionID| table sessionID
| stats dc(detail.User)

[chimell]

HI IRHM73
I added detail.User field in fields command
Just re test and let know if it works

 | set union [search index=main auditSource=auth-frontend auditType=Login | fields tags.X-Session-ID detail.User] [search  index=main auditSource=per-frontend auditType=RequestReceived  | fields tags.X-Session-ID detail.User]| rename tags.X-Session-ID as sessionID| table sessionID| stats dc(detail.User)

[IRHM73]

Hi @chimnell,

Thank you for taking the time to come back to me with this.

I have tried the query you kindly provided, but unfortunately it is still returning zero results.

Many thanks and kind regards

Chris

[chimell]

Hi
Try this

 index=main (auditSource=auth-frontend auditType=Login) OR (auditSource=per-frontend auditType=RequestReceived)| rename tags.X-Session-ID as sessionID | table sessionID | stats dc(detail.User)

[IRHM73]

Hi @chimnell thank you for this, but unfortunately its' returning a zero total.

It's certainly a tricky one is this. I've been racking my brain for a couple of days on this 🙂

Many thanks and kind regards

Chris

[chimell]

give me some values of detail.User field

[chimell]

Hi
Try separately these two searches and let know the result

first search

 index=main auditSource=auth-frontend auditType=Login | rename tags.X-Session-ID as sessionID|table sessionID| stats dc(detail.User)

second search

  index=main auditSource=per-frontend auditType=RequestReceived| rename tags.X-Session-ID as sessionID|table sessionID | stats dc(detail.User)

[chimell]

you say you want to count the detail.IdaUser from events which have both the "aurth-frontend" and the "per-frontend"

use this search code

| set intersect [search index=main auditSource=auth-frontend auditType=Login | fields tags.X-Session-ID detail.IdaUser] [search  index=main auditSource=per-frontend auditType=RequestReceived  | fields tags.X-Session-ID detail.IdaUser]| stats dc(detail.IdaUser)

[chimell]

here i use intersect to filter the events of the "aurth-frontend" and the "per-frontend" which have both detail.IdaUser field

[IRHM73]

Hi @chimell, thank you for cominhg back to me with this.

Unfortunately this doesn't return any results. For clarification the detail.IdaUser is only on the "auditSource=auth-frontend".

But please don't spend anymore time on this because I've been fortunate enough to receive a solution from @javiergn.

Many thanks for all your help and kind regards

Chris

[IRHM73]

Hi @chimnell, thank you for coming back to me with this and my apologies for not coming back to you sooner.

Yes I can confirm that "tags.X-Session-ID" is a field.

Many thanks and kind regards

Chris

[IRHM73]

Hi @chimnell, thank you very much for taking the time to reply to my post.

Whilst you were sending this, I did update the description which may help i.e. part of the issue I have with the working query is that it hits the data row limit.

I have tried the query you kindly sent and although it runs it doesn't return any results.

Many thanks and kind regards

Chris

[chimell]

look very well your data and re test this search it work well

[IRHM73]

Hi @chimnell thank you for this. I've just tried the query again and it's still not working I'm afraid. The results are shown as zero.

[chimell]

tags.X-Session-ID is it a field ?

[javiergn · Accepted solution]

If you want to maintain the structure and keep using join, then simply apply some field filtering and see how that helps:

index=main auditSource=auth-frontend auditType=Login
| fields _time, tags.X-Session-ID, detail.User
| rename tags.X-Session-ID as sessionID
| join sessionID [
    search index=main auditSource=per-frontend auditType=RequestReceived
    | fields tags.X-Session-ID
    | rename tags.X-Session-ID as sessionID
    | table sessionID
]
| stats dc(detail.User)

Alternatively you should be able to replace the join with stats and that should help too. See the following posts for help. If you can't manage to work it out by yourself let me know and I'll try to help:

https://answers.splunk.com/answers/34107/joining-two-searches-with-and-without-stats-command.html
https://answers.splunk.com/answers/822/simulating-a-sql-join-in-splunk.html

Thanks,
Javier

[IRHM73]

Hi @javiergn, many thanks for coming back to me with this, I'll take a look at the guidance you've kindly provided.

Kind Regards

Chris

[IRHM73]

Hi @javiergn, I'm sorry to trouble you again, but I wonder whether you may be able to help.

Using the two posts you provided links for I put together the following query but the problem is, is that it is not returning any data.

(auditSource=auth-frontend auditType=Login)OR(auditSource=per-frontend auditType=RequestReceived)
| rename tags.X-Session-ID as sessionID
| table sessionID
| stats dc(detail.IdaUser)

Could you have a look at it please and see where I've gone wrong.

Many thanks and kind regards

Chris

[javiergn]

I think you forgot the index name and also be careful when using table as you got rid of the User field by doing that and therefore you can't use it afterwards. You also need to group by the common field (look at your join above), in this case as part of your stats:

index=main ((auditSource=auth-frontend auditType=Login) OR (auditSource=per-frontend auditType=RequestReceived))
| stats dc(detail.IdaUser) by tags.X-Session-ID