Failed Package Deployment Count

[apap]

We use sql reports to schedule package deployments.

What I would like to add to these reports is the ability to count the number of failed deployment attempts ( possibly by errorcode ) that a particular package has had on an asset. This would be viewed in a report or configured as an exception to prevent a package schedule from including an asset after X number of attempts.

I have read and attempted to write this query but it is a bit beyond my current sql level.

I think that I would need to hardcode the PackageID and pull a count from tsysPackageLogs after an assetID join.

Below is a typical schedule report I run.

Thanks for any assistance on this.

Andrew

Select Top 1000000 tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tblAssets.Username,
tblAssets.Lastseen,
tblAssets.Lasttried
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Inner Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Where tblSoftwareUni.softwareName Like '%Reader%' And
tblSoftware.softwareVersion = '9.3.0' And tblAssetCustom.State = 1

[apap]

Thank you very much Daniel. This will be a huge help not only to our deployments but in my sql knowledge. Not that I could even start to recreate this but very helpful to read through.

Appreciate the tip on tblAssets.AssetID, I often remove that as I work just to simplify and clean things up.

Thanks again
Andrew

[Daniel_B · Accepted solution]

That is a bit complex indeed, but you can achieve your goal by using a subquery which counts failed deployments of a specific package. Package logs are stored in table tsysPackageLogs. In order to filter on the name of the package, you need to join tblPackages as well.
Please find an example report below which lists assets having that software installed and deployments of package "YourExamplePackage" failed more than 3 times.

Select Top 1000000 tblAssets.AssetName,
  tblAssets.AssetID,
  tsysAssetTypes.AssetTypename,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblSoftwareUni.softwareName,
  tblSoftware.softwareVersion,
  tblAssets.Username,
  tblAssets.Lastseen,
  tblAssets.Lasttried
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
  Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
  Inner Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
    And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Where tblSoftware.AssetID Not In (Select tUnsuccessfulCount.AssetID
  From (Select tsysPackageLogs.AssetID,
      Count(tsysPackageLogs.PackageLogID) As [number unsuccessful]
    From tsysPackageLogs
      Inner Join tsysPackages On tsysPackageLogs.PackageID =
        tsysPackages.PackageID
    Where tsysPackageLogs.Success = 0 And tsysPackages.PackageName Like
      'YourExamplePackage'
    Group By tsysPackageLogs.AssetID
    Having Count(tsysPackageLogs.PackageLogID) > 3) tUnsuccessfulCount) And
  tblSoftwareUni.softwareName Like '%Reader%' And tblSoftware.softwareVersion =
  '9.3.0' And tblAssetCustom.State = 1

Note: In order to use a report as target for deployments, it needs to contain column tblAssets.AssetID.