topic Re: Lookup match in Splunk Search

Lookup match

robettinger — Tue, 06 Feb 2018 09:12:52 GMT

Hi, I am trying to do the following:

1 - Search an index;
2 - For each result, search for matches in lookup table 1, based on the timestamp, in 1 hour bins.

For example:

Step 1 - [base search] | eval Period=day + ' - ' + hour

Result:

 User      Period
User1     Monday - 11
User2     Monday - 12
User3     Monday - 09

Step 2 - Match each user and period against a lookup and populate a list (can be multi-value) with hits

User       Period             Hits
User1     Monday - 11     No hits
User2     Monday - 12     05/02/18 12:02:45
                          05/02/18 12:44:13
User3     Monday - 09     05/02/18 09:28:15

How can I achieve this? The lookup table has only User and a Timestamp, the 'Period' field is calculated. How can I do the "...For each result on Step1, search the lookup table, and if hits found, list it alongside the row ...

Not sure if this is clear enough ...

Thank you!

Re: Lookup match

493669 — Tue, 06 Feb 2018 09:23:41 GMT

Try this:

 [base search] | eval Period=day + ' - ' + hour|lookup <lookupname> User OUTPUT Hits

Here lookup command will join using User as primary field and gives output as Hits..
If this doesnt work then try this:

[base search] | eval Period=day + ' - ' + hour|join User[|inputlookup <lookupname>|stats values(Hits) by User]

Re: Lookup match

bojanisch — Tue, 06 Feb 2018 09:26:10 GMT

You can use a time-based kvstore lookup. This will compare the event time with the defined time field in the kvstore. A useful primer would be the previously questions answers here on this forum: https://answers.splunk.com/answers/209693/time-based-lookups-and-kvstore.html for example.

However if your event time differs from the hourly digit in your table, you should try to modify your _time value before doing the lookup.

Re: Lookup match

mayurr98 — Tue, 06 Feb 2018 10:03:46 GMT

hey you can try something like this

[ base search]
| stats count by User 
| table User
| lookup <mylookup.csv> User OUTPUT hits 
| eval Period=strftime(strptime(hits,"%d/%m/%y %H:%M:%S"),"%A - %H")
| fillnull Period value="No hits"

let me know if this helps!

Re: Lookup match

robettinger — Tue, 06 Feb 2018 10:09:57 GMT

Unfortunately the lookup doesn't have a Period field, I have to calculate it AFTER the lookup 😞

Re: Lookup match

robettinger — Tue, 06 Feb 2018 10:17:58 GMT

This works partially ... It only populates a table if there are hits in the lookup table. If there arent, the events are show in the "events" tabs, but no table is displayed, despite the fact I used the "table" command.

Re: Lookup match

493669 — Tue, 06 Feb 2018 10:24:43 GMT

try like this:

[base search] | eval Period=day + ' - ' + hour|join User[|inputlookup <lookupname>|fillnull|stats values(Hits) by User]|table ...

Re: Lookup match

mayurr98 — Tue, 06 Feb 2018 10:40:16 GMT

i have changed my query please check and let me know.

Re: Lookup match

robettinger — Tue, 06 Feb 2018 13:02:10 GMT

nope ... it is still discarding the rows which are NOT in the lookup table ... 😞

Re: Lookup match

493669 — Tue, 06 Feb 2018 13:05:39 GMT

try left join :

[base search] | eval Period=day + ' - ' + hour|join type=left User[|inputlookup <lookupname>|fillnull|stats values(Hits) by User]|table ...

Re: Lookup match

robettinger — Wed, 07 Feb 2018 07:59:20 GMT

Ok. I solved the issue with the items not in the lookup table by changing the join type from inner to left (duh, lol), but one last question: how can I have a multivalue field when I join searches? Currently there is a 1-2-1 relationship and I would like a 1-2-many...

Thank you 🙂

Re: Lookup match

493669 — Wed, 07 Feb 2018 08:08:24 GMT

try mvexpand:

[base search] | eval Period=day + ' - ' + hour|join type=left User[|inputlookup <lookupname>|fillnull|stats values(Hits) as Hits by User|mvexpand Hits ]|table ...

Re: Lookup match

robettinger — Thu, 08 Feb 2018 08:40:39 GMT

Nope... if I put the mvexpand INSIDE the join subquery, it separates a hit per row, causing a 1-2-1 relationship, and if I put the mvexpand OUTSIDE the subquery, it shows all values in a single row, but flattened:

Inside:
User Hits
1        a
1        b
1        c

Outside:
User Hits
1     a b c

I would like:

1     a
       b
       c

Re: Lookup match

robettinger — Thu, 08 Feb 2018 08:43:44 GMT

I am afraid not. I think the solution is probably achieved using a join instead of a lookup... I may be wrong, though.

Re: Lookup match

robettinger — Thu, 08 Feb 2018 09:04:00 GMT

Got it .. I used makemv instead ...

Thank you!