topic Re: Forescout: How to generate a report for month over month AV compliance? in Splunk Search

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

tmaltizo — Wed, 26 Oct 2016 14:06:19 GMT

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

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

sundareshr — Wed, 26 Oct 2016 14:43:59 GMT

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

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

tmaltizo — Wed, 26 Oct 2016 16:16:29 GMT

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.

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

sundareshr — Wed, 26 Oct 2016 16:26:31 GMT

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

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

tmaltizo — Wed, 26 Oct 2016 20:30:44 GMT

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?

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

sundareshr — Tue, 29 Sep 2020 11:33:54 GMT

Like this

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

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

tmaltizo — Wed, 26 Oct 2016 20:45:36 GMT

I'll try that. Ok Thanks!

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

tmaltizo — Thu, 27 Oct 2016 13:48:35 GMT

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....?

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

sundareshr — Thu, 27 Oct 2016 13:52:44 GMT

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

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

tmaltizo — Tue, 01 Nov 2016 17:06:39 GMT

Thanks for all of your help @sundareshr!