Splunk Search

Query to find records in different events

mendi
Observer

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

Labels (1)
0 Karma

mendi
Observer

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 

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

 

0 Karma
Get Updates on the Splunk Community!

Observability Unlocked: Kubernetes Monitoring with Splunk Observability Cloud

 Ready to master Kubernetes and cloud monitoring like the pros? Join Splunk’s Growth Engineering team for an ...

Update Your SOAR Apps for Python 3.13: What Community Developers Need to Know

To Community SOAR App Developers - we're reaching out with an important update regarding Python 3.9's ...

October Community Champions: A Shoutout to Our Contributors!

As October comes to a close, we want to take a moment to celebrate the people who make the Splunk Community ...