topic Re: Join Search Hitting Row Limit - Stats Alternative in Splunk Search

Join Search Hitting Row Limit - Stats Alternative

IRHM73 — Tue, 26 Jan 2016 14:50:09 GMT

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

Re: Join Search Hitting Row Limit - Stats Alternative

javiergn — Tue, 26 Jan 2016 14:57:58 GMT

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

Re: Join Search Hitting Row Limit - Stats Alternative

IRHM73 — Tue, 26 Jan 2016 15:15:37 GMT

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

Re: Join Search Hitting Row Limit - Stats Alternative

IRHM73 — Tue, 26 Jan 2016 15:44:50 GMT

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

Re: Join Search Hitting Row Limit - Stats Alternative

javiergn — Tue, 26 Jan 2016 15:52:45 GMT

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

Re: Join Search Hitting Row Limit - Stats Alternative

IRHM73 — Wed, 27 Jan 2016 07:25:10 GMT

Hi thank you for coming back to me with this but unfortunately it's not working.

I think the issue is that it is not extracting those records which have both sets of parameters. In addition it is listing the 'Session ID' in the table rather than the count of the detail.IdaUser

Re: Join Search Hitting Row Limit - Stats Alternative

chimell — Wed, 27 Jan 2016 08:59:14 GMT

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)

Re: Join Search Hitting Row Limit - Stats Alternative

IRHM73 — Wed, 27 Jan 2016 09:15:30 GMT

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

Re: Join Search Hitting Row Limit - Stats Alternative

javiergn — Wed, 27 Jan 2016 09:21:07 GMT

Hi, could you post an example of your data source 1 (auth-frontend), data source 2 (per-frontend) and then the outcome you are expecting after joining both?
Otherwise I'm going to keep trying without too much success I'm afraid.

Based on your last comment, if you want to both group by Session-ID and count the distinct users, I would try this instead, but it would really help if you could post some examples as I mentioned above:

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

Thanks,
J

Re: Join Search Hitting Row Limit - Stats Alternative

chimell — Wed, 27 Jan 2016 10:18:08 GMT

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

Re: Join Search Hitting Row Limit - Stats Alternative

IRHM73 — Wed, 27 Jan 2016 10:25:58 GMT

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.

Re: Join Search Hitting Row Limit - Stats Alternative

IRHM73 — Wed, 27 Jan 2016 10:55:45 GMT

Hi thank you for coming back to me with this.

I appreciate your comment in respect of the data, but unfortunately by data is restricted.

I've looked back at my original query and perhaps it wasn't as explicit as it should have been, so I'll give it another shot.

For each tags.X-Session-ID which contain: auditSource=per-frontend and auditType=RequestReceived then auditSource=auth-frontend and auditType=Login
Count distinct values from the detail.IdaUser field.

I'm not sure whether this helps.

Many thanks and kind regards

Chris

Re: Join Search Hitting Row Limit - Stats Alternative

chimell — Wed, 27 Jan 2016 12:57:10 GMT

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

Re: Join Search Hitting Row Limit - Stats Alternative

IRHM73 — Thu, 28 Jan 2016 06:27:14 GMT

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

Re: Join Search Hitting Row Limit - Stats Alternative

javiergn — Thu, 28 Jan 2016 10:00:19 GMT

Hi Chris,

I understand data might be restricted but it's probably worth obfuscating it in order for us to better understand what you are trying to achieve.

Based on your comments 1 and 2 above, I'm pretty sure you still need to group by "tags.X-Session-ID" first (comment 1) and then either use dc or count.

Anyway, if you could post three tables that represent your data as follows, it would definitely help:

auth-frontend

tags.X-Session-ID, detail.IdaUser
100, User1
100, User2
101, User2

per-frontend

tags.X-Session-ID
100
101

result

whatever you are trying to achieve

Re: Join Search Hitting Row Limit - Stats Alternative

chimell — Thu, 28 Jan 2016 10:22:04 GMT

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)

Re: Join Search Hitting Row Limit - Stats Alternative

IRHM73 — Thu, 28 Jan 2016 10:51:00 GMT

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

Re: Join Search Hitting Row Limit - Stats Alternative

chimell — Thu, 28 Jan 2016 11:36:24 GMT

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)

Re: Join Search Hitting Row Limit - Stats Alternative

IRHM73 — Thu, 28 Jan 2016 11:49:13 GMT

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

Re: Join Search Hitting Row Limit - Stats Alternative

chimell — Thu, 28 Jan 2016 12:03:42 GMT

give me some values of detail.User field