5 Replies Latest reply on Jul 6, 2018 5:29 AM by yaquaholic

    Control M Jobs failed Dashboard or count status?

    er.vansh17091

      Hello Team,

       

      I just wondering for Control M jobs dashboard in Solarwinds. Is that possible like can we see the job details or JOB failed to count in solarwinds?

      We are monitoring control m jobs via trap messages and converted critical jobs via Servicenow alert. or is there any possibilities through trap messages to get the Controm M job failed count in solarwinds?

        • Re: Control M Jobs failed Dashboard or count status?
          yaquaholic

          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]

                ,[IPAddress]

                ,[Community]

                ,[Hostname]

                ,[NodeID]

                ,[TrapType]

            FROM [SolarWinds].[dbo].[Traps]

          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

            FROM [SolarWinds].[dbo].[Traps]

          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

          1 of 1 people found this helpful
            • Re: Control M Jobs failed Dashboard or count status?
              er.vansh17091

              Hi yaquaholic,

               

              I appreciate your response thanks a lot.

               

              Looks like I am not getting the output.

               

               

               

               

                • Re: Control M Jobs failed Dashboard or count status?
                  yaquaholic

                  I have uploaded a report for you, with some notes for you and I have placed some comments in the SQL query.

                   

                  SNMP_traps_from_SQL_query_example.xml

                   

                  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.

                      • Re: Control M Jobs failed Dashboard or count status?
                        yaquaholic

                        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