SPL QUERY

[Omarop]

Hello, 

I am trying to create a SPL query that will provide the following:

Active_Repository 

Qualifying Statement - Scan Policy

Qualifying Statement - Credentialed_Scan:true

Agent_Repository 

Qualifying Statement - Agent_Policy

Qualifying Statement - Credentialed checks : yes 

Query 

earliest=-7d@d index=acas "Credentialed Check" OR "credentialed check"

| rex field=operatingSystem "^(?P<OS_Type>\D+)\s(?P<OS_Version>.*)"

| rex field=dnsName "^(?P<hostname>\w+)\.(?P<domain>.*)$"

| rex field=system "^(?P<manufacture>\w+)\.(?P<serialnumber>.*)$

| eval AWS=if(like(dnsName,"cloud%"),"TRUE,"FALSE")

| iplocation ip

| eventstats count(eval(severity="low")) as low, count(eval(severity="medium")) as medium, count(eval(severity="critical")) as critical by ip,hostname, plugin_id

| dedup ip, hostname,plugin_id

| eval total = low+medium+high+critical

| where total>4

| table ip, repository.dataFormat, netbiosName, dnsName, AWS, hostname, macAddress, OS_Type, OS_Version, operatingSystem, SystemManufacutre, SystemSerialNumber, SystemModel, AWSAccountNumber, AWSInstanceID, AWSENI, plugin_id, pluginName, repository.name, cpe, low, medium, high, critical, total, Country, lat, lon

Unfortunately, I am not able to get all severity scores.   I keep getting a total of either a 0 or 1 for (low, medium, high, critical)  severity.   

Has anyone come across this issue?  

[Omarop]

Thank you for the response.  I did try using the command you provided sum(eval(severity="low")) as low but it only comes back for the low severity level but does not for medium, high, and critical.  I keep getting zero.

[richgalloway]

Do you have events with non-low severity?  Please share the complete, modified query.  It would help to see some sample events, too.

[Omarop]

Sure down below is the modified SPL QUERY

Query 

earliest=-7d@d index=acas "Credentialed Check" OR "credentialed check"

| rex field=operatingSystem "^(?P<OS_Type>\D+)\s(?P<OS_Version>.*)"

| rex field=dnsName "^(?P<hostname>\w+)\.(?P<domain>.*)$"

| rex field=system "^(?P<manufacture>\w+)\.(?P<serialnumber>.*)$

| eval AWS=if(like(dnsName,"cloud%"),"TRUE,"FALSE")

| iplocation ip

| eventstats sum(eval(severity="low")) as low, count(eval(severity="medium")) as medium, count(eval(severity="critical")) as critical by ip,hostname, plugin_id

| dedup ip, hostname,plugin_id

| eval total = low+medium+high+critical

| where total>4

| table ip, repository.dataFormat, netbiosName, dnsName, AWS, hostname, macAddress, OS_Type, OS_Version, operatingSystem, SystemManufacutre, SystemSerialNumber, SystemModel, AWSAccountNumber, AWSInstanceID, AWSENI, plugin_id, pluginName, repository.name, cpe, low, medium, high, critical, total, Country, lat, lon

[johnhuang]

I think the issue is that you are adding fields with null values and the result becomes null.

Try this before adding the values:

| fillnull value=0 low medium high critical
| eval total=low+medium+high+critical

Here's a different way to skin the cat.

| eval {severity}=1
| eventstats sum(low) AS low sum(medium) AS medium sum(high) AS high sum(critical) AS critical by ip,hostname, plugin_id
| fillnull value=0 low medium high critical
| eval total=low+medium+high+critical
| where total>4

[richgalloway]

The eventstats command was updated for severity=low, but not for the other severity levels.  Apply the same change (s/count/sum/) to them and see what results you get.

[Omarop]

I tried inputting the following and did not get any results.

| eventstats count sum(eval(severity="low")) as low, count sum(eval(severity="medium")) as medium, count sum(eval(severity="high")) as high, count sum(eval(severity="critical")) as critical

[richgalloway]

I may have missed it in an earlier response, but the correct function is "sum", not "count sum".

| eventstats sum(eval(severity="low")) as low, sum(eval(severity="medium")) as medium, sum(eval(severity="high")) as high, sum(eval(severity="critical")) as critical

[richgalloway]

The stats expression count(eval(severity="low")) as low will always return the same value, regardless of the severity level.  This is because the eval function returns either 0 or 1 and count merely says how many 0s and 1s there were - not what you're looking for.

Try using sum(eval(severity="low")) as low.