12-19-2023
04:00 PM
- last edited on
03-31-2024
03:44 PM
by
Mercedes_O
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
Solved! Go to Solution.
12-21-2023 02:43 PM
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.
12-20-2023 03:59 PM
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
12-21-2023 09:33 AM
Thanks Kevin, but i have a error:
12-21-2023 02:43 PM
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.
12-28-2023 11:45 AM
Hey @balebaje thanks for marking this question as solved
12-22-2023 03:26 PM
Apologies, was out sick yesterday, but happy you figured it out!
12-28-2023 11:44 AM
Sorry to hear that @KevinA-REJIS hope you're much better now !
12-28-2023 06:05 PM
Thanks @Mercedes_O! Luckily it was only a couple days, feeling much better now.
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now