• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 414
  • Last Modified:

Sql Query

I'm having a problem getting this query to work correctly.  What I really want to do is to be able to concat the OIDValue colum where Traps.TrapID = TrapVarbinds.TrapID  Is there a better way to write this query?  What I'm currently getting is a time from creation entry returned for my OIDValue AS Message column

Any help would greatly be appreciated.
SET NOCOUNT ON
Declare @OIDValue varchar(8000)
Select DISTINCT @OIDValue = ISNULL(@OIDValue + ' ','') + OIDValue from trapvarbinds,traps where trapvarbinds.trapid = traps.trapid 
 
set ROWCOUNT 25
SELECT DISTINCT 
Traps.DateTime AS Date_Time,
Traps.Hostname AS HostName,
@OIDValue AS Message,
Traps.Tag AS SNMP_Tag,
Traps.TrapID AS Trap_ID
 
FROM 
Traps, Trapvarbinds  
WHERE 
 
(
  (Traps.Tag = 'Alert') 
)
AND
(
   (Traps.TrapID=TrapVarbinds.TrapID)
)
ORDER BY DateTime DESC, Message DESC

Open in new window

0
cybersapp
Asked:
cybersapp
  • 3
  • 3
1 Solution
 
reb73Commented:
I suppose you could create a scalar UDF (User defined function) and invoke it in the SELECT list as indicated below -
/*
CREATE FUNCTION [dbo].[fn_GetMessageByTrapId] (@TrapID int, @Tag varchar(100))
RETURNS varchar(8000)
AS
BEGIN
	DECLARE @OIDValue varchar(8000)
	SELECT DISTINCT @OIDValue = ISNULL(@OIDValue + ' ','') + OIDValue
	FROM Traps T
	INNER JOIN Trapvarbinds TV ON TV.TrapID = T.TrapID
	WHERE	T.TrapID= @TrapId
	AND	T.Tag	= @Tag
	
	RETURN @OIDValue
END
*/
 
DECLARE @Tag varchar(100)
SET     @Tag = 'Alert'
 
SELECT DISTINCT TOP 25 
	Traps.DateTime AS Date_Time,
	Traps.Hostname AS HostName,
	[dbo].[fn_GetMessageByTrapId] (Traps.TrapID, @Tag) AS Message,
	Traps.Tag AS SNMP_Tag,
	Traps.TrapID AS Trap_ID
FROM 
	Traps
INNER JOIN
	Trapvarbinds ON TrapVarbinds.TrapID = Traps.TrapID
WHERE 
	Traps.Tag = @Tag
ORDER BY
	DateTime DESC, Message DESC

Open in new window

0
 
cybersappAuthor Commented:
My original problem - explained
The problem with this query is that it returns multiple rows for each row it concatenates.  Example (there are up to 6 entries in the TrapVarbinds table for each alerts.  I want to concatenate the OIDValue into one row then present that information as one row as opposed to 6 rows of the same data matched on the TrapID.).  How can I get the query to only return one row for each trapID and concatenated OIDValue?

-- problem with current query -
The results I am getting, runs correctly once and then returns just the system uptime not sure why the message (OIDVALUE) is not returning the concatenated rows.

-- reb73 suggestion
The function works but it is also returning the same results as my query where it works once but doesn't work on sequential executions
0
 
reb73Commented:
Can you provide sample output from Traps and Trapvarbinds tables for a particular tag and trapid?

I've changed the function and the query slightly below which may work, but looking at sample data may help clarify your requirements better..
/*
CREATE FUNCTION [dbo].[fn_GetMessageByTrapId] (@TrapID int, @Tag varchar(100))
RETURNS varchar(8000)
AS
BEGIN
        DECLARE @OIDValue varchar(8000)
        SELECT DISTINCT @OIDValue = ISNULL(@OIDValue + ' ','') + OIDValue
        FROM Trapvarbinds TV 
        WHERE   TV.TrapID = @TrapId
        AND EXISTS (SELECT NULL FROM Traps T WHERE T.Tag = @Tag AND T.TrapID = TV.TrapID)
 
        RETURN @OIDValue
END
*/
 
DECLARE @Tag varchar(100)
SET     @Tag = 'Alert'
 
SELECT DISTINCT TOP 25 
        Traps.DateTime AS Date_Time,
        Traps.Hostname AS HostName,
        [dbo].[fn_GetMessageByTrapId] (Traps.TrapID, @Tag) AS Message,
        Traps.Tag AS SNMP_Tag,
        Traps.TrapID AS Trap_ID
FROM 
        Traps
WHERE 
        Traps.Tag = @Tag
