Join or Subquery Help

Follow

munya.mufambisi

• January 21, 2023 08:19

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? 

0

• 

  Andrew McLean

  • January 23, 2023 18:04

  This should work: 
  
  _sourceHost=host 1
  | json field=_raw "data.type" as type
  | where type ="gd_auth_succeed"
  | where [subquery: _sourceHost=prod_auth0
  | json field=_raw "data.type" as type
  | where type ="gd_auth_failed"
  | compose user_id]
  | count user_id
  
  

  0

  Comment actions Permalink

Please sign in to leave a comment.