Splunk Search

join two lookup tables

awedmondson
Explorer

Hi,
I have two lookup tables created by a search with outputlookup command ,as:
table_1.csv with fields _time, A,B
table_2.csv with fields _time, A,C

I can use [|inputlookup table_1 ] and call the csv file ok.

I need to somehow join the two tables to get _time, A,B,C
NOTE: the common field in A

One fly in the ointment is that field A is not exclusive, by that I mean that it appears more than once in both tables, but within 30 seconds in both tables. So the time I need it to join is within A appearing within a 30s span of each other.

I need to join to be as one event (one line on the outputed csv file)

Then output to csv for a report which is scheduled daily.
thanks

Tags (2)
0 Karma

Michael
Contributor

Easy peazy, I needed to include three tables and simply used:

| inputlookup table1.csv | inputlookup append=true table2.csv | inputlookup append=true table3.csv

jeandez
Explorer

hi ,
just to share something i was looking for.
i had two lookups A and B with one field .and i wanted to know if value in lookup A was in lookup B.
so i rename the field by the same name 'c' (in the both lookups). i applied this search and i found what i wanted : |inputlookup A |join c [inputlookup B].

0 Karma

Lowell
Super Champion

If your lookup tables have time, then it's possible that they should be indexed events and not lookup tables. In which case it would be possible to use the transaction command to issue the time restrictions you are looking for. This is still technically possible but a bit more awkward.

Try something like this:

| intputlookup table_1 | sort - _time | transaction A maxspan=30s | lookup table_2 A OUTPUT C | table _time, A, B, C

This assumes that the correct max_matches setting for the definition of table_2. However, the date effectivity of the lookup will be dependent on on proper settings for time_field,
time_format, max_offset_secs, and min_offset_secs. See the docs for transforms.conf for more details.

Another approach could be something like this:

| intputlookup table_1 | inputlookup append=t table_2 | sort - _time | transaction A maxpause=30s | table _time, A, B, C
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...