10 Replies Latest reply on Jun 18, 2013 6:30 AM by svindler

    Database model

    svindler

      Is there an overview of the exposed tables where I can see how the entities in each table relate to each other?

       

      It some times take a bit of guess work to figure out that for instance Orion.Nodes.NodeID and Orion.Events.NetObjectID relate to each other. And if Cirrus.Nodes.NodeID of type System.Guid match up with Orion.Nodes.NodeID of type System.Int32

       

      The Schemas that are installed together with the SDK does not specify relationships.

        • Re: Database model
          Leon Adato

          In my copious spare time (COUGH!) I've been playing with the database modeler utility inside Microsoft SQL Studio, but I'm not having luck getting it to export in any meaningful way.

           

          I opened a ticket a while ago about this and was told that the schema is considered part of SolarWinds intellectual property, and as such (like the specific oids they use) we are free to suss it out ourselves but they (official channels) will not be providing us with the information.

           

          If anyone has anything already created - even the primary tables and their fields/relationships, that would be an awesome thing to post here.

            • Re: Database model
              tdanner

              For the SWIS schema (which is more or less a layer on top of the database schema) there are relationships defined between the entity types. You can see these in the API reference documentation (C:\Program Files (x86)\SolarWinds\Orion SDK\Documentation\Schema\3.0\index.html) and in the left pane of SWQL Studio when you expand each entity - these are the "chain link" icons.

                • Re: Database model
                  svindler

                  I'm sorry, if I'm a bit dense here, but I can't see where I would be able to confirm that Orion.Nodes.NodeID is related to Orion.Events.NetObjectID? I can't find it in the Schema or in the left pane of SWQL Studio...

                  Same goes for your example with Orion.Nodes.NodeID and Cirrus.Nodes.CoreNodeID

                    • Re: Database model
                      Jan Pelousek

                      The relationships info is stored in SWIS schemas shipped with Orion, which is exposed by the entity Metadata.Relationship.

                      I'm sure query "SELECT Name, BaseType, SourceType, TargetType, SourcePrimaryKeyNames, SourceForeignKeyNames FROM Metadata.Relationship" will provide you all the info you need (relationship names, Type of relationship, Source and target entity and names of properties which are used to join on).

                      Honza

                        • Re: Database model
                          svindler

                          When I execute your query, I don't see anyhing in the result that provide the relationship info for the two specific examples I provided.

                           

                          If you see something that clearly indicates the relationship, I would like to know how to interpret the result, so I can compare with my installation. Because then either our installation has a problem or I'm embarrasingly dense.

                            • Re: Database model
                              tdanner

                              The problem is not with your installation or your understanding. The problem is that our schema does not have a relationship defined for these two combinations. Other than this forum thread, there is no documentation of these two connections.

                                • Re: Database model
                                  svindler

                                  Is this based on a decision not to publish a database model, or is it because not enough people have requested this as a feature?

                                  If it is the latter, and I want to make a feature request, should I put a feature request under each individual product? I don't see a Feature Request forum for Orion SDK.

                      • Re: Database model
                        tdanner

                        For joining Orion.Nodes and Cirrus.Nodes, you can use a query like this:

                         

                        SELECT o.Caption, c.NodeCaption

                        FROM Orion.Nodes o

                        LEFT JOIN Cirrus.Nodes c on o.NodeID=c.CoreNodeID

                        • Re: Database model
                          svindler

                          I have made the following two feature requests for NPM and NCM:

                          http://thwack.solarwinds.com/ideas/2360

                          http://thwack.solarwinds.com/ideas/2361

                           

                          Please vote for both of them, if you ever feel lost when building a new query.