Report of the clients with the last execution of a deplyoment, or where it has never been executed b

[Internal-IT]

Hi,

I have a report which shows me all clients where a certain deployment is older than X days. Now I want to add to the same report the clients, where the deployments was never executed.

Is this possible and if not, is it possible to creats this in a seperated report?

Thank you all.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  web50repwindeploymentlogs.Package,
  Max(web50repwindeploymentlogs.LogDate) As LastInstall,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join web50repwindeploymentlogs On tblAssets.AssetID =
    web50repwindeploymentlogs.AssetID
Where web50repwindeploymentlogs.Package Like 'DriverUpdate - Test' And
  tblAssetCustom.Manufacturer Like 'Hewlett-Packard' And tblAssetCustom.State =
  1
Group By tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  web50repwindeploymentlogs.Package,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model
Having Max(web50repwindeploymentlogs.LogDate) < GetDate() - 0

[Andy_Sismey]

Hi ,

Is this what your after, this should display a yes or no if the deployment is logged and colour coded, its setup for Chrome :

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  Case
    When soft01.AssetID Is Null Then '#AEEEEE'
    Else '#BCED91'
  End As backgroundcolor,
  Case
    When soft01.AssetID Is Null Then 'NO'
    Else 'YES'
  End As [Chrome Deployment],
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  Max(web50repwindeploymentlogs.LogDate) As LastInstall,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model
From tblAssets
  Left Join (Select tblAssets.AssetID
      From tblAssets
        Inner Join web50repwindeploymentlogs On tblAssets.AssetID =
          web50repwindeploymentlogs.AssetID
      Where web50repwindeploymentlogs.Package Like '%Chrome%') As soft01 On
    soft01.AssetID = tblAssets.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Full Join web50repwindeploymentlogs On tblAssets.AssetID =
    web50repwindeploymentlogs.AssetID
Where tblAssetCustom.Manufacturer Like 'Hewlett-Packard' And
  tblAssetCustom.State = 1
Group By tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  soft01.AssetID
Having Max(web50repwindeploymentlogs.LogDate) < GetDate() - 0