How do I fine tune my custom scoring system?

[UMDTERPS]

| inputlookup scanner_visibility.csv
| lookup visibility_blue.csv Acronym AS application local=t OUTPUTNEW "Risk Score"
| lookup server_dump.csv Acronym AS application local=t OUTPUTNEW "Authorization Removal Date"
| rename norton_assets as norton 
| lookup servertypes_scanner_weights.csv servertype OUTPUTNEW norton_weight nessus_weight metasploit_weight  nexpose_weight
| eval norton = if(like(norton, "%2019") AND relative_time(now(), "-30d@d") < strptime(norton,"%m/%d/%Y"), norton_weight, 0)
| eval nessus = if(like(nessus, "%2019") AND relative_time(now(), "-30d@d") < strptime(nessus,"%m/%d/%Y"), nessus_weight, 0)
| eval metasploit = if(like(metasploit, "%2019") AND relative_time(now(), "-30d@d") < strptime(metasploit,"%m/%d/%Y"), metasploit_weight,0)
| eval nexpose = if(like(nexpose, "%2019") AND relative_time(now(), "-30d@d") < strptime(nexpose,"%m/%d/%Y"), nexpose_weight, 0)
|eventstats count(ip) as total 
sum(norton) as norton_count
sum(nessus) as nessus_count
sum(meteasploit) as metasploit_count
sum(nexpose) as nexpose_count
| eval norton_score = round (((norton_count / total)*100), 2)
| eval nessus_score = round (((nessus_count / total)*100), 2)
| eval metasploit_score = round (((metasploit_count / total)*100), 2)
| eval nexpose_score = round (((nexpose_count / total)*100), 2)
| eval date = strftime(now(), "%m/%d/%Y")
| eval _time = strptime(date, "%m/%d/%Y") 
| fields _time  date  norton_score  nessus_score  metasploit_score  nexpose_score  

Above, is two abbreviated/dummy data CSV’s (visibility and scoring) and SPL that generates a risk score based on its visibility and scoring CSV’s. The code and everything works, however we want to get more accurate scores. If you look at the visibility CSV it has dates on when a particular system was seen by scanner and the servertypes_scanner_weights.csv will give it a weight. Those weights are then added up using “sum” and then divided by the total to get a % score. The issue is, some of our system devices cannot be seen by a few of the scanners. For example, the Cisco_ASA and Juniper_Switch cannot be seen by Meteasploit and Nexpose scanners, so they don’t get weights for those scanners and we increase the weights for the scanners that can see those two devices.

Any ideas in SPL how to exclude systems that can’t be seen by Metasploit and Nexpose scanners so they are not counted in the "sum" of the final score for each scanner?

[UMDTERPS · Accepted solution]

| inputlookup scanner_visibility.csv
| lookup visibility_blue.csv Acronym AS application local=t OUTPUTNEW "Risk Score"
| lookup server_dump.csv Acronym AS application local=t OUTPUTNEW "Authorization Removal Date"
| rename norton_assets as norton 
| lookup servertypes_scanner_weights.csv servertype OUTPUTNEW norton_weight nexpose_weight nessus_weight metasploit_weight 
openvas_weight nexpose_weight
| eval norton = if(like(norton, "%2019") AND relative_time(now(), "-30d@d") < strptime(norton,"%m/%d/%Y"), norton_weight, 0)
| eval nessus = if(like(nessus, "%2019") AND relative_time(now(), "-30d@d") < strptime(nessus,"%m/%d/%Y"), nessus_weight, 0)
| eval metasploit = if(like(metasploit, "%2019") AND relative_time(now(), "-30d@d") < strptime(metasploit,"%m/%d/%Y"), metasploit_weight,0)
| eval nexpose = if(like(nexpose, "%2019") AND relative_time(now(), "-30d@d") < strptime(nexpose,"%m/%d/%Y"), nexpose_weight, 0)
|eventstats count(ip) as total 
sum(norton) as norton_points
sum(nessus) as nessus_points
sum(meteasploit) as metasploit_points
sum(nexpose) as nexpose_points
count(eval(found="Yes")) as found_yes by system
sum(norton_weight) as norton_points_possible
sum(nessus_weight) as nessus_points_possible
sum(metasploit_weight) as metasploit_points_possible
sum(nexpose_weight) as nexpose_points_possible
| eval norton_score = round (((norton_points / norton_points_possible)*100), 2)
| eval nessus_score = round (((nessus_points / nessus_points_possible)*100), 2)
| eval metasploit_score = round (((metasploit_points / metespliot_points_possible)*100), 2)
| eval nexpose_score = round (((nexpose_points /nexpose_points_possible)*100), 2)
| dedup system
| eval date = strftime(now(), "%m/%d/%Y")
| eval _time = strptime(date, "%m/%d/%Y") 
| fields _time  date  norton_score  nessus_score  metasploit_score  nexpose_score  

So, I did some re-configuring by adding the "sum" of the count of systems that have a "_weight" or "0" and add the "sum" of the "_weights." Then divide the total of systems that have a weight by the "sum" of the "_weights."

This appears to work =0)

[to4kawa]

| inputlookup scanner_visibility.csv
| search Metasploit_Date=* OR Nexpose_Date=*
| lookup visibility_blue.csv Acronym AS application local=t OUTPUTNEW "Risk Score"
....