I have resolved.
Select Distinct Top 1000000 a.AssetID,
a.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
a.IPAddress,
a.Lastseen,
a.Lasttried
From tblAssets a
Inner Join tblAssetCustom On a.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = a.Assettype
Inner Join tblServices On a.AssetID = tblServices.AssetID
Inner Join tblServicesUni On tblServicesUni.ServiceuniqueID =
tblServices.ServiceuniqueID
Where tblServicesUni.Caption Not Like '%LMservice%' And tblAssetCustom.State = 1
And Not Exists(Select Null
From tblAssets b Inner Join tblAssetCustom c On b.AssetID = c.AssetID
Inner Join tsysAssetTypes d On d.AssetType = b.Assettype Inner Join
tblServices e On b.AssetID = e.AssetID Inner Join tblServicesUni f On
f.ServiceuniqueID = e.ServiceuniqueID
Where f.Caption Like '%LMservice%' And c.State = 1 And b.AssetID =
a.AssetID)
Order By a.AssetName