topic How can I join 2 tables to create a new table with the given conditions? in Splunk Search

How can I join 2 tables to create a new table with the given conditions?

gts_ame_tfo_cty — Thu, 01 Feb 2018 15:18:48 GMT

So this is what I want to do, and I don't know if Splunk can do this.

This is the result for Table A

Table A
hostA         brand
aaa          ios
bbb         nxos
ddd         metamako 
ccc          arista

This the result for table B

Table B
hostB            user
aaa-l1          user1
bbb-l1         user2
ddd-l1         user3 
ccc-l1           user4
kkk-l1          user5
klo-l1           user6

I want the join match TableA(hostA) with TableB(hostB) where like(hostB,"%"+hostA+"%")
and the result will be

 TableC
hostA            brand                    hostB            user
aaa                ios                          aaa-l1          user1
bbb               nxos                       bbb-l1         user2
ddd               metamako             ddd-l1         user3  
ccc                 arista                      ccc-l1           user4

Re: How can I join 2 tables to create a new table with the given conditions?

FrankVl — Thu, 01 Feb 2018 15:58:52 GMT

If the actual data is as straight forward as your example, then you can just add a calculated field to table B with the truncated hostname and use that to join?

But given that you are asking for a like(hostB,"%"+hostA+"%") that seems to imply the actual case is more complex than your example?

Re: How can I join 2 tables to create a new table with the given conditions?

gts_ame_tfo_cty — Thu, 01 Feb 2018 16:12:01 GMT

let me show you a real example

table A
host
tor-rt-dist02-03
tor-rt-dist01
tor-rt-wan01
tor-rt-wan02
tor-rt-ext01

tor-rt-ext02

TableB
host
tor-rt-dist02-03-l1
tor-rt-dist01-l1-3
tor-rt-wan01-l2-1
tor-rt-wan02-eth1-3

so as you can see with this example i cannot add truncated hostname and use that to join because sometime "tor-rt-wan01-l2-1" and sometime the hostname "tor-rt-wan02-eth1-3" so i cannot add a regex to extart and join.

is there any way to make it work ?

Re: How can I join 2 tables to create a new table with the given conditions?

493669 — Thu, 01 Feb 2018 16:14:03 GMT

If you could share your exact host value so that accordingly field can be build which will use for join

Re: How can I join 2 tables to create a new table with the given conditions?

gts_ame_tfo_cty — Thu, 01 Feb 2018 16:27:36 GMT

thanks you for the answer,

let me give you a real example

TableA
host
tor-rt-dist02-3
tor-rt-dist01
tor-rt-wan01-6
tor-rt-wan02
tor-rt-ext01
tor-rt-ext02

TableB
host
tor-rt-dist02-3-l0-6
tor-rt-dist01-l1-26
tor-rt-wan01-6-eth0
tor-rt-wan02-mgmt0

here is some of the hosts

Re: How can I join 2 tables to create a new table with the given conditions?

493669 — Tue, 29 Sep 2020 17:53:19 GMT

Hi @gts_ame_tfo_cty,
Try this:

<query> |  table hostA brand | rename hostA as host | join type=inner host [search <query>| rex field=hostB "(?<host>^[^\d]+\d+(\-\d+)*)" | table host user]

Let me know if it works!

Re: How can I join 2 tables to create a new table with the given conditions?

niketn — Tue, 29 Sep 2020 17:56:33 GMT

@gts_ame_tfo_cty please share your query for Table A and Table B. Also make sure you use the Code Button 101010 on Splunk Answers while posting your queries so that special characters in them do not escape.

PS: Adding four spaces before each line of code will also format the entire content as code.