Posts Tagged ‘SCCM’

Further to my recent SCCM report post to return All advertisements run in the last 24 hours, here’s a way to include if a program requires users logged on, or off, or either for a particular program.

1. Join the v_Program table to your query. In my query it’s included by join v_Program pgm on adv.PackageID=pgm.PackageID and adv.ProgramName=pgm.ProgramName

2. Select pgm.ProgramFlags. This will probably return you at least a 7 digit number that you’ll want to  look for some bit flags in. The values in question are documented on http://msdn.microsoft.com/en-us/library/cc144361 and of particular interest are 0x20000 and 0x4000

3. Rather than just selecting the pgm.ProgramFlags value, add a case statement to return something in words that people can understand. In this instance, if 0x20000 is set, it’s logged out only. If 0x4000 is set it’s logged on only. If neither is set, it’s either logged on or logged off. This is the part you’ll want in your select statement:

CASE WHEN (pgm.ProgramFlags & 0x20000 != 0) THEN ‘Logged out only’
WHEN (pgm.ProgramFlags & 0x4000 != 0) THEN ‘Logged on only’
WHEN (pgm.ProgramFlags & 0x4000 = 0 AND pgm.ProgramFlags & 0x20000 = 0) THEN ‘Logged on or off’
ELSE ‘Unknown’
END AS “Logged on or off”


Read Full Post »

With the help of the internet, I have created the greatest and most best SCCM Report ever. This report indicates how many advertisement executions have taken place in the last 24 hours. Its genesis lies in a report whose source eludes me that shows all advertisements created in the last X days. This is fabulous as it shows which long standing advertisements get any use. You could expand it to show advertisements run in the last year for instance.


The query is below and the most important items are in bold. The bold sections join the advertisement status table to the report and select only items with the status time in the last 24 hours. Note that my query uses @__timezoneoffset that you’ll need in order to make this report accurate unless you live in England. I also have the links tab set to link to another report and this is the Software Distribution – Advertisement Status – Status of a specific advertisement report. The prompts for this are set to Name=AdvertID, Prompt Text=Advertisement ID, Column = 12


select adv.AdvertisementName, pkg.Name as C001, pgm.ProgramName, coll.Name as C002,
adv.PresentTime as ‘Creation Time’,
count(*) as ‘Total’,
sum(case LastState when 13 then 1 else 0 end) as ‘Success’,
 sum(case LastAcceptanceState when 0 then 1 else 0 end) as ‘No Status’,
sum(case LastState when 8 then 1 else 0 end) as ‘Waiting’,
sum(case LastState when 11 then 1 else 0 end) as ‘Fail’,
sum(case LastAcceptanceState when 2 then 1 else 0 end) as ‘Reject’,
from v_Advertisement adv
join v_Package pkg on adv.PackageID=pkg.PackageID
join v_Program pgm on adv.PackageID=pgm.PackageID and adv.ProgramName=pgm.ProgramName
join v_Collection coll on adv.CollectionID=coll.CollectionID
join v_ClientAdvertisementStatus stat on adv.AdvertisementID=stat.AdvertisementID

where DATEDIFF(hour, DATEADD(ss,@__timezoneoffset,stat.LastStatusTime),GETDATE())<24

group by adv.AdvertisementID, adv.AdvertisementName, adv.PresentTime, adv.Comment,
       pkg.Name, pgm.ProgramName, adv.SourceSite, coll.Name, adv.IncludeSubCollection,
               (CASE WHEN adv.IncludeSubCollection!=0 then ‘*’ else ” END),
 CASE WHEN AssignedScheduleEnabled != 0 or
                                      (AdvertFlags & 0x720) != 0
  THEN ‘*’
  ELSE ”
       (case when (0x00001000&ProgramFlags)!=0 then ‘*’ else ‘ ‘ end)
order by adv.PresentTime DESC

Read Full Post »

There are a variety of different methods for configuring SCCM software distribution and reasons for choosing one way or the other. I’m going to look to expand this list, but would emphasise that this is personal opinion. Without any consultation or authority, here are what I consider to be best practice for some tasks in SCCM:

  • Programs that upgrade installed software, set the Environment to Program can run: Only when no user is logged on. This will reduce the likelihood of software being in use and failing to upgrade/uninstall as expected.
  • Programs that reboot, set the Environment to Program can run: Only when no user is logged on. This isn’t always practical, but if you wanted to upgrade something like Internet Explorer and you want or require a reboot, try and get it to run when nobody’s using the PC.

Read Full Post »

This came up at work again, so I’ve decided that so I can find it this time, I’ll post it here. This is directly from SMS Guys who have moved on to here.

1. Create a collection that will contain all clients to be excluded from another collection (we’ll call it ‘ExclusionsCollection’ for this example.)

2. Once you have that, go into the Collections table in the SMS SQL database and find the row that contains ‘ExclusionsCollection’. Note the corresponding ‘ResultTableName’. EDIT: A simpler way is to look in the Collection’s properties – the ID is at the bottom.

3. In the collection that you want to exclude the members of ‘ExclusionsList’ from, copy and paste the following query:

select SMS_R_System.ResourceID,SMS_R_System.ResourceType,
from SMS_R_System where Client = 1 and ClientType = 1 and
ResourceId not in (select ResourceID from SMS_CM_RES_COLL_XXXxxxxx)

Read Full Post »