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

Lansweeper zerologin posted report
by  Antikas   Go to last post Go to first unread
Last post: Today at 9:42:54 AM(UTC)
Lansweeper Report doesn't show empty results for a field
by  AlexMZetec  
Go to last post Go to first unread
Last post: Yesterday at 3:43:08 PM(UTC)
Lansweeper Renamed Pcs / Laptops report
by  RC62N   Go to last post Go to first unread
Last post: Yesterday at 3:36:35 PM(UTC)
Lansweeper Servers without AV Report
by  Elwood472  
Go to last post Go to first unread
Last post: 9/27/2020 2:50:10 AM(UTC)
Lansweeper Adding Group by and Sum to Existing Report
by  RC62N  
Go to last post Go to first unread
Last post: 9/25/2020 3:43:49 PM(UTC)
Lansweeper Custom Fields on Report for Helpdesk Tickets
by  plangham_eurotech   Go to last post Go to first unread
Last post: 9/24/2020 2:43:41 PM(UTC)
Lansweeper September Patch Tuesday
by  Gilles B.  
Go to last post Go to first unread
Last post: 9/24/2020 7:47:49 AM(UTC)