Is there any SQL query for finding the status of deployed application in sccm

Question

Is there any SQL query for finding the status of deployed Application in sccm

solved 0
Saurabh Sharma 3 months 4 Answers 238 views Beginner 0

Answers ( 4 )

  1. Thanks Anoop & Kathik

  2. Select
    Pac.PackageID as ‘App_ID’,
    col.CollectionID as ‘AppCollection_ID’,
    Vaa.ApplicationName as ‘ApplicationName’,
    Ds.CollectionName as ‘CollectionName’,
    CASE when col.CollectionType = 1 Then ‘User’ when col.CollectionType = 2 Then ‘Device’ Else ‘Others’ End as ‘CollType’,
    CASE when Vaa.DesiredConfigType = 1 Then ‘Install’ when vaa.DesiredConfigType = 2 Then ‘Uninstall’ Else ‘Others’ End as ‘DeploymentType’,
    CASE when Ds.DeploymentIntent = 1 Then ‘Required’ when Ds.DeploymentIntent = 2 Then ‘Available’ when Ds.DeploymentIntent = 3 Then ‘Simulate’ End as ‘Purpose’,
    Ds.DeploymentTime as ‘AvailableTime’,
    Ds.EnforcementDeadline as ‘RequiredTime’,
    Vaa.CreationTime as ‘CreatedOn’,
    Vaa.LastModificationTime as ‘LastModifiedOn’,
    Vaa.LastModifiedBy as ‘LastModifiedBy’
    from v_DeploymentSummary Ds
    left join v_ApplicationAssignment Vaa on Ds.AssignmentID = Vaa.AssignmentID
    left join v_Package Pac on Vaa.ApplicationName = Pac.Name
    left join v_collection col on Ds.CollectionName = col.Name
    Where Ds.FeatureType = 1
    order by Ds.DeploymentTime desc

    Best answer
  3. Well, the above one doesn’t provide the status
    Try this one … nice report
    https://sccm-zone.com/application-deployment-detailed-status-49890f32785f

  4. Try using the following post and remove the where condition as mentioned below

    https://www.anoopcnair.com/sql-query-all-sccm-applications-with-no-deploy/

    I have not tested it ..it seems to work ok

    select
    apps.DisplayName as ‘ApplicationName’,
    apps.Softwareversion as ‘Version’,
    pkg.PackageID,
    CASE pkg.PackageType
    WHEN 0 THEN ‘Package’
    WHEN 3 THEN ‘Driver’
    WHEN 4 THEN ‘TaskSequence’
    WHEN 5 THEN ‘SoftwareUpdate’
    WHEN 6 THEN ‘DeviceSettings’
    WHEN 7 THEN ‘Virtual’
    WHEN 8 THEN ‘Application’
    WHEN 257 THEN ‘Image’
    WHEN 258 THEN ‘BootImage’
    WHEN 259 THEN ‘OSInstall’
    END AS ‘PackageType’,
    apps.NumberOfDeploymentTypes as ‘NoofDT’,
    apps.NumberOfDeployments,
    apps.NumberOfDependentTs
    from fn_ListLatestApplicationCIs(1033) apps
    left join v_TaskSequencePackageReferences tspr on tspr.ObjectID = apps.ModelName
    left join vSMS_ApplicationAssignment ass on ass.AssignedCI_UniqueID = apps.CI_UniqueID
    left join v_Package pkg on pkg.SecurityKey = apps.ModelName
    where
    PackageType = 8
    order by apps.DisplayName

Leave an answer

Sorry, you do not have a permission to answer to this question .