topic Re: Group by and filter query in Splunk Search

How to create a search for group by and filter query?

anrak33 — Tue, 24 Jan 2023 15:11:39 GMT

My data looks like the following

student_id	browser_id	guid	datetime	x_id
12_a	Chrome_2	1122	1/9/23 14:45	788a
13_a	Chrome_4	1213	1/12/23 19:13	33b
14_a	Chrome_3	1422	1/13/23 1:42	24c
15_b	FireFox_1	1289	1/16/23 15:46	12d
12_a	Chrome_2	1132	1/11/23 21:50	788a

Ideally, we shouldn't have different guids given same student_id, browser_id and x_id. I am trying to find all those student_ids who violate this rule aka student_ids with same browser_id and x_id but different guid. So for the above, I'd like to see something like -

12_a Chrome_2 1122 1/9/23 14:45 788a 12_a Chrome_2 1132 1/11/23 21:50 788a

I am trying -

index="main_idx" app="student_svc" | stats count by student_id browser_id guid datetime x_id | where count > 1 | stats list(count) by student_id

But it doesn't seem to be yielding the result. What should be the fix? Thanks

Re: Group by and filter query

gcusello — Wed, 18 Jan 2023 07:21:39 GMT

Hi @anrak33,

let me understand: for each student_id you should have only one uids, browser_id and x_id, is it correct?

if this is your need, you should try to use dc function in stats command, so to have the ex eption you could run something like this:

index="main_idx" app="student_svc" | stats dc(browser_id) AS browser_id_count dc(guid) AS guid_count dc(x_id) AS x_id_count BY student_id | where browser_id_count>1 OR guid_count>1 OR x_id_count>1

See my approach and adapt my sample to your needs.

Ciao.

Giuseppe

Re: Group by and filter query

anrak33 — Wed, 18 Jan 2023 07:48:45 GMT

Thanks @gcusello - it seems close - one thing I want to get rid of in the search is empty guid - how to add that?
And the problem statement is for each (student_id + browser_id + x_id) there should be one guid.

Re: Group by and filter query

gcusello — Wed, 18 Jan 2023 09:53:20 GMT

Hi @anrak33,

please try this:

index="main_idx" app="student_svc" | fillnull guid value="empty" | stats dc(browser_id) AS browser_id_count dc(guid) AS guid_count dc(x_id) AS x_id_count dc(eval(if(guid="empty",1,0))) AS empty_guid_count BY student_id | where browser_id_count>1 OR guid_count>1 OR x_id_count>1 OR empty_guid_count>0

Ciao.

Giuseppe

Re: Group by and filter query

anrak33 — Mon, 23 Jan 2023 08:33:53 GMT

@gcusello thx - this is not exactly giving me the result I want though.

I want all the student_ids who has more than 1 guid for a particular browser_id and x_id combination.

I tried changing the ORs to ANDs but no luck.

Re: Group by and filter query

gcusello — Mon, 23 Jan 2023 08:42:09 GMT

Hi @anrak33,

you can add also browser_id and x_id to the grouping keys or buid a different where condition, in this case, remember to use paranthesis.

Ciao.

Giuseppe

Re: Group by and filter query

anrak33 — Tue, 24 Jan 2023 07:36:54 GMT

thanks

Re: Group by and filter query

gcusello — Tue, 24 Jan 2023 07:39:46 GMT

Hi @anrak33,

good for you, see next time!

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated 😉