How do you make a decision based on a row value?

[vasanthkumar23]

I'm trying to create a traffic-color dashboard for my applications based on their status and Tier level. If any one of the application status is RED, I want the tier to be shown as RED, even though there are other applications in the same tier level that are GREEN or AMBER.

Can you suggest me how my search query should be?

example data

SrV |App| Tier |Status
S1 |A1 |Tier1 |AMBER
S2 |A1 |Tier1 |AMBER
S3 |A2 |Tier2 |AMBER
S4 |A3 |Tier3 |GREEN
S5 |A4 |Tier2 |GREEN
S6 |A2 |Tier2 |AMBER
S7 |A4 |Tier2 |GREEN
S8 |A5 |Tier1 |RED

to Something like

Tier1 Tier2 Tier3
RED AMBER GREEN

[richgalloway]

Assign a numeric severity value to each Status value then take the max severity for each tier.

... | eval severity=case(Status=="GREEN", 1, Status=="AMBER", 2, Status=="RED", 3, 1=1, 99) 
| stats max(severity) as severity by Tier 
| eval Status=case(severity=1, "GREEN", severity=2, "AMBER", severity=3, "RED", 1=1, "ERROR")

There's probably a way to do it without the second case statement, but I'm drawing a blank right now.