This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Newbie: Combining UDT and Interface data in an repport

Hi there.

I am an newbie and trying to make a report on for each mac address from UDT whit following data:

Mac Address, interface caption,date last seen on network, ip address that it had (if known).

Any help would be appreciated 

  • So unless I missed something it looks like the GUI doesn't have the option to include endpoint info in the reports,

    Since I was curious I went ahead and made a custom SWQL query that pulls in the details you asked for. In my test lab I came across some buggy info so I can't say this is 100% but it seems to be a start.

    select distinct m.MACAddress, m.MACUrl as [_linkfor_MACAddress], e.lastupdate, m.PortName, m.PortIcon as [_iconfor_PortName], m.PortUrl as [_linkfor_PortName], m.UserName, m.UserUrl as [_linkfor_UserName], m.IPAddress

    FROM Orion.UDT.MACAddressInfo m

    join Orion.UDT.Endpoint e on e.macaddress=m.rawmac

    where macaddress not like ''

    order by m.macaddress

    -Marc Netterfield

        Loop1 Systems: SolarWinds Training and Professional Services

  • Well first of all thanks for you reply.

    Tried whit GUI but i did not succeed so i turned to to SWQL.

    I opened SWQL studio just to see what i would get to modify it but it seems like this query is to heavy for the system as i get:

    Message: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. So i assumed it's a very heavy job for database.

    Just to be sure SWQL studio is working i asked all data FROM Orion.UDT.MACAddressInfo and this one worked,

    Anyway i see that you used rawmac for connection between two tables. May I ask why not EndpointID?

  • Just picked the columns that I was looking at, endpointid should probably work just fine.

    In my lab that Orion.UDT.MACAddressInfo had some 37000 entries and we barely use the lab here so I could imagine it being a beastly table to work with in a bigger environment.

    I only spent a short time playing with it so there may be more efficient methods to piece the query together but hopefully you can find what works for your environment.  Maybe excluding wireless would make it into a more manageable set?

  • Nope,

    Excluding wireless did't help nether. Thanks anyway for your suggestion...

  • Nobody?

    My final goal is to have this fields:

    <Mac Address>  <interface caption>, <date last seen on network> , <ip address that it had (if known)>

    At this moment I have:

    SELECT MACAddress, LastSeen, PortDescription, IpAddress, HostName FROM [dbo].[UDT_MACCurrentInformation]

    MACLastSeenPortDescriptionIpAddressHostName
    10:60:4B:6B:FA:EA27-10-2016 08:55:21FAC.02.13-D/26-04

    Where date is same each day..

  • Is this what you're looking for?

    Select Top (5) MACAddress,PortName,LastSeen,IPAddress

    From UDT_MacCurrentInformation

    Remove the 'Top (5)' for a full dataset.

  • No as you can see when you run that you will only get current dates. Not historical ones. Eg. The machine which was connected 10 days ago will not be there.