https://community.splunk.com/t5/Splunk-Search/Forescout-How-to-calculate-the-average-time-of-all-devices/m-p/273373#M82395 <P>Hi <a href="https://community.splunk.com/t5/user/viewprofilepage/user-id/140181">@sundareshr</a>. Thanks again for your help in all this.<BR /> I modified the search to apply to our data:</P> <PRE><CODE>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 </CODE></PRE> <P>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?</P> Tue, 29 Sep 2020 11:33:51 GMT tmaltizo 2020-09-29T11:33:51Z https://community.splunk.com/t5/Splunk-Search/Forescout-How-to-calculate-the-average-time-of-all-devices/m-p/273371#M82393 <P>We're looking to get the average time, given all, devices/laptops that are non-compliant with encryption.</P> <P>In Forescout, these are the fields for detecting compliancy:<BR /> <STRONG>status</STRONG> = compliant, non-compliant<BR /> <STRONG>description</STRONG> = "Laptop Encryption is not installed", "Symantec Encryption Running, Activated"</P> <P>Once, we obtain the average time, we want to monitor any improvement in the form of Encryption posture.</P> <P>Thanks for any help!<BR /> Trista</P> Wed, 26 Oct 2016 14:16:44 GMT https://community.splunk.com/t5/Splunk-Search/Forescout-How-to-calculate-the-average-time-of-all-devices/m-p/273371#M82393 tmaltizo 2016-10-26T14:16:44Z https://community.splunk.com/t5/Splunk-Search/Forescout-How-to-calculate-the-average-time-of-all-devices/m-p/273372#M82394 <P>Try this *<STRONG><EM>UPDATED</EM></STRONG>*</P> <PRE><CODE> 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 </CODE></PRE> Wed, 26 Oct 2016 14:34:12 GMT https://community.splunk.com/t5/Splunk-Search/Forescout-How-to-calculate-the-average-time-of-all-devices/m-p/273372#M82394 sundareshr 2016-10-26T14:34:12Z https://community.splunk.com/t5/Splunk-Search/Forescout-How-to-calculate-the-average-time-of-all-devices/m-p/273373#M82395 <P>Hi <a href="https://community.splunk.com/t5/user/viewprofilepage/user-id/140181">@sundareshr</a>. Thanks again for your help in all this.<BR /> I modified the search to apply to our data:</P> <PRE><CODE>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 </CODE></PRE> <P>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?</P> Tue, 29 Sep 2020 11:33:51 GMT https://community.splunk.com/t5/Splunk-Search/Forescout-How-to-calculate-the-average-time-of-all-devices/m-p/273373#M82395 tmaltizo 2020-09-29T11:33:51Z https://community.splunk.com/t5/Splunk-Search/Forescout-How-to-calculate-the-average-time-of-all-devices/m-p/273374#M82396 <P>Try this... </P> <PRE><CODE>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 </CODE></PRE> Wed, 26 Oct 2016 20:19:11 GMT https://community.splunk.com/t5/Splunk-Search/Forescout-How-to-calculate-the-average-time-of-all-devices/m-p/273374#M82396 sundareshr 2016-10-26T20:19:11Z https://community.splunk.com/t5/Splunk-Search/Forescout-How-to-calculate-the-average-time-of-all-devices/m-p/273375#M82397 <P>ok, is that avg_duration in seconds, minutes, hours, days? The output is coming up as a negative number.... -2819509.457109</P> Wed, 26 Oct 2016 20:33:40 GMT https://community.splunk.com/t5/Splunk-Search/Forescout-How-to-calculate-the-average-time-of-all-devices/m-p/273375#M82397 tmaltizo 2016-10-26T20:33:40Z https://community.splunk.com/t5/Splunk-Search/Forescout-How-to-calculate-the-average-time-of-all-devices/m-p/273376#M82398 <P>If duration is negative, make this change <CODE>eval duration=noncompliant-compliant</CODE> . </P> Wed, 26 Oct 2016 20:39:26 GMT https://community.splunk.com/t5/Splunk-Search/Forescout-How-to-calculate-the-average-time-of-all-devices/m-p/273376#M82398 sundareshr 2016-10-26T20:39:26Z https://community.splunk.com/t5/Splunk-Search/Forescout-How-to-calculate-the-average-time-of-all-devices/m-p/273377#M82399 <P>Yes, I noticed that and made that change. So, this duration is in seconds? How would I change it to hours?</P> Wed, 26 Oct 2016 20:44:30 GMT https://community.splunk.com/t5/Splunk-Search/Forescout-How-to-calculate-the-average-time-of-all-devices/m-p/273377#M82399 tmaltizo 2016-10-26T20:44:30Z https://community.splunk.com/t5/Splunk-Search/Forescout-How-to-calculate-the-average-time-of-all-devices/m-p/273378#M82400 <P>Add this to the end <CODE>| eval duration=tostring(duration, "duration")</CODE> OR if you only want hours, divide by 3600 <CODE>| eval duration_hrs=round(duration/3600, 0)</CODE></P> Wed, 26 Oct 2016 21:18:27 GMT https://community.splunk.com/t5/Splunk-Search/Forescout-How-to-calculate-the-average-time-of-all-devices/m-p/273378#M82400 sundareshr 2016-10-26T21:18:27Z https://community.splunk.com/t5/Splunk-Search/Forescout-How-to-calculate-the-average-time-of-all-devices/m-p/273379#M82401 <P>This is great @sundareshr! Thank you!</P> <P>One last thing...how would I derive the lowest and highest duration that is contributing to this avg?</P> Thu, 27 Oct 2016 13:25:37 GMT https://community.splunk.com/t5/Splunk-Search/Forescout-How-to-calculate-the-average-time-of-all-devices/m-p/273379#M82401 tmaltizo 2016-10-27T13:25:37Z https://community.splunk.com/t5/Splunk-Search/Forescout-How-to-calculate-the-average-time-of-all-devices/m-p/273380#M82402 <P>See updated answer</P> Thu, 27 Oct 2016 13:31:06 GMT https://community.splunk.com/t5/Splunk-Search/Forescout-How-to-calculate-the-average-time-of-all-devices/m-p/273380#M82402 sundareshr 2016-10-27T13:31:06Z https://community.splunk.com/t5/Splunk-Search/Forescout-How-to-calculate-the-average-time-of-all-devices/m-p/273381#M82403 <P>Thank you so much for your help <a href="https://community.splunk.com/t5/user/viewprofilepage/user-id/140181">@sundareshr</a>!</P> <P>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?</P> <P>The minimum duration time<BR /> The maximum duration time<BR /> Avg duration time</P> Tue, 29 Sep 2020 11:34:09 GMT https://community.splunk.com/t5/Splunk-Search/Forescout-How-to-calculate-the-average-time-of-all-devices/m-p/273381#M82403 tmaltizo 2020-09-29T11:34:09Z https://community.splunk.com/t5/Splunk-Search/Forescout-How-to-calculate-the-average-time-of-all-devices/m-p/273382#M82404 <P>@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!</P> Tue, 08 Nov 2016 00:49:01 GMT https://community.splunk.com/t5/Splunk-Search/Forescout-How-to-calculate-the-average-time-of-all-devices/m-p/273382#M82404 aaraneta_splunk 2016-11-08T00:49:01Z https://community.splunk.com/t5/Splunk-Search/Forescout-How-to-calculate-the-average-time-of-all-devices/m-p/273383#M82405 <P>@aaraneta, I just sent a followup comment to him.Thanks!</P> Wed, 30 Nov 2016 19:32:30 GMT https://community.splunk.com/t5/Splunk-Search/Forescout-How-to-calculate-the-average-time-of-all-devices/m-p/273383#M82405 tmaltizo 2016-11-30T19:32:30Z