Splunk Search

Forescout: How to calculate the average time of all devices/laptops that are non-compliant with encryption?

tmaltizo
Path Finder

We're looking to get the average time, given all, devices/laptops that are non-compliant with encryption.

In Forescout, these are the fields for detecting compliancy:
status = compliant, non-compliant
description = "Laptop Encryption is not installed", "Symantec Encryption Running, Activated"

Once, we obtain the average time, we want to monitor any improvement in the form of Encryption posture.

Thanks for any help!
Trista

0 Karma
1 Solution

sundareshr
Legend

Try this *UPDATED*

 index=forescout sourcetype="fs_encryption_compliance" | stats earliest(eval(if(status="non-compliant", _time, null()))) as noncompliant earliest(eval(if(status="compliant", _time, null()))) as compliant by src_nt_host | where isnotnull(noncompliant) | eval duration=compliant-noncompliant | eventstats max(duration) as max min(duration) as min | stats values(max) as max max(min) as min avg(duration) as avg_duration values(eval(if(max=duration, src_nt_host, "null()))) as max_contrib values(eval(if(min=duration, src_nt_host, "null()))) as min_contrib 

View solution in original post

0 Karma

sundareshr
Legend

Try this *UPDATED*

 index=forescout sourcetype="fs_encryption_compliance" | stats earliest(eval(if(status="non-compliant", _time, null()))) as noncompliant earliest(eval(if(status="compliant", _time, null()))) as compliant by src_nt_host | where isnotnull(noncompliant) | eval duration=compliant-noncompliant | eventstats max(duration) as max min(duration) as min | stats values(max) as max max(min) as min avg(duration) as avg_duration values(eval(if(max=duration, src_nt_host, "null()))) as max_contrib values(eval(if(min=duration, src_nt_host, "null()))) as min_contrib 
0 Karma

tmaltizo
Path Finder

Hi @sundareshr. Thanks again for your help in all this.
I modified the search to apply to our data:

index=forescout sourcetype="fs_encryption_compliance" | stats earliest(eval(if(status="non-compliant", _time, null()))) as noncompliant earliest(eval(if(status="compliant", _time, null()))) as compliant by src_nt_host | where isnotnull(noncompliant) | eval duration="non-compliant" | stats avg(duration) as avg_duration by src_nt_host

However, the output is listing src_nt_host (device) and NULL avg_duration numbers. We're actually looking for just one avg number for all of these devices. So, can we simply add the duration numbers from each src_nt_host and then derive the avg from that?

0 Karma

sundareshr
Legend

Try this...

index=forescout sourcetype="fs_encryption_compliance" | stats earliest(eval(if(status="non-compliant", _time, null()))) as noncompliant earliest(eval(if(status="compliant", _time, null()))) as compliant by src_nt_host | where isnotnull(noncompliant) | eval duration=compliant-noncompliant | stats avg(duration) as avg_duration
0 Karma

tmaltizo
Path Finder

ok, is that avg_duration in seconds, minutes, hours, days? The output is coming up as a negative number.... -2819509.457109

0 Karma

sundareshr
Legend

If duration is negative, make this change eval duration=noncompliant-compliant .

0 Karma

tmaltizo
Path Finder

Yes, I noticed that and made that change. So, this duration is in seconds? How would I change it to hours?

0 Karma

sundareshr
Legend

Add this to the end | eval duration=tostring(duration, "duration") OR if you only want hours, divide by 3600 | eval duration_hrs=round(duration/3600, 0)

0 Karma

tmaltizo
Path Finder

This is great @sundareshr! Thank you!

One last thing...how would I derive the lowest and highest duration that is contributing to this avg?

0 Karma

sundareshr
Legend

See updated answer

0 Karma

tmaltizo
Path Finder

Thank you so much for your help @sundareshr!

I'm getting the src_nt_host names for the max and min contributors. How do I obtain the duration time for these two values?

The minimum duration time
The maximum duration time
Avg duration time

0 Karma

aaraneta_splunk
Splunk Employee
Splunk Employee

@tmaltizo - Did @sundareshr happen to answer your original question? If yes, please click "Accept" to resolve your post. If not, feel free to leave another comment with feedback for him. Thanks!

0 Karma

tmaltizo
Path Finder

@aaraneta, I just sent a followup comment to him.Thanks!

0 Karma
Get Updates on the Splunk Community!

Thanks for the Memories! Splunk University, .conf24, and Community Connections

Thank you to everyone in the Splunk Community who joined us for .conf24 – starting with Splunk University and ...

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...