How to get the step of a Task Sequence with a SQL query
Hi everyone,

I tried to create a report based on a Task Sequence to retrieve all packages that a TS contains. The only thing I'm able to have is the package name, version, etc. Is there a table or a view that contains the value : Type, Name, Package, Program and the order (all the fields that are in the task sequence editor panel) ?

Thanks

Here my current query :

select v_TaskSequencePackage.Name 'TaskSequenceName',
v_TaskSequenceReferencesInfo.PackageID 'TaskSequenceID',
 v_TaskSequenceReferencesInfo.ReferenceName 'PackageName',
 v_TaskSequenceReferencesInfo.ReferenceVersion 'Version'
 from v_TaskSequencePackage
inner join v_TaskSequenceReferencesInfo on v_TaskSequenceReferencesInfo.PackageID = v_TaskSequencePackage.PackageID
where v_TaskSequencePackage.Name like 'Insert your Task Sequence Name Here'
  • Edited by EricPare Tuesday, November 29, 2011 2:15 PM
August 29th, 2011 1:25pm

This link should help you out. http://technet.microsoft.com/en-us/library/dd334563.aspx
Free Windows Admin Tool Kit Click here and download it now
August 29th, 2011 11:40pm

take a look at configmgr 2007 SQL views document excel file http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=22052 for list of views and columns avilable with it.

you have viwe called v_TaskSequencePackage that has list of values like program name,type,version etc which you pipe into report.

August 30th, 2011 1:25pm

I already browse the SQL views document and there is nothing about the details Task Sequence.

I need the details of each tasks inside a task sequence (Type, Package, Program and the order).

Maybe I will have to ask the DBA to create a custom view for that.

Free Windows Admin Tool Kit Click here and download it now
September 7th, 2011 2:28pm

I already browse the SQL views document and there is nothing about the details Task Sequence.

I need the details of each tasks inside a task sequence (Type, Package, Program and the order).

Maybe I will have to ask the DBA to create a custom view for that.


I think, it's NOT supported to create a new view directly in SCCM DB.
September 7th, 2011 2:51pm

I already browse the SQL views document and there is nothing about the details Task Sequence.

I need the details of each tasks inside a task sequence (Type, Package, Program and the order).

Maybe I will have to ask the DBA to create a custom view for that.


did u taken look at tab sql views by view name ? row number 3224 ? and these views are read only

.

Free Windows Admin Tool Kit Click here and download it now
September 7th, 2011 3:30pm

When I list the ProgramName of a task sequence, the return value is * because there is no program with a task sequence. Program are for Package.

With v_TaskSequenceReferencesInfo, I'm able to see all packages from a Task Sequence but I still don't know the execution order or which program is running for a package.

  • Edited by EricPare Wednesday, September 07, 2011 7:24 PM
September 7th, 2011 7:21pm

Sorry I didn't see that you had edited your post after the notification that I recevied and that you have seen this v_TaskSequenceReferencesInfo view.  I'm sure that the data is there... 

Free Windows Admin Tool Kit Click here and download it now
September 7th, 2011 8:38pm

TS_References will give you the order from lowest to highest for each TS.

September 7th, 2011 10:29pm

Nope.. the 2 tables from TS_References are TS_ID and TS_ReferenceID. Nothing tell me the about the order.

Here the query :

select v_TaskSequencePackage.Name 'TaskSequenceName',
v_TaskSequenceReferencesInfo.PackageID 'TaskSequenceID',
 v_TaskSequenceReferencesInfo.ReferenceName 'PackageName',
 v_TaskSequenceReferencesInfo.ReferenceVersion 'Version',
  TS_References.TS_ID 'TSID',
  TS_References.TS_ReferenceID 'Ref_ID'
 
 from v_TaskSequencePackage
inner join v_TaskSequenceReferencesInfo on v_TaskSequenceReferencesInfo.PackageID = v_TaskSequencePackage.PackageID
inner join TS_References on TS_References.TS_ID = v_TaskSequencePackage.TS_ID
where v_TaskSequencePackage.Name like 'OSISoft PI Suite - Kamloops'

And the results :

My order in my task is :

OSISoft PI SDK 1.3.4 EN, OSISoft PI ProcessBook 3.0.15.7 EN, OSISoft PI Datalink 3.1.6.1 EN and OSISoft PI Server Setup Kamloops 1.1 EN

Free Windows Admin Tool Kit Click here and download it now
September 8th, 2011 1:27pm

try this.

select 
	TSP.Name,
	TSR.TS_ReferenceID, 
	prog.PkgID, 
	prog.Name
from 
	TS_References as TSR
	join PkgPrograms as prog on TSR.TS_ReferenceID = prog.ProgramID 
	join v_TaskSequencePackage TSP on TSR.TS_ID = TSP.TS_ID
where 
	TSP.Name = 'OSISoft PI Suite - Kamloops'

 BTW Are you in Kamloops?

September 8th, 2011 2:09pm

Hmm... we are getting somewhere !!! With your request, I have the program Name, which is good!

But the TS_ReferenceID is not the order of my task. PkgID MTL000E9 (Ref_ID : 461) is OSISoft PI Datalink 3.1.6.1 which is the third in my task.

No, i'm from Montreal but we have a site in Kamloops :)


  • Edited by EricPare Thursday, September 08, 2011 3:04 PM
Free Windows Admin Tool Kit Click here and download it now
September 8th, 2011 3:02pm

I don’t know how this slip through my inbox but..

 

I’m not sure how to get the exact order, I was using SQL trace to get the table/views but once you said it not the exact order I re-ordered my task sequence and it didn’t renumber each item..

 

However Have you seen this?

http://blogs.technet.com/b/deploymentguys/archive/2008/08/17/documenting-your-task-sequences-automagically.aspx

 

 

 

BTW I’m in Ottawa. I should have guessed that you were in Montreal based on the site code but..

September 10th, 2011 8:42pm

Well, this is something I had to do each time when I create/edit a task sequence. We don't have time for that :P.

I think our only choice will be a custom view.

Free Windows Admin Tool Kit Click here and download it now
September 12th, 2011 2:31pm

But to do a custom view, you need to know how to join the tables together/view together. so... Until you find that, you are are stuck.
September 12th, 2011 2:45pm

My colleague found it!

Select Distinct Max(v_TaskExecutionStatus.Step)'Step',
v_TaskExecutionStatus.ActionName
from v_TaskExecutionStatus, v_Advertisement
where v_TaskExecutionStatus.AdvertisementID=v_Advertisement.AdvertisementID
AND v_Advertisement.PackageID='MTL0015A'
AND LastStatusMessageID=11134
Group By v_TaskExecutionStatus.ActionName
Order By Step

The task sequence must be runned at least once to make sure that the table as data in it.

  • Marked as answer by EricPare Tuesday, November 29, 2011 2:14 PM
Free Windows Admin Tool Kit Click here and download it now
November 29th, 2011 2:14pm

Hi,

Just to know if we can use the below query for getting the same results that's given by the above query:

select

tsr.packageID, tsr.referencepackageid, tsr.referencename, tsp.packageid, tsp.name

from

v_tasksequenceReferencesInfo tsr

join

v_tasksequencePackage tsp On tsr.packageID = tsp.packageid

WHERE

(tsr.packageID ='Task Sequence Package ID')

Regards

Pranav

July 4th, 2015 7:18pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics