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

    Powershell , UDT Adhoc reporting results




      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






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







        • Re: Powershell , UDT Adhoc reporting results

          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



          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

              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

                  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


                  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



                  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

                      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

                          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!