cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 11

SQL Clauses in Reporting

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? 

0 Kudos
4 Replies

Select distinct works the same in custom reports as it does anywhere else, I use it often.

 

Any chance you could post the query and we can find a reason it's behaving oddly?

- Marc Netterfield, Github
0 Kudos

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

0 Kudos

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

0 Kudos

Hi @mesverrum

Did you have a chance to look at this?  

0 Kudos