→ The Lansweeper Customer Excellence Awards 2024 - Submit Your Project Now! Learn More & Enter Here
‎05-07-2019 06:57 PM
Select Top 1000000 tblAssets.AssetName,
tblAssets.AssetID,
tblAssets.IPAddress,
tblAssets.Username,
tblADusers.Firstname,
tblADusers.Lastname,
tblAssets.Lasttried,
tblAssets.Lastseen,
Case
When Exists(Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftware.AssetID = tblAssets.AssetID And
tblSoftwareUni.softwareName Like
'%DameWare Mini Remote Proxy Service%') Then 'YES'
Else 'NO'
End As DameWare,
tsysOS.OSname
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblADusers On tblADusers.Username = tblAssets.Username
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode,
tblAssetGroups
Where tblAssetGroups.AssetGroup = '!All Workstations'
‎05-10-2019 07:34 PM
‎05-09-2019 10:47 PM
Select Top 1000000
tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
Case
When Exists(Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftware.AssetID = tblAssets.AssetID And
tblSoftwareUni.softwareName Like
'%DameWare Mini Remote Proxy Service%') Then 'YES'
Else 'NO'
End As DameWare,
tsysOS.OSname,
tblAssetGroups.AssetGroup
From
tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID = tblAssetGroupLink.AssetGroupID
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where
tblAssetCustom.State = 1
And tblAssets.Assettype = -1
‎05-10-2019 01:51 AM
RC62N wrote:
Try this:Select Top 1000000
tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
Case
When Exists(Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftware.AssetID = tblAssets.AssetID And
tblSoftwareUni.softwareName Like
'%DameWare Mini Remote Proxy Service%') Then 'YES'
Else 'NO'
End As DameWare,
tsysOS.OSname,
tblAssetGroups.AssetGroup
From
tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID = tblAssetGroupLink.AssetGroupID
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where
tblAssetCustom.State = 1
And tblAssets.Assettype = -1
The LEFT JOIN tsysOS causes all the assets to be listed, so the WHERE tblAssetsAssetType = -1 limits the list to just Windows assets.
That query should list all your Windows assets and all the groups to which they belong. If the list looks right, add your original AND tblAssetGroups.AssetGroup = '!All Workstations' to the WHERE clause and see if it produces what you expected.
‎05-09-2019 08:52 PM
‎05-09-2019 10:05 PM
RC62N wrote:
First, you haven't related the tblAssetGroups to tblAssets in your list of selected tables.
Second, do you definitely have an asset group called "!All Workstations"?
If you do have an asset group "!All Workstations" defined, start by defining how you want tblAssetGroups linked to tblAssets. If you check the database documentation, you'll see that goes
- tblAssets -> tblAssetGroupLink -> tblAssetGroups
If you don't have that group defined, LANSweeper notes the domain role of computers. You might consider trying:
- remove tblAssetGroups from the list of tables
- add tblComputerSystem instead (joined on AssetID)
- create a filter WHERE tblComputerSystem.DomainRole < 2
(If you check the contents of tblComputerSystem, you'll see that 0 is standalone workstation, 1 is member workstation and 2-6 are various categories of server.)
If you want to see domain role descriptions, add a join to tblDomainRoles and add tblDomainRoles.DomainRoleName to your report.
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now