Dashboard widget with All Active Directory Groups.

[dm3]

Greetings LS, 

I have been looking for an easy way to add a Dashboard widget with all the Active Directory Groups. The list would be clickable to give me real time report of all assets within that group.

I can achieve this through an individual asset under the  Active directory groups section. Would like a more accessible version for our domain through the dashboard.

Thank you in advance.

[dm3]

Count(tblADMembership.ChildAdObjectID) counter,

Is there a better way to count the Assets in Ad group as opposed to a child? The counter displays a number that doesn't match the actual asset count at the top of the report page. Not sure where the additional numbers are coming from as that is not listed in the report.

Thank you.

[Mister_Nobody]

It's not issue of data. 

It's restriction of report.aspx - doesn't show nested groups. 

[dm3]

Brilliant! The results for each group show a count, however on click, the report shows no data of assets. Any suggestions? Perfect that it opens in a new tab as well.

[Mister_Nobody]

There is a restricton in the  LS's report.aspx: no nested groups suport.

Click middle mouse button to open in a new tab.

[Mister_Nobody]

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