topic How to edit my search to use a custom field created with eval in my time chart search? in Splunk Search

How to edit my search to use a custom field created with eval in my time chart search?

trevorr2004 — Wed, 24 Aug 2016 21:41:24 GMT

I have a search that comes up with a score based off a custom formula from nessus scan results. I want to plot that vulnscore over the past 90 days IE the score from 0-30/30-60/60-90. Can anyone provide a little guidance on how I can make use of timechart with a custom eval field rather than simply running a search and using those results in the timechart. Listed below is my base search.

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 
|stats count by signature_id cvss_base_score 
|eval  scoreadjust=floor(cvss_base_score) 
| eval scoreadjust=round(pow(scoreadjust * 0.1, 3)*10,2)
|  eval riskscore=count*scoreadjust/5500
| stats sum(riskscore) as VulnScore

Re: How to edit my search to use a custom field created with eval in my time chart search?

sundareshr — Wed, 24 Aug 2016 22:34:47 GMT

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*"  earliest=-90d@d 
| dedup dest_dns signature_id 
| eval range=case(_time>relative_time(now(), "-90d@d") AND _time<relative_time(now(), "-60d@d"), "60-90 days", _time>relative_time(now(), "-60d@d") AND _time<relative_time(now(), "-30d@d"), "30-60 days", _time>relative_time(now(), "-30d@d"), "0-30 days", 1=1, "UNK")
| stats count by range signature_id cvss_base_score 
| eval  scoreadjust=floor(cvss_base_score) 
| eval scoreadjust=round(pow(scoreadjust * 0.1, 3)*10,2)
| eval riskscore=count*scoreadjust/5500
| stats sum(riskscore) as VulnScore by range

Re: How to edit my search to use a custom field created with eval in my time chart search?

trevorr2004 — Thu, 25 Aug 2016 15:51:25 GMT

This worked perfectly for a line graph. Do you have any suggestions on how I could use this same search to use the single value to give just a comparison from previous months?

Re: How to edit my search to use a custom field created with eval in my time chart search?

sundareshr — Thu, 25 Aug 2016 16:09:30 GMT

Try this for the Prev Month comparison (I assume you are referring to trend in single value)

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*"  earliest=-60d@d 
 | 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 * 0.1, 3)*10,2)
 | eval riskscore=count*scoreadjust/5500
 | stats sum(riskscore) as VulnScore by _time

Re: How to edit my search to use a custom field created with eval in my time chart search?

trevorr2004 — Thu, 25 Aug 2016 16:32:58 GMT

This worked also. Final question, say I have for instance an eval

eval PatchScorePerHost=PatchScore/5550

and I don't need to sum but need to plot that in timetables also. Is there any function of the stat command to just plot the output of the final eval. Basically I am working on creating a risk score that we can track in splunk.

Re: How to edit my search to use a custom field created with eval in my time chart search?

sundareshr — Thu, 25 Aug 2016 17:42:26 GMT

Try using values(PatchScorePerHost) for a dedup'd list OR list(PatchScorePerHost) for all entries

Re: How to edit my search to use a custom field created with eval in my time chart search?

trevorr2004 — Thu, 25 Aug 2016 21:24:02 GMT

I was able to rearrange my equation like such with your guidance.

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

Instead of using the static number of 5500, is there a way I could dynamically count my host scan then use that in an eval statement so

stats dc(dest_dns) and then use that instead of the 5500?