Tag Archives: query

ConfigMgr and “Who updated All Systems–again”?

As this questions shows up at all places for the same reason; Someone pressed the “Updated Membership” on a collection way up in hierarchy of collections. A simple right-click and press – and then accept.

image

The action is simple, however if the collection is ‘far up’ in the hierarchy and have all (or many) collections indirectly connected to it also start to update – an unforgiven workload. Delays, frustration and mayhem will ensue (perhaps a bit over the top here..)

If you are a type of organisation that blames people – well, here you go!

image

Choose to create a new Status Message Query and use the following query to filter the search based on time and only look for specific the refresh action of collections

Query;

select stat.*, ins.*, att1.*, stat.Time 
from SMS_StatusMessage as stat 
left join SMS_StatMsgInsStrings as ins on stat.RecordID = ins.RecordID 
left join SMS_StatMsgAttributes as att1 on stat.RecordID = att1.RecordID 
where stat.MessageType = 768 and 
stat.MessageID = 30104 and 
stat.Time >= ##PRM:SMS_StatusMessage.Time## 
order by stat.Time desc

image

End result – with the user account and collection to blame;

image

SCCM 2012, Software Center and applications made available to users

As noted quite early in the marketing for SCCM 2012 there was a change on how users were presented with available applications. The fundamental change is that any software made available (as opposed to required) were only presented in the new Application Catalog. Microsoft posted a blog-article detailing the different scenarios that could happen, which presents a good overview table on what is seen where when deployed to different resources.

As opposed to start communicating users to either use Application Catalog or Software Center depending on how we have decided to target the users or the computers within SCCM, a better way would be to leverage the traditional method of targeting computers. If targeting computers any software made available will be presented in Software Center, however SCCM 2012 has also been released with the mindset to start handling users – and not computers.

What to do?

Use SCCM to target software to the user’s devices. This would avoid the following topics in the forums; user-based available application not showing up in Software Center by design?!

Previously a few blog-articles have discussed this method for targeting Software Updates and a few rare occasions have shown up with no specific purpose.

To boil it down we will be leveraging the fact that SCCM can track users primary devices and thereby maintaining a relationship between devices and users. This method will allow us to query the database for the following information;

All the devices for users belonging to AD-group X.

Create a collection with the below query and this will allow you to provision software to users, but target their computers.

select SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name,
SMS_R_SYSTEM.SMSUniqueIdentifier,
SMS_R_SYSTEM.ResourceDomainORWorkgroup,
SMS_R_SYSTEM.Client
from SMS_R_System
JOIN SMS_UserMachineRelationship ON
SMS_R_System.Name=SMS_UserMachineRelationship.ResourceName
JOIN SMS_R_User ON
SMS_UserMachineRelationship.UniqueUserName = SMS_R_User.UniqueUserName
WHERE   SMS_UserMachineRelationship.Types=1
AND   SMS_R_User.UserGroupName="DOMAIN\\AD-GROUP"