Computers used by users in AD Group

[Argon0]

Hi,

I have a specific problem, I need to install Office 365 to the Executive Team at my location.

They are all members of a specific AD group (EXECS) and it looks like LanSweeper is able to identify the machines last used by these users (if I search individually), but I need to easily identify and then deploy, all machines used by this group of users.

I COULD use another tool (ManageEngine AD Audit Plus for instance) to get a csv list of machines used by this group, and then import it into LanSweeper as a static group???

BUT it would be much a much superior solution to have a dynamic group for these users....

Is this possible?

If so how does one do this?

Thanks

Argon0

[ylandrum]

Using Argon0's query I made a few minor adjustments and it now produces a dynamic list of machines whose users are members of a specified group. The key is to replace:

('COMMA', 'AND', 'QUOTE', 'SEPARATED', 'LIST', 'OF_USERS')

with:

(Select tblADusers.Username

From tblADusers Left Join tblADMembership On tblADMembership.ChildAdObjectID =

tblADusers.ADObjectID Left Join tblADGroups
On tblADMembership.ParentAdObjectID = tblADGroups.ADObjectID

Where tblADGroups.Name = 'Test_Group')

Here then is "Computers: Test Group Virtual Machines" which produces a list of machines of the user members of "Test_Group" in order to run deployment packages against them. It is currently set to only show virtual desktops, to exclude machines in the OU "legacy," and to list only machines seen in the last 30 days:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename As AssetType,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.Domain,
  tblAssetCustom.Model,
  tblAssetCustom.Manufacturer,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblAssets.Mac As MACAddress,
  tblADComputers.OU,
  tblstate.Statename As State,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblAssets.Description,
  tblAssetCustom.PurchaseDate,
  tblAssetCustom.Warrantydate,
  tblAssets.FQDN,
  tblAssetCustom.DNSName,
  tblAssetCustom.LastPatched,
  tblAssetCustom.LastFullbackup,
  tblAssetCustom.LastFullimage,
  tblAssetCustom.Location,
  tblAssetCustom.Building,
  tblAssetCustom.Department,
  tblAssetCustom.Branchoffice,
  tblAssetCustom.BarCode,
  tblAssetCustom.Contact,
  tblAssetCustom.Serialnumber,
  tblAssetCustom.OrderNumber,
  Case tblAssetCustom.PreventCleanup When 0 Then 'No' When 1 Then 'Yes'
  End As PreventCleanup,
  tblAssets.Scanserver,
  tblADComputers.Description As AD_Description,
  tblAssets.Username
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tsysIPLocations On tsysIPLocations.LocationID =
    tblAssets.LocationID
  Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
  Inner Join tblstate On tblstate.State = tblAssetCustom.State
  Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblAssetCustom.Manufacturer Like '%vmware%' And
  tblADComputers.OU Not Like '%legacy%' And tblAssets.Lastseen > GetDate() - 30
  And tblAssets.Username In (Select Top 1000000 tblADusers.Username
  From tblADusers Left Join tblADMembership On tblADMembership.ChildAdObjectID =
      tblADusers.ADObjectID Left Join tblADGroups
      On tblADMembership.ParentAdObjectID = tblADGroups.ADObjectID
  Where tblADGroups.Name = 'Test_Group')
Order By tblAssets.AssetName

[Argon0]

Well I used almost exactly this method to create a report, and to target users machines, it does need an edit of the report for each set of users you want to deploy to, but it does seem to work see below for entire report definition (it uses the "Assets: All columns from the assets menu", as its base, with Usernames from the tblAssets.Username table added in (as well as the AD description field, which, in my case, contains some helpful descriptions of the PCs as being owned by an Exec...).

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename As AssetType,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.Domain,
Coalesce(tsysOS.OSname, tblLinuxSystem.OSRelease, tblMacOSInfo.SystemVersion)
As OS,
tblAssetCustom.Model,
tblAssetCustom.Manufacturer,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssets.Mac As MACAddress,
tblADComputers.OU,
tblState.Statename As State,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblAssets.Description,
tblAssetCustom.PurchaseDate,
tblAssetCustom.Warrantydate,
tblAssets.FQDN,
tblAssetCustom.DNSName,
tblAssetCustom.LastPatched,
tblAssetCustom.LastFullbackup,
tblAssetCustom.LastFullimage,
tblAssetCustom.Location,
tblAssetCustom.Building,
tblAssetCustom.Department,
tblAssetCustom.Branchoffice,
tblAssetCustom.BarCode,
tblAssetCustom.Contact,
tblAssetCustom.Serialnumber,
tblAssetCustom.OrderNumber,
Case tblAssetCustom.PreventCleanup When 0 Then 'No' When 1 Then 'Yes'
End As PreventCleanup,
tblAssets.Scanserver,
tblADComputers.Description As AD_Description,
tblAssets.Username
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join tblMacOSInfo On tblAssets.AssetID = tblMacOSInfo.AssetID
Where tblAssets.Username In ('COMMA', 'AND', 'QUOTE', 'SEPARATED', 'LIST',
'OF_USERS')
Order By tblAssets.AssetName

[Bruce_B]

Unfortunately there's no dynamic group filter available to filter assets based on their user. Since you can deploy based on a report though you could work around this by creating a report that lists the assets of your executive team. If you're sure they're all listed as the users of the computers, it could be a pretty straightforward report with a where clause such as the one below.

Where tblassets.username in('username1','username2','username3')