sub search join not working , returning incorrect results

[msrama5]

Hello, I have 3 queries as below and all 3 return starid, I need to check if starid from query 1 exists on starid from (Query2 + Query3) and return those star ids that does not exist for Query 2 + Query 3, I wrote the join query but returning incorrect results , How can this query be written to return correct results for starid exists on Query1 only that does exist in Query2+ Query3

Query 1 - [search earliest=-3d latest=-2d index=_* OR index=* sourcetype=OPENAPI_STARS_LOGS "Processing AwardStarsRequest complete" *prmen* | rename Star_TranID as starid]
Query 2 - [ search earliest =-4d environment=test8 sourcetype=FEI_Utility Level=Information SessionM
| search messagetype=transaction
| eval MessageTemplate=replace(MessageTemplate,"\\\\\"","\"")
| spath MessageTemplate
| mvexpand MessageTemplate
| rex field=MessageTemplate "message\":\"(?<msg>.*)"
| search msg="*"
| eval _raw=replace(msg,"\\\\\"","\"")
| spath
| table transaction.sourceTransactionId
| rename transaction.sourceTransactionId as starid]

Query 3- 
[ search earliest =-4d environment=test8 sourcetype=FEI_Utility Level=Information *prmen*
| search messagetype=accrual
| spath MessageTemplate
| mvexpand MessageTemplate
| rex field=field3 "message\":\"(?<msg>.*)"
| search msg="*"
| eval _raw=replace(msg,"\\\\\"","\"")
| spath
| table "accrual.sourceTransactionId"
| rename "accrual.sourceTransactionId" as starid ]

[javiergn]

Hi @msrama5, 

Based on the queries you pasted above you could do something like:

| union [| search earliest=-3d latest=-2d index=_* OR index=* sourcetype=OPENAPI_STARS_LOGS "Processing AwardStarsRequest complete" *prmen* | fields Star_TranID | rename Star_TranID as starid | eval query=1]
[ | search earliest =-4d environment=test8 sourcetype=FEI_Utility Level=Information messagetype=transaction SessionM 
| eval MessageTemplate=replace(MessageTemplate,"\\\\\"","\"")
| spath MessageTemplate
| mvexpand MessageTemplate
| rex field=MessageTemplate "message\":\"(?<msg>.*)"
| search msg="*"
| eval _raw=replace(msg,"\\\\\"","\"")
| spath
| table transaction.sourceTransactionId
| rename transaction.sourceTransactionId as starid | eval query=2]
[| search earliest =-4d environment=test8 sourcetype=FEI_Utility Level=Information *prmen* messagetype=accrual
| spath MessageTemplate
| mvexpand MessageTemplate
| rex field=field3 "message\":\"(?<msg>.*)"
| search msg="*"
| eval _raw=replace(msg,"\\\\\"","\"")
| spath
| table "accrual.sourceTransactionId"
| rename "accrual.sourceTransactionId" as starid | eval query=3]
| stats values(query) as values, dc(query) as dc by starid
| where dc=1 AND values=1

All I did was to merge your queries and run them with union, then last two lines read as:

- Group by starid and show me the different values for query (1, 2, 3) and the distinct count of query.

- If the distinct count of query is 1 and the value is 1, that particular starid is only present on query 1. You can easily replace that last line to match your logic

Keep in mind I am not reviewing the rest of the code, as all those spath and mvexpand in there could be problematic if the number of events is large.

If your time range was the same in all your queries you could avoid the union command and just search across the 3 datasets with the first line, which should be faster to run and won't hit any limits.

[javiergn]

Hi @msrama5, did you have any chance to review this?

If so, did it work for you so that we can close this topic?

Thanks,

J

[javiergn]

Hi @msrama5, any news on this topic? Can we close it?