→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
HammettMike
Engaged Sweeper III
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.
1 ACCEPTED SOLUTION
Daniel_B
Lansweeper Alumni
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

View solution in original post

4 REPLIES 4
Daniel_B
Lansweeper Alumni
@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
Lansweeper Alumni
FYI: there are sample AD group reports in the Report Center as well, here and here.
Daniel_B
Lansweeper Alumni
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
Engaged Sweeper III
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.