I have been working on a report recently which requires a SQL query to obtain the data from the database.
I have written the query, and tested and confirmed using Database Manager and I get the correct approximation of results.
Unfortunately, when I bring the query in to the report, the datasource shows many, many more results that Database Manager.
I believe this is due to the query utilising the DISTINCT clause.
In DBM, this works wonders and I get approximately 300 results which is expected.
However, in the report, the query brings back over 2000 records.
Previously, this wouldn't have been an issue if using Report Writer, however it is now obsolete.
I am converting my query to SWQL to see if this works, however it is taking longer than anticipated.
Is anyone else having this issue? Or does anyone know of a workaround for it?
I hope this is the case and it is just me being a fool... I will look to get the script up this afternoon for you to look over, if that is ok?
Thank you for taking your time to respond 🙂
IPC.IPAddress as HostIP,
DNSC.DNSName as HostName,
P.Name as [Port Name],
ND.Caption as SwitchName,
N.Category_site as Site
FROM UDT_IPUserInfo as IPUI
JOIN UDT_Endpoint as E with(nolock) on IPUI.EndpointID=E.EndpointID
JOIN UDT_PortToEndpointCurrent as P2EC with(nolock) on P2EC.EndpointID=E.EndpointID
JOIN UDT_VLAN as V with(nolock) on (V.VlanID=P2EC.VlanID AND V.PortID=P2EC.PortID)
JOIN UDT_IPAddressCurrent as IPC with (nolock) on IPC.EndpointID=E.EndpointID
JOIN UDT_DNSNameCurrent as DNSC with (nolock) on DNSC.IPAddressID=IPC.IPAddressID
JOIN UDT_Ports as P with(nolock) on P.PortID=P2EC.PortID
JOIN NodesData as ND with (nolock) on ND.NodeID=P.NodeID
JOIN Nodes as N with (nolock) on N.NodeID=ND.NodeID
Where IPUI.LoginTime >= dateadd(day,datediff(day,1,GETDATE()),0)
AND IPUI.LoginTime < dateadd(day,datediff(day,0,GETDATE()),0)
AND IPUI.UserName NOT LIKE 'NULL'
AND IPUI.UserName NOT LIKE '*******************%'
AND DNSC.DNSName LIKE '***%'
Order By IPUI.LoginTime Asc
I am hoping it is not something obvious! 🙂
SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community. More than 150,000 members are here to solve problems, share technology and best practices, and directly contribute to our product development process.