Select Top 1000000 tblAssets.AssetName,
tblAssets.Domain,
tblAssets.AssetID,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.Lastseen,
tblAssets.Lasttried,
case when count.count is null
then 0
else count.count end as [Number of 10016]
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
left Join (select Eventcode, assetid, count(assetid) as Count from tblntlog where TimeGenerated > GetDate() - 7 and Eventcode = 10016 group by assetid,eventcode) Count on Count.assetid = tblassets.assetid
Where (tblAssets.AssetName in ('XXXXX01','XXXXX02','XXXXX03','XXXXX04'))
Order By AssetName,
tblAssets.Domain