Splunk Search

Should I use a lookup or an inner query for the following search?

joydeep741
Path Finder

Sample Logs:

  • Incident=112 Group=ABC Status = Open
  • Incident=113 Group=ABC Status = Open - Incident=113 Group=XYZ Status = Closed
  • Incident=114 Group=ABC Status = Open - Incident=114 Group=ABC Status = Closed
  • Incident=115 Group=ABC Status = Open
  • Incident=116 Group=ABC Status = Open - Incident=116 Group=XYZ Status = Closed

I want write a query to get only those Incidents which are currently opened with group ABC.
The result should NOT have : 114 As it was closed by ABC group.
The result should NOT have : 113 and 116 As they were eventually closed by some other group.

My result should be :
112 and 115

index=test sourcetype=test_st | search group="ABC" AND status="Open"

But this query gives my 113 and 116 also.

Shall I use inner query or lookup for this ?

Please suggest some query logic.

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

@joydeep741,

Try,

index=test sourcetype=test_st|stats values(Status) as Status,values(Group) as Group by Incident
|eval isClosed=mvfind(Status,"Closed")|where isnull(isClosed)|fields - isClosed
Happy Splunking!
0 Karma

renjith_nair
SplunkTrust
SplunkTrust

@joydeep741,

Try

    index=test sourcetype=test_st group="ABC" status="open" NOT (Incident=113 OR Incident=114 OR Incident=116)
Happy Splunking!
0 Karma

joydeep741
Path Finder

@renjith.nair This was sample data.. In reality we have data worth of million rows and I do not know In advance which incidents I have to put in the "NOT" list.

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

sorry I overlooked at the events. Added the answer

Happy Splunking!
0 Karma
Get Updates on the Splunk Community!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...