Report Writer Question

Ok I'm having a problem generating a report that will concatenate the Trapvarbinds.OIDValue

I've written the following custom report, but I'm having a problem getting it to work using the Report Writer. The result is schewed because for every TrapIndex it is duplicating the message. How can I correct this problem? (I.E - if I have 5 entries for each OIDVALUE it is duplicating the concatenated entry 5 times in the final report row.)Is there a better way to write this report?

SET NOCOUNT ON
Declare @OIDValue varchar(8000)
Select DISTINCT @OIDValue = ISNULL(@OIDVALUE + ' ','') + OIDVALUE from trapvarbinds,traps where trapvarbinds.trapid = traps.trapid

SELECT TOP 25
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
(
( DateTime BETWEEN 39923.4166666667 AND 39924.4583333333 )
)
AND
(
(Traps.Tag = 'Alert')
)
AND
(
(Traps.TrapID=TrapVarbinds.TrapID)
)

ORDER BY DateTime DESC, Message DESC

Find more posts tagged with

concatenate

report_writer

Accepted answers

All comments

cybersapp

I'm just checking to see if anybody had a chance to review my question. Thanks for your help in advance.

lchance

I've been tinkering with this, and I might add with NO success yet, but I'd also like to see it work. It sounds like an interesting report.

Can you perhaps take an example of some VarBinds and write it out into this thread so we can see what you expect this to look like as final output from Report Writer?

In my own tables I'd really be surprised in a case of Trap Indexes such as 0 through 22 with a potential of 1000 char each...don't know if that can be presented other than in rows.

Maybe it will work or else I have the wrong impression of what you want presented in a report. That's why I think seeing an example of your final report might help. Good luck.

cybersapp

I just got it to work (kinda). The problem is it doesn't like Select DISTINCT and TOP and the same time. I replaced Select TOP with SELECT DISTINCT and it works. However, I only want to see 25 records. So, if you can help me with that that'll be tremendous.

SET NOCOUNT ON
Declare @OIDValue varchar(8000)
Select DISTINCT @OIDValue = ISNULL(@OIDValue + ' ','') + OIDValue from trapvarbinds,traps where trapvarbinds.trapid = traps.trapid

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
(
( DateTime BETWEEN 39923.4166666667 AND 39924.4583333333 )
)
AND
(
(Traps.Tag = 'Alert')
)
AND
(
(Traps.TrapID=TrapVarbinds.TrapID)
)
ORDER BY DateTime DESC, Message DESC

cybersapp

Example of final report. I need to now change the date to reflect last 24hrs and to only present 25 enteries.

Date Time	Hostname	Message	SNMP_Tag	Trap_ID
23-Apr-09 10:59 AM	LP-Primary	<164>Apr 08 2009 09:22:48: %ASA-4-733100: [ Scanning] drop rate-1 exceeded. Current burst rate is 10 per second, max configured rate is 10; Current average rate is 55 per second, max configured rate is 5; Cumulative total count is 33388	Alert	626296
23-Apr-09 10:44 AM	LP-Primary	<164>Apr 08 2009 09:22:48: %ASA-4-733100: [ Scanning] drop rate-1 exceeded. Current burst rate is 10 per second, max configured rate is 10; Current average rate is 55 per second, max configured rate is 5; Cumulative total count is 33388	Alert	626291
23-Apr-09 10:44 AM	LP-Primary	<164>Apr 08 2009 09:22:48: %ASA-4-733100: [ Scanning] drop rate-1 exceeded. Current burst rate is 10 per second, max configured rate is 10; Current average rate is 55 per second, max configured rate is 5; Cumulative total count is 33388	Alert	626290
23-Apr-09 10:29 AM	LP-Backup	<164>Apr 08 2009 09:22:48: %ASA-4-733100: [ Scanning] drop rate-1 exceeded. Current burst rate is 10 per second, max configured rate is 10; Current average rate is 55 per second, max configured rate is 5; Cumulative total count is 33388	Alert	626289
23-Apr-09 10:29 AM	LP-Backup	<164>Apr 08 2009 09:22:48: %ASA-4-733100: [ Scanning] drop rate-1 exceeded. Current burst rate is 10 per second, max configured rate is 10; Current average rate is 55 per second, max configured rate is 5; Cumulative total count is 33388	Alert	626288

cybersapp

ok I added the set rowcount = 25 variable and it fixed the return 25 rows problem. Now can somebody explain or help me with the date for the past 24 hours?

I will post the final report once I'm done. Hopefully somebody will find use in it.

I'm so happy to be back in the swing of things people!

I tutored Steele Smith at my last job - he's an expert with Solarwinds. I noticed he posted some of my previous diagrams on here a few years ago. Makes me proud!