Help with joining two queries

[binay2634]

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

[binay2634]

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

[binay2634]

 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

[bowesmana]

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.

[binay2634]

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 

[bowesmana]

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.

[binay2634]

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]

[bowesmana]

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.