Query to find records in different events

[mendi]

I have thousands of records (events), I would like to search field a if it exists in field b of other event (record).

e.g.

Name;Reference,Status;Date;Creator;NewReference;Type

Test;Abc1;DONE;2022-09-09;Me;Null;INS

Hello;Null;OPEN;2022-09-09;Me;Abc1;UPD

So I would like to find  records where Reference(Abc1) with status (Done) is present  in another record whose NewReference is equal to Referenc eof earlier record (Abc1) and status is OPEN

The logs will have thousands of records

Thanks

[mendi]

Thanks Giuseppe for your quick reply. It doesn't seem to work.

So e.g. there are 6 records,

Name;Reference,Status;Date;Creator;NewReference;Type

Test;Abc1;DONE;2022-09-09;Me;Null;INS

Hello;Null;OPEN;2022-09-09;Me;Abc1;UPD

Test;Abc2;DONE;2022-09-09;Me;Null;INS

Hello;Null;OPEN;2022-09-09;Me;Abc2;UPD

Test;Abc3;DONE;2022-09-09;Me;Null;INS

Hello;Abc5;OPEN;2022-09-09;Me;Abc4;UPD

So in above example, I would like to find records where reference with status DONE is not found in any other record under field New Reference with status OPEN.

In abov example, abc1 and abc2 finds matched record but Abc3 &Abc5 doesn't find.

many Thanks 

[gcusello]

Hi @mendi ,

ok, it's a different condition:

<your_search>
| eval key=if(Status="DONE",Reference,NewReference)
| fields Name Reference Status Date Creator NewReference Type
| stats
     dc(Status) AS Status_count
     count(Status) AS Status
     values(*) AS *
     BY key
| where Status_count=1 AND Status="DONE"
| table Name Reference Status Date Creator NewReference Type

Ciao.

Giuseppe

[gcusello]

Hi @mendi ,

let me understand: do you have Reference and NewReference in each event?

if yes you have to create a search using the field to compare as keys, something ike this:

<your_search>
[ search <your_search> | rename Reference AS NewReference | fields NewReference ]
| fields Name Reference Status Date Creator NewReference Type
| stats
     dc(Status) AS Status_count
     values(*) AS *
     BY Reference
| where Status_count=2
| table Name Reference Status Date Creator NewReference Type

if you have more conditions, you can add them to the where.

Ciao.

giuseppe