Is this possible - SQL JOIN ?

Question

Hi,

I have 2 tables in a SQL DB:

Table 1 called 'dbp.FAIL' contains a column called 'MsgText' with the following data :

17294: 029514: Dec 11 10:06:54: %DOT1X-5-FAIL: Authentication failed for client (d4be.d96b.c87a) on Interface Fa0/6 AuditSessionID AC1E010C00000419AD57A754.

Table 2 called 'Lookup' contains 2 columns called 'MAC' and 'Hostname' :

MAC                             HOSTNAME

d4be.d96b.c87a           PC01

What I want to do is lookup the value (d4be.d96b.c87a) in Table 1 and replace it from the corresponding value from Table 2, I.e the MsgText column would then be :

17294: 029514: Dec 11 10:06:54: %DOT1X-5-FAIL: Authentication failed for client (PC01) on Interface Fa0/6 AuditSessionID AC1E010C00000419AD57A754.

Is this possible ?

Thanks

Bill

 

Answer 1

You can use REPLACE function to start. A simple sample:

Create table dbo.FAIL ( id int, MsgText varchar(4000))
Create table Lookup (Mac varchar(50), HOSTNAME varchar(50))

insert into Lookup values ('d4be.d96b.c87a','PC01')

insert into FAIL values (1,'17294: 029514: Dec 11 10:06:54: %DOT1X-5-FAIL: Authentication failed for client (d4be.d96b.c87a) on Interface Fa0/6 AuditSessionID AC1E010C00000419AD57A754.')
 
select replace(MsgText,a.Mac, a.HOSTNAME) as MsgText from Lookup a, dbo.FAIL b

drop table lookup
drop table dbo.FAIL

Answer 2

Use the Replace function, for example,

Declare @Fail Table(MsgText varchar(200));
Insert @Fail(MsgText)
Select '17294: 029514: Dec 11 10:06:54: %DOT1X-5-FAIL: Authentication failed for client (d4be.d96b.c87a) on Interface Fa0/6 AuditSessionID AC1E010C00000419AD57A754.';

Declare @Table2 Table(MAC varchar(16), HostName varchar(50));
Insert @Table2(MAC, HostName)
Select 'd4be.d96b.c87a', 'PC01';

Select Replace(f.MsgText, t.MAC, t.HostName) As MsgText
From @Fail f
Inner Join @Table2 t On f.MsgText Like '%' + t.MAC + '%';

Tom

Answer 3

Something like this (untested)

UPDATE f
SET MsgText = REPLACE(MsgText,l.MAC,l.HOSTNAME)
FROM dbo.FAIL f
JOIN dbo.Lookup l
	ON f.MsgText like '%' + l.MAC + '%'

---

Chuck Pedretti | Magenic – North Region | magenic.com

Answer 4

Thanks,

I forgot to say that there will be lots of different values in the source table / column, i.e. not just (d4be.d96b.c87a).

I guess it would need something extra to get the value between the ( ) and then look this up in the 'lookup table' ?

Bill

Answer 5

Then create and use a function

CREATE FUNCTION fn_ReplaceHostLookup
(
	@Text NVARCHAR(500)
)
RETURNS NVARCHAR(500)
AS
BEGIN
	DECLARE @Tab TABLE
	(
		MAC NVARCHAR(50),
		HostName NVARCHAR(50)
	)
	
	INSERT INTO @Tab SELECT Mac, HostName FROM [Lookup]
	
	DECLARE @Out NVARCHAR(500)
	DECLARE @TempMac NVARCHAR(50)
	DECLARE @TempHostName NVARCHAR(50)
	SET @Out = @Text
	
	WHILE EXISTS (SELECT * FROM @Tab)
	BEGIN
		SELECT TOP 1 @TempMac = MAC, @TempHostName = HostName FROM @Tab
		SET @Out = REPLACE(@Out, @TempMac, @TempHostName)
		DELETE FROM @Tab WHERE Mac = @TempMac
	END
	
	RETURN @Out
END
GO

select msgtext, dbo.fn_ReplaceHostLookup(msgtext) from fail

Answer 6

And the above techniques will work with multiple values, so, for example

Declare @Fail Table(MsgText varchar(200));
Insert @Fail(MsgText)
Select '17294: 029514: Dec 11 10:06:54: %DOT1X-5-FAIL: Authentication failed for client (d4be.d96b.c87a) on Interface Fa0/6 AuditSessionID AC1E010C00000419AD57A754.'
Union All Select 'blah blah blah (abcd.1234.fedc) blah blah)'
Union All Select 'yada yada (1234.5678.90ab) yada yada yada)';

Declare @Table2 Table(MAC varchar(16), HostName varchar(50));
Insert @Table2(MAC, HostName)
Select 'd4be.d96b.c87a', 'PC01'
Union All Select 'abcd.1234.fedc', 'PC02'
Union All Select '9876.5432.1010', 'PC03'
Union All Select '1234.5678.90ab', 'PC99';

Select Replace(f.MsgText, t.MAC, t.HostName) As MsgText
From @Fail f
Inner Join @Table2 t On f.MsgText Like '%(' + t.MAC + ')%';

Tom

Answer 7

Hello Bill,

Try this code below,

Declare @Fail Table(MsgText varchar(200));
Insert @Fail(MsgText)
Select '17294: 029514: Dec 11 10:06:54: %DOT1X-5-FAIL: Authentication failed for client (d4be.d96b.c87a) on Interface Fa0/6 AuditSessionID AC1E010C00000419AD57A754.';

Declare @Table2 Table(MAC varchar(16), HostName varchar(50));
Insert @Table2(MAC, HostName)
Select 'd4be.d96b.c87a', 'PC01';

select 
REPLACE(MsgText,MAC,HOSTNAME)
from @Fail a
join @Table2 b on 
SUBSTRING(msgtext,charindex('(',msgtext)+1,datalength(msgtext)-charindex('(',msgtext)-CHARINDEX(')',reverse(msgtext)))
= b.MAC

---

Dineshkumar Please "Mark as Answer" if my post answers your question or "Vote as Helpful" if it helps you