topic Re: Using join to show fields for two sourcetypes in one table, why are some fields empty? in Splunk Search

Using join to show fields for two sourcetypes in one table, why are some fields empty?

mdorobek — Wed, 29 Jun 2016 10:05:00 GMT

Hi,

I need to show fields for two sourcetypes in one table. Those two sourcetypes have the same ID field: "plugin_id" and "id". For this reason, I tried to join them with the following search:

...|rename plugin_id as id| table id, host-fqdn, hostname, severity | join id type=left [search index=nessus sourcetype="nessus:plugin"| table id, cvss_base_score, description, exploit_available, exploitability_ease, family_name, plugin_name, risk_factor] | table host-fqdn, hostname, severity, id, cvss_base_score, description, exploit_available, exploitability_ease, family_name, plugin_name, risk_factor

The failure is that some fields in the Table are just empty.

Re: Using join to show fields for two sourcetypes in one table, why are some fields empty?

Raghav2384 — Wed, 29 Jun 2016 16:49:44 GMT

try appendcols

Example sourcetype=abc|table x,y,z,id|appendcols [search sourcetype=def|blah blah|fields id]

Hope this helps,

Thanks,
Raghav

Re: Using join to show fields for two sourcetypes in one table, why are some fields empty?

sundareshr — Wed, 29 Jun 2016 18:12:51 GMT

Try this

(index=foo sourcetype=bar) or (index=nessus sourcetype="nessus:plugin") | eval eid=coalesce(id, plugin_id) | fields host-fqdn, hostname, severity, eid, cvss_base_score, description, exploit_available, exploitability_ease, family_name, plugin_name, risk_factor | stats values(*) as * by host-fqdn eid | table host-fqdn, hostname, severity, id, cvss_base_score, description, exploit_available, exploitability_ease, family_name, plugin_name, risk_factor

Re: Using join to show fields for two sourcetypes in one table, why are some fields empty?

mdorobek — Tue, 29 Sep 2020 10:04:49 GMT

Thank you, but this doesn't work eighter. The fields cvss_base_score, description, exploit_available, exploitability_ease, family_name and risk_factor are still empty

Re: Using join to show fields for two sourcetypes in one table, why are some fields empty?

mdorobek — Thu, 30 Jun 2016 06:41:18 GMT

Thank you, but I still have got the same problem.

Re: Using join to show fields for two sourcetypes in one table, why are some fields empty?

mdorobek — Tue, 29 Sep 2020 10:04:52 GMT

The fields are getting created and I can display those fields in the table. As soon as I use one of the fields "host-fqdn, hostname, severity, id, cvss_base_score, description or plugin_name", the fields "cvss_base_score, description, exploit_available, exploitability_ease and family_name" wont be displayed.

Re: Using join to show fields for two sourcetypes in one table, why are some fields empty?

sundareshr — Thu, 30 Jun 2016 12:37:27 GMT

What do you get when you run this

(index=foo sourcetype=bar) or (index=nessus sourcetype="nessus:plugin") | eval eid=coalesce(id, plugin_id) | table host-fqdn, hostname, severity, eid, cvss_base_score, description, exploit_available, exploitability_ease, family_name, plugin_name, risk_factor

Re: Using join to show fields for two sourcetypes in one table, why are some fields empty?

rschoensee — Thu, 30 Jun 2016 13:50:16 GMT

To better understand this particular Question it is important that it reflects on the intended usage of TA-Nessus.

The Sourcetype nessus:plugin is primarily used to create and maintain several lookup-files with included saved searches. Those searches are not scheduled by default and can be run manually or scheduled as needed.

All requested fields are automatically enriched via lookups to nessus:scan events, if the lookup-files are created or maintained.

So my answer to this question is to run and/or schedule the included saved searches of TA-Nessus and all needed fields will be accessible for nessus:scan events without further Searches using Join.