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 
.
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 Comments
any command to check whether application is part of TS
using powershell
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
If you could provide a download in RDL format for sccm 2012 would be awesome.
Thanks,
Mark
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"
If you could get this report into a SCCM 2012 RDL format, would be awesome. When I tried to import the MOF via client settings, I got an error.
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"
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
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
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%.
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.
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
Hi Eswar,
how to declare @TS1?
it is given the report with syntax How to create prompt for TS1:
Try this: select PackageID,Name from v_TaskSequencePackage
Thanks! I imported the MOF and it works great now!
great!
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'.
ok i exported the report for you into MOF file and attached here.Should ease for you.
Thanks I just tried the new statement and still get the same error. BTW I'm using this in SCCM 2007 R3.
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)
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?
Try the query now.I uncommented the case statement for branch DP.
When i copy the script, the quotes are exactly copied as you put them in the script?
Did the report work ? If so no worries .What i guess is it will give error so you need to remove the quotes and type in again.
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?
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.