5 Replies Latest reply on Feb 12, 2017 3:36 PM by britv8

    Powershell , UDT Adhoc reporting results

    britv8

      Hi,

       

      I am trying to use the powershell component of the SDK and am wanting to get a results back containing all UDT items, just like the adhoc reporting screen

      I assume I do this using the

      Get-SwisData

       

       

      function

       

      can anyone give me some pointers on the select statement or documentation on the database structure

       

      thanks!

       

       

       

       

        • Re: Powershell , UDT Adhoc reporting results
          britv8

          SolarWinds Information Service v3.0 Schema Documentation Index

          Which helps a lot!

          I ended up with

           

          Get-SwisData $swis 'SELECT UDT.macaddress,UDT.rawMAC,UDT.ipaddress,UDT.hostname,UDT.LastSeen,UDT.PortName,UDT.PortID,UDT.NodeAccessPoint,UDT.NodeID
                              FROM Orion.UDT.MACCurrentInformation UDT
                             '
          

           

          which is sufficient for what I want. nearly....:)

           

          My next question is how do I map the

          UDT.NodeAccessPoint,UDT.NodeID to The container the object is in

           

          this

          Get-SwisData $swis 'SELECT  ContainerID,Name,Members
                              FROM Orion.Container 
                              '
          

           

          gives me an error as it does not like the members object

            • Re: Powershell , UDT Adhoc reporting results
              tdanner

              Orion.Container.Members is a navigation property. In SWQL terms it is short-hand for a join to the Orion.ContainerMembers entity. So you could run a query like this:

               

              SELECT ContainerID, Name, Container.Members.DisplayName

              FROM Orion.Container

               

              And get some results. But I'm not sure that is really what you are looking for. When you say "how do I map the UDT.NodeAccessPoint,UDT.NodeID to The container the object is in", what kind of "container" do you mean?

                • Re: Powershell , UDT Adhoc reporting results
                  britv8

                  thanks tdanner.

                  that explains the links well

                   

                  I meant a group

                   

                  to explain better what I an trying to achieve.

                  I am trying to track down the location of  a device so I can go an replace it.

                   

                  I have a device

                  it is plugged into a port on a switch

                  that switch is allocated ato  group in Solarwinds

                  I want a result like

                  MAC address,IPAddress,HOSTNAME,Switch port, switch ,Group

                  e.g.

                  Device1 is connected to Port 4 of Switch X , that is located in Rack B of Floor 2, of Building 1  in the Seattle Campus, in Seattle, USA

                   

                  To put it another way in solarwinds web interface

                  I want to report on the devices connected to the HAM switch , located in the Campb Group, which is in the ERB Group, which is in the Campus group

                  Device1,Port1,HAM,Campb,ERB,CAMPUS

                  Device2,Port3,HAM,Campb,ERB,CAMPUS

                  I got this far

                   

                   

                  Get-SwisData $swis 'SELECT macaddress,rawMAC,ipaddress,hostname,LastSeen,PortName,PortID,NodeAccessPoint,NodeID,MACCurrentInformation.Node.DisplayName,
                                      MEMBERS.ContainerID, MEMBERS.MemberPrimaryID, MEMBERS.MemberEntityType, MEMBERS.Name,MEMBERS.Container.Displayname AS ContainerName
                                      FROM Orion.UDT.MACCurrentInformation 
                                      JOIN Orion.ContainerMembers MEMBERS on MACCurrentInformation.Node.DisplayName = MEMBERS.Fullname
                                     '
                  

                  but don't like the join?

                    • Re: Powershell , UDT Adhoc reporting results
                      tdanner

                      What do you mean "don't like the join"? Is there an error message?

                       

                      Joining to Orion.ContainerMembers could work, but that will only get you the innermost group ("Campb") in your picture. To work up to higher groups, you will need to run a separate query for each level out you need to go.

                       

                      And remember that an entity can be a member of more than one group. And a group can be a member of more than one group as well. So you may not get a unique answer to the "what group is this node in" question. You might have an easier time with this kind of lookup using custom properties to store the location information instead of trying to reverse it out from groups.

                        • Re: Powershell , UDT Adhoc reporting results
                          britv8

                          Thanks for the reply.

                           

                          what I mean by "I don't like the join" is that I was expecting/wanting to find NodeID in the Orion.ContainerMembers table to JOIN on rather than the fullname (I like joining on IDs  (a key) rather than , for all I know, a Text field.

                           

                          Yeah I saw that I would need to "run up the tree", luckily , at the moment the Node description contains the "Campus ERB Campb" in its description

                           

                          Thanks for letting me know about the members can belong to multiple groups and custom properties, I can at lease code to identify that prior to running my query

                           

                          Having this sort of access to Solarwinds via PowerShell is just great!

                           

                          thanks for your help!