Fabio Costa - ConfigMgr Blog

Profissional System Center

Script em PowerShell para automatizar a criação de Collections para o Configuration Manager 2012 (ATUALIZADO EM 01/03/2016)

Disponibilizei um script PowerShell para automatizar a criação de Collections para administração Configuration Manager 2012 com uma manutenção bem simples.

IMPORTANTE: Faça a execução sempre em um ambiente de testes, homologação e após sua total validação programe sua execução.

 

Copie o código abaixo e salve o mesmo como exemplo MSFT-Collections.ps1

##########################################################################

# SAMPLE POWERSHELL SCRIPT #

##########################################################################

# Removes all text from the current display

Clear-Host

# Type the path from CSV file

$SourcePath = Read-Host -Prompt “Type the path from CSV file”
$SourceFile = Read-Host -Prompt “Type the filename using CSV extension”

# Import the Configuration Manager PS Module (You must have the Admin Console installed for this to work)

Import-module ($Env:SMS_ADMIN_UI_PATH.Substring(0,$Env:SMS_ADMIN_UI_PATH.Length-5) + ‘\ConfigurationManager.psd1’) -force

# Get ConfigMgr Provider

$PSD = Get-PSDrive -PSProvider CMSite

# Open ConfigMgr Site

CD “$($PSD.Name):\”

# Get current date and time

$Date = Get-Date -Format g

# Set Update Schedule for Collections

$Schedule = New-CMSchedule -Start $Date -RecurInterval Days -RecurCount 1

# Create Default limiting collections

$LimitingCollection = “All Systems”

# Create Defaut Folder

$DefaultFolderName = “MSFT”

if (!(Test-Path “$($PSD.Name):\DeviceCollection\$DefaultFolderName”)){

New-Item -Name $DefaultFolderName -Path “$($PSD.Name):\DeviceCollection”

}

# Import Collections from CSV List

Import-Csv -Path $SourcePath\$SourceFile | ForEach-Object {

# Create device collections

$getCollection = Get-CMDeviceCollection -Name $_.CollectionName

# Check if collection exists

if ($getCollection -eq $null){
New-CMDeviceCollection -Name $_.CollectionName -LimitingCollectionName $LimitingCollection -RefreshSchedule $Schedule -RefreshType Periodic

# Create device collections

Add-CMDeviceCollectionQueryMembershipRule -CollectionName $_.CollectionName -QueryExpression $_.Query -RuleName $_.CollectionName

# Move the collection to the right folder

$FolderPath = “$($PSD.Name):\DeviceCollection\$($DefaultFolderName)”

Move-CMObject -FolderPath $FolderPath -InputObject (Get-CMDeviceCollection -Name $_.CollectionName)

$cont = $cont+1

$cont

}
}

##########################################################################

##########################################################################

Crie um arquivo como exemplo MSFT-CollectionsList.csv: Este arquivo contém a informação como Nome das Collections a esquerda, e query WQL na direita.

CollectionName,Query
All Obsolete Records,”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 where SMS_R_System.Obsolete = 1 or SMS_R_System.Decommissioned = 1″
All Clients with Duplicated Netbios Name (Non-Client Record),”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 where SMS_R_System.ResourceId in (select Group1.ResourceID from SMS_R_System as Group1 full join SMS_R_System as Group2 on Group2.NetbiosName = Group1.NetbiosName where (Group1.ResourceId != Group2.ResourceId) AND (Group2.Client = 1) AND (Group1.Client is NULL or Group1.Client = 0))”
All Clients with Duplicated Netbios Name (Oldest HB Record),”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 where SMS_R_System.ResourceId in (select Group1.ResourceID from SMS_R_System as Group1 full join SMS_R_System as Group2 on Group2.NetbiosName = Group1.NetbiosName where (Group1.ResourceId != Group2.ResourceId) AND (Group1.AgentTime < Group2.AgentTime) AND (Group2.AgentName = ‘Heartbeat Discovery’) and (Group1.AgentName = ‘Heartbeat Discovery’))”
All Clients with Duplicated Netbios Name,”select R.ResourceID,R.ResourceType,R.Name,R.SMSUniqueIdentifier,R.ResourceDomainORWorkgroup,R.Client from SMS_R_System as r full join SMS_R_System as s1 on s1.ResourceId = r.ResourceId full join SMS_R_System as s2 on s2.Name = s1.Name where s1.Name = s2.Name and s1.ResourceId != s2.ResourceId and R.Client = null”
All Clients Filtered Due To No Recent Active Directory Data,”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 where ResourceID in ( select ResourceID from SMS_R_System where SMS_R_System.AgentName = ‘SMS_AD_SYSTEM_DISCOVERY_AGENT’ and SMS_R_System.AgentTime <= DateAdd(dd, -7, getdate()))”
All Clients with CCMEval – Failed Status,”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 where resourceid in (select resourceid from SMS_CH_EvalResult where SMS_CH_EvalResult.Result in (3,4,5) and SMS_CH_EvalResult.EvalTime >= DateAdd(dd, -7, getdate()))”
All Clients with CCMEval – No Status,”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 where resourceid in (select resourceid from SMS_CH_EvalResult where SMS_CH_EvalResult.Result = 1 and SMS_CH_EvalResult.EvalTime >= DateAdd(dd, -7, getdate()))”
All Clients with CCMEval – Inactive Status,”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 inner join SMS_G_System_CH_ClientSummary on SMS_G_System_CH_ClientSummary.ResourceID = SMS_R_System.ResourceId where SMS_G_System_CH_ClientSummary.ClientActiveStatus = 0″
All Clients with Heartbeat missing or Greater than 7 Days,”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 where SMS_R_System.ResourceId in (select ResourceID from SMS_R_System where (SMS_R_SYSTEM.AgentTime <= DateAdd(dd,-8,getdate())) and AgentName = ‘Heartbeat Discovery’) and SMS_R_System.ResourceId NOT in (select ResourceID from SMS_R_System where (SMS_R_SYSTEM.AgentTime > DateAdd(dd,-8,getdate())) and AgentName = ‘Heartbeat Discovery’) and SMS_R_System.ResourceId in (select ResourceId from SMS_G_System_CH_ClientSummary Where ClientActiveStatus = 1)”
All Clients Not-Approved,”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 inner join SMS_CM_RES_COLL_SMS00001 on SMS_CM_RES_COLL_SMS00001.ResourceId = SMS_R_System.ResourceId where SMS_CM_RES_COLL_SMS00001.IsApproved <> 1″
All Clients Not Installed and Error 112 No Disk Space during Client Push,”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 where SMS_R_System.Name in (Select InsStrValue from SMS_StatusMessage as stat  left outer join SMS_StatMsgAttributes as att  on stat.recordid = att.recordid  left outer join SMS_StatMsgInsStrings as ins  on stat.recordid = ins.recordid  WHERE (COMPONENT=’SMS_CLIENT_CONFIG_MANAGER’)  AND MessageID = 3014 AND Win32Error = 112 AND stat.Time >= DateAdd(dd, -7, getdate())) and SMS_R_System.ResourceId not in (select SMS_R_SYSTEM.ResourceID from SMS_R_System where SMS_R_System.Client = 1)”
All Clients Not Installed and Error 5 Access Denied during Client Push,”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 where SMS_R_System.Name in (Select InsStrValue from SMS_StatusMessage as stat  left outer join SMS_StatMsgAttributes as att  on stat.recordid = att.recordid  left outer join SMS_StatMsgInsStrings as ins  on stat.recordid = ins.recordid  WHERE (COMPONENT=’SMS_CLIENT_CONFIG_MANAGER’)  AND MessageID = 3014 AND Win32Error = 5 AND stat.Time >= DateAdd(dd, -7, getdate())) and SMS_R_System.ResourceId not in (select SMS_R_SYSTEM.ResourceID from SMS_R_System where SMS_R_System.Client = 1)”
All Clients Not Installed and Not Assigned,”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 where SMS_R_System.ResourceId not in (select SMS_R_SYSTEM.ResourceID from SMS_R_System where SMS_R_System.Client = 1) and SMS_R_System.SMSAssignedSites is null and SMS_R_System.ResourceId in (select ResourceID from SMS_R_System where AgentName in (“”SMS_AD_SYSTEM_DISCOVERY_AGENT””) and AgentTime >= DateAdd(dd, -7, getdate()))”
All Non-Clients Records,”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 where SMS_R_System.Client = 0 OR SMS_R_System.Client is NULL”
All Clients Not Receiving Policy (Invalid Signature),”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 where SMS_R_SYSTEM.SMSUniqueIdentifier in (select instr.insstrvalue from SMS_StatusMessage as sm join SMS_StatInsStr as instr on sm.recordid=instr.recordid where sm.messageid = 5448 and instr.insstrvalue like ‘GUID%’ and sm.Time >= DateAdd(dd,-7,getdate()))”
All Clients missing Hardware Inventory last 7 Days or Null,”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 left join SMS_G_System_WORKSTATION_STATUS on SMS_G_System_WORKSTATION_STATUS.ResourceId = SMS_R_System.ResourceId where SMS_R_SYSTEM.Client=1 AND (DATEDIFF(dd, SMS_G_System_WORKSTATION_STATUS.LastHardwareScan,GetDate()) > 7 OR SMS_G_System_WORKSTATION_STATUS.LastHardwareScan is NULL)”
All Clients missing Software Inventory last 7 Days or Null,”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 left join SMS_G_System_LastSoftwareScan on SMS_G_System_LastSoftwareScan.ResourceId = SMS_R_System.ResourceId where SMS_R_System.Client=1 AND (DATEDIFF(dd,SMS_G_System_LastSoftwareScan.LastScanDate,GetDate()) > 7 or SMS_G_System_LastSoftwareScan.LastScanDate is NULL)”
All Clients with Activities and Healthy ,”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 inner join SMS_G_System_CH_ClientSummary on SMS_G_System_CH_ClientSummary.ResourceId = SMS_R_System.ResourceId where SMS_R_System.Client = “”1″” and (SMS_G_System_CH_ClientSummary.ClientActiveStatus = 1 and (SMS_G_System_CH_ClientSummary.ClientCheckPass = 1 or SMS_G_System_CH_ClientSummary.ClientCheckPass = 3))”
All Clients with Inactivity and Unhealthy ,”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 inner join SMS_G_System_CH_ClientSummary on SMS_G_System_CH_ClientSummary.ResourceID = SMS_R_System.ResourceId where SMS_R_System.Client = “”1″” and (SMS_G_System_CH_ClientSummary.ClientActiveStatus = 0 or (SMS_G_System_CH_ClientSummary.ClientCheckPass = 2))”

Siga os passos abaixo:

Passo 1: Criar uma pasta na unidade exemplo “C:\MSFT” e copie os 2 arquivos para dentro desta pasta

Passo 2: Executar o arquivo “MSFT-Collections.ps1” com o PowerShell para iniciar a criação das Collections

Poderá receber uma mensagem semelhante a tela abaixo, digite Y para continuar

Passo 3: Digite o caminho e o nome do arquivo CSV, em nosso exemplo para pasta “C:\MSFT”. Ao digitar o nome do arquivo não esqueça de colocar .CSV.

Se o script PowerShell iniciar com sucesso, teremos uma tela semelhante ao print abaixo

Passo 4:  Como resultado devemos ter uma pasta chamada “MSFT” e no painel da direita a estrutura de Collections

Abs!