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

[joydeep741]

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.

[renjith_nair]

@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

[renjith_nair]

@joydeep741,

Try

    index=test sourcetype=test_st group="ABC" status="open" NOT (Incident=113 OR Incident=114 OR Incident=116)

[joydeep741]

@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.

[renjith_nair]

sorry I overlooked at the events. Added the answer