From User Group to list of PCs

[HammettMike]

I've tried linking various tables together for something else for a report before (don't have it anymore) and I couldn't make it work.

I'd like to be able to take an AD group of users and deploy a package to their last-logged in PCs. I can deploy a package to a report, so I'm trying to figure out how to make a report that displays what I'm after.

[Daniel_B]

@HammettMike: Thanks for your feedback. The amount of data scanned by Lansweeper and the complexity of the database requires a bit of abstraction. The database dictionary shows links between tables and as described before, most tables will be automatically joined to your report if you doubleclick on them in the report editor.

[Susan_A]

FYI: there are sample AD group reports in the Report Center as well, here and here.

[Daniel_B · Accepted solution]

Normally the report builder automatically creates links between two tables if you doubleclick on the table you would like to add. Only in some cases, you have to manually link them by drag and drop: Pull the column of one table onto it's matching counterpart of the other table. For information on which data is stored in which table, refer to the Database Dictionary. In this case, you would add tblADusers to your report and link tblAssets.Username to tblADusers.username and tblAssets.Userdomain to tblADusers.Userdomain. Afterwards, add tblADMembership and connect tblADusers.ADobjectID to tblADMembership.ChildADObjectID. Now add tblADgroups to your report and connect tblADMembership.ParentADObjectID to tblADgroups.ADobjectID. Now select tblADgroups.name to be displayed in your report and add a filter criterium to it. You might filter on the Last seen date of the asset as well in order to deploy only to computers which have recently been scanned.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblADGroups.Name
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblADusers On tblADusers.Username = tblAssets.Username And
    tblADusers.Userdomain = tblAssets.Userdomain
  Inner Join tblADMembership On tblADusers.ADObjectID =
    tblADMembership.ChildAdObjectID
  Inner Join tblADGroups On tblADMembership.ParentAdObjectID =
    tblADGroups.ADObjectID
Where tblADGroups.Name Like '%your group%' And tblAssets.Lastseen = GetDate() -
  3 And tblAssetCustom.State = 1

[HammettMike]

Daniel.B wrote:
Normally the report builder automatically creates links between two tables if you doubleclick on the table you would like to add. Only in some cases, you have to manually link them by drag and drop: Pull the column of one table onto it's matching counterpart of the other table. For information on which data is stored in which table, refer to the Database Dictionary. In this case, you would add tblADusers to your report and link tblAssets.Username to tblADusers.username and tblAssets.Userdomain to tblADusers.Userdomain. Afterwards, add tblADMembership and connect tblADusers.ADobjectID to tblADMembership.ChildADObjectID. Now add tblADgroups to your report and connect tblADMembership.ParentADObjectID to tblADgroups.ADobjectID. Now select tblADgroups.name to be displayed in your report and add a filter criterium to it. You might filter on the Last seen date of the asset as well in order to deploy only to computers which have recently been scanned.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblADGroups.Name
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblADusers On tblADusers.Username = tblAssets.Username And
    tblADusers.Userdomain = tblAssets.Userdomain
  Inner Join tblADMembership On tblADusers.ADObjectID =
    tblADMembership.ChildAdObjectID
  Inner Join tblADGroups On tblADMembership.ParentAdObjectID =
    tblADGroups.ADObjectID
Where tblADGroups.Name Like '%your group%' And tblAssets.Lastseen = GetDate() -
  3 And tblAssetCustom.State = 1

I looked through that database description. It's no doubt accurate and complete, but less non-programmer friendly than I care for. It did help me on the way, but what you described above worked perfectly.