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

[tmaltizo]

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

[sundareshr · Accepted solution]

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

[tmaltizo]

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.

[sundareshr]

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

[tmaltizo]

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?

[sundareshr]

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

[tmaltizo]

I'll try that. Ok Thanks!

[tmaltizo]

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

[sundareshr]

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

[tmaltizo]

Thanks for all of your help @sundareshr!