→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
jmichiels
Engaged Sweeper III
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?
1 ACCEPTED SOLUTION
Daniel_B
Lansweeper Alumni
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

View solution in original post

3 REPLIES 3
Daniel_B
Lansweeper Alumni
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
Engaged Sweeper III
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
Lansweeper Alumni
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