SLA Dashboard All Request showing Pending Deployement.

AjaykumarPatil · ‎May 26, 2021

While checking the SLA dashboard all requests showing pending deployment but in My Request most of the requests showing as succeeded.

CharlesW · ‎May 26, 2021

I can not boil down the dashboard to a single query, as there is quite a bit of logic in the SLA Dashboard page, but I can summarize where the SLA dashboard numbers are coming from with a few queries. The first query gives the total number of requests being processed for deployment progress... The Second query gives the number of successful requests, and the third gives the failed requests. The number of pending requests are calculated based on (total - (success + failure)

--Total requests query (note that the date range changes):
Select Count(*) FROM ( select distinct pr.* from WD_PackageRequests pr join WD_WebPackages wp on pr.PackageID_FK=wp.PackageID join WD_Package_Deployment pd on wp.PackageID=pd.PackageID_FK AND pr.DeploymentTechnology=pd.DeploymentTechnology where pr.inserted=1 and pr.deleted<>1 and pd.PackageType not in (1,3) and PR.DateTime between '04/28/2016 00:00:00:00' and '10/09/2018 23:59:59:999' ) softwareReq

--successful deployments (note that the date range changes)
select count(distinct PR.requestID) from WD_PackageRequests pr join WD_WebPackages wp on pr.PackageID_FK=wp.PackageID join WD_Package_Deployment pd on wp.PackageID=pd.PackageID_FK AND pr.DeploymentTechnology=pd.DeploymentTechnology where pr.inserted=1 and pr.deleted<>1 and ( (pr.LastStateID=1001 AND pd.PackageType=25) OR (pr.LastStateID=1001 AND pd.PackageType=18) OR (pr.LastStateID=1 AND pd.PackageType=15) OR (pr.LastStateID=1001 AND pd.PackageType in (19,20,21,22,23)) OR (pr.LastStateID in (13,15,17,18,19) and pd.PackageType in (0) and pd.DeploymentTechnology=2) OR (pr.LastStateID in (18,19,17,13,15) and pd.PackageType in (0) and pd.DeploymentTechnology=1) OR (pr.LastStateID in (18,19,17,13,106,100,15) and pd.PackageType in (0) and pd.DeploymentTechnology=8) OR(pr.LastStateID=13 and LastStatusID=11171 and pd.PackageType=4) ) and PR.DateTime between '04/28/2016 00:00:00:00' and '10/09/2018 23:59:59:999'

--failed deployments ((note that the date range changes)
select count(distinct PR.requestID) from WD_PackageRequests pr join WD_WebPackages wp on pr.PackageID_FK=wp.PackageID join WD_Package_Deployment pd on wp.PackageID=pd.PackageID_FK AND pr.DeploymentTechnology=pd.DeploymentTechnology where pr.inserted=1 and pr.deleted<>1 and ( (pr.LastStateID=1002 AND pd.PackageType=25) OR (pr.LastStateID=1002 AND pd.PackageType=18) OR (pr.LastStateID=5 AND pd.PackageType=15) OR (pr.LastStateID=1002 AND pd.PackageType in (19,20,21,22,23)) OR (pr.LastStateID in (101,11,230) and pd.PackageType in (0) and pd.DeploymentTechnology=2) OR (pr.LastStateID in (101,11) and pd.PackageType in (0) and pd.DeploymentTechnology=1) OR (pr.LastStateID in (101,11,230) and pd.PackageType in (0) and pd.DeploymentTechnology=8) OR(pr.LastStateID=11 and LastStatusID=11170 and pd.PackageType=4) ) and PR.DateTime between '04/28/2016 00:00:00:00' and '10/09/2018 23:59:59:999'

Note that rejected and canceled requests, those awaiting approval, and those requests which have not yet been inserted are not included in the totals. Obviously, the date range in the above queries would need to be changed to reflect the data range entered on the SLA dashboard.

One other thing to note: The queries I provided may result in slightly different results in your environment, as you may have changed the status mappings from their defaults. For example, in the failed query, you will see "pr.LastStateID in (101,11,230) and pd.PackageType in (0)". The in clause values of "101,11,230" are getting pulled from the setting FailureStatusIDs_ConfigMgr in wd_appsettings (which can be set under settings->deployment->config Mgr).

I can only assume that based on the results, you may be modifying your DB manually in some way, and are neglecting to set the LastStatusID or LastStateID columns correctly.

AjaykumarPatil · ‎May 27, 2021

How to resolve this issue?

jdempsey · ‎May 27, 2021

If the information Charles has provided doesn't help you investigate this further, I would suggest opening a support case and working with Charles (or another engineer) directly.

Anything expressed here is my own view and not necessarily that of my employer, Flexera. If my reply answers a question you have raised, please click "ACCEPT AS SOLUTION".

CharlesW · ‎Jun 02, 2021

First, you can tell me if you are, or have been, updating the WD_packageRequests table by running queries against the App Portal DB? If so, then it is likely that you are doing so incorrectly. As mentioned, I'd guess that it would likely be that the lastStateID or LastStatusID columns are not set correctly. The queries I provided previously show you generally what values App Broker is expecting to either flag a request as successful or failed...

I see that you now have two separate support cases open, which appear to pertain to the SLA dashboard so let's try to keep this discussion going either through a single support case or the community post, to avoid duplication of efforts.

AjaykumarPatil · ‎Jun 03, 2021

Hi @CharlesW, We have raised a case that has 2 issues in that so we have split that into separate cases. One can which we have opened for the SCCM issue and another for the Dashboard issue.

AjaykumarPatil · ‎Jun 03, 2021

HI, @CharlesW we are not updating anything in the database manually.

CharlesW · ‎Jun 03, 2021

OK, thanks for the update.. I'm working with the support case owners to see if we can get this resolved.. It looks like the status update in the App Portal DB is not occurring correctly, due to some problem with requestID 94344. Steps will be provided shortly which will hopefully resolve the issue.. I'll continue to work with the case owners until this gets resolved.. Note that the SLA dashboard showing all pending requests appears to be a side effect of the bigger problem, which would occur if the status is not getting updated in the DB.