→ The Lansweeper Customer Excellence Awards 2024 - Submit Your Project Now! Learn More & Enter Here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
seand
Engaged Sweeper II
Currently using this report to find out what machines are missing Trend Micro. I have a few I would like to exclude, can anyone advise on how to accomplish this? Sorry not an expert in this area!

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Where tblAssets.AssetID Not In (Select Top 1000000 tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftwareUni.softwareName Like 'Trend%') And
tblAssetCustom.State = 1
Order By tblAssets.Domain,
tblAssets.AssetName
4 REPLIES 4
RCorbeil
Honored Sweeper II
You're reviving an old exchange where the solution presumably worked since there was no followup. If it's not working for you, consider sharing your code to see if anyone can spot a flaw in the selection logic.
RC62N wrote:
You're reviving an old exchange where the solution presumably worked since there was no followup. If it's not working for you, consider sharing your code to see if anyone can spot a flaw in the selection logic.


Got it to work. I had to have the And statement after the parenthesis

Where tblSoftwareUni.softwareName Like 'Trend%')
And tblAssets.AssetName Not Like '%PC12%' And tblAssets.AssetName Not Like
'%PC34%' And tblAssets.AssetName Not Like '%PC56%'


mpalmieri
Engaged Sweeper
This looked promising, but adding the AND statements didn't fix this in my environment. Any other suggestions?
Andy_Sismey
Champion Sweeper III
Hi

try this, so this will exclude any pcs with names containing PC12 , PC34 and PC56

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Where tblAssets.AssetID Not In (Select Top 1000000 tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftwareUni.softwareName Like 'Trend%' And
tblAssets.AssetName Not Like '%PC12%' And tblAssets.AssetName Not Like
'%PC34%' And tblAssets.AssetName Not Like '%PC56%') And
tblAssetCustom.State = 1
Order By tblAssets.Domain,
tblAssets.AssetName