cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Internal-IT
Engaged Sweeper II
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
1 REPLY 1
Andy_Sismey
Champion Sweeper III
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