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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

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