Notification

Icon
Error

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

Posted: Wednesday, June 24, 2020 5:38:36 AM(UTC)
SPG IT

SPG IT

Member Original PosterPosts: 2
0
Like
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.

Code:
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.S
#1Andy.S Member Posts: 27  
posted: 7/2/2020 12:33:32 PM(UTC)
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 :

Code:
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

Active Discussions

Installer Windows 10 Upgrade
by  CyberCitizen  
Go to last post Go to first unread
Last post: 7/8/2020 1:07:47 AM(UTC)
Installer Installer - Microsoft Office
by  Florian_Eigsi   Go to last post Go to first unread
Last post: 6/29/2020 3:33:20 PM(UTC)
Installer Firefox ESR 68.8.0 MSI Installer
by  PLSJohnJohn  
Go to last post Go to first unread
Last post: 5/11/2020 4:33:50 PM(UTC)
Installer Update Chrome browser with GoogleUpdate.exe ?
by  Inna Ptushkina   Go to last post Go to first unread
Last post: 5/4/2020 8:39:29 PM(UTC)
Installer Bios Update for Dell all in one
by  Florian_Eigsi  
Go to last post Go to first unread
Last post: 4/2/2020 11:36:43 AM(UTC)
Installer Uninstall - Adobe Acrobat 9x
by  palemmo   Go to last post Go to first unread
Last post: 3/25/2020 7:25:05 PM(UTC)
Installer OneDrive Silent Install
by  Steven.C  
Go to last post Go to first unread
Last post: 3/13/2020 2:55:19 PM(UTC)