Unknown search command 'isnull'

[sakanet]

Hi,

I am trying the following search syntax in Splunk to build out a report of our top 25 riskiest systems. But when I run it, I get “Unknown search command 'isnull'” message.

Thanks in advance!

index=utexas-chomp (app=TENABLE event=INTEL OR event=VULN family_type!="compliance"severity_name=* NOT hasBeenMitigated=1)  OR (app=SCAVENGER event=INTEL OR event=VULN scan_net=ots_network OR scan_net=cluster_network) OR (app=BITSIGHT_FINDINGS  event=INTEL OR event=VULN affects_rating="True" grade!=GOOD grade!=NEUTRAL) earliest=-7d

 

| eval severity_name=if(app=="BITSIGHT_FINDINGS","seen from bitsight

| (!!!)",severity_name) eval pluginName=if(app=="BITSIGHT_FINDINGS" and

| isnull(pluginName), remediations_message, pluginName) eval

| pluginName=if(app=="BITSIGHT_FINDINGS" and isnull(pluginName),

| details_message, pluginName) eval

| pluginName=if(app=="BITSIGHT_FINDINGS" and isnull(pluginName),

| infection_family, pluginName) eval

| pluginName=if(app=="BITSIGHT_FINDINGS", "BITSIGHT_" + pluginName,

| pluginName) eval pluginID=if(app=="BITSIGHT_FINDINGS", pluginName,

| pluginID) eval pluginText=if(app=="BITSIGHT_FINDINGS",_raw,

| pluginText) eval

| severity_id=if(app=="BITSIGHT_FINDINGS",1,severity_id)

| eval host_seen_from_bitsight=if(app=="BITSIGHT_FINDINGS",1,0)

 

| eval severity_name=if(app=="SCAVENGER" AND

| scan_net="ots_network","seen from internet (!!!)",severity_name) eval

| pluginID=if(app=="SCAVENGER" AND

| scan_net="ots_network","seen_from_internet-"+protocol+port,pluginID)

| eval pluginName=if(app=="SCAVENGER" AND

| scan_net="ots_network","seen_from_internet-"+protocol+port,pluginName)

| eval pluginText=if(app=="SCAVENGER" AND

| scan_net="ots_network","seen_from_internet-"+protocol+port,pluginText)

| eval severity_id=if(app=="SCAVENGER" AND scan_net="ots_network"

| ,1,severity_id) eval host_seen_from_internet=if(app=="SCAVENGER" AND

| scan_net=="ots_network",1,0)

 

| eval severity_name=if(app=="SCAVENGER" AND

| scan_net=="cluster_network","seen from campus (!)",severity_name) eval

| pluginID=if(app=="SCAVENGER" AND

| scan_net=="cluster_network","seen_from_campus-"+protocol+port,pluginID

| ) eval pluginName=if(app=="SCAVENGER" AND

| scan_net=="cluster_network","seen_from_campus-"+protocol+port,pluginNa

| me) eval pluginText=if(app=="SCAVENGER" AND

| scan_net=="cluster_network","seen_from_campus-"+protocol+port,pluginTe

| xt) eval severity_id=if(app=="SCAVENGER" AND

| scan_net=="cluster_network" ,1,severity_id) eval

| host_seen_from_campus=if(app=="SCAVENGER" AND

| scan_net=="cluster_network",1,0)

 

| extract pairdelim=" ,", kvdelim="=", auto=f, limit=500000,

| maxchars=1204800 mv_add=f rex field=pluginText "Credentialed checks : (?<credentialed_checks>[^|]+)"

| where severity_id > 0

| dedup pluginID, srcip

| eventstats sum(host_seen_from_internet) as internet_ports_open

| sum(host_seen_from_campus) as campus_ports_open

| sum(host_seen_from_bitsight) as bitsight_ports_open by port, srcip

| `tenable_severity`

 

| eval last_seen = strftime(_time, "%m/%d/%Y %I:%M:%S %p") rex field=cpe

| "cpe:/[a-z]:(?<cpe_vendor>[^:]+):(?<cpe_software>[a-z0-9]+)"

| rex field=pluginText "Credentialed checks : (?<credentialed_checks>[^|]+)"

