Select Top 1000000 Coalesce(tblADGroups.Name, tblADObjects.sAMAccountName,
'unknown') hyperlink_name_Group,
'https://your_server_FQDN/report.aspx?det=web50usersbyadgroup&title=Active Directory group: '
+ Coalesce(tblADGroups.Name, tblADObjects.sAMAccountName, 'unknown') +
'&@id=' + Cast(tblADGroups.ADObjectID As nvarchar) As hyperlink_Group,
tblADGroups.Description,
tblADGroups.LastChanged,
Count(tblADMembership.ChildAdObjectID) counter,
tblADObjects.domain,
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 tblADGroups
Inner Join tblADObjects On tblADObjects.ADObjectID = tblADGroups.ADObjectID
Inner Join tblADMembership On tblADObjects.ADObjectID =
tblADMembership.ParentAdObjectID
Group By tblADGroups.Description,
tblADGroups.LastChanged,
tblADObjects.domain,
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,
tblADObjects.sAMAccountName,
tblADGroups.Name,
tblADGroups.ADObjectID
Order By hyperlink_name_Group