‎06-28-2024 05:38 PM
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.
Solved! Go to Solution.
‎07-01-2024 07:03 PM - edited ‎07-01-2024 07:04 PM
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
‎07-01-2024 05:31 PM
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.
‎07-01-2024 07:03 PM - edited ‎07-01-2024 07:04 PM
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
‎07-02-2024 12:35 PM
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.
‎07-02-2024 05:42 PM
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.
‎06-28-2024 06:49 PM
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.
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now