Failing to do a join on 2 simple queries

Follow

munya.mufambisi

• January 21, 2023 23:24

Hi,

I have the following queries:

Query 1:

_sourceHost=host 1
| json field=_raw "data.type" as type
| where type ="gd_auth_succeed"
| fields user_id

Query 2

_sourceHost=prod_auth0
| json field=_raw "data.type" as type
| where type ="gd_auth_failed"
| fields user_id

Id want to return user_id tha exist in both Query 2 and Query 1. 

I have tried Subquery but it wont accept "where type="  I have looked at join as well and it looks like it wont accept where.Any idea how i can achieve this without doing Lookup tables? 

 

This is where i am at:

 

_sourceHost=<redacted>
| join (parse "user_id:*" as userId, "type:*" as type | where type="gd_auth_failed") as T1,
(parse "user_id:*" as userId, "type:*" as type | where type="gd_auth_succeed") as T2
on T1.userId=T2.userId

 

1

• 

  Graham Watts

  • January 23, 2023 03:07

  Hi Munya,
  
  Here is one option to show userId's that have a success and failure, is this what you're looking for?
  
  If not, what use case are you trying to solve?
  
  _sourceHost=<redacted> (gd_auth_failed OR gd_auth_succeed)
  | parse "user_id:*" as userId, "type:*" as type
  | if(type="gd_auth_succeed",1,0) as gd_auth_failed
  | if(type="gd_auth_failed",1,0) as gd_auth_failed
  | sum(gd_auth_succeed) as succeed_total, sum(gd_auth_failed) as failed_total by userId
  | where succeed_total > 0 and failed_total > 0
  
  
  

  0

  Comment actions Permalink
• 

  munya.mufambisi

  • January 23, 2023 03:19

  thanks Graham! that actually solves my use case!

  0

  Comment actions Permalink

Please sign in to leave a comment.