Trying do not use a join

[pierre_weg]

Hi guys!

I have a static snapshot lookup that stores a lot of information about vulnerabilities actives on my hosts in mar/01.

This SPL shows me a full list of the unique identifiers.

| inputlookup gvul:collectMar.csv | table UniqID

This SPL shows me a list of the unique identifiers actives today

earlyest=-1d index=myindex sourcetype=mysourcetype | table UniqID 

My team works to fix this vulnerabilities, so I want a timechart to show the work progress, based on the snapshot lookup. I don care about new vulnerabilities since the snapshot.

This is the SPL that I'm using to do this.

earliest=1677719215 index=myindex sourcetype=mysourcetype | join type=inner UniqID [ | inputlookup gvul:collectMar.csv | table UniqID] | timechart span=1d count(UniqID)

So, is there a way to do this but not using a join statement?

[PickleRick]

If you want to limit your search to just the values included in the lookup, there are (at least) two different techniques that can be used.

1) As @gcusello showed - you can use a subsearch to generate a set of conditions which will be passed to the outer search limiting the result set.

2) Use lookup directly to filter the results from the main search

<your_search>
| lookup gvul:collectMar.csv UniqID OUTPUT UniqID
| search UniqID=*

The trick here is that if there is no matching UniqID in the lookup file the lookup command will set this value to null so you can filter out the events which do not contain this field.

[gcusello]

Hi @pierre_weg ,

in general you can use the lookup command (https://docs.splunk.com/Documentation/Splunk/9.1.0/SearchReference/Lookup)  that's like a left join.

But let me understand: you need to filter results using the lookup to have the distribution of events with the UniqID from the table, is it correct?

if this is your requirement, you could try something like this:

earliest=1677719215 index=myindex sourcetype=mysourcetype [ | inputlookup gvul:collectMar.csv | table UniqID] 
| timechart span=1d count(UniqID)

 Ciao.

Giuseppe