Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials

Query failing

damon_2021

I have a below query that fails:

result = swis.query("SELECT HostName, IPAddress, MACAddress, ConnectedTo, PortNumber, PortName, VLAN, MACVendor, NodeID, FirstSeen " \
                         "FROM Orion.UDT.AllEndpoints " \
                         "WHERE ipAddress LIKE '{0}' " \
                         "OR Hostname LIKE '{0}%' " \
                         "OR MACAddress LIKE '{0}'".format(query))
print(result)

I get an error: "Cannot resolve property FirstSeen"

If I remove 'FirstSeen' from the SELECT line, it works. I ran the same query on the Database Manager GUI on Orion and it works. Any reason why this is not working?

I am using Python to run the query.

Orion OS ver: 10.0.14393.0 / Windows Server 2016

Find more posts tagged with

SWQL

sdk

orion sdk

Accepted answers

All comments

sum_giais

Looks like there isn't a FirstSeen property on the Orion.UDT.AllEndpoints entity as shown in the latest schema docs --> https://solarwinds.github.io/OrionSDK/2020.2/schema/Orion.UDT.AllEndpoints.html

I don't have experience with UDT however these other entities within UDT do appear to have that property --> Orion.UDT.Endpoint, Orion.UDT.EndpointIP, Orion.UDT.EndpointDNS

Without anything in front of me though, no idea off-hand how to connect if even possible the Orion.UDT.AllEndpoints entity to any of the above.

KMSigma.SWI

Yep - No FirstSeen in the Orion.UDT.AllEndpoints entity.

Above is from a Platform 2023.4.0 installation.

damon_2021

There is a FirstSeen column in the UDT_AllEndPoints when using Database Manger via GUI.

See screenshot:

sum_giais

That is interesting... but note that's in the Database Manager which is using SQL queries directly against the database. SWIS queries use SWQL (SolarWinds Query Language) which is different and queries the SolarWinds Information Service (aka the API). They are not the same and won't always be in direct relation to one another.

Related, a SQL "View" is not a direct SQL table -- it's basically the output of a custom SQL query that is saved and able to be referenced similar to that of a SQL table.

So digging into this a bit, it looks like you can see what the SQL query is that makes of the view with this:

sp_helptext 'SolarWindsOrion.dbo.UDT_AllEndpoints'

And that provides this

SELECT DISTINCT  
 [ip].[IPAddress] AS IPAddress  
 , [dns].[DNSName] AS HostName  
 , dbo.udt_FormatMACAddressForUI([e].[MACAddress]) AS MACAddress  
 , [n].[Caption] AS ConnectedTo  
 , [p].[Name] AS PortNumber  
 , [p].[PortDescription] AS PortName  
 , CASE [p2e].[VlanID] WHEN 0 THEN NULL ELSE [p2e].[VlanID] END AS VLAN  
 , [n].[Status] AS NodeStatus  
 , [n].[NodeID] AS NodeID  
 , [p].[PortID] AS PortID  
 , [p2e].[ConnectionType] AS ConnectionType  
 , [p2e].[FirstSeen] AS FirstSeen,  
case [p2e].ConnectionType  
 when 1 then N'Direct'  
 when 2 then N'Indirect'  
 else N'Unknown'  
end as ConnectionTypeName   
 ,e.Vendor as MACVendor  
 ,ISNULL(v.Icon,'Unknown.gif') as MACVendorIcon  
 FROM dbo.Nodes AS n WITH(NOLOCK)  
 INNER JOIN dbo.UDT_Port AS p WITH(NOLOCK) ON (([p].[NodeID] = [n].[NodeID]) AND ([p].[Flag] <> 1))   
 INNER JOIN dbo.UDT_PortToEndpointCurrent AS p2e WITH(NOLOCK) ON ([p2e].[PortID] = [p].[PortID])      
 INNER JOIN dbo.UDT_Endpoint AS e WITH(NOLOCK) ON ([e].[EndpointID] = [p2e].[EndpointID])       
 LEFT JOIN dbo.UDT_IPAddressCurrent AS ip WITH(NOLOCK) ON (([e].[EndpointID] = [ip].[EndpointID]))    
 LEFT JOIN dbo.UDT_DNSNameCurrent AS dns WITH(NOLOCK) ON (([dns].[IPAddress] = [ip].[IPAddress]))   
 LEFT JOIN Vendors v on e.Vendor = v.Name  
 WHERE (([p2e].[ConnectionType] IS NOT NULL))

The key point from the above info is that the "FirstSeen" column is derived from the UDT_PortToEndpointCurrent SQL table via joining Nodes --> UDT_Port --> UDT_PortToEndpointCurrent.