7 Replies Latest reply on Jul 16, 2018 12:13 PM by dclissold

    Custom SQL Query to a Non-Solarwinds DB in the UI

    jbiggley

      Has anyone built a custom SQL query that uses a piece of Solarwind node data (ie IP address) to pull back data from an external DB?  We're redesigning our node details page and I would love to be able to pull in the open incidents from our ServiceNow instance for the node in focus but I don't really have any idea where to start.

       

      Anyone?

        • Re: Custom SQL Query to a Non-Solarwinds DB in the UI
          pat_m

          This is a great idea, I am sorry I don't have a solution but I want to watch this to see if it is doable and how.

           

          -Pat

            • Re: Custom SQL Query to a Non-Solarwinds DB in the UI
              jbiggley

              One of my coworkers has a few exciting ideas that would leverage stored procedures and/or custom tables. When we finally develop a solution, we'll be sure to post it here.

                • Re: Custom SQL Query to a Non-Solarwinds DB in the UI
                  Ezeqiel

                  Hi Joshua,

                   

                  Did you ever progress this solution further from last year?

                   

                  We're looking into doing something similar but are hitting a few (major) hurdles.

                    • Re: Custom SQL Query to a Non-Solarwinds DB in the UI
                      jbiggley

                      No, unfortunately not.  The widgets for Orion naturally connect only to the Orion DB.  The inability to execute a SQL query against a non-Orion DB was the first problem, but being able to pass a common identifier was the second, and perhaps more challenging, problem.  For example, if the SQL query was to use the IP address of the node then we would want to be able to do a "SELECT foo, bar FROM db WHERE IP_Address = ${NodesData.IP_Address}"  In the limited testing we do we couldn't get that function to work either.

                       

                      At this point we've set it aside for other priorities.  The thought is that we might be able to do something with our Splunk DB Connect app and dashboards that way, but we haven't tested that either.  It's more like the mad ramblings of a monitoring engineer

                        • Re: Custom SQL Query to a Non-Solarwinds DB in the UI
                          familyofcrowes

                          We wanted to do this for years using BMC Remedy but lack of SQL talent and available time has left it a dream. 

                          Our idea was to create a custom field or fields for nodes and pull a unique identifier for that same asset in Remedy DB.  We would import that identifier into the solarwinds DB for all nodes and hopefully run that job daily or on demand.  This would then allow us to have a common field we could use to perform queries.

                          That's about as far as we dreamed  :-)

                            • Re: Custom SQL Query to a Non-Solarwinds DB in the UI
                              jbiggley

                              We actually do that today with our ServiceNow implementation.  ciulei did some fun things with the ServiceNow database and some SSIS packages.  We've managed to link any node in SolarWinds that has a CI entry in the DB to the sys_id in ServiceNow.  We also came up with an ingenious way (if I do say so myself!) of linking our application monitors to the application CI in CMDB too.  This means that both nodes and application monitors can now reference their CMDB equivalent in ServiceNow and extract data from that CI for populating custom properties.

                               

                              Although we haven't leveraged it as far as we want to do, we plan to use those fields to replace as many of the manually entered fields as we can.  This will impact alerting, dashboarding, etc. very quickly.  The grand plan is to allow teams to directly influence what should and should not be monitored and alerted on in SolarWinds.  Want to stop alerting on a particular interface because of switch maintenance?  Just select it was part of the change management process and it will be auto-muted for alerting during the change window.

                               

                              Want to decommission a node?  Automation will take care of that too.

                               

                              Now if only we could pause all our other project work and make things stop breaking then we might actually around to pursuing that 'pie in the sky' goal.