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
@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.
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!
If you could share your exact host value so that accordingly field can be build which will use for join
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
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?
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 ?