→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
smozie
Engaged Sweeper
I am trying to write a report to find assets within our US domain, that are tied to the tblADUsers.Department %IT% and do no have versions 6.0.0 or 6.0.1 of Traps on their system. I run the code below and what I get back is a bit of a mess, looking for help.


Select Top 100000 tblAssets.AssetID,
tblAssets.Domain,
tblAssets.Username,
tblADusers.Firstname,
tblADusers.Lastname,
tblADusers.Department
From tblAssets
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Left Join tblADusers On tblADusers.Username = tblAssets.Username And
tblADusers.Userdomain = tblAssets.Userdomain
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Where tblSoftwareUni.softwareName Like '%traps%' And tblSoftware.softwareVersion Not
Like '6.0.0%' And tblSoftware.softwareVersion Not Like '6.0.1%' AND tblADusers.Userdomain Like '%US%' And tblADusers.Department Like '%IT%'
Order By tblAssets.AssetName



1 REPLY 1
Jelly
Engaged Sweeper
smozie wrote:
I am trying to write a report to find assets within our US domain, that are tied to the tblADUsers.Department %IT% and do no have versions 6.0.0 or 6.0.1 of Traps on their system. I run the code below and what I get back is a bit of a mess, looking for help.


Select Top 100000 tblAssets.AssetID,
tblAssets.Domain,
tblAssets.Username,
tblADusers.Firstname,
tblADusers.Lastname,
tblADusers.Department
From tblAssets
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Left Join tblADusers On tblADusers.Username = tblAssets.Username And
tblADusers.Userdomain = tblAssets.Userdomain
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Where tblSoftwareUni.softwareName Like '%traps%' And tblSoftware.softwareVersion Not
Like '6.0.0%' And tblSoftware.softwareVersion Not Like '6.0.1%' AND tblADusers.Userdomain Like '%US%' And tblADusers.Department Like '%IT%'
Order By tblAssets.AssetName





Try this changed your left join and a few other things. Personally I wouldn't post with the software names/versions/domain/department info not anonymized.

Select Top 100000 tblAssets.AssetID,
tblAssets.Domain,
tblAssets.Username,
tblADusers.Firstname,
tblADusers.Lastname,
tblADusers.Department
From tblAssets
Inner Join tsysIPLocations On tsysIPLocations.LocationID = tblAssets.LocationID
Left Join tblADusers On tblAssets.Username = tblADusers.Username
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Where tblSoftwareUni.softwareName Like '%traps%'
And tblSoftware.softwareVersion Not Like '6.0.0%'
And tblSoftware.softwareVersion Not Like '6.0.1%'
AND tblAssets.Domain = 'us'
And tblADusers.Department Like '%IT%'
Order By tblAssets.AssetName