| eval cpe_vendor = if(pluginName like "seen_from_internet%",

| "seen_from_internet", cpe_vendor) eventstats count as cpe_count, by

| cpe_vendor, srcip eval VULNID = if(cpe_count > 4, cpe_count + " " +

| cpe_vendor + " vulnerabilities", pluginName) eval VULNID =

| if(isnull(cpe_vendor), pluginName, VULNID)

 

| eval wholecpe = cpe_vendor + ":" + cpe_software

 

| stats first(dnsName) as fqdn max(severity) as max_severity 

| values(VULNID) as vulns first(last_seen) as last_seen, 

| values(wholecpe) as vulnerable_software count as vulnerablities

| first(deptcode) as deptcode by srcip eval fqdn = if(fqdn == "" or

| isnull(fqdn), "Could Not Resolve", fqdn)

 

| sort 25 - max_severity

[inventsekar · Accepted solution]

Hi @sakanet  I have "approximately" edited your query, still lot of editings needed I think.  (this tip will be useful to you.... you can copy paste your search query into splunk search bar and press "Ctrl and \", which will format your search query in a readable style..)

index=utexas-chomp (app=TENABLE event=INTEL OR event=VULN family_type!="compliance"severity_name=* NOT hasBeenMitigated=1) OR (app=SCAVENGER event=INTEL OR event=VULN scan_net=ots_network OR scan_net=cluster_network) OR (app=BITSIGHT_FINDINGS event=INTEL OR event=VULN affects_rating="True" grade!=GOOD grade!=NEUTRAL) earliest=-7d 
| eval severity_name=if(app=="BITSIGHT_FINDINGS","seen from bitsight(!!!)",severity_name) 
| eval pluginName=if(app=="BITSIGHT_FINDINGS" and isnull(pluginName), remediations_message, pluginName) 
| eval pluginName=if(app=="BITSIGHT_FINDINGS" and isnull(pluginName), details_message, pluginName) 
| eval pluginName=if(app=="BITSIGHT_FINDINGS" and isnull(pluginName), infection_family, pluginName) 
| eval pluginName=if(app=="BITSIGHT_FINDINGS", "BITSIGHT_" + pluginName, pluginName) 
| eval pluginID=if(app=="BITSIGHT_FINDINGS", pluginName,pluginID) 
| eval pluginText=if(app=="BITSIGHT_FINDINGS",_raw,pluginText) 
| eval severity_id=if(app=="BITSIGHT_FINDINGS",1,severity_id) 
| eval host_seen_from_bitsight=if(app=="BITSIGHT_FINDINGS",1,0) 
| eval severity_name=if(app=="SCAVENGER" AND scan_net="ots_network","seen from internet (!!!)",severity_name) 
| eval pluginID=if(app=="SCAVENGER" AND scan_net="ots_network","seen_from_internet-"+protocol+port,pluginID) 
| eval pluginName=if(app=="SCAVENGER" AND scan_net="ots_network","seen_from_internet-"+protocol+port,pluginName) 
| eval pluginText=if(app=="SCAVENGER" AND scan_net="ots_network","seen_from_internet-"+protocol+port,pluginText) 
| eval severity_id=if(app=="SCAVENGER" AND scan_net="ots_network",1,severity_id) 
| eval host_seen_from_internet=if(app=="SCAVENGER" AND scan_net=="ots_network",1,0) 
| eval severity_name=if(app=="SCAVENGER" AND scan_net=="cluster_network","seen from campus (!)",severity_name) 
| eval pluginID=if(app=="SCAVENGER" AND scan_net=="cluster_network","seen_from_campus-"+protocol+port,pluginID) 
| eval pluginName=if(app=="SCAVENGER" AND scan_net=="cluster_network","seen_from_campus-"+protocol+port,pluginName) 
| eval pluginText=if(app=="SCAVENGER" AND scan_net=="cluster_network","seen_from_campus-"+protocol+port,pluginText) 
| eval severity_id=if(app=="SCAVENGER" AND scan_net=="cluster_network" ,1,severity_id) 
| eval host_seen_from_campus=if(app=="SCAVENGER" AND scan_net=="cluster_network",1,0) 
| extract pairdelim=" ,", kvdelim="=", auto=f, limit=500000, maxchars=1204800 mv_add=f 
| rex field=pluginText "Credentialed checks : (?<credentialed_checks>[^|]+)" 
| where severity_id > 0 
| dedup pluginID, srcip 
| eventstats sum(host_seen_from_internet) as internet_ports_open
    sum(host_seen_from_campus) as campus_ports_open
    sum(host_seen_from_bitsight) as bitsight_ports_open by port, srcip 
| `tenable_severity` 
| eval last_seen = strftime(_time, "%m/%d/%Y %I:%M:%S %p") 
| rex field=cpe "cpe:/[a-z]:(?<cpe_vendor>[^:]+):(?<cpe_software>[a-z0-9]+)" 
| rex field=pluginText "Credentialed checks : (?<credentialed_checks>[^|]+)" 
| eval cpe_vendor = if(pluginName like "seen_from_internet%", "seen_from_internet", cpe_vendor) eventstats count as cpe_count, by cpe_vendor, srcip 
| eval VULNID = if(cpe_count > 4, cpe_count + " " + cpe_vendor + " vulnerabilities", pluginName) 
| eval VULNID = if(isnull(cpe_vendor), pluginName, VULNID) 
| eval wholecpe = cpe_vendor + ":" + cpe_software 
| stats first(dnsName) as fqdn max(severity) as max_severity, values(VULNID) as vulns first(last_seen) as last_seen, values(wholecpe) as vulnerable_software count as vulnerablities
    first(deptcode) as deptcode by srcip 
| eval fqdn = if(fqdn == "" or isnull(fqdn), "Could Not Resolve", fqdn) 
| sort 25 - max_severity

[sakanet]

Hi @inventsekar ,I really appreciate your time and effort! I tried it and got the following two errors:

Error in 'eval' command: The expression is malformed.

The search job has failed due to an error. You may be able view the job in the 

Luckily, the person originally gave me this query fixed it. Apparently when it was copied and pasted, it messed up the formatting.

This is what worked:

I tried putting the code in </> as HTML/XML but when I submit, it says: "Your post has been changed because invalid HTML was found in the message body. The invalid HTML has been removed. Please review the message and submit the message when you are satisfied." I am not sure what language you guys select when you share code.)

--------------------------------------

index=utexas-chomp (app=TENABLE event=INTEL OR event=VULN family_type!="compliance"severity_name=* NOT hasBeenMitigated=1) OR (app=SCAVENGER event=INTEL OR event=VULN scan_net=ots_network OR scan_net=cluster_network) OR (app=BITSIGHT_FINDINGS event=INTEL OR event=VULN affects_rating="True" grade!=GOOD grade!=NEUTRAL) deptcodegroup=UTEXAS earliest=-7d

| eval severity_name=if(app=="BITSIGHT_FINDINGS","seen from bitsight (!!!)",severity_name)
| eval pluginName=if(app=="BITSIGHT_FINDINGS" and isnull(pluginName), remediations_message, pluginName)
| eval pluginName=if(app=="BITSIGHT_FINDINGS" and isnull(pluginName), details_message, pluginName)
| eval pluginName=if(app=="BITSIGHT_FINDINGS" and isnull(pluginName), infection_family, pluginName)
| eval pluginName=if(app=="BITSIGHT_FINDINGS", "BITSIGHT_" + pluginName, pluginName)
| eval pluginID=if(app=="BITSIGHT_FINDINGS", pluginName, pluginID)
| eval pluginText=if(app=="BITSIGHT_FINDINGS",_raw, pluginText)
| eval severity_id=if(app=="BITSIGHT_FINDINGS",1,severity_id)
| eval host_seen_from_bitsight=if(app=="BITSIGHT_FINDINGS",1,0)

| eval severity_name=if(app=="SCAVENGER" AND scan_net="ots_network","seen from internet (!!!)",severity_name)
| eval pluginID=if(app=="SCAVENGER" AND scan_net="ots_network","seen_from_internet-"+protocol+port,pluginID)
| eval pluginName=if(app=="SCAVENGER" AND scan_net="ots_network","seen_from_internet-"+protocol+port,pluginName)
| eval pluginText=if(app=="SCAVENGER" AND scan_net="ots_network","seen_from_internet-"+protocol+port,pluginText)
| eval severity_id=if(app=="SCAVENGER" AND scan_net="ots_network" ,1,severity_id)
| eval host_seen_from_internet=if(app=="SCAVENGER" AND scan_net=="ots_network",1,0)

| eval severity_name=if(app=="SCAVENGER" AND scan_net=="cluster_network","seen from campus (!)",severity_name)
| eval pluginID=if(app=="SCAVENGER" AND scan_net=="cluster_network","seen_from_campus-"+protocol+port,pluginID)
| eval pluginName=if(app=="SCAVENGER" AND scan_net=="cluster_network","seen_from_campus-"+protocol+port,pluginName)
| eval pluginText=if(app=="SCAVENGER" AND scan_net=="cluster_network","seen_from_campus-"+protocol+port,pluginText)
| eval severity_id=if(app=="SCAVENGER" AND scan_net=="cluster_network" ,1,severity_id)
| eval host_seen_from_campus=if(app=="SCAVENGER" AND scan_net=="cluster_network",1,0)

| extract pairdelim=" ,", kvdelim="=", auto=f, limit=500000, maxchars=1204800 mv_add=f
| rex field=pluginText "Credentialed checks : (?<credentialed_checks>[^|]+)"
| where severity_id > 0
| dedup pluginID, srcip
| eventstats sum(host_seen_from_internet) as internet_ports_open sum(host_seen_from_campus) as campus_ports_open sum(host_seen_from_bitsight) as bitsight_ports_open by port, srcip
| `tenable_severity`

| eval last_seen = strftime(_time, "%m/%d/%Y %I:%M:%S %p")
| rex field=cpe "cpe:/[a-z]:(?<cpe_vendor>[^:]+):(?<cpe_software>[a-z0-9]+)"
| rex field=pluginText "Credentialed checks : (?<credentialed_checks>[^|]+)"
| eval cpe_vendor = if(pluginName like "seen_from_internet%", "seen_from_internet", cpe_vendor)
| eventstats count as cpe_count, by cpe_vendor, srcip
| eval VULNID = if(cpe_count > 4, cpe_count + " " + cpe_vendor + " vulnerabilities", pluginName)
| eval VULNID = if(isnull(cpe_vendor), pluginName, VULNID)

| eval wholecpe = cpe_vendor + ":" + cpe_software

| stats first(dnsName) as fqdn max(severity) as max_severity values(VULNID) as vulns first(last_seen) as last_seen, values(wholecpe) as vulnerable_software count as vulnerablities first(deptcode) as deptcode by srcip
| eval fqdn = if(fqdn == "" or isnull(fqdn), "Could Not Resolve", fqdn)

| sort 25 - max_severity

index=utexas-chomp (app=TENABLE event=INTEL OR event=VULN family_type!="compliance"severity_name=* NOT hasBeenMitigated=1)  OR (app=SCAVENGER event=INTEL OR event=VULN scan_net=ots_network OR scan_net=cluster_network) OR (app=BITSIGHT_FINDINGS  event=INTEL OR event=VULN affects_rating="True" grade!=GOOD grade!=NEUTRAL) deptcodegroup=UTEXAS earliest=-7d

| eval severity_name=if(app=="BITSIGHT_FINDINGS","seen from bitsight (!!!)",severity_name)
| eval pluginName=if(app=="BITSIGHT_FINDINGS" and isnull(pluginName), remediations_message, pluginName)
| eval pluginName=if(app=="BITSIGHT_FINDINGS" and isnull(pluginName), details_message, pluginName)
| eval pluginName=if(app=="BITSIGHT_FINDINGS" and isnull(pluginName), infection_family, pluginName)
| eval pluginName=if(app=="BITSIGHT_FINDINGS", "BITSIGHT_" + pluginName, pluginName)
| eval pluginID=if(app=="BITSIGHT_FINDINGS", pluginName, pluginID)
| eval pluginText=if(app=="BITSIGHT_FINDINGS",_raw, pluginText)
| eval severity_id=if(app=="BITSIGHT_FINDINGS",1,severity_id)
| eval host_seen_from_bitsight=if(app=="BITSIGHT_FINDINGS",1,0)

| eval severity_name=if(app=="SCAVENGER" AND scan_net="ots_network","seen from internet (!!!)",severity_name)
| eval pluginID=if(app=="SCAVENGER" AND scan_net="ots_network","seen_from_internet-"+protocol+port,pluginID)
| eval pluginName=if(app=="SCAVENGER" AND scan_net="ots_network","seen_from_internet-"+protocol+port,pluginName)
| eval pluginText=if(app=="SCAVENGER" AND scan_net="ots_network","seen_from_internet-"+protocol+port,pluginText)
| eval severity_id=if(app=="SCAVENGER" AND scan_net="ots_network" ,1,severity_id)
| eval host_seen_from_internet=if(app=="SCAVENGER" AND scan_net=="ots_network",1,0)

| eval severity_name=if(app=="SCAVENGER" AND scan_net=="cluster_network","seen from campus (!)",severity_name)
| eval pluginID=if(app=="SCAVENGER" AND scan_net=="cluster_network","seen_from_campus-"+protocol+port,pluginID)
| eval pluginName=if(app=="SCAVENGER" AND scan_net=="cluster_network","seen_from_campus-"+protocol+port,pluginName)
| eval pluginText=if(app=="SCAVENGER" AND scan_net=="cluster_network","seen_from_campus-"+protocol+port,pluginText)
| eval severity_id=if(app=="SCAVENGER" AND scan_net=="cluster_network" ,1,severity_id)
| eval host_seen_from_campus=if(app=="SCAVENGER" AND scan_net=="cluster_network",1,0)

| extract pairdelim=" ,", kvdelim="=", auto=f, limit=500000, maxchars=1204800 mv_add=f
| rex field=pluginText "Credentialed checks : (?<credentialed_checks>[^|]+)"
| where severity_id > 0
| dedup pluginID, srcip
| eventstats sum(host_seen_from_internet) as internet_ports_open sum(host_seen_from_campus) as campus_ports_open sum(host_seen_from_bitsight) as bitsight_ports_open by port, srcip
| `tenable_severity`

| eval last_seen = strftime(_time, "%m/%d/%Y %I:%M:%S %p")
| rex field=cpe "cpe:/[a-z]:(?<cpe_vendor>[^:]+):(?<cpe_software>[a-z0-9]+)"
| rex field=pluginText "Credentialed checks : (?<credentialed_checks>[^|]+)"
| eval cpe_vendor = if(pluginName like "seen_from_internet%", "seen_from_internet", cpe_vendor)
| eventstats count as cpe_count, by cpe_vendor, srcip
| eval VULNID = if(cpe_count > 4, cpe_count + " " + cpe_vendor + " vulnerabilities", pluginName)
| eval VULNID = if(isnull(cpe_vendor), pluginName, VULNID)

| eval wholecpe = cpe_vendor + ":" + cpe_software

| stats first(dnsName) as fqdn max(severity) as max_severity  values(VULNID) as vulns first(last_seen) as last_seen,  values(wholecpe) as vulnerable_software count as vulnerablities first(deptcode) as deptcode by srcip
| eval fqdn = if(fqdn == "" or isnull(fqdn), "Could Not Resolve", fqdn)

| sort 25 - max_severity

[sakanet]

Hi renjith_nair, 

I just signed up last night and posted this question, I swear I did not see all the formatting tools, it was late at night, maybe I just missed them.

This code was given to me by someone else. I am very new to this, I will try to reach out to him and see if he can help.

Thanks!

[inventsekar]

Hi @sakanet 

its difficult and confusing to edit/update your query(the "|" are misplaced ).. so lets do this step by step.. 

this is the format of the "isnull" command(isnull always works inside the "eval")

| eval VULNID = if(isnull(cpe_vendor), pluginName, VULNID)

now, you can edit your query accordingly and then copy paste the query, then, we will edit/correct the typing issues. 

isnull documentation - 

https://docs.splunk.com/Documentation/Splunk/8.1.0/SearchReference/InformationalFunctions#isnull.28X...

[renjith_nair]

Please use code sample (</>) for search snippets for better readability.

Are those pipe( | ) symbols in each line part of your search ? If yes, your search is wrongly formatted and the search terms are wrongly placed.