Computer list and autorun

[jmichiels]

Hello,

In our company we encrypt all laptops with trend micro encryption software.
We want know how many device don't have trend micro encryption running, I tried to make query that show list with computers that don't have encryption software running. I can make list that software well have running but not without software running (it give many same computers)

That report is for show list computers with encryption software installed (work fine)

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblComputerSystemProduct.Version As Model,
tblAutorunUni.Caption
From tblAssets
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblComputerSystemProduct On tblAssets.AssetID =
tblComputerSystemProduct.AssetID
Inner Join tblAutorun On tblAssets.AssetID = tblAutorun.AssetID
Inner Join tblAutorunUni On tblAutorunUni.AutorunUNI = tblAutorun.AutorunUNI
Where (tblAutorunUni.Caption Like 'TMFDE' Or tblAutorunUni.Caption Like
'dataarmor') And tblAssetCustom.State = 1
Order By tblAssets.Domain,
tblAssets.AssetName


I tried to make same report but without encrpyption running but I receive all auto run software (not working)

Select Top 1000000 tblAssets.AssetID,
tsysOS.Image As icon,
tblAssets.AssetName,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblComputerSystemProduct.Version As Model,
tblAutorunUni.Caption
From tblAssets
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblComputerSystemProduct On tblAssets.AssetID =
tblComputerSystemProduct.AssetID
Inner Join tblAutorun On tblAssets.AssetID = tblAutorun.AssetID
Inner Join tblAutorunUni On tblAutorunUni.AutorunUNI = tblAutorun.AutorunUNI
Where tblAssets.AssetID Not In (Select tblAutorun.AssetID
From tblAutorun Inner Join tblAutorunUni On tblAutorunUni.AutorunUNI =
tblAutorun.AutorunUNI
Where tblAutorunUni.Caption Like 'TMFDE') And tblAssetCustom.State = 1
Order By tblAssets.AssetName


ps: I don't know also why that in some report computername are not clickable?

[Daniel_B · Accepted solution]

As one computer has many processes, you need to use two subqueries in order to get the result you are looking for:

Select Top 1000000 tblAssets.AssetID,
  tsysOS.Image As icon,
  tblAssets.AssetName,
  tblAssets.IPAddress,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblComputerSystemProduct.Version As Model
From tblAssets
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblComputerSystemProduct On tblAssets.AssetID =
    tblComputerSystemProduct.AssetID
Where tblAssets.AssetID Not In (Select tblAutorun.AssetID
  From tblAutorun Inner Join tblAutorunUni On tblAutorunUni.AutorunUNI =
      tblAutorun.AutorunUNI
  Where tblAutorunUni.Caption Like 'TMFDE') And tblAssets.AssetID Not In (Select
    tblAutorun.AssetID
  From tblAutorun Inner Join tblAutorunUni On tblAutorunUni.AutorunUNI =
      tblAutorun.AutorunUNI
  Where tblAutorunUni.Caption Like 'dataarmor') And tblAssetCustom.State = 1
Order By tblAssets.AssetName

[jmichiels]

Thanks and works fine but I forgot to add second running proces.
I need list with computers that don't have 2 auto-run proces like first report (dataarmor and TMFDE)
I tried to combine but no luck.



Select Top 1000000 tblAssets.AssetID,
tsysOS.Image As icon,
tblAssets.AssetName,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblComputerSystemProduct.Version As Model
From tblAssets
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblComputerSystemProduct On tblAssets.AssetID =
tblComputerSystemProduct.AssetID
Where tblAssets.AssetID Not In (Select tblAutorun.AssetID
From tblAutorun Inner Join tblAutorunUni On tblAutorunUni.AutorunUNI =
tblAutorun.AutorunUNI Where (tblAutorunUni.Caption Like 'TMFDE' Or tblAutorunUni.Caption Like 'dataarmor') And
tblComputerSystemProduct.Version Like 'Thinkp%' And tblAssetCustom.State = 1
Order By tblAssets.AssetName

[Daniel_B]

In your second report you included tblAutorun and tblAutorunUni. This will cause the report to list all autorun entries on each computer. If you would like to have each computer only once, remove these tables from your report. The subquery you used to filter out Assets which have the software configured still will do it's job.

Select Top 1000000 tblAssets.AssetID,
  tsysOS.Image As icon,
  tblAssets.AssetName,
  tblAssets.IPAddress,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblComputerSystemProduct.Version As Model
From tblAssets
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblComputerSystemProduct On tblAssets.AssetID =
    tblComputerSystemProduct.AssetID
Where tblAssets.AssetID Not In (Select tblAutorun.AssetID
  From tblAutorun Inner Join tblAutorunUni On tblAutorunUni.AutorunUNI =
      tblAutorun.AutorunUNI
  Where tblAutorunUni.Caption Like 'TMFDE') And tblAssetCustom.State = 1
Order By tblAssets.AssetName