APNs Troubleshooting

External links

• VMware KB article on APNs renewal - https://kb.vmware.com/s/article/2960965
• APNs for app renewal in on-prem environments - https://kb.vmware.com/s/article/50121242

APNs incorrect renewal

•  First you need to get the GroupID of the organization group that has APNs configured.  In Chrome, you can do this by right-clicking the organization group drop down as shown below and choosing Inspect Element.  In the HTML, search for data-current-lg and note the GroupID.
• Perform the following SQL query in the database to retrieve information about the APNs certificate:

SELECT DISTINCT DC.CertificateID, DAGS.appleid, DC.SubjectName,
                DC.serialnumber, DC.certificate thumprint,
                DC.notBefore, DC.NotAfter
FROM
      dbo.Certificate (NOLOCK) AS DC
      JOIN
            dbo.ApnsGenerationStatus (NOLOCK) as DAGS
            ON DC.CertificateID = DAGS.IssuedCertID
WHERE
      DAGS.LocationGroupID = 10289
ORDER BY NotBefore ASC

• Validate the results of this query.  In a correct renewal, both the appleid and SubjectName columns of the results should match between the latest certificate and the previous certificate.  You can tell which certificate is which based on the notBefore and notAfter columns, which match the validity dates of the certificate.

• If the appleid field is different, then the administrator used an incorrect Apple ID when renewing the APNs certificate, and so the SubjectName will not match.  The administrator must use the same Apple ID when renewing APNs, or all devices will lose the ability to communicate.  If appleid matches but SubjectName is different, then the administrator used the correct Apple ID but chose the wrong certificate it Apple’s portal to renew.  They must go back to Apple’s portal and renew the correct certificate.
• Note: All devices enrolled after the notBefore date of an incorrect APNs certificate must re-enroll when the certificate is corrected.  These devices will lose communication once the certificate is correct.
• Note: If the administrator previously cleared out the APNs certificate (by selecting the Clear button in the APNs settings page and saving), they will lose communication with all devices.  In this event, a database backup is required to restore the tokens, and this process generally cannot be completed on SaaS environments.

APNs Database Queries

Below are a list of helpful SQL queries specifically when troubleshooting APNs. All of the queries below are SELECT statements, which allows us to view the information but not update it.

1. This statement will display an overall view of the location group. This allows us to look at certain fields as well as the Location Group ID for the location where the APNs certificate is being uploaded. By inputting the name, you can receive the Group ID.

SELECT * FROM dbo.locationgroup lg (NOLOCK) WHERE lg.Name like '%%'

2. This statement displays the APNs settings at and above the LG provided. We are looking for these first four fields and which have values that are not NULL. It should only be configured at one level and inherited below. You simply need to input the group ID found in the first query.

SELECT lgf.LGlvl, lg.MDMAppleApplicationID, lg.MDMAppleDeviceProfileID, lg.AppleMDMEnabled, lg.MDMSettingsInheritable,lg.*
FROM dbo.LocationGroup lg (NOLOCK)
 JOIN dbo.LocationGroupFlat lgf (NOLOCK)
  ON lgf.ParentLocationGroupID = lg.LocationGroupID
WHERE lgf.ChildLocationGroupID = ###
ORDER BY lgf.LGlvl DESC

3. This statement shows the APNs settings at or below the LG provided. You simply need to input the group ID found in the first query. The first four columns after the level should be NULL at every level below the level where the cert was uploaded at. You can also view the levels of the tree in the first column. If you do see multiple levels with values not being NULL, we can look at the different APNs certs and the number of devices associated. This will require some update statements which a DB or T3 Member can assist with.

SELECT lgf.LGlvl, lg.MDMAppleApplicationID, lg.MDMAppleDeviceProfileID, lg.AppleMDMEnabled, lg.MDMSettingsInheritable,lg.*
FROM dbo.LocationGroup lg (NOLOCK)
 JOIN dbo.LocationGroupFlat lgf (NOLOCK)
  ON lgf.ChildLocationGroupID = lg.LocationGroupID
WHERE lgf.ParentLocationGroupID = ###
ORDER BY lgf.LGlvl ASC

4. This statement will display all the APNs certificates in the environment. You simply need to input the group ID found in the first query. It is important to match this with the previous query so we know which cert is at each level. It is also important to view if the topic is the same for different certs. If it is, we can change the application ID associated to the device without any end user interaction or effect. If the topics are different, devices will need to re-enroll. From this query, you will have the ProductionCertificateID and ApplicationID.

SELECT a.ProductionCertificateID, lg.LocationGroupID, lg.Name, a.*
FROM deviceApplication.Application a (NOLOCK)
 JOIN dbo.LocationGroupFlat lgf (NOLOCK)
  ON a.RootLocationGroupID = lgf.ChildLocationGroupID
 JOIN dbo.LocationGroup lg (NOLOCK)
  ON lgf.ChildLocationGroupID = lg.LocationGroupID
WHERE lgf.ParentLocationGroupID = ###
 AND a.PackageID like '%com.apple.mgmt%'

5. This statement will confirm if the certificates are alive by viewing the date. You simply need to input the ProductionCertificateID found using the queries previous.

SELECT *
FROM dbo.Certificate c (NOLOCK)
where c.CertificateID IN (###)

6. This statement will tell the devices that are associated with the Application ID. You simply need to input the ApplicationID found using the queries previous. This will assist if the customer has multiple APNs certificates active in the console and you are trying to determine which to use. Obviously, we will want to choose the cert with the most devices for the least end user impact.

SELECT *
FROM deviceApplication.ApplicationDeviceCredential adc (NOLOCK)
WHERE adc.ApplicationId IN (###)

Condensed query:

SELECT adc.DeviceID, a.ApplicationID, a.PackageID, adc.NotificationID
FROM deviceApplication.ApplicationDeviceCredential adc
LEFT JOIN
deviceApplication.Application a
ON adc.ApplicationID = a.ApplicationIDWHERE DeviceID =

7. This statement will show the serial number associated with the APNs certificate. This will assist if you need to relay this information to Apple to determine if they have a corrupt certificate where AirWatch is unable to continue troubleshooting. Simply input the name of the Location Group where the APNs Certificate is uploaded.

SELECT lg.Name, c.SerialNumber from dbo.Certificate c
inner join deviceApplication.Application a
on ProductionCertificateID = CertificateID
inner join dbo.LocationGroup lg
on lg.MDMAppleAPplicationID = a.ApplicationID
where lg.Name like '%__%'

8. This statement will show you all the APNs certificates that have been uploaded to the console at or below the LG provided. You simply need to input the group ID found in the first query.

SELECT c.SerialNumber,c.SubjectName, c.NotBefore, c.NotAfter, ags.*
FROM dbo.ApnsGenerationStatus ags (NOLOCK)
JOIN dbo.Certificate c (NOLOCK)
ON c.CertificateID = ags.IssuedCertID
WHERE ags.LocationGroupID IN (###)
ORDER BY CreatedDate DESC

PushMagic Token

In Database:

SELECT *
FROM dbo.AppleDeviceEx
WHERE DeviceID = ###

APNs in logs

Console (CN) server logs:

• MessengingServiceLog (C:\AirWatch → Logs → Services) Search for:
• gateway.push.apple.com
• APNs token
• PushMagic token