I have a table like below. Which plots different services under one column Service A (Subservices - A1 to A5) / Service B (Subservices - B1 to B5) . I need to take a new column denotes one Final Status like this if any of one Status is RED then the final status is RED, If there is no RED but one YELLOW And many GREEN then final status if YELLOW. What will be the best condition i can use to achieve the final one result
Service | Status |
A1 | GREEN |
A2 | RED |
A3 | YELLOW |
A4 | GREEN |
A5 | GREEN |
Try
|eventstats values(Status) as StatusList by Parent
|eval FinalStatus=case(isnotnull(mvfind(StatusList,"RED")),"RED",isnotnull(mvfind(StatusList,"YELLOW")),"YELLOW",isnotnull(mvfind(StatusList,"GREEN")),"GREEN",1==1,"NA")
|fields - StatusList
Here Parent is 'A'
Run anywhere example
|makeresults|eval Service="A1 A2 A3 A4 A5"|makemv Service|mvexpand Service
|appendcols [|makeresults | eval Status="GREEN RED YELLOW GREEN GREEN"|makemv Status|mvexpand Status]
|table Service, Status
|rex field=Service "(?<Parent>\D+)"
|eventstats values(Status) as StatusList by Parent
|eval FinalStatus=case(isnotnull(mvfind(StatusList,"RED")),"RED",isnotnull(mvfind(StatusList,"YELLOW")),"YELLOW",isnotnull(mvfind(StatusList,"GREEN")),"GREEN",1==1,"NA")
|fields - StatusList
Parent extraction is a simple rex for this dummy data and you should change based on actual data
You can replace eventstats with stats if you want only one status per service