4 Replies Latest reply on Jan 10, 2018 10:37 AM by yaquaholic

    Build a report using sql

    trimulya

      I'm new to running sql queries and I need to run a report that i believe it only can be done by sql query. this report will be use as View / dashboard.

      Can someone help me build a query that will return the following:

       

      - Node IP Address

      - Node Name

      - Node Group

      - Node Status (down only)

      - Time Node Down

       

      I really appreciate any help you can provide.

       

      thank you

        • Re: Build a report using sql
          yaquaholic

          You need to look at the Nodes (view) firstly, here you will find the device IP addresses, names, statuses and any custom properties (Groups).

          You then need to find the active alerts and match them up, I found that the AlertMessages from the AlertStatusView worked quite nicely (you may need to ensure that the text matches your AlertMessage text correctly).

           

          SQL

           

          SELECT n.IP_Address, n.Caption,

                 CASE n.Status

                   WHEN 1 Then 'Up'

                   WHEN 2 THEN 'Down'

                   WHEN 9 THEN 'Unmanaged'

                   WHEN 11 THEN 'External'

             END AS Status,

             a.TriggerTimeStamp AS Since

          FROM Nodes n

          INNER JOIN AlertStatusView a

                         ON n.NodeID = a.ActiveObject

          WHERE n.Status = 2

                AND a.AlertMessage LIKE 'The Device % is down%'

           

          Output:

          IP_Address     Caption           Status    Since

          x.y.z.101           ABC01XYZ01  Down      2017-12-11 11:00:32.733

           

          You might want to add a few other items in there, such as URL links (for hyperlinks in the report), or status or vendor icons.

          Have a good look at the Nodes table, it uses NodeID for cross linking tables and is usually the best place to start when exploring the database.

           

          This might not work straight away for you, but hopefully it will give you a head start, and let you work it out for yourself.

           

          Regards,

          Rich

            • Re: Build a report using sql
              yaquaholic

              Without out knowing your custom properties, I can only guess at what your Groups are...

                • Re: Build a report using sql
                  trimulya

                  yes, we have custom properties such as Region A, Region B to Region I

                   

                  anyway, what is the different of Nodes.Caption and n.Caption?

                   

                  is  a.TriggerTimeStamp is a table name in Solarwinds database?

                  because i'm not sure where i can find this entry.

                   

                  thank you

                    • Re: Build a report using sql
                      yaquaholic

                      If you are new to SQL,  start by having a look over sites like - W3schools SQL Tutorial  and have a good Google, there are plenty of good resources out there and by picking up the basics, you will get a much better understanding of how the Orion DB works for doing so. 

                       

                      Add to that that the native API language of Orion is SolarWinds Query Language (SWQL), which has very SQL like syntax.

                      Use SolarWinds Query Language (SWQL - SWIS) - SolarWinds Worldwide, LLC. Help and Support

                       

                      If you use the Database Manager (part of the Orion install), you can easily connect to your database.

                      Use Database Manager to view the SolarWinds database - SolarWinds Worldwide, LLC. Help and Support

                      Have a look at the Nodes table, for each of the (node) Custom Properties you will find a corresponding column name. Simply add these to your SQL script. You could also use MS SQL Studio (usually installed with your SQL database) or many other 3rd party tools.

                       

                      To explain the SQL a bit more for you:

                       

                      SELECT n.IP_Address, n.Caption,                                       --Get the Nodes name and IP address,

                             CASE n.Status                                                               --CASE translates (in this example) numbers to text

                               WHEN 1 Then 'Up'                                                       --

                               WHEN 2 THEN 'Down'                                                 --

                               WHEN 9 THEN 'Unmanaged'                                      --

                               WHEN 11 THEN 'External'                                          --

                         END AS Status,                                                                 --END the CASE statement

                         a.TriggerTimeStamp AS Since                                          --with the Active Alert timestamp - when the device went down

                      FROM Nodes n                                                                    --Using Nodes view fisrt

                      INNER JOIN AlertStatusView a                                           --Then join the AlertStatusView table

                                     ON n.NodeID = a.ActiveObject                             --matching Nodes.NodeID to AlertStatusView.ActiveObject (which is also the NodeID)

                      WHERE n.Status = 2                                                           --WHERE Node Status = Down

                            AND a.AlertMessage LIKE 'The Device % is down%'   --AND the alert message is something like "Device Down"

                                                                                                                   --% is an SQL wildcard character

                       

                       

                      The n.Caption Vs Nodes.Caption is just an abbreviation, defined when you call the tables in the SQL.

                                     "From Nodes n"

                      Where 'n' is the abbreviation for 'Nodes', so n.Caption and Nodes.Caption are the same, just saves some typing.

                       

                      The timestamp is prefixed with an 'a', so again this is an abbreviation.

                      If you look, at the SQL script, you will see that I have joined a table:

                      INNER JOIN AlertStatusView a

                                     ON n.NodeID = a.ActiveObject

                      This matches the entries, by using the NodeID, which happens to correspond with the AlertStatusView.ActiveObject, between the two tables.

                      So when I select nodes which are n.Status = 2 (aka down), it retrieves the nodes name, IP, status, it also ties in the active alert for the node down

                      Links to help: SQL Joins

                       

                      I hope it helps!