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
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
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?
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?
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.
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!