‎07-21-2021 08:15 PM
Solved! Go to Solution.
‎07-22-2021 04:58 PM
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,
tblAssets.Lastseen,
tblAssets.Lasttried,
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) / 3600 / 24))) + ' days ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) / 3600 % 24))) + ' hours ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) % 3600 / 60))) + ' minutes' As UptimeSinceLastReboot,
tblADGroups.Name As ADGroupName,
tblADGroups.Description As ADGroupDescription,
Case tblADGroups.GroupType
When -2147483646 Then 'Security - Global'
When -2147483644 Then 'Security - Local'
When -2147483643 Then 'Built-in'
When -2147483640 Then 'Security - Universal'
When 2 Then 'Distribution - Global'
When 4 Then 'Distribution - Local'
When 8 Then 'Distribution - Universal'
End As ADGroupType
From
tblAssets
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.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 tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Left Join tblADMembership On tblADMembership.ChildAdObjectID = tblADComputers.ADObjectID
Left Join tblADGroups On tblADMembership.ParentAdObjectID = tblADGroups.ADObjectID
Where
tblState.Statename = 'Active'
And tblComputerSystem.DomainRole > 1
Order By
tblAssets.Domain,
tblAssets.AssetName
Wherefor all active servers in the "Security - Global" AD group.
tblState.Statename = 'Active'
And tblComputerSystem.DomainRole > 1
And tblADGroups.GroupType = -2147483646
‎07-22-2021 08:20 PM
‎07-22-2021 06:07 PM
Just like to learn like the others here.
‎07-22-2021 05:50 PM
This seems to only work on the Static Groups, not the Dynamic Groups.
Using Dynamic Asset groups for reports is not possible. In fact, through custom reports you can do everything what you can do with dynamic asset groups (plus much more).
‎07-22-2021 05:54 PM
RC62N wrote:This seems to only work on the Static Groups, not the Dynamic Groups.
I'm pretty sure it was determined a number of years ago that this was the case. Your testing confirms that that's unchanged.
Searching... Here we go.
Posted by Daniel.B on 2015-06-25:Using Dynamic Asset groups for reports is not possible. In fact, through custom reports you can do everything what you can do with dynamic asset groups (plus much more).
‎07-22-2021 04:58 PM
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,
tblAssets.Lastseen,
tblAssets.Lasttried,
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) / 3600 / 24))) + ' days ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) / 3600 % 24))) + ' hours ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) % 3600 / 60))) + ' minutes' As UptimeSinceLastReboot,
tblADGroups.Name As ADGroupName,
tblADGroups.Description As ADGroupDescription,
Case tblADGroups.GroupType
When -2147483646 Then 'Security - Global'
When -2147483644 Then 'Security - Local'
When -2147483643 Then 'Built-in'
When -2147483640 Then 'Security - Universal'
When 2 Then 'Distribution - Global'
When 4 Then 'Distribution - Local'
When 8 Then 'Distribution - Universal'
End As ADGroupType
From
tblAssets
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.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 tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Left Join tblADMembership On tblADMembership.ChildAdObjectID = tblADComputers.ADObjectID
Left Join tblADGroups On tblADMembership.ParentAdObjectID = tblADGroups.ADObjectID
Where
tblState.Statename = 'Active'
And tblComputerSystem.DomainRole > 1
Order By
tblAssets.Domain,
tblAssets.AssetName
Wherefor all active servers in the "Security - Global" AD group.
tblState.Statename = 'Active'
And tblComputerSystem.DomainRole > 1
And tblADGroups.GroupType = -2147483646
‎07-22-2021 04:55 AM
"I'm going to assume that when you say you want computers that are members of groups, you mean asset groups "
‎07-21-2021 10:10 PM
Wherebecomes
Case tblComputersystem.PartOfDomain
When 0 Then 'No'
When 1 Then 'Yes'
End = 'Yes'
And tblState.Statename = 'Active'
Where
Case tblComputersystem.PartOfDomain
When 0 Then 'No'
When 1 Then 'Yes'
End = 'Yes'
And tblState.Statename = 'Active'
And tblComputerSystem.DomainRole > 1
Select Top 1000000
...
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) / 3600 / 24))) + ' days ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) / 3600 % 24))) + ' hours ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) % 3600 / 60))) + ' minutes' As UptimeSinceLastReboot
From
...
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,
tblAssets.Lastseen,
tblAssets.Lasttried,
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) / 3600 / 24))) + ' days ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) / 3600 % 24))) + ' hours ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) % 3600 / 60))) + ' minutes' As UptimeSinceLastReboot
From
tblAssets
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.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
Where
tblState.Statename = 'Active'
And tblComputerSystem.DomainRole > 1
Order By
tblAssets.Domain,
tblAssets.AssetName
tblAssets --- tblAssetGroupLink --- tblAssetGroups
Where
tblState.Statename = 'Active'
And tblComputerSystem.DomainRole > 1
And tblAssets.AssetID IN ( Select
tblAssetGroupLink.AssetID
From
tblAssetGroupLink
Inner Join tblAssetGroups ON tblAssetGroups.AssetGroupID = tblAssetGroupLink.AssetGroupID
Where
tblAssetGroups.AssetGroup IN ('Group 1', 'Group 2', 'Group 3')
)
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,
tblAssets.Lastseen,
tblAssets.Lasttried,
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) / 3600 / 24))) + ' days ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) / 3600 % 24))) + ' hours ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) % 3600 / 60))) + ' minutes' As UptimeSinceLastReboot,
-- combine all the asset groups the asset belongs to into a single output column
Stuff( ( Select ', ' + Cast(tblAssetGroups.AssetGroup As VarChar(30)) -- arbitrarily truncate to 30 chars; source field is defined as VarChar(150)
From tblAssetGroupLink
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID = tblAssetGroupLink.AssetGroupID
Where tblAssets.AssetID = tblAssetGroupLink.AssetID
For Xml Path('')), 1, 2, '') As [Asset Groups]
From
tblAssets
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.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
Where
tblState.Statename = 'Active'
And tblComputerSystem.DomainRole > 1
And tblAssets.AssetID IN ( Select
tblAssetGroupLink.AssetID
From
tblAssetGroupLink
Inner Join tblAssetGroups ON tblAssetGroups.AssetGroupID = tblAssetGroupLink.AssetGroupID
Where
tblAssetGroups.AssetGroup IN ('Group 1', 'Group 2', 'Group 3')
)
Order By
tblAssets.Domain,
tblAssets.AssetName
‎07-22-2021 05:32 PM
RC62N wrote:
I'm going to assume that when you say you want computers that are members of groups, you mean asset groups (Configuration > Asset Groups)
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now