Software Missing for Group of Users

[DaveClark5]

There are various reports that get me very close to a solution but I can't make it work. Grateful for any assistance...

We add users to a whitelist group - "VPN Group" - that allows them to log onto VPN.

I'd like to run a report to show computers that are used by the user group "VPN Group" that are missing the VPN software.

Against this report, I am going to automatically deploy the VPN software.

Ultimately, adding a user to the "VPN Group" will give them permissions to use the software and then Lapsweeper will install the software on any computers used by those users and are missing the software.

[DaveClark5]

IMO having software and icons installed on computers which don't work is confusing for users. I'd rather users weren't aware of it's existence so we don't get users asking for it to work when they don't really need it (or we don't want them to have it). I do agree it takes more managing this way, but our VPN licensing per-user so I don't want any unnecessary Helpdesk requests asking for access. Hope that makes sense. 

[Jacob_H · Accepted solution]

Sounds good to me -  here's something that should work (i'm not official support) just change the software and the AD group:

Select 
  Top 1000000 tsysOS.Image As icon, 
  tblAssets.AssetID, 
  vpn_users.Displayname, 
  tblAssets.AssetName, 
  tblAssets.Domain, 
  tblAssets.Username, 
  tblAssets.Userdomain, 
  tblAssets.IPAddress, 
  tblAssets.Firstseen, 
  tblAssets.Lastseen, 
  tblAssets.Lasttried 
From 
  tblAssets 
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID 
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode 
  Right Join (
    Select 
      Top 1000000 tblADusers.Username, 
      tblADusers.Userdomain, 
      tblADusers.Firstname, 
      tblADusers.Lastname, 
      tblADusers.Name, 
      tblADusers.Displayname, 
      tblADusers.email, 
      tblADGroups.Name As ADGroupName 
    From 
      tblADusers 
      Left Join tblADMembership On tblADMembership.ChildAdObjectID = tblADusers.ADObjectID 
      Left Join tblADGroups On tblADMembership.ParentAdObjectID = tblADGroups.ADObjectID 
    Where 
      tblADGroups.name = 'Domain Users' 
    Order By 
      tblADusers.Userdomain, 
      tblADusers.Username, 
      ADGroupName
  ) as vpn_users on tblassets.username = vpn_users.Username 
  and tblassets.Userdomain = vpn_users.Userdomain 
Where 
  tblAssets.AssetID not In (
    Select 
      Top 1000000 tblSoftware.AssetID 
    From 
      tblSoftware 
      Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID 
    Where 
      tblSoftwareUni.softwareName Like '%notepad++%'
  ) 
  And tblAssetCustom.State = 1

[DaveClark5]

That's great thank you.

Just tweaked it with a Last Seen date within the last 7 days and an onsite IP address, but that's working as I described.

Many thanks for your help.

[Jacob_H]

sweet! glad it worked out and good catch on the last seen -   here's what I do when doing something like this -  I put last seen < 10 minutes and then run the deployment every 15 minutes or so, with the package telling it to rescan after success... that way I make sure I catch them while they are on the corporate or vpn network before they go off somewhere.

[Jacob_H]

I am on lunch break but - is there a reason why you wouldn't just deploy the VPN software to any laptop (for example) and just control the use through the whitelist itself?  (i.e. I have the VPN software, but I'm not part of the group, so when I go to sign on, I get access denied when logging on to the vpn server).  That way, you wouldn't also have to develop a plan to uninstall or take action when a user is removed from the whitelist group, or have to deal with users who are part of the group that log in to multiple machines which may or may not qualify for needing the VPN software.