Notification

Icon
Error

Jelly
#0Jelly Member Posts: 7  
posted: 5/17/2019 12:09:20 AM(UTC)
Originally Posted by: smozie Go to Quoted Post
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.

Code:

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.
Code:

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



Active Discussions

Lansweeper local admin users of a specific device
by  kdunnett   Go to last post Go to first unread
Last post: Today at 9:30:26 PM(UTC)
Lansweeper How to get total disk usage of all VM assets
by  Erik.T  
Go to last post Go to first unread
Last post: Today at 4:49:58 PM(UTC)
Lansweeper Windows Server 2016 & Patch Tuesday May 2020
by  Hendrik.VE   Go to last post Go to first unread
Last post: 5/22/2020 8:20:05 PM(UTC)
Lansweeper Users mapped "shared" printers
by  Andy.S  
Go to last post Go to first unread
Last post: 5/22/2020 4:16:23 PM(UTC)
Lansweeper Report on Assets in a Static Group
by  Andy.S   Go to last post Go to first unread
Last post: 5/22/2020 2:55:03 PM(UTC)
Lansweeper Windows 10 Activation
by  TruSynergy  
Go to last post Go to first unread
Last post: 5/21/2020 7:54:25 PM(UTC)
Lansweeper Filtering Report Based On Active Status
by  CyberCitizen   Go to last post Go to first unread
Last post: 5/21/2020 4:04:33 AM(UTC)
Lansweeper Windows: Unauthorized Administrators (Built-in)
by  Jackie.L  
Go to last post Go to first unread
Last post: 5/20/2020 8:01:17 PM(UTC)