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!
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]
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!
Unfortunately the lookup doesn't have a Period field, I have to calculate it AFTER the lookup 😞
i have changed my query please check and let me know.
I am afraid not. I think the solution is probably achieved using a join instead of a lookup... I may be wrong, though.
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.
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]
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.
try like this:
[base search] | eval Period=day + ' - ' + hour|join User[|inputlookup <lookupname>|fillnull|stats values(Hits) by User]|table ...
nope ... it is still discarding the rows which are NOT in the lookup table ... 😞
try left join :
[base search] | eval Period=day + ' - ' + hour|join type=left User[|inputlookup <lookupname>|fillnull|stats values(Hits) by User]|table ...
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 🙂
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 ...
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
Got it .. I used makemv instead ...
Thank you!