This report identifies disabled Active Directory (AD) accounts that are still linked to active assets within the organization. When an AD account is disabled—whether due to employee departure, role changes, or security measures—any associated devices should be reassigned, decommissioned, or removed from the network to prevent unauthorized access. This report helps IT teams pinpoint orphaned assets that may still be in use, ensuring compliance with security policies and reducing potential risks. By providing clear visibility into these discrepancies, organizations can streamline device reassignment, deprovisioning, and access control processes to maintain a secure and up-to-date IT environment.
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblState.Statename,
tblAssets.Domain,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
Coalesce(tsysOS.OSname, tblLinuxSystem.OSRelease, tblMacOSInfo.KernelVersion)
As OS,
tblAssets.SP,
tblAssets.Lastseen,
tblAssets.Lasttried,
tsysAssetRelationTypes.Name As RelationType,
tblAssetUserRelations.Comments As RelationComments,
tblAssetUserRelations.StartDate As RelationStartDate,
tblAssetUserRelations.Username As RelationUser,
tblAssetUserRelations.Userdomain As RelationUserDomain,
Case
When Coalesce(tblADusers.IsEnabled, 0) = 0 Then 'False'
Else 'True'
End As EnabledInAD
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblAssetUserRelations On
tblAssets.AssetID = tblAssetUserRelations.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tsysAssetRelationTypes On tblAssetUserRelations.Type =
tsysAssetRelationTypes.RelationTypeID
Left Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
Left Join tblMacOSInfo On tblAssets.AssetID = tblMacOSInfo.AssetID
Left Outer Join tblADusers On
tblADusers.Userdomain = tblAssetUserRelations.Userdomain And
tblADusers.Username = tblAssetUserRelations.Username
Left Outer Join tblADGroups On tblADGroups.ADObjectID =
tblAssetUserRelations.AdObjectId
Left Outer Join tblAzureAdUser On tblAzureAdUser.UserPrincipalName =
tblAssetUserRelations.Upn
Where tblState.Statename = 'Active' And Coalesce(tblADusers.IsEnabled, 0) = 0
Order By tblAssets.IPNumeric,
tblAssets.Domain,
tblAssets.AssetName,
RelationStartDate Desc
Explore the full platform, free for 14 days.
No credit card required.