How to include a distinct count in an eval statement?

[trevorr2004]

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

[sundareshr · Accepted solution]

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

[trevorr2004]

Worked like a charm. Thanks again sundareshr!

[trevorr2004]

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

[sundareshr]

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