Splunk Search

Timecharts and Multiple Operating Systems

giventofly08
Explorer

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.

0 Karma

giventofly08
Explorer

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

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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

---
If this reply helps you, Karma would be appreciated.
0 Karma

giventofly08
Explorer

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.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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.

---
If this reply helps you, Karma would be appreciated.
0 Karma

giventofly08
Explorer

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.

0 Karma
Get Updates on the Splunk Community!

This Week's Community Digest - Splunk Community Happenings [9.26.22]

Get the latest news and updates from the Splunk Community here! Upcoming User Group Events! 👏 Check ...

BSides Splunk 2022 - The Call for Papers is now Open!

TLDR; Main Site: https://bsidessplunk.com CFP Site: https://bsidessplunk.com/cfp CFP Opens: December 15th, ...

Sending Metrics to Splunk Enterprise With the OpenTelemetry Collector

This blog post is part of an ongoing series on OpenTelemetry. The OpenTelemetry project is the second largest ...