Report Request: Find computers missing a certain piece of software

[sbrady]

Hello,

I'm trying to generate a report of all Windows 7 and Windows 10 client machines missing a software package named either 'DeviceLock', 'DeviceLock Service' or 'DeviceLock Service x64'.

Ideally, I want to only include machines with either Dell Inc. or Intel as the manufacturer.

So far I've created the below, however it is missing certain computers which have the software missing:

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried,
tsysOS.OSname,
tblOperatingsystem.Caption
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Where (tblAssets.AssetID Not In (Select Top 1000000 tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%DeviceLock Service%') And
tsysOS.OSname = 'Win 7' And tblAssetCustom.State = 1) Or
(tblAssets.AssetID Not In (Select Top 1000000 tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%DeviceLock Service%') And
tsysOS.OSname = 'Win 10' And tblAssetCustom.State = 1)
Order By tblAssets.Domain,
tblAssets.AssetName

[Nick_VDB]

We are happy to hear that you have found a solution to your issue.

New Windows builds get added to the installer for the next Lansweeper version this can indeed cause computers to not appear when making use of the tsysOs table a this will try and find the correct Windows OS. In cases where the OS is a new build and has not been added in a Lansweeper build then it will not appear in the report.

[sbrady · Accepted solution]

Hi Nick,

Thanks for the help!

I managed to work out what the problem was, we were using version 6.0.0.22.
After updating the 6.0.0.42 the missing computers began to appear in the reports I was running.

It looks like the missing devices were PCs running the Windows 10 Anniversary Update, therefore the below bug-fix must have sorted this:
Added: #483035 Latest Windows 10 build version

Thanks again!

Sam

[Nick_VDB]

Did a very small modification to the report. Check if you see all the correct computers this time.

Select Top 1000000 tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  tblAssets.IPAddress,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tsysOS.OSname,
  tblOperatingsystem.Caption
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join tblOperatingsystem
    On tblAssets.AssetID = tblOperatingsystem.AssetID
Where
  (tblAssets.AssetID Like
  'Select Top 1000000 tblAssets.AssetID As AssetID1 From tblSoftware   Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID   Inner Join tblAssets On tblAssets.AssetID = tblSoftware.AssetID Where tblSoftwareUni.softwareName Like ''%DeviceLock%''' And tsysOS.OSname = 'Win 7' And tblAssetCustom.State = 1) Or
  (tblAssets.AssetID Like
  'Select Top 1000000 tblAssets.AssetID As AssetID1 From tblSoftware   Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID   Inner Join tblAssets On tblAssets.AssetID = tblSoftware.AssetID Where tblSoftwareUni.softwareName Like ''%DeviceLock%''' And tsysOS.OSname = 'Win 10' And tblAssetCustom.State = 1)
Order By tblAssets.Domain,
  tblAssets.AssetName