1 of 1 people found this helpful
You could set up a report to read the traps, from the Traps database table.
First use some SQL to first find the trap types you want:
SELECT TOP (250)[DateTime]
WHERE Hostname LIKE '<enter trap origin hostname/IP here>' --In my table we have a mix of IPs and hostnames, you may need to try both
Once you have identified the TrapType, which has a name format of the MIB it originated from (guessing your might look like Control-M-MIB:something-something) and the trap type from within it you can filter for just those traps and get a count.
This SQL could be used in a report to show a table of the number of traps received by each host, in the past day.
SELECT Hostname, COUNT(NodeID) AS Trap_Count
WHERE TrapType LIKE '<trap type you identified in 1st step here>'
AND DATETIME >= DATEADD(day,-1, getdate()) -- limits the result to last 1 of traps, or remove this to get all time history
GROUP BY Hostname
ORDER BY COUNT(NodeID) DESC
You could also re-add AND Hostname LIKE '<enter trap origin hostname/IP here>' to show the trap count from a single host.
Now you know the TrapType to filter upon, the trap varbinds (the info within the trap) are held in separate table (TrapVarBinds) so you'll need to join the tables together.
This query below will show this data, you will have to examine the traps RawValues column to see if the data you want is in there:
SELECT TOP 250 t.DateTime, t.IPAddress, t.Hostname, tv.RawValue, tv.OIDName, tv.OIDvalue, tv.OID, t.TrapType
FROM SolarWinds.dbo.Traps t
INNER JOIN SolarWinds.dbo.TrapVarbinds tv ON t.TrapID = tv.TrapId
WHERE t.IPAddress LIKE '<trap source IP here>'
AND t.TrapType LIKE '<trap type you identified in 1st step here>'
ORDER BY t.DateTime DESC
You can now start building the SQL query with IPaddress, TrapType and the text string you want from tv.RawValue into your report.
I hope it helps
I have uploaded a report for you, with some notes for you and I have placed some comments in the SQL query.
This runs fine on my system, I hope it does on yours (shout if your stuck), but it is only the start of what you were after.
Read my comments, from the link above, as you'll still need to find the OIDName(s) and OIDvalue(s) in which your desired data can be found, but that should get you started.
Okay, so I am guessing that your database isn't called SolarWinds.
Let's go back to the SQL first and get that working in your environment.
Easiest way to do this is log onto your Orion server and open Database Manager from your start menu, or C:\Program Files (x86)\SolarWinds\Orion\DatabaseManager.exe.
Open this and click on "Add default server", this will then connect you with your database.
Select the database instance where your SolarWinds data exists, this is your database name and this is where it is probably failing.
I am guessing that you'll need to update the SQL so that instead of it saying: [SolarWinds].[dbo].[Traps] it will need to say [<your database's name>].[dbo].[Traps].
The square brackets are required if you use keywords or special characters in the column names or identifiers. You could name a column [Table Index Column] (with spaces), but then you'd need to use brackets every time you referred to that column. Most SQL editors add them everywhere, just in case or for consistency.
To test this, as it's easier to troubleshoot from a SQL query tool, locate the Traps table, from your Database Manager and select it, then right click and select "Query table".
This will populate a script window with a select everything statement.
Delete that, and paste in your first SQL query, to identify the traps from your device.
SELECT TOP (250) [DateTime] ,[IPAddress] ,[Community] ,[Hostname] ,[NodeID] ,[TrapType]
FROM [<your database's name>].[dbo].[Traps]
WHERE [Hostname] LIKE '<enter trap origin hostname here>' --this needs the single quotes on either side, as it is a text sting
OR [IPAddress] LIKE '<enter trap origin IP Address>' --as does this
Then click the Execute Query button, at the top left of the script pane.
Did it work?
If it did, update the DB name in the report's SQL queries and it should be good to go.
If it didn't post your results.
Hope it helps