Splunk Search

How to include a distinct count in an eval statement?

trevorr2004
Engager

I am currenlty trying to make a search a little more dynamic based off scanned devices rather than a static number

index=network sourcetype=nessus severity!=informational signature!=*Windows* signature!=*Adobe* signature!=*Java* signature_family!="Windows : Microsoft Bulletins" signature_family!="Red Hat Local Security Checks" signature!="Google Chrome*" signature!="Firefox*" signature!="MS*" signature!="Flash Player*" signature!="Solaris*"     
| dedup dest_dns signature_id    
| bin span=1mon _time   
| stats count by _time signature_id cvss_base_score    
| eval  scoreadjust=floor(cvss_base_score)    
| eval scoreadjust=round(pow(scoreadjust,3)/100)   
| eval riskscore=count*scoreadjust/5500 
| eval my_time=_time 
| convert timeformat="%m-%Y" ctime(my_time)   
| stats sum(riskscore) as VulnScore by my_time

I want to replace the 5500 with using the dc(dest_dns) for scanned devices rather then a number we think we scan.

Any suggestions on how I can mold my search or include this would be very helpful

0 Karma
1 Solution

sundareshr
Legend

Try this

 index=network sourcetype=nessus severity!=informational signature!=*Windows* signature!=*Adobe* signature!=*Java* signature_family!="Windows : Microsoft Bulletins" signature_family!="Red Hat Local Security Checks" signature!="Google Chrome*" signature!="Firefox*" signature!="MS*" signature!="Flash Player*" signature!="Solaris*"     
 | dedup dest_dns signature_id    
 | eventstats dc(dest_dns) as TotalDNS
 | bin span=1mon _time   
 | stats count max(TotalDNS) as TotalDNS by _time signature_id cvss_base_score    
 | eval  scoreadjust=floor(cvss_base_score)    
 | eval scoreadjust=round(pow(scoreadjust,3)/100) 
 | eval riskscore=count*scoreadjust/TotalDNS 
 | eval my_time=_time 
 | convert timeformat="%m-%Y" ctime(my_time)   
 | stats sum(riskscore) as VulnScore by my_time

View solution in original post

0 Karma

sundareshr
Legend

Try this

 index=network sourcetype=nessus severity!=informational signature!=*Windows* signature!=*Adobe* signature!=*Java* signature_family!="Windows : Microsoft Bulletins" signature_family!="Red Hat Local Security Checks" signature!="Google Chrome*" signature!="Firefox*" signature!="MS*" signature!="Flash Player*" signature!="Solaris*"     
 | dedup dest_dns signature_id    
 | eventstats dc(dest_dns) as TotalDNS
 | bin span=1mon _time   
 | stats count max(TotalDNS) as TotalDNS by _time signature_id cvss_base_score    
 | eval  scoreadjust=floor(cvss_base_score)    
 | eval scoreadjust=round(pow(scoreadjust,3)/100) 
 | eval riskscore=count*scoreadjust/TotalDNS 
 | eval my_time=_time 
 | convert timeformat="%m-%Y" ctime(my_time)   
 | stats sum(riskscore) as VulnScore by my_time
0 Karma

trevorr2004
Engager

Worked like a charm. Thanks again sundareshr!

0 Karma

trevorr2004
Engager

I have the same issue with this query but tried to repeat the previous process. I read and read the stats command page but it's still confusing when doing the max on dest_dns. Could you provide a little logic behind how you come up with these replies?

 index=network sourcetype=nessus severity!=informational signature=*Windows* OR signature=*Adobe* OR signature=*Java* OR signature_family="Windows : Microsoft Bulletins" OR signature_family="Red Hat Local Security Checks" OR signature="Google Chrome*" OR signature="Firefox*" OR signature="MS*" OR signature="Flash Player*" OR signature="Solaris*"  
    | dedup dest_dns signature_id 
    |  bin span=1mon _time 
    | eval PatchScore=cvss_base_score/5500 
    | eval Time=_time 
    | convert timeformat="%m-%Y" ctime(Time)   
    |  stats sum(PatchScore) AS "Avg Host Patch Score" by Time
0 Karma

sundareshr
Legend

The eventstats command get the dc(dns) and assigns it to the totaldns field on every event. In the original query, you had a stats command to get count. The only field that remain after the stats command are the one's that are included in the stats command. So I added the max(dest_dns), (could have used anything, values, first, last etc.) only so the field carries over and can be used later. In the second query, since you do not have the stats command, you don't need the max piece. So try this

index=network sourcetype=nessus severity!=informational signature=*Windows* OR signature=*Adobe* OR signature=*Java* OR signature_family="Windows : Microsoft Bulletins" OR signature_family="Red Hat Local Security Checks" OR signature="Google Chrome*" OR signature="Firefox*" OR signature="MS*" OR signature="Flash Player*" OR signature="Solaris*"  
     | dedup dest_dns signature_id 
     | eventstats dc(dest_dns) as TotalDNS
     |  bin span=1mon _time 
     | eval PatchScore=cvss_base_score/TotalDNS 
     | eval Time=_time 
     | convert timeformat="%m-%Y" ctime(Time)   
     |  stats sum(PatchScore) AS "Avg Host Patch Score" by Time
0 Karma
Get Updates on the Splunk Community!

Updated Data Type Articles, Anniversary Celebrations, and More on Splunk Lantern

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

A Prelude to .conf25: Your Guide to Splunk University

Heading to Boston this September for .conf25? Get a jumpstart by arriving a few days early for Splunk ...

4 Ways the Splunk Community Helps You Prepare for .conf25

.conf25 is right around the corner, and whether you’re a first-time attendee or a seasoned Splunker, the ...