topic Re: Query to find records in different events in Splunk Search

Query to find records in different events

mendi — Mon, 08 Jul 2024 10:25:11 GMT

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

Re: Query to find records in different events

gcusello — Mon, 08 Jul 2024 10:36:52 GMT

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:

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

Ciao.

giuseppe

Re: Query to find records in different events

mendi — Mon, 08 Jul 2024 11:04:17 GMT

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

Re: Query to find records in different events

gcusello — Mon, 08 Jul 2024 12:30:06 GMT

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