Package run Query

myITforum.com Community Forum

Home Forums Blogs Live Support chat Search Articles Wiki FAQ Email Lists Register Login My Profile Inbox Address Book My Subscription My Forums

Photo Gallery Member List Search Calendars FAQ Ticket List Log Out

All Forums RSS Feed Subscription:

Package run Query

View related threads: (in this forum | in all forums)

Logged in as: Guest

Printable Version

All Forums >> [Management Products] >> System Center Products >> System Center Configuration Manager >> Package run Query

Page: [1]

Message

<< Older Topic Newer Topic >>

Package run Query - 4/1/2008 4:56:49 AM

chiners_68

Posts: 150
Score: 0
Joined: 10/31/2007
Status: offline

How would I setup a query to get a collection of machines that have run a specific SCCM package? The package runs an exe file which subsequantly removes itself & it dosent record in Add/remove programs.

The package does however leave a result.xml file in this path C:\WINDOWS\system32\CCM\Cache\LAN00017.1.System

< Message edited by chiners_68 -- 4/1/2008 5:06:29 AM >

Post #: 1

RE: Package run Query - 4/1/2008 5:18:20 PM

kcas

Posts: 63
Score: 1
Joined: 3/25/2003
Status: offline

Try this out...

http://www.myitforum.com/articles/8/view.asp?id=9552

of course you'd want to change the Advert ID and change LastStateName = "succeeded"

< Message edited by kcas -- 4/1/2008 5:30:00 PM >

(in reply to chiners_68)

Post #: 2

RE: Package run Query - 4/2/2008 6:12:01 AM

chiners_68

Posts: 150
Score: 0
Joined: 10/31/2007
Status: offline

Tried that it didnt return any machines& I know the package has been deployed to 10 machines at least for testing.

quote:

Select sys.ResourceID,sys.ResourceType,sys.Name,sys.SMSUniqueIdentifier,sys.ResourceDomainORWorkgroup,sys.Client FROM sms_r_system as sys inner join SMS_ClientAdvertisementStatus as offer on sys.ResourceID=offer.ResourceID
WHERE AdvertisementID = 'LAN00017' and LastStateName = "succeeded"

I cannot find a attribute class in SCCM that has AdvertisementID

Ive also tried this what I found on the web but they all relate to SMS not SCCM. Guess what it dosent work.

quote:

Select SMS_R_System.Name,
SMS_R_System.ResourceID from SMS_R_System,
SMS_ClientAdvertisementStatus Where
SMS_R_System.ResourceID=SMS_ClientAdvertisementStatus.ResourceID
AND SMS_ClientAdvertisementStatus.LastStateName = 'Succeeded'
And SMS_ClientAdvertisementStatus.AdvertisementID ='LAN00017'
AND Not SMS_R_System.ResourceID
IN(SELECT ResourceID
from SMS_CM_RES_COLL_LAN00031)

< Message edited by chiners_68 -- 4/2/2008 8:17:48 AM >

(in reply to kcas)

Post #: 3

RE: Package run Query - 4/2/2008 1:56:45 PM

kcas

Posts: 63
Score: 1
Joined: 3/25/2003
Status: offline

I see what you mean, I don't see the class either. Is the package removing any .exe's or removing anything from Add/Remove Pgms? Something could be created that way.

Back to getting status from the Advert, I do see the view but not the Class. Not sure if this would help you you could create a web report base on the following, and then modify it for your needs.

SELECT [AdvertisementID]
,[ResourceID]
,[LastAcceptanceMessageIDSeverity]
,[LastAcceptanceMessageID]
,[LastAcceptanceMessageIDName]
,[LastAcceptanceState]
,[LastAcceptanceStateName]
,[LastAcceptanceStatusTime]
,[LastStatusMessageIDSeverity]
,[LastStatusMessageID]
,[LastStatusMessageIDName]
,[LastState]
,[LastStateName]
,[LastStatusTime]
,[LastExecutionResult]
,[LastExecutionContext]
,[IsActive]
FROM [v_ClientAdvertisementStatus]
Where [AdvertisementID] = 'Advert ID here xxxxx'

(in reply to chiners_68)

Post #: 4

RE: Package run Query - 4/2/2008 3:35:36 PM

chiners_68

Posts: 150
Score: 0
Joined: 10/31/2007
Status: offline

The package runs an exe file which then once processed the exe file removes itself but leaves a result.xml file. Nothing is recorded on Add/remove programs for the process that is run.

(in reply to chiners_68)

Post #: 5

RE: Package run Query - 4/2/2008 4:56:36 PM

rbennett806

Posts: 659
Score: 8
Joined: 6/14/2006
Status: offline

It might be easier to simply use a VBScript wrapper in your package. Something like...

If objFSO.FileExists("C:\WINDOWS\system32\CCM\Cache\LAN00017.1.System\result.xml") Then
'don't run your package because the result.xml file exists on the machine
Else
'execute the package
End If

Of course, you'd want to beef up the script a little more to use variables and such...

(in reply to chiners_68)

Post #: 6

RE: Package run Query - 4/2/2008 5:05:58 PM

kcas

Posts: 63
Score: 1
Joined: 3/25/2003
Status: offline

Of course another solution, but might be too late if you've already run your pgm, is to have the package drop a .exe file (or something similar) on the device that inventory would pick up.

(in reply to rbennett806)

Post #: 7

RE: Package run Query - 4/3/2008 5:10:47 AM

chiners_68

Posts: 150
Score: 0
Joined: 10/31/2007
Status: offline

rbennett806,
Yes thats an idea ill keep in mind, justseems a bit backwards relying on scripts when SCCM should be able to perform a query if a package has been run or not.

Kcas,
already deploying the package.

< Message edited by chiners_68 -- 4/3/2008 5:11:31 AM >

(in reply to kcas)

Post #: 8

RE: Package run Query - 4/4/2008 1:48:17 PM

lcpc78

Posts: 34
Score: 0
Joined: 10/4/2007
Status: offline

Another option you can do is to include the name of the *.xml in your software inventory and it will pick up during it's next inventory. This will allow you the ability to query for that particular *.xml to build a collection since you know where it is supposed to be anyways.

(in reply to chiners_68)

Post #: 9

RE: Package run Query - 4/4/2008 3:04:58 PM

chiners_68

Posts: 150
Score: 0
Joined: 10/31/2007
Status: offline

One of the MS guys suggested using one of their reports. Some of the reports do actually query advertisments. Just need to use some of the sql from the reports in a sql query.

(in reply to lcpc78)

Post #: 10

RE: Package run Query - 4/16/2008 4:37:58 AM

chiners_68

Posts: 150
Score: 0
Joined: 10/31/2007
Status: offline

I added *.xml to the inventoried files & set the path to just above the advetisment folders. no xml files were inventoried. Several days after testing with no results I now find out from Wally on technet that SCCM dosent look for inventoried files in its cache were these files im looking for are located.

(in reply to chiners_68)

Post #: 11

RE: Package run Query - 4/21/2008 7:50:23 PM

phaustein

Posts: 859
Score: 32
Joined: 3/21/2005
From: Washington, DC
Status: offline

One thing that you could do is to create a registry key that you can place in the uninstall key that includes the software information. We have several custom applications that install in a similar way and our best solution was to wrap a reg key file and import it as part of the install.

_____________________________

Hope this helps.
Paul

(in reply to chiners_68)

Post #: 12