Splunk Search

Help with joining two queries

binay2634
Explorer

Hi all I am trying to join two queries but unable to get the expected result.

I am using join command to extract username from base query and then look for the details of username from main query. I am also trying to accomodate time constraints here, ex look for a user in main query if the time difference it was captured in sub query and main query is 120 secs.

I am also using multiple eval commands and also tried appendcols

Labels (2)
0 Karma

binay2634
Explorer

@bowesmana , any suggestions here would be great , it's like a loop I am stuck in

0 Karma

binay2634
Explorer

IMG_20240314_043933.jpg

 now this is giving me result but I want it to pickup the user from subquery and only fetch details from main query for time greater than=120 secs, also there would be multiple users

0 Karma

bowesmana
SplunkTrust
SplunkTrust

I'm not fully understanding your pictured query as you are currently doing an AND query for data in two indexes, which is impossible - so you will get no events from index="a" AND index="app_cim", so I can't see how you are getting results.

Perhaps can can describe the data you have in what index it exists and the output you are looking for. What I can see is that you are trying to get a count of users who have eventName="xxx" in the join query.

Does User come from index=a or index=app_cim, as it appears User comes from Principal{} when it has eventName=xxx - that first search doesn't make a lot of sense at the moment.

Lines 4, 9 and 10 have no purpose, but you seem to be trying to do something with _time. Do you actually want to calculate min(_time) as firstTime in your stats?

Which events do you want to constrain with the 120 seconds and where is that 120 seconds calculated from.

 

0 Karma

binay2634
Explorer

Hi @bowesmana , sorry for confusion

There is only one index="a"

Yes line 4 has no purpose for now, and I want to pick a user from sub query let's say user1 we got at 1:23:20AM  from sub query now I want to look for all the logs in the mentioned main query for user1 within 1:25:20AM. And if I get one then I want it to be listed.

And also I want only the users from sub query to be in the list but no other users which it picks up from main query.

Please let me know if I was able to explain it 

0 Karma

bowesmana
SplunkTrust
SplunkTrust

If there is only one index, then you are saying you want to find one user from a query where eventname=xxx and index=a and then find all other events for that user where some other condition exists then the following logic is how you do things.

index=a (condition 1 OR condition 2)
| eval x=if(condition=1, 1, 0)
| eval y=if(condition=2, 1, 0)
| stats sum(x) as x sum(y) as y by user
| where x>0 AND y>0

I still think I am missing something from your question - if this is not what you are after, please try to describe all of your data inputs and the output you want to get as a result.

 

0 Karma

binay2634
Explorer

I also tried adding this to the below query but it still pickedup more users from the main query. While I only want it to take into account the users I am getting from sub query.

|appendcols

[ search index="a" eventName="xxx"

***other conditions here**

 |rename principal{} as User

| where firstime > _time

| where maxTime < _time

| stats count by User]
0 Karma

bowesmana
SplunkTrust
SplunkTrust

Maybe you could share your query as there is not much anyone can suggest other than do not use "join" as it is not really the way to join things in Splunk.

0 Karma
Get Updates on the Splunk Community!

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud  In today’s fast-paced digital ...

Observability protocols to know about

Observability protocols define the specifications or formats for collecting, encoding, transporting, and ...

Take Your Breath Away with Splunk Risk-Based Alerting (RBA)

WATCH NOW!The Splunk Guide to Risk-Based Alerting is here to empower your SOC like never before. Join Haylee ...