7 Replies Latest reply on Dec 11, 2013 6:47 AM by Jan Pelousek

    Extracting Data from Orion database

    giteepag1987

      Hello Experts,

       

      I am new to solarwinds. I am in a situation where I need to extract data from Orion database. Basically I have been provided with orion SDK Software to access Orion database.

       

      I can login to the database and it has tables inside.

       

      Does anyone know how to extract data from them? Do we have any document which explains it?

       

      Thanks,

       

      -Prashant Girennavar.

        • Re: Extracting Data from Orion database
          Jan Pelousek

          Hello,

           

          It very depends on what you need to do. If you need to use database operations like "DELETE", "UPDATE", etc. in SQL syntax, then you should use rather DB manager shipped with Orion or SQL management studio. If you need to work with Orion objects as they are designed, you can use SWQL studio (part of Orion SDK) or if you need it for some kind of automation, you can use e.g. PowerShell cmdlets such as Get-SwisData. (Get-SwisData $swisConnection $query). Please specify your needs. However the OrionSDK docummentation (included in SDK installer) should give you sufficient information, incl. how to use CRUD operations, etc.

            • Re: Extracting Data from Orion database
              giteepag1987

              Hello Jan,

               

              Thanks for the reply. I just want to extract information which is stored in the table. I will be not updating or creating or deleting any data inside the database.

               

              Talking about powershell , from where I can download the module for this? Can you please let me know.

               

              Currently I am using Invoke-SQLCmd for SQL Database , same way I can extract the data if I have module for this

               

              Thanks,

               

              -Prashant Girennavar.

                • Re: Extracting Data from Orion database
                  Jan Pelousek

                  The module is the part of Orion SDK and related assemblies should be installed while installing the package. Then is enough to add the snapin to powershell by "Add-PSSnapin SwisSnapin" command.

                  Then you can start your scripts e.g. like this:

                  ########################################################################################################################

                  Clear-Host

                  if (!(Get-PSSnapin -Name "SwisSnapin" -ErrorAction SilentlyContinue))

                    {

                    Add-PSSnapin SwisSnapin -ErrorAction SilentlyContinue

                    }

                   

                  $swisTarget = 'localhost'   ## IP or hostname of SWIS

                  [System.String]$username="admin"

                  [System.String]$password="123"

                   

                  $SWIS_Connection = Connect-Swis -host $swisTarget -UserName $username -Password $password

                   

                  ##Then you can get your results e.g.:

                  $Results = Get-SwisData $SWIS_Connection "Select NodeID, Caption from Orion.Nodes"

                  ########################################################################################################################

                   

                  Then the $Results variable will be filled by array of the results

                    • Re: Extracting Data from Orion database
                      giteepag1987

                      Hi Jan,

                       

                      this query works and is there any ways we can query all the table values within the DB as Select * From Query is not supported. or is there any list for the listing of the serial number and other information.

                        • Re: Extracting Data from Orion database
                          Jan Pelousek

                          Hello,

                           

                          Select * from xx is currently not supported. The list of properties (columns) from specific entity you can get by generating it directly from SWQL studio, which is part of the SDK package, or if you need to build the query dynamicaly, you can use construction like:

                          example for Orion.Nodes entity:

                           

                          [System.String]$Entity = 'Orion.Nodes'

                          [System.String]$Properties = ((((Get-SwisData $SWIS_Connection ("Select Name from Metadata.Property WHERE EntityName='" + $Entity + "' AND IsNavigable = 'false'")) | Out-String) -Replace("`n", ","))).Trimend(",")

                          ##Then you can get your results like:

                          [System.String]$Query = ("Select " + $Properties + " from " + $Entity)

                          [System.Array]$Results = Get-SwisData $SWIS_Connection $Query

                            • Re: Extracting Data from Orion database
                              giteepag1987

                              Thanks Jan,

                               

                              connecting to the DB via SWQL studio showed me the different tables and their properties. one more question.

                               

                              can we use the same power shell query to query tables in the same way as mentioned such as NCM.NODE or NCM.Entityphysical with the same power shell query to pull out its details?

                               

                              Get-SwisData $SWIS_Connection "Select NodeID, Caption from Orion.Nodes"


                              Can  use  NCM.NODE or NCM.Entityphysica instead of Orion.nodes will it work.with different properties?

                                • Re: Extracting Data from Orion database
                                  Jan Pelousek

                                  It would most probably work, but just please keep in mind, that released NCM still runs under SWISv2, so to query against this endpoint, you'll need to add -v2 switch to the Connect-Swis cmdlet as:


                                  $SWIS_Connection = Connect-Swis -host $swisTarget -UserName $username -Password $password -V2

                                   

                                  Just let know in case of doubts or possible problems