topic Timecharts and Multiple Operating Systems in Splunk Search

Timecharts and Multiple Operating Systems

giventofly08 — Wed, 30 Sep 2020 02:29:26 GMT

I'm currently attempting to make a 6 month trend of multiple OS' compliance percentages into one timechart, but am running into trouble separating it by Operating system (the field containing this information is "check_checklist_name")

Basically, I'd like for it to be one timechart that includes specific OS that I specify (via check_checklist_name) and then shows them all on a line chart with their trends over the past 6 months (so 6 lines on one 6 month trend chart). Would I need to include a bin as well to ensure the % are accurate from a few months ago and are not deduped out?

Currently, I have the following:

index=bigfix sourcetype="bigfix:compliance" check_checklist_name="Windows 2008" OR check_checklist_name="Windows 2012" OR check_checklist_name="Windows 7" OR check_checklist_name="Windows 10" OR check_checklist_name="RHEL 6" OR check_checklist_name="RHEL 7" state="passed" OR state="failed"
| dedup comp_id check_id
| timechart span=1mon count(eval(state="passed")) AS Passed, count(eval(state="failed")) AS Failed
| eval Percent_Compliance=(100-((Failed/(Passed+Failed))*100))
| table _time Percent_Compliance

This gets me the overall percentage every month, but it does not break it down by the Operating System. Any help would be appreciated.

Re: Timecharts and Multiple Operating Systems

richgalloway — Mon, 07 Oct 2019 13:48:15 GMT

Have you tried ... | timechart span=1mon count(eval(state="passed")) AS Passed, count(eval(state="failed")) AS Failed BY check_checklist_name | ... ?

Re: Timecharts and Multiple Operating Systems

giventofly08 — Wed, 30 Sep 2020 02:29:30 GMT

I've also noticed when I attempt to round the percentage to 11 decimal it breaks the query. Could someone please explain to me why that is?

enter code hereindex=bigfix sourcetype="bigfix:compliance" check_checklist_name="Windows 2008" OR check_checklist_name="Windows 2012" OR check_checklist_name="Windows 7" OR check_checklist_name="Windows 10" OR check_checklist_name="RHEL 6" OR check_checklist_name="RHEL 7" state="passed" OR state="failed"
| dedup comp_id check_id

| timechart span=1mon count(eval(state="passed")) AS Passed, count(eval(state="failed")) AS Failed
| eval Percent_Compliance=(100-((Failed/(Passed+Failed))*100))
| eval Compliance=round(Compliance,1)
| table _time Percent_Complianceenter code here

Re: Timecharts and Multiple Operating Systems

giventofly08 — Mon, 07 Oct 2019 14:05:22 GMT

That did move me in the right direction, It now displays all 7 of the respective OS's into a total of 14 columns (1 for passed: OS and one for failed:OS for each OS), but no percentages.

Would I need to then right a massive eval statement where it becomes

    | timechart span=1mon count(eval(state="passed")) AS Passed, count(eval(state="failed")) AS Failed by check_checklist_name
    | eval Perc_RHEL7=(100-((Failed: RHEL 7*/(Passed: RHEL 7*+Failed: RHEL 7*))*100)) ...etc for all OS

I've also noticed when I try to round to the nearest .1% it breaks the query as well...any thoughts? Thanks so much for getting me past the initial mental block.
Same with trying to add a bin so that it looks at everything in 1 month intervals without deduping previous months.

Apologies for the amount of questions, these items have just been grating me for a while now where they work sometimes, but don't work other times.

Re: Timecharts and Multiple Operating Systems

richgalloway — Mon, 07 Oct 2019 14:31:59 GMT

You only need one eval statement to compute percentages for each OS. The one in your original query should work.
Adding round should be painless: ... | eval Percent_Compliance=round(Percent_Compliance,1) | ....
I'm not sure what you mean by "without deduping previous months". Dedup should not be needed.

Re: Timecharts and Multiple Operating Systems

giventofly08 — Mon, 07 Oct 2019 14:51:15 GMT

Sorry, it looks like the difference between dedup and not is super negligible so I can simply remove the dedup and the bin.

However; when I change the syntax to:

index=bigfix sourcetype="bigfix:compliance" check_checklist_name="Windows 2008 " OR check_checklist_name="Windows 2012" OR check_checklist_name="Windows 2012" OR check_checklist_name="Windows 7" OR check_checklist_name="Windows 10" OR check_checklist_name="RHEL 6" OR check_checklist_name="RHEL 7"  state="passed" OR state="failed" 

| timechart span=1mon count(eval(state="passed")) AS Passed, count(eval(state="failed")) AS Failed by check_checklist_name
| eval Percent_Compliance=(100-((Failed/(Passed+Failed))*100))
| eval Percent_Compliance=round(Percent_Compliance,1)

It shows the chart like before with 14 total columns for Passed and Failed for each OS (such as: Failed: RHEL6, Failed: RHEL7, Passed: RHEL6 etc), broken down by 6 months worth of rows. it does not compute the percentage though

Thanks again for all of your help.