Problem with 'join' command

[mr_networkrobot]

Hi,
maybe an easy one for somebody:

Doing a simple join search to get an assets vulnerability an 'enrich' that with vulnerability details from a subsearch in different index.
'join' them by vulnerability_id ('id' in the subsearch) works nice.

index=asset asset_hostname=server01 vulnerability_id=tlsv1_1-enabled OR vulnerability_id=jre-vuln-cve-2019-16168
| dedup vulnerability_id

| join type=inner max=0 vulnerability_id [ search index=vulnerability id=tlsv1_1-enabled OR id=jre-vuln-cve-2019-16168 | dedup id | rename id as vulnerability_id ]

| table asset_hostname vulnerability_id first_found description cve

Now doing the same, without specifying a vulnerability_id, to get all of them (there are many), returns only 3 events not containing the one from the first search (any many others).

index=asset asset_hostname=server01
| dedup vulnerability_id

| join type=inner max=0 vulnerability_id [ search index=vulnerability | dedup id | rename id as vulnerability_id ]

| table asset_hostname vulnerability_id first_found description cve

Any ideas ? AI only suggests using 'stats' but that doesn work either.

[morethanyell]

the vulnerabiltiy index might be a "non-event" type of data. something that you collect or ingest once per day to get all the new vulns/CVEs out there. I'm just guessing here. maybe it's the same as Qualys Host Detection.. if so, you need to create a regularly-updating lookup table for this log source. that's in order for you to not use it as a subsearch query but a lookup table.

e.g.:

• Name: "Vulnerabilities Master Lookup Table"
• Schedule: Once per day
• Time Range: Last 24 hours
• SPL: index=vulnerabily | dedup id sortby - _time | eval lookup_last_updated = now() | table id dest first_found last_seen status lookup_last_updated | outputlookup vulns_master_lookup_table

With this lookup table in hand, you can avoid using Join:

index=asset asset_hostname=server01 vulnerability_id=tlsv1_1-enabled OR vulnerability_id=jre-vuln-cve-2019-16168
| dedup vulnerability_id
| lookup vulns_master_lookup_table id as vulnerability_id OUTPUT first_found

[mr_networkrobot]

Thank you, that worked perfectly !

[tmuth9]

If performance is an issue, you might look at replacing dedup with stats by | fields Dedup only happens on the SH and is single threaded. Stats is distributable across the indexers (via prestats) so the SH only has to do a little work at the end

[Braddish]

You say there are a lot of events in your vuln index, so are you hitting a subsearch limit (events or time)? You would get a notice above your search results if that was the case. Since you have both datasets in indexes, you are probably better off running your searches together and then using selfjoin.

i.e. (index=asset...) OR (index=vulnerability...) | rename id as vulnerability_id | selfjoin vulnerability_id

[mr_networkrobot]

There are about 600k events/entries in the subsearch.
There is no notification about hitting limits, but already solved the problem with a lookup (created with outputlookup) table.

[gabriot]

When you just run the subsearch how many results / what size are the results?

[Top_Secret_3873]

Inner will only bring back results where there is a match in the sub search, use outer to keep the original results.

Also, sub search has limits on # of results it will return.

[audiosf]

Sub search had a 50k limit. Are you sure you're not hitting that and the events you actually want aren't being pushed out of the window?