Friday, May 23, 2014

Server in which SCCM Collection?

Most servers in SCCM would be a member in a couple collections and it would be pretty challenging to track which collections it is a member of.
The following SQL query will enable you just to do that 

select v_FullCollectionMembership.CollectionID, v_Collection.Name, v_R_System.Name0 from v_FullCollectionMembership 
JOIN v_R_System on v_FullCollectionMembership.ResourceID = v_R_System.ResourceID 
JOIN v_Collection on v_FullCollectionMembership.CollectionID = v_Collection.CollectionID 
Where v_R_System.Name0='Servername'

** Replace Servername with the servername you are checking against.

Wednesday, May 21, 2014

Cleaning up expired updates in SCCM

In SCCM, it is essential to house keep the update lists to remove expired updates in order to ensure that the compliance is reported correctly.

Of course you can manually go through all the update lists and deployment packages that you have created to remove those but why not use some tools to help you achieve that instead?

The below post is a VBS written by someone in the community which is very useful for this.

I have made minor amendments to the vbs which you can download from here
To use simply download the file and place it in a folder on the SCCM Server.
Open up command prompt and run the command below in the folder where you have placed the vbs

cscript ExpireClear.vbs [-d] -l|-r:<remoteserver> -u:<Username> -p:<password>

Switches defnitions
-d enable debug logging
-l connect to local machine
-r connect to remote machine

If -r is used, the user's credentials has to be provided.

When the vbs completes, you will be prompted to run a full synchronization whihc can be achieve using the below

1) In the Configuration Manager console at the highest site in the hierarchy with an active software update point, navigate to System Center Configuration Manager / Site Database / Computer Management / Software Updates / Update Repository.

2) Right-click Update Repository, and then click Run Synchronization. Software updates synchronization is initiated as soon as possible.
When synchronization is complete, a request is sent to child sites to initiate synchronization.

List Alerts for Specific SCOM Group

There was a query in the Microsoft System Center Community Forum requesting for assistance for a SQL query to list all alerts for a particular SCOM Group within a specific time frame.
I have create the query below and hopefully this will help anyone that needs something similar.
All is needed is to replace those in bold and underlined based on your needs.

select AlertName
,RGV.targetMonitoringObjectDisplayName [Server Name]
,RGV.SourceMonitoringObjectDisplayName [SCOM Group Name]
,AlertDescription [Alert]
,ars.DWCreatedDateTime [Alert Date]
,alt.Priority [Priority]
,alt.Severity [Severity]
      ,day(ars.DWCreatedDateTime) [triggerdate]
    ,month(ars.DWCreatedDateTime) [triggermonth]
      ,year(ars.DWCreatedDateTime) [triggeryear]
       from Alert.vAlertResolutionState ars
inner join Alert.vAlertDetail adt on ars.alertguid = adt.alertguid
inner join Alert.vAlert alt on ars.alertguid = alt.alertguid
left join dbo.vManagedEntity ME on ME.ManagedEntityRowId = alt.ManagedEntityRowId
left join OperationsManager.dbo.RelationshipGenericView RGV on RGV.TargetMonitoringObjectDisplayName = ME.DisplayName
where ars.DWCreatedDateTime between 'start date (mm/dd/yy)' and  'end date (mm/dd/yy)'
and ME.DisplayName in (select TargetMonitoringObjectDisplayName
from OperationsManager.dbo.RelationshipGenericView
where isDeleted=0
AND SourceMonitoringObjectDisplayName = 'Group Name' )