Obtaining Weighted Values to Multiple Stats Queries

[giventofly08]

Apologies as this one is smashing my head into a wall.

I'm currently looking to obtain 3 values in the end: A regular base percentage, a weighted percentage, and a result of percentage if the weighted percentage is above 90 and there are no entries with a severity level of "high" and a value "failed". The end result should basically break it down into rows of Checklists with columns that reflect the average weighted compliance of said checklist, and the weighted compliance if you factor in weighted compliance with 0 High failed checks.

The weighted values are: High = 10, Medium = 5, Low = 1.

I've attempted to create the original percentage first and then build the eval for the weighted percentage after, followed by looking for the final Pass/Fail check.

I know my query won't function properly with 2 stats queries in it, but I'm unsure of how else to get the items that I need to get.

Search query

| stats count(eval(compliance_result="passed" OR compliance_result="excepted_passed")) AS Passed, count(eval(compliance_result="failed")) AS Failed, count(eval(source_severity="high" AND (compliance_result="passed" OR compliance_result="excepted_passed"))) AS HPassed, count(eval(source_severity="high" AND compliance_result="failed")) AS HFailed, count(eval(source_severity="medium" AND (compliance_result="passed" OR compliance_result="excepted_passed"))) AS MPassed, count(eval(source_severity="medium" AND compliance_result="failed")) AS MFailed, count(eval(source_severity="low" AND (compliance_result="passed" OR compliance_result="excepted_passed"))) AS LPassed, count(eval(source_severity="low" AND compliance_result="failed")) AS LFailed by computer_dns_name, check_checklist_name
| eval Compliance=(100-((Failed/(Passed+Failed))*100))
| eval Compliance=round(Compliance,1)

| eval WP_High=(HPassed*10), WP_Med=(MPassed*5), WP_Low=(LPassed*1), WF_High=(HFailed*10), WF_Med(MFailed*5), WF_Low=(LFailed*1)
| eval Weighted_Failed=(WF_High+WF_Med+WF_Low), Weighted_Passed=(WP_High+WP_Med+WP_Low)
| eval WCompliance=(100-((Weighted_Failed/(Weighted_Passed+Weighted_Failed))*100))
| eval WCompliance=round(WCompliance,1)

| rename computer_dns_name AS Computer, check_checklist_name AS Checklist, Compliance AS All_Compliance, Failed AS All_Failed_Checks, HFailed AS High_Failed_Checks, MFailed AS Medium_Failed_Checks, LFailed AS Low_Failed_Checks
| eval Weighted_Failed_No_High=if((High_Failed_Checks!=0 OR WCompliance<90), "Failed", "")
| stats count(eval(Weighted_Failed_No_High="Failed")) AS WCHR_Failed, count(eval(Weighted_Failed_No_High!="Failed")) AS WCHR_Passed by Computer, Checklist
| eval Weighted_Compliance_No_High=(100-((WCHR_Failed/(WCHR_Passed+WCHR_Failed))*100))
| eval Weighted_Compliance_No_High=round(Weighted_Compliance_No_High,1)
| eval Weighted_Compliance_No_High=Weighted_Compliance_No_High."%"

[giventofly08]

I updated my syntax to almost get to the end result. I can now get it to display the compliance and the weighted compliance; however, I cannot seem to get the 3rd part (weighted compliance with 0 high_failed_checks)

I get accurate results provided I use my stats count using both computer_dns_name and Checklist; however in the table I only want it to reflect the compliance values of the checklists and not by dns_name and checklist. In removing the dns_name portion from the stats query it ensures though that the weighted_compliance_with_no_high will always be 0 since 1 high_failed_check since it bases it off the respective checklist versus each individual machine within that checklist.

Thoughts are appreciated:

My Search Query

| stats count(eval(compliance_result="passed" OR compliance_result="excepted_passed")) AS Passed, count(eval(compliance_result="failed")) AS Failed, count(eval(source_severity="high" AND (compliance_result="passed" OR compliance_result="excepted_passed"))) AS HPassed, count(eval(source_severity="high" AND compliance_result="failed")) AS HFailed, count(eval(source_severity="medium" AND (compliance_result="passed" OR compliance_result="excepted_passed"))) AS MPassed, count(eval(source_severity="medium" AND compliance_result="failed")) AS MFailed, count(eval(source_severity="low" AND (compliance_result="passed" OR compliance_result="excepted_passed"))) AS LPassed, count(eval(source_severity="low" AND compliance_result="failed")) AS LFailed by check_checklist_name computer_dns_name
| eval Compliance=(100-((Failed/(Passed+Failed))*100))
| eval Compliance=round(Compliance,1)
| eval WP_High=HPassed*10, WP_Med=MPassed*5, WP_Low=LPassed*1, WF_High=HFailed*10, WF_Med=MFailed*5, WF_Low=LFailed*1
| eval Weighted_Passed=WP_High+WP_Med+WP_Low, Weighted_Failed=WF_High+WF_Med+WF_Low
| eval WC_Perc=(100-((Weighted_Failed/(Weighted_Passed+Weighted_Failed))*100))
| eval WC_Perc=round(WC_Perc,1)
| eval Weighted_Comp_Passed=if((HFailed=0 AND WC_Perc>90), "1", "0")
| eval Weighted_Comp_Failed=if((HFailed!=0 OR WC_Perc<90), "1", "0")
| eval Weighted_Compliance_With_No_High=(100-((Weighted_Comp_Failed/(Weighted_Comp_Passed+Weighted_Comp_Failed))*100))
| eval Weighted_Compliance_With_No_High=round(Weighted_Compliance_With_No_High,1)
| rename computer_dns_name AS Computer, check_checklist_name AS Checklist, Compliance AS All_Compliance, Failed AS All_Failed_Checks, HFailed AS High_Failed_Checks, MFailed AS Medium_Failed_Checks, LFailed AS Low_Failed_Checks, WC_Perc AS Weighted_Compliance
| table Computer Checklist All_Compliance Weighted_Compliance High_Failed_Checks Weighted_Compliance_With_No_High