Splunk Search

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

anrak33
Explorer

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

Labels (2)
Tags (3)
0 Karma
1 Solution

gcusello
SplunkTrust
SplunkTrust

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

View solution in original post

0 Karma

anrak33
Explorer

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.

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

anrak33
Explorer

@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.

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

anrak33
Explorer

thanks

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @anrak33,

good for you, see next time!

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated 😉

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma
Get Updates on the Splunk Community!

Enterprise Security Content Update (ESCU) | New Releases

In the last month, the Splunk Threat Research Team (STRT) has had 2 releases of new security content via the ...

Announcing the 1st Round Champion’s Tribute Winners of the Great Resilience Quest

We are happy to announce the 20 lucky questers who are selected to be the first round of Champion's Tribute ...

We’ve Got Education Validation!

Are you feeling it? All the career-boosting benefits of up-skilling with Splunk? It’s not just a feeling, it's ...