→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
soucyinter
Engaged Sweeper III
Hello,

I have created this SQL Report this morning and I have double entries. I guess it has something to do with the fact I use TblADUsers and TblComputers but I ain't a 100% sure about it... I know there's a trick with Inner Join or Left join....

I'd say that 85% to 95% of the entries are in double, others are not.
Here it is :
________________

Select Top 1000000 tblerrors.CFGname, tblComputers.Lastseen, tblComputers.FQDN,
tblComputers.Computer, tblComputers.LastknownIP, tblADusers.OU
From tblComputers
Inner Join
tblerrors On tblComputers.Computername = tblerrors.Computername
Inner Join
tblADComputers On tblComputers.Computername = tblADComputers.Computername
Inner Join
tblADusers On tblComputers.Username = tblADusers.Username
Where tblerrors.CFGname Like 'WMI%' And tblADusers.OU Like '%SPI%'
_________________
3 REPLIES 3
Hemoco
Lansweeper Alumni
TblComputers and tblADUsers must be linked on both the Username and Userdomain fields. Please try the modified report below.

Select Top 1000000 tblerrors.CFGname, tblComputers.Lastseen, tblComputers.FQDN,
tblComputers.Computer, tblComputers.LastknownIP, tblADusers.OU
From tblComputers Inner Join
tblerrors On tblComputers.Computername = tblerrors.Computername Inner Join
tblADusers On tblComputers.Username = tblADusers.Username And
tblADusers.Userdomain = tblComputers.Userdomain
Where tblerrors.CFGname Like 'WMI%' And tblADusers.OU Like '%SPI%'

soucyinter
Engaged Sweeper III
Lansweeper wrote:
TblComputers and tblADUsers must be linked on both the Username and Userdomain fields. Please try the modified report below.

Select Top 1000000 tblerrors.CFGname, tblComputers.Lastseen, tblComputers.FQDN,
tblComputers.Computer, tblComputers.LastknownIP, tblADusers.OU
From tblComputers Inner Join
tblerrors On tblComputers.Computername = tblerrors.Computername Inner Join
tblADusers On tblComputers.Username = tblADusers.Username And
tblADusers.Userdomain = tblComputers.Userdomain
Where tblerrors.CFGname Like 'WMI%' And tblADusers.OU Like '%SPI%'



After trying your query, it is not working. Still all the duplicated data.

I will use the "distinct" function then.

Thank you for supporting us this fast!
You guys have a great support team.
soucyinter
Engaged Sweeper III
Lansweeper wrote:
TblComputers and tblADUsers must be linked on both the Username and Userdomain fields. Please try the modified report below.

Select Top 1000000 tblerrors.CFGname, tblComputers.Lastseen, tblComputers.FQDN,
tblComputers.Computer, tblComputers.LastknownIP, tblADusers.OU
From tblComputers Inner Join
tblerrors On tblComputers.Computername = tblerrors.Computername Inner Join
tblADusers On tblComputers.Username = tblADusers.Username And
tblADusers.Userdomain = tblComputers.Userdomain
Where tblerrors.CFGname Like 'WMI%' And tblADusers.OU Like '%SPI%'



Hello,

I will try your query and see what it does.
I have modified the one I created this morning, I found out that by using "distinct", it clears my double entries (because all rows have the same exact information). Then I cleaned up a few lines of code.

Here it is :

Select Distinct Top 1000000 tblerrors.CFGname, tblComputers.Lastseen,
tblComputers.FQDN, tblComputers.Computer, tblComputers.LastknownIP,
tblADusers.OU
From tblComputers Inner Join
tblerrors On tblComputers.Computername = tblerrors.Computername Inner Join
tblADusers On tblComputers.Username = tblADusers.Username
Where tblerrors.CFGname Like 'WMI%' And tblADusers.OU Like '%SPI%'