SPL query to match fields value and create new field value based on matching condition.

[RSS_STT]

i have fields value in events something like below.

TOOL_Status description Event_ID Host_Name
CLOSED 21alerts has been issued abc 2143nobi11 abc
CLOSED 21alerts has been issued abc 2143nobi11 abc
OPEN 21alerts has been issued abc 2143nobi11 abc
OPEN 21alerts has been issued 111 2143nobi12 111
CLOSED 21alerts has been issued 111 2143nobi12 111
CLOSED 21alerts has been issued xyz 2143nobi15 xyz
CLOSED 21alerts has been issued xyz 2143nobi15 xyz
CLOSED 21alerts has been issued xyz 2143nobi15 xyz

If TOOL_Status=OPEN & TOOL_Status=CLOSED both exist for similar event ID than create new field new_status=1 and should be ignored if only TOOL_Status=CLOSED TOOL_Status exist for similar event ID . 

[gcusello · Accepted solution]

Hi @RSS_STT,

at first, did you extracted all the fields?

if yes, you have to use eval to create the new field applying the conditions you described:

<your_search>
| stats 
   dc(TOOL_Status) AS TOOL_Status_count 
   values(TOOL_Status) AS TOOL_Status 
   values(description) AS description 
   values(Host_Name) AS Host_Name
   BY Event_ID 
| eval new_status=if(TOOL_Status_count=2,1,0)
| where TOOL_Status_count=2 OR TOOL_Status="OPEN"

in this way you have all the Event_IDs with both the status or Status=OPEN.

If your condition that the I supposed, you can change the search applying it following my logic.

Ciao.

Giuseppe

[gcusello]

Hi @RSS_STT ,

good for you, see next time!

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated 😉