1 of 1 people found this helpful
without being knee deep in this...
select unique(<whatever>) from <your query> join nodes n on d.nodeid=n.nodeid
select * from discoverednodes where nodeid not in (select nodeid from nodes)
off top of my head...
I had thought of that, but still end up with the 431 devices listed and only 10 nodes in the Scheduled Discovery Results list on the Web.
SELECT Name, PrimaryAddress, Timestamp, IgnoredNodeID, d.NodeID FROM DiscoveryNodes d (nolock) WHERE d.NodeID NOT IN ( SELECT n.NodeID from Nodes n) AND Timestamp > 1375690020 AND IgnoredNodeID IS NULL AND ProfileID IN ('2','3','4','10','11','12','13','14','59','60')
that might make sense...discovered items would not have THE nodeid...
let me take a look at the table. maybe joining on IP_address instead.
select d.NodeID, d.IPAddress, d.Hostname, n.NodeID, n.ip_address, n.hname
from DiscoveredNodes d left join Nodes n on d.IPAddress=n.IP_Address
-- where n.ip_address is null
test, then uncomment where clause
Thanks for the help!!!
SELECT d.NodeID AS DiscoveryID, PrimaryAddress AS DiscoveryAddress, Name AS DiscoveryName, n.NodeID, IP_Address FROM DiscoveryNodes d LEFT JOIN Nodes n on PrimaryAddress=IP_Address WHERE IP_Address is NULL AND IgnoredNodeID IS NULL AND ProfileID IN ('2','3','4','10','11','12','13','14','59','60')