6 Replies Latest reply on Apr 26, 2011 8:46 AM by robertlj

    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

        • Re: Report Writer Question

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

            • Re: Report Writer Question
              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.

                • Re: Report Writer Question

                  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

                  • Re: Report Writer Question

                    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 
                     

                      • Re: Report Writer Question

                        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!