Report Registration Key

[balebaje]

Good afternoon, I am making a report on devices that do not have a registration key. I find that I get duplicates, that is, I get those that do not have that registration key but several of the same device.

Can you help me to get only the name of the device?

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.SP,
tblAssetCustom.Serialnumber,
tblState.Statename,
tblRegistry.Regkey
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblDomainroles On tblDomainroles.Domainrole =
tblComputersystem.Domainrole
Inner Join tblRegistry On tblAssets.AssetID = tblRegistry.AssetID
Where tblAssets.Domain In ('BME', 'FBME') And tblRegistry.Regkey Not Like
'%HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\SMS\Mobile Client%' And
tblDomainroles.Domainrolename = 'Member Workstation'
Order By tblAssets.Domain,
tblAssets.AssetName

Thanks

[KevinA-REJIS]

Give this report a try:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.SP,
tblAssetCustom.Serialnumber,
tblState.Statename,
tblRegistry.Regkey
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblDomainroles On tblDomainroles.Domainrole =
tblComputersystem.Domainrole
Inner Join tblRegistry On tblAssets.AssetID = tblRegistry.AssetID
Where tblAssets.Domain In ('BME', 'FBME') And tblAssets.AssetID Not In
(Select Top 1000000 tblAssets.AssetID,
tblRegistry.Regkey
From tblAssets
Where tblRegistry.Regkey Like '%HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\SMS\Mobile Client%'
Inner Join tblRegistry On tblAssets.AssetID = tblRegistry.AssetID)
And tblDomainroles.Domainrolename = 'Member Workstation'
Order By tblAssets.Domain,
tblAssets.AssetName

[balebaje]

Thanks Kevin, but i have a error:

[balebaje · Accepted solution]

I have already succeeded with the help of a colleague.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.SP,
tblAssetCustom.Serialnumber,
tblState.Statename
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblDomainroles On tblDomainroles.Domainrole =
tblComputersystem.Domainrole
Where tblAssets.AssetID Not In (Select tblRegistry.AssetID From tblRegistry
Where
tblRegistry.Regkey Like
'%HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\SMS\Mobile Client%') And
tblAssets.Domain In ('BME', 'FBME') And tblDomainroles.Domainrolename =
'Member Workstation'
Order By tblAssets.Domain,
tblAssets.AssetName

Thanks to all.

[Mercedes_O]

Hey @balebaje  thanks for marking this question as solved 

[KevinA-REJIS]

Apologies, was out sick yesterday, but happy you figured it out!

[Mercedes_O]

Sorry to hear that @KevinA-REJIS  hope you're much better now !

[KevinA-REJIS]

Thanks @Mercedes_O! Luckily it was only a couple days, feeling much better now.