SQL Clauses in Reporting

Question

Hi all,

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?

jnewell · Answer

Hi @mesverrum,

Did you have a chance to look at this?

jnewell · Answer

Hi @mesverrum

SELECT DISTINCT

IPUI.Username,
IPUI.UserID,
IPUI.LoginTime,
IPUI.EndpointID,
E.MacAddress,
P2EC.VlanID,
V.VlanName,
IPC.IPAddress as HostIP,
DNSC.DNSName as HostName,
P.Name as [Port Name],
P.PortType,
P.PortDescription,
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!

Kind regards 
James

jnewell · Answer

Hi Marc,

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

Regards,

James