Query Internal Apps

Check app versions

Find out the list and version of applications installed on the device by running the following SQL query:

select * from interrogator.applicationlist al
inner join interrogator.application a
on a.applicationid = al.applicationid
where al.deviceid = #affecteddeviceid

How to verify if devices have installed the latest provisioning profile for an internal application?

Search for devices who are reporting the application as installed, but do not have the updated provisioning profile installed

SELECT * FROM interrogator.ApplicationList (NOLOCK)
WHERE DeviceID in (
    SELECT iaa1.DeviceID From deviceApplication.InternalAppAssignment (NOLOCK) iaa1
    WHERE InternalAppID = {INTERNAL_APP_ID}
    AND iaa1.DeviceId NOT IN (
        SELECT iaa.DeviceID FROM deviceApplication.InternalAppAssignment (NOLOCK) iaa
        JOIN deviceProfile.DeviceProfileDevicePool dpdp
        ON iaa.DeviceID = dpdp.DeviceID
        WHERE iaa.InternalAppID = {INTERNAL_APP_ID}
        AND dpdp.DeviceProfileID = {DEVICE_PROFILE_ID}
    )
)
AND ApplicationID = {APPLICATION_ID}
AND IsInstalled = 1

The logic behind this script is:

• Check for devices reporting the provisioning profile as installed (deviceProfile.DeviceProfileDevicePool)
• Check against devices that are supposed to be assigned this application (deviceApplication.InternalAppAssignment)
• Check against devices that are reporting the application as installed (interrogator.ApplicationList)

To find the variables in this query:

• INTERNAL_APP_ID - This is found in the deviceApplication.Application database table, or in the URL when viewing the summary page for the internal application.
• DEVICE_PROFILE_ID - This is found in the deviceApplication.Application table as the ProvisioningDeviceProfileID.
• APPLICATION_ID - This is found in the interrogator.Application table as the ApplicationID.  To find this value you can use the following query:

SELECT ia.* FROM interrogator.Application (NOLOCK) ia
JOIN DeviceApplication.Application daa
    ON ia.Identifier = daa.PackageID
    AND ia.VersionHash = daa.VersionHash
WHERE daa.ApplicationID = {INTERNAL_APP_ID}

Verify if commands are in the command queue to update the provisioning profile

The following query can be used to identify if, for devices that have not yet installed the updated profile, commands have been queued to install it on the next device check in:

SELECT * FROM DeviceCommandQueue.DeviceQueue (nolock) dq
JOIN deviceProfile.DeviceProfileVersion dpv
    ON dq.DeviceProfileVersionID = dpv.DeviceProfileVersionID
WHERE dq.CommandID = 13 --CommandID for InstallProvisioningProfile
AND dpv.DeviceProfileID = {DEVICE_PROFILE_ID}

Search for application upload events

The following query is generated by HUB -> Reports and Analytics -> Events -> Console Events in the console, but the console only searches for Last Month, while manual procedure allows to search further back

exec eventLog.EventLog_Search @LocationGroupID=571,@SeverityID=NULL,@eventGroupTypeID=2,@Module=N'Apps',@StartDateTime='2018-06-01 15:32:34.843',@EndDateTime='2018-07-27 15:33:34.843',
@EventDirectionID=NULL,@EventTypeID=0,@EventActionID=0,@StartRowIndex=50,@MaximumRows=1000,@SortExpression=NULL,@SortAscending=1,@SearchText=NULL