Splunk Search

Can I even do this with an IF?

edenzler
Path Finder

Hi, multi value field called OverallStatus - states are On Track, Marginal, Critical. Another field ID, contains a unique value to count.

| stats count(ID) AS Event count(eval(OverallStatus="On Track")) AS OnTrack count(eval(OverallStatus="Marginal")) AS Marginal count(eval(OverallStatus="Critical")) AS Red by Project ID

Here is the metric: ( basically a Red - Yellow - Green )

Green = 80% or more projects are green and none are Red. Yellow = 70% - 79% projects are green or 1 – 20% are Red. Red = under 70% projects are green or over 20% are Red.

I just can't get my head around the "IF" ... Really only need to show this back in a Red Yellow or Green.

Any help or direction, would be greatly appreciated!

Cheers...

Tags (2)
0 Karma

lguinn2
Legend

Try this

yoursearchhere
| stats count(ID) AS Event count(eval(OverallStatus="On Track")) AS OnTrack count(eval(OverallStatus="Marginal")) AS 
    Marginal count(eval(OverallStatus="Critical")) AS Critical by ProjectID
| eval percentOnTrack = OnTrack * 100 / (OnTrack + Marginal + Critical)
| eval percentMarginal = Marginal * 100 / (OnTrack + Marginal + Critical)
| eval percentCritical = Critical * 100 / (OnTrack + Marginal + Critical)
| eval Status = case(percentOnTrack > 79 AND percentCritical = 0, "Green",
                     percentOnTrack > 69 AND percentOnTrack < 80, "Yellow",
                     percentCritical > 0 AND percentCritical < 21, "Yellow",
                     percentOnTrack < 70,"Red",
                     percentCritical > 20,"Red",
                     1=1,"Unknown")
| table ProjectID Status percentOnTrack percentMarginal percentCritical OnTrack Marginal Critical

You could do it with an if, but I just think it is easier with a case function.

lguinn2
Legend

I don't know which one is faster, I just like the case function because I find it easier to read and debug!

You could try them both and look at the search job inspector for the run time... that's not perfectly accurate because search load varies moment to moment - but if there is a big difference you will see it. (Search job inspector = the box with the "i" in case you didn't know)

0 Karma

edenzler
Path Finder

Thanks Iguinn! I managed to do it this way, wondering which one would be better?

| stats count(ID) AS Event count(eval(OverallStatus="On Track")) AS Green count(eval(OverallStatus="Marginal")) AS Yellow count(eval(OverallStatus="Critical")) AS Red by Product
| eval Green1=Green / Event * 100
| eval Yellow1=Yellow / Event * 100
| eval Red1=Red / Event * 100
| table Product Event Red Yellow Green Red1 Yellow1 Green1 | eval "Overall Status" = if (Green1 <= 70 OR Red1 >= 20, "Red", if (Green1 >= 70 AND Green1 <= 79.9 AND Red1 <= 20, "Yellow", if (Green1 >= 80 AND Red <= 0, "Green","Unknown")))

Cheers!

0 Karma
Get Updates on the Splunk Community!

New Dates, New City: Save the Date for .conf25!

Wake up, babe! New .conf25 dates AND location just dropped!! That's right, this year, .conf25 is taking place ...

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud  In today’s fast-paced digital ...

Observability protocols to know about

Observability protocols define the specifications or formats for collecting, encoding, transporting, and ...