ORDER BY
        DateTime DESC, Message DESC

Open in new window

0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
cybersappAuthor Commented:
I've attached a sample table entry from TrapVarBinds and Traps

and I've attached sample results from your suggested solution query
TRAPVARBINDS-TABLE-FOR-ONE-ALERT.doc
0
 
reb73Commented:
Ok, just a simple change - remove the DISTINCT keyword within the function..  See complete test code used below -
/*
CREATE TABLE Traps (TrapID int not null,EngineID int not null, DateTime datetime not null,IPAddress varchar(100) null,
		     Community varchar(100) null, Tag varchar(100) null, Acknowledge int null, 
		     Hostname varchar(100) null, NodeID int null, TrapType varchar(100) null, ColorCode varchar(100) null)
CREATE TABLE Trapvarbinds (TrapID int not null,TrapIndex int not null,OID varchar(100) null,
			    OIDNAME varchar(100) null, OIDVALUE varchar(100) null, RAWVALUE varchar(100) null)
 
insert Traps select 659297,2,'4/29/2009 9:08:07 AM','10.100.41.2    ','public','Alert',0,'SAV-ALB-R',257,'DATASMART-MIB:datasmart.0.5002','33023'
 
insert Trapvarbinds select 659297,8,'1.3.6.1.6.3.1.1.4.3.0','snmpTrapEnterprise','DATASMART-MIB:datasmart','1.3.6.1.4.1.181.2.2'
insert Trapvarbinds select 659297,7,'1.3.6.1.3.1057.1','experimental.1057.1','10.100.41.2','10.100.41.2'
insert Trapvarbinds select 659297,6,'1.3.6.1.2.1.10.18.7.1.5.1','dsx1CurrentUASs.1','0','0'
insert Trapvarbinds select 659297,5,'1.3.6.1.2.1.10.18.7.1.2.1','dsx1CurrentESs.1','0','0'
insert Trapvarbinds select 659297,4,'1.3.6.1.2.1.10.18.6.1.10.1','dsx1LineStatus.1','1','1'
insert Trapvarbinds select 659297,3,'1.3.6.1.2.1.2.2.1.2.1','ifDescr.1','T1 Network Interface','T1 Network Interface'
insert Trapvarbinds select 659297,2,'1.3.6.1.2.1.2.2.1.1.1','ifIndex.1','1','1'
insert Trapvarbinds select 659297,1,'1.3.6.1.6.3.1.1.4.1.0','snmpTrapOID','DATASMART-MIB:datasmart.0.5002','1.3.6.1.4.1.181.2.2.0.5002'
insert Trapvarbinds select 659297,0,'1.3.6.1.2.1.1.3.0','sysUpTime','27 days 11 hours 22 minutes 46.08 seconds','237376608'
*/
 
/*
CREATE FUNCTION [dbo].[fn_GetMessageByTrapId] (@TrapID int, @Tag varchar(100))
RETURNS varchar(8000)
AS
BEGIN
        DECLARE @OIDValue varchar(8000)
        SELECT @OIDValue = ISNULL(@OIDValue + ' ','') + OIDValue
        FROM Trapvarbinds TV 
        WHERE   TV.TrapID = @TrapId
 
        RETURN @OIDValue
END
*/
 
DECLARE @Tag varchar(100)
SET     @Tag = 'Alert'
 
SELECT DISTINCT TOP 25 
        Traps.DateTime AS Date_Time,
        Traps.Hostname AS HostName,
        [dbo].[fn_GetMessageByTrapId] (Traps.TrapID, @Tag) AS Message,
        Traps.Tag AS SNMP_Tag,
        Traps.TrapID AS Trap_ID
FROM 
        Traps
WHERE 
        Traps.Tag = @Tag
ORDER BY
        DateTime DESC, Message DESC
 
/* Results
Date_Time                                              HostName                                                                                             Message                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          SNMP_Tag                                                                                             Trap_ID     
------------------------------------------------------ ---------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ----------- 
2009-04-29 09:08:07.000                                SAV-ALB-R                                                                                            DATASMART-MIB:datasmart 10.100.41.2 0 0 1 T1 Network Interface 1 DATASMART-MIB:datasmart.0.5002 27 days 11 hours 22 minutes 46.08 seconds                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        Alert                                                                                                     659297 
*/
/*
  DROP TABLE Traps, Trapvarbinds
  DROP FUNCTION dbo.fn_GetMessageByTrapId
*/

Open in new window

0
 
cybersappAuthor Commented:
The function approach really solved my problem.  I didn't quite understand how the function worked in ms sql, but after this answer, I can understand the syntax.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now