topic Re: Obtaining values from a stats command to then utilize within a Timechart. in Splunk Enterprise Security

Obtaining values from a stats command to then utilize within a Timechart.

giventofly08 — Tue, 21 Jul 2020 17:51:27 GMT

Apologies, as this is a bit lengthy, but I'm completely stuck. I'm having to show data that shows a compliance percentage after adding weighted values to each result over the past 4 months

Basically, an item can have two states (pass or fail), and a score attributed based on severity. If an item is high severity then it is worth 72 points, if it's a medium then 36 points, and a low is worth 12.

I then calculate the weighted percentage of each machine using those numbers mentioned above (72/36/12).

From there I have to find one more value called Actual Percentage...this means you must have a weighted percentage of over 90% AND you must have 0 high severity fails (this results in basically a binary 1/0 type result...1 if you meet the requirements, and 0 if you do not meet both requirements).

I was able to get this working for a singular time (such as 30 days) as shown below, but I'm unable to make this work with a timechart if I want to see the "Actual Percentage" of all the machines over the past 4 months broken down by each month (1 mon).

Basically it's -> determine values for each compliance item -> calculate percentage to gain weighted percentage -> calculate the actual percentage of the machine by looking for weighted percentage above 90% and no high failed checks -> Produce output of the environment over the past 4 months spanning each month.

Thank you for any ideas to get me off this syntax block.

search query | dedup comp_id check_id | stats count(eval(compliance_result="passed" OR compliance_result="excepted_passed" OR compliance_result="excepted_failed")) AS Passed, count(eval(compliance_result="failed")) AS Failed, count(eval(source_severity="high" AND (compliance_result="passed" OR compliance_result="excepted_passed" OR compliance_result="excepted_failed"))) 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" OR compliance_result="excepted_failed"))) 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" OR compliance_result="excepted_failed"))) AS LPassed, count(eval(source_severity="low" AND compliance_result="failed")) AS LFailed by comp_id | eval High_Failed=HFailed | eval WP_High=HPassed*72, WP_Med=MPassed*36, WP_Low=LPassed*12, WF_High=HFailed*72, WF_Med=MFailed*36, WF_Low=LFailed*12 | 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 goodMachines= if(WC_Perc>90 AND HFailed=0, 1, 0) | stats sum(goodMachines) as sumOfGoodMachines, count(goodMachines) as countOfMachines | eval percentGoodMachines = ((sumOfGoodMachines / countOfMachines)*100) | eval percentGoodMachines=round(percentGoodMachines,2) | eval percentGoodMachines=percentGoodMachines."%" | rename percentGoodMachines AS "Actual Compliance", | table checklist "Actual Compliance"

Re: Obtaining values from a stats command to then utilize within a Timechart.

richgalloway — Tue, 21 Jul 2020 19:35:25 GMT

The timestamp command requires the _time field, which the stats command filters out if it is not explicitly referenced.

Re: Obtaining values from a stats command to then utilize within a Timechart.

giventofly08 — Tue, 21 Jul 2020 19:43:31 GMT

So I should be utilizing the _time within the first stats command as in:

Stats query by comp_id _time?

What then would I put for the second stats line that calculates the percentage after doing the weighted conversion?

As you can tell I'm not very good with the timechart mechanism.

Re: Obtaining values from a stats command to then utilize within a Timechart.

richgalloway — Tue, 21 Jul 2020 20:05:40 GMT

Add "by _time".
And you need "| timechart" somewhere. 😉

To make the stats command work better when grouping by _time, insert a bin command early in your search. Use the same span that timechart uses.

| bin span=1h _time | stats ...

Re: Obtaining values from a stats command to then utilize within a Timechart.

to4kawa — Tue, 21 Jul 2020 20:05:05 GMT

search query | bin _time span=1month | stats count(eval(compliance_result="passed" OR compliance_result="excepted_passed" OR compliance_result="excepted_failed")) AS Passed, count(eval(compliance_result="failed")) AS Failed, count(eval(source_severity="high" AND (compliance_result="passed" OR compliance_result="excepted_passed" OR compliance_result="excepted_failed"))) 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" OR compliance_result="excepted_failed"))) 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" OR compliance_result="excepted_failed"))) AS LPassed, count(eval(source_severity="low" AND compliance_result="failed")) AS LFailed by comp_id _time | eval High_Failed=HFailed | eval WP_High=HPassed*72, WP_Med=MPassed*36, WP_Low=LPassed*12, WF_High=HFailed*72, WF_Med=MFailed*36, WF_Low=LFailed*12 | 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 goodMachines= if(WC_Perc>90 AND HFailed=0, 1, 0) | stats sum(goodMachines) as sumOfGoodMachines, count(goodMachines) as countOfMachines by _time | eval percentGoodMachines = ((sumOfGoodMachines / countOfMachines)*100) | eval percentGoodMachines=round(percentGoodMachines,2) | eval percentGoodMachines=percentGoodMachines."%" | rename percentGoodMachines AS "Actual Compliance", | table _time "Actual Compliance"

what's checklist? your query can work?

Re: Obtaining values from a stats command to then utilize within a Timechart.

giventofly08 — Tue, 21 Jul 2020 20:06:30 GMT

So first stats command should be by comp id _time

I believe the second stats command should instead be a timechart sum(GoodMachines), but this is really where my knowledge falls off the rails.

Re: Obtaining values from a stats command to then utilize within a Timechart.

giventofly08 — Tue, 21 Jul 2020 20:12:47 GMT

Apologies, the checklist at the very end should not be there. It was an artifact from prior testing.

It should be just _time and actual compliance for the chart itself.

Re: Obtaining values from a stats command to then utilize within a Timechart.

to4kawa — Tue, 21 Jul 2020 20:29:41 GMT

@giventofly08 how about my query?

Re: Obtaining values from a stats command to then utilize within a Timechart.

giventofly08 — Tue, 21 Jul 2020 21:42:16 GMT

I put everything in below the dedup line and it appears to provide results that are in line with what I would expect for the first month; however, month 2 appeared abnormally high when it should have been stagnant change (or maybe 1%) It showed last month correct at about .85% but for May it was around 3.4% before my splunk instance crashed.

Re: Obtaining values from a stats command to then utilize within a Timechart.

to4kawa — Wed, 22 Jul 2020 09:33:03 GMT

>below the dedup line

Why? I didn't do like that. Do you know how dedup works?

Re: Obtaining values from a stats command to then utilize within a Timechart.

giventofly08 — Wed, 22 Jul 2020 13:25:39 GMT

I had deduped in the past to remove any duplication of a computer_id (Comp_id) and the specific check in question (check_id) so that if a machine reports in more than once in a month it will take the most recent check-in results.

Please correct me if I'm wrong, but if I don't dedup the comp_id and check_id then if Machine A reports in 9x in a month at 0% and Machine B reports in 1 time at 100%, instead of it being 50% it would be 10%, no?

Re: Obtaining values from a stats command to then utilize within a Timechart.

to4kawa — Wed, 22 Jul 2020 13:52:55 GMT

There are no results shown here, no logs, just queries.
I think only you can determine that.

dedup works for the entire search period.
Doesn't it aggregate every month?

Re: Obtaining values from a stats command to then utilize within a Timechart.

giventofly08 — Wed, 22 Jul 2020 14:25:33 GMT

Sorry, this query basically takes log files that an agent on the computer reports in, every day or so and will provide a log file to state what the check was, the severity of it, and if it passed or failed said check.

The next day it will do the same for all checks again. Same for each subsequent day. So really we want a percentage of all machines with 90% success and no failed high checks. This should be done for the last day of each month.

I believe if we dedup by the computer_id, the check_id, and _time that should accomplish it with your bin statement?

Re: Obtaining values from a stats command to then utilize within a Timechart.

giventofly08 — Mon, 27 Jul 2020 12:13:15 GMT

Your query did in fact work, I just had to make sure to dedup by _time and ensure that _time was accounted for in the stats lines. Thank you very much for the help!