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
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)
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

0 Karma
.conf21 CFS Extended through 5/20!

Don't miss your chance
to share your Splunk
wisdom in-person or
virtually at .conf21!

Call for Speakers has
been extended through
Thursday, 5/20!