Notification

Icon
Error

User association report query - List all Active Directory users that don't have an asset of type X associated

Posted: Monday, June 24, 2019 5:38:51 PM(UTC)
dan508

dan508

Member Alpha Tester Original PosterPosts: 3
0
Like
Hi all.
slowly learning SQL, and I need a bit of assistance on this one query.
I want to query all Active directory users that don't have an asset of type 909 assigned to them. To do this, I have the following script, but it isn't working :(

select username
from tblADusers
where not exists
(select tbladusers.username
,tblAssetUserRelations.AssetID
,tblassets.Assettype

from tblADusers
inner join tblassetuserrelations on tbladusers.username=tblassetuserrelations.username
inner join tblAssets on tblAssetuserrelations.AssetID=tblAssets.AssetID
where tblassets.assettype =909
)


If I run the sub Query, it pull in all AD users that do have asset type 909 assigned to them. so shouldn't it be as simple as running a query to pull the user that aren't in the sub query?

Any help will be appreciated.

Thanks in advanceBrick wall ,
Dan
endyk
#1endyk Member Posts: 24  
posted: 6/26/2019 9:47:25 PM(UTC)
Hello Dan,

I think this should do it for you.

SELECT tbladusers.username,
tblAssetUserRelations.AssetID,
tblassets.Assettype
FROM tblADusers
INNER JOIN tblassetuserrelations ON tbladusers.username=tblassetuserrelations.username
INNER JOIN tblAssets ON tblAssetuserrelations.AssetID=tblAssets.AssetID
WHERE tblassets.assettype <> 909

Active Discussions

Lansweeper report of workstation with 2nd screen
by  Esben.D   Go to last post Go to first unread
Last post: 7/12/2019 12:43:16 PM(UTC)
Lansweeper Lansweeper warnings sent to an email
by  MilicaM  
Go to last post Go to first unread
Last post: 7/12/2019 10:36:44 AM(UTC)
Lansweeper Number of users in a group
by  matt steele   Go to last post Go to first unread
Last post: 7/12/2019 10:34:17 AM(UTC)
Lansweeper Report of assets names behind switches/routers
by  dsimonspan  
Go to last post Go to first unread
Last post: 7/11/2019 10:41:17 AM(UTC)
Lansweeper Intel SSD and Processor Diagnostic Tool Vulnerability
by  Esben.D   Go to last post Go to first unread
Last post: 7/11/2019 8:24:34 AM(UTC)
Lansweeper Patch Tuesday report, last 3 months
by  Viper  
Go to last post Go to first unread
Last post: 7/11/2019 2:32:20 AM(UTC)
Lansweeper Adobe Security Update July
by  Esben.D   Go to last post Go to first unread
Last post: 7/10/2019 3:45:16 PM(UTC)
Lansweeper Firefox 68 Security Update
by  Esben.D  
Go to last post Go to first unread
Last post: 7/10/2019 3:35:17 PM(UTC)