topic Re: SPL QUERY in Training + Certification Discussions

SPL QUERY

Omarop — Wed, 01 Dec 2021 21:01:32 GMT

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?

Re: SPL QUERY

richgalloway — Thu, 02 Dec 2021 13:35:39 GMT

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.

Re: SPL QUERY

Omarop — Thu, 02 Dec 2021 18:48:18 GMT

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.

Re: SPL QUERY

richgalloway — Thu, 02 Dec 2021 19:12:17 GMT

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

Re: SPL QUERY

Omarop — Thu, 02 Dec 2021 20:17:15 GMT

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

Re: SPL QUERY

richgalloway — Fri, 03 Dec 2021 13:09:47 GMT

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.

Re: SPL QUERY

Omarop — Mon, 06 Dec 2021 14:36:54 GMT

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

Re: SPL QUERY

richgalloway — Mon, 06 Dec 2021 16:40:16 GMT

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

Re: SPL QUERY

johnhuang — Tue, 07 Dec 2021 02:20:48 GMT

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