SCCM Report status of Packages used in Task Sequence

Another Interesting report which will help you to identify if there are any packages used in Task Sequence are not available on Distribution Points.

If the Reference packages used in Task Sequence not available on DP,your Task sequence will fail and then u come back to fix the issues so lets be proactive Smile.

SCCM 2012 presents nice view with compliance level for the reference packages used in Task Sequence.

select
pkg.Name as [Package Name],
Case pkg.PackageType
When 0 Then 'Software Distribution Package'
When 3 Then 'Driver Package'
When 4 Then 'Task Sequence Package'
When 5 Then 'software Update Package'
When 6 Then 'Device Settings Package'
When 7 Then 'Virtual Package'
When 257 Then 'Image Package'
When 258 Then 'Boot Image Package'
When 259 Then 'OS Install Package'
Else ' '
END AS 'Package Type',
pkg.PackageID,
SUBSTRING(dp.ServerNALPath, CHARINDEX('\\', dp.ServerNALPath) + 2, CHARINDEX('"]', dp.ServerNALPath) - CHARINDEX('\\', dp.ServerNALPath) - 3 ) AS [Server Name],
dp.SiteCode,
dp.LastRefreshTime,
stat.SourceVersion,
stat.LastCopied,
stat.SummaryDate,
stat.InstallStatus,
case when dp.IsPeerDP=1 then '*' else '' end as BranchDP
from v_Package pkg
join v_DistributionPoint dp on pkg.PackageID=dp.PackageID
join v_PackageStatusDistPointsSumm stat on dp.ServerNALPath=stat.ServerNALPath
                                             and dp.PackageID=stat.PackageID
where stat.State!=0 and pkg.PackageID in (select ReferencePackageID from v_TaskSequenceReferencesInfo
where PackageID =@TS1)

Prompt for TS:

select PackageID,Name from v_TaskSequencePackage

You can create n number of Prompts with above Query if you want to look for multiple task sequences.

Note: Please replace the quotes used in this report as they converted to Fancy by Blog.

Attached MOF file Status of Task sequence which you can import directly into SCCM

25 Responses to "SCCM Report status of Packages used in Task Sequence"

    1. haven't tried it but it can be done using powershell. I always recommended to go with reports as it is easier to run by anyone and can access using the URL without much needs.
      When you have easier method,why to look at other methods .

      Thanks,
      Eswar

      Reply
    1. There is default report to check the status of reference packages used in particular task sequence. check report "Task Sequence - References > Content referenced by a specific task sequence"

      Reply
    1. There is default report to check the status of reference packages used in particular task sequence. check report "Task Sequence - References > Content referenced by a specific task sequence"

      Reply
  1. Hi Eswar,

    I run the query, it doesn't give any errors. But when I select the PackageID of the Task sequence, I want to check, it simply displays an empty report. Is there something that I miss here ?

    Thanks in advance!

    BR,
    Konstantin Popov

    Reply
    1. Also,

      This query references only the Packages. I am using SCCM 2012, and I want to show the referenced Applications by the Task Sequence also. Any idea how I can do that ?

      Thanks!

      Cheers,
      Konstantin

      Reply
      1. yes,this is for CM07 and in CM12,packages and applications are stored in different tables .To know the status of packages used in the task sequence,you can simply look at references tab from your task sequence ,you will have column name compliance%.

        Reply
    2. This report was created for CM07 ,CM12 has different tables and views for task sequences.if you are using CM12,you can directly check this from sccm console by looking at task sequence references tab to know the package compliance.

      Reply
      1. Hi Eswar,

        Thank you very much for taking the time to answer my questions. I am aware of the references and the status of the applications, shown there. My point was to make a report out of it, so that I can present it to a customer for example. But anyway, I will search the tables and views and see what I can come up with. If I get it, I will post the query here.

        Cheers,
        Konstantin

        Reply
    1. it is given the report with syntax How to create prompt for TS1:
      Try this: select PackageID,Name from v_TaskSequencePackage

      Reply
  2. I did replace both the single and double quotes along with the dashes. If you'd like you can always e-mail me the original in a text document to see if that matters. This time I also ran in SQL Management Studio 2008 R2 (does NOT have SP1) and I received the following error.

    Msg 103, Level 15, State 4, Line 23
    The identifier that starts with ' end as BranchDP
    from v_Package pkg
    join v_DistributionPoint dp on pkg.PackageID=dp.PackageID
    join v_PackageStatusDistPointsS' is too long. Maximum length is 128.
    Msg 105, Level 15, State 1, Line 23
    Unclosed quotation mark after the character string ' end as BranchDP
    from v_Package pkg
    join v_DistributionPoint dp on pkg.PackageID=dp.PackageID
    join v_PackageStatusDistPointsSu'.
    Msg 102, Level 15, State 1, Line 23
    Incorrect syntax near ' end as BranchDP
    from v_Package pkg
    join v_DistributionPoint dp on pkg.PackageID=dp.PackageID
    join v_PackageStatusDistPointsS'.

    Reply
    1. it worked for me without any issues....try this .I ran in SQL Management Studio.Dont forget to replace the quoates

      select
      pkg.Name as [Package Name],
      Case pkg.PackageType
      When 0 Then 'Software Distribution Package'
      When 3 Then 'Driver Package'
      When 4 Then 'Task Sequence Package'
      When 5 Then 'software Update Package'
      When 6 Then 'Device Settings Package'
      When 7 Then 'Virtual Package'
      When 257 Then 'Image Package'
      When 258 Then 'Boot Image Package'
      When 259 Then 'OS Install Package'
      Else ' '
      END AS 'Package Type',
      pkg.PackageID,
      SUBSTRING(dp.ServerNALPath, CHARINDEX('\\', dp.ServerNALPath) + 2, CHARINDEX('"]', dp.ServerNALPath) - CHARINDEX('\\', dp.ServerNALPath) - 3 ) AS [Server Name],
      dp.SiteCode,
      dp.LastRefreshTime,
      stat.SourceVersion,
      stat.LastCopied,
      stat.SummaryDate,
      stat.InstallStatus,
      case when dp.IsPeerDP=1 then '*' else '' end as BranchDP
      from v_Package pkg
      join v_DistributionPoint dp on pkg.PackageID=dp.PackageID
      join v_PackageStatusDistPointsSumm stat on dp.ServerNALPath=stat.ServerNALPath
      and dp.PackageID=stat.PackageID
      where stat.State!=0 and pkg.PackageID in (select ReferencePackageID from v_TaskSequenceReferencesInfo
      where PackageID =@TS1)

      Reply
  3. Hello Eskonr,

    I removed the quotes in notepad but I'm getting an error after hitting OK in the report SQL statement that says "Unclosed quotation mark after the character string ' end as BranchDP..." it then lists the rest of the code. Any thoughts?

    Reply
  4. Hello Eskonr,

    This is exactly the script i was looking for, In your note you say replace the quotes, but can you please explain what quotes you mean to change into what?

    Reply
    1. Quotes could be single(') or double(").If you copy the code into Notepad and look at the quotes you will see the difference what am talking about.

      Reply

Leave a Reply