Splunk Search

Forescout: How to generate a report for month over month AV compliance?

tmaltizo
Path Finder

I need to provide month over month AV compliance given the following calculation:

(Total # AV compliant servers / Total servers) x 100 = % Compliant

Total # AV compliant servers:

index=forescout sourcetype="fs_av_compliance" description="Server*" | dedup src_ip |chart latest(status) as Latest_Status over src_ip | search Latest_Status="compliant" | stats count(src_ip)

Total servers:

index=forescout sourcetype="fs_av_compliance" description="Server*" | dedup src_ip | stats count(src_ip)

I need assistance with the calculation and do this calculation month over month...maybe a date is necessary for this (the 1st of every month)?

Thanks in advance for any help with this!
Trista

0 Karma
1 Solution

sundareshr
Legend

Try this

index=forescout sourcetype="fs_av_compliance" description="Server*" | dedup src_ip | chart count over src_ip by status | addtotals | eventstats sum(Total) as total | where isnotnull(compliant) | eval percCompliant=compliant/total*100 | table src_ip compliant total percCompliant

View solution in original post

0 Karma

sundareshr
Legend

Try this

index=forescout sourcetype="fs_av_compliance" description="Server*" | dedup src_ip | chart count over src_ip by status | addtotals | eventstats sum(Total) as total | where isnotnull(compliant) | eval percCompliant=compliant/total*100 | table src_ip compliant total percCompliant
0 Karma

tmaltizo
Path Finder

Thanks @sundareshr!

The output I get is the following:
src_ip compliant total percCompliant
1.2.3.4 1 2930 0.034130
1.2.3.5 0 2930 0
.....

How can we get the total all of the compliant "1"s" and then divide that by the total to get one percentage number?

We want to run this at the first of every month and then chart it month over month to see if we're getting better or worse at AV compliance.

0 Karma

sundareshr
Legend

So you don't want by src_ip? Try this

 index=forescout sourcetype="fs_av_compliance" description="Server*" | dedup src_ip | eval src="Of All Devices" | chart count over src by status | addtotals | eval percCompliant=compliant/Total*100 | table src compliant Total percCompliant
0 Karma

tmaltizo
Path Finder

Yes! That works. Thank you @sundareshr!

So, how do we add this to capture the percCompliant from the 1st of each month and chart it for month over month comparison?

0 Karma

sundareshr
Legend

Like this

index=forescout sourcetype="fs_av_compliance" description="Server*" earliest=-3mon@mon | dedup src_ip | eval src="Of All Devices" | timechart span=1mon count by status | addtotals | eval percCompliant=compliant/Total*100 | table _time compliant Total percCompliant

Now, depending on the volume of data in your index, this could be a very slow query. To optimize it, you will have to use accelerated datamodel or summary indexes. Here's more on that

http://docs.splunk.com/Documentation/Splunk/6.5.0/Knowledge/Usesummaryindexing

http://docs.splunk.com/Documentation/Splunk/6.5.0/Knowledge/Aboutdatamodels

0 Karma

tmaltizo
Path Finder

I'll try that. Ok Thanks!

0 Karma

tmaltizo
Path Finder

Right now the volume is not too bad. We only have data going back to this June. But, we'll plan to optimize the query. Thanks for the info links!

Now, regarding the current search query, for the given earliest=3mon@mon, would the output be based on the date the query is run? I ran it today, 10/27/2016, so for the previous 3mon, it will do to compliance calculation for 9/27, 8/27, etc....?

0 Karma

sundareshr
Legend

It will be start of the month, so if you run your query today, it will be 9/1, 10/1 etc "-3mon@mon"

0 Karma

tmaltizo
Path Finder

Thanks for all of your help @sundareshr!

0 Karma
Get Updates on the Splunk Community!

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...