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

[mdorobek]

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.

[rschoensee · Accepted solution]

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.

[sundareshr]

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

[mdorobek]

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

[mdorobek]

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.

[sundareshr]

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

[Raghav2384]

try appendcols

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

Hope this helps,

Thanks,
Raghav

[mdorobek]

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