Splunk Search

Lookup match

robettinger
Explorer

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!

Tags (2)
0 Karma
1 Solution

493669
Super Champion

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]

View solution in original post

0 Karma

mayurr98
SplunkTrust
SplunkTrust

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!

0 Karma

robettinger
Explorer

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

0 Karma

mayurr98
SplunkTrust
SplunkTrust

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

0 Karma

robettinger
Explorer

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

0 Karma

bojanisch
Explorer

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.

0 Karma

493669
Super Champion

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]

View solution in original post

0 Karma

robettinger
Explorer

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.

0 Karma

493669
Super Champion

try like this:

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

robettinger
Explorer

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

0 Karma

493669
Super Champion

try left join :

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

robettinger
Explorer

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 🙂

0 Karma

493669
Super Champion

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 ...
0 Karma

robettinger
Explorer

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
0 Karma

robettinger
Explorer

Got it .. I used makemv instead ...

Thank you!

0 Karma