@rashi83 to get total of fail, pass , nearpass use below
index=x | stats count(eval(statusCategory="Pass")) as "Pass", count(eval(statusCategory="NearPass")) as NearPass ,count(eval(statusCategory=="Fail")) as "Fail" by region | eval Pass=Pass + NearPass
Does this work?
| index=x | search statusCategory="B" OR statusCategory="C" | timechart count by statusCategory
Alternatively, if you need to define the "statusCategory" before the timechart, you can use:
| index=x | eval statusCategory=if(statusCategory="B_string", "B", if(statusCategory="C_string", "C", null)) | where isnotnull(statusCategory) | timechart count by statusCategory
Ahh, I see!
If I am understanding correctly, would using
...|timechart count by statusCategory
in one of my previous examples do the trick?
Thank you so much...I was working more on this query and was trying to get percentage of "Pass" . Pass % will include - statusCategory="Pass" and statusCategory="NearPass"
index=x | search statusCategory="Pass" OR statusCategory="NearPass" | stats count(statusCategory) as "Pass" | stats count(eval(statusCategory=="Fail")) as "Fail" by region
| foreach Compliant, NonCompliant [| eval "<> %"=round((<>/Total)100,2)] | sort - "Pass %" | table region " %" | rename region as Region
But it fails to recognize count of statusCategory=Fail
How can this be modified?
Hello again rashi! No problem at all, it is my intention to help out however I can.
The reason it fails to recognize count of statusCategory="Fail" is because the search pipe and the stats pipe removes all instances of fail statuses from the data. Let's try to fix that!
I'm operating under the assumption that we're working with these two fields for this search:
Is this correct? The reason I'm asking is because I see a "Compliant" field and a "NonCompliant" field in the foreach command, and I'm not sure how they come into play.
That said, if we are just looking for a "Pass %" by region, the query below should work:
|index = x | eval PassCheck = if(statusCategory="Pass", 1, if(statusCategory="NearPass", 1, 0)) | eval FailCheck = if(PassCheck=0, 1, 0) | stats sum(FailCheck) AS Fail sum(PassCheck) AS Pass by region | eval total_by_area = Fail + Pass | eval area_percent = round((Pass / total_by_area),2) *100 | table region area_percent | sort - area_percent | rename area_percent AS "Pass %", region AS Region
Let me know if anything goes wrong, or if anything doesn't make sense!