with ItemHierarchy (UniqueID, Name, ParentID, Path, Level) as ( select fol.UniqueID, fol.Name, fol.ParentID, CAST(fol.Name as varchar(max)), 0 from FOLDERS fol where fol.ParentID IS NULL union all select fol.UniqueID, fol.Name, fol.ParentID, CAST(par.Path + '\' + fol.Name AS varchar(max)), par.Level + 1 from FOLDERS fol inner join ItemHierarchy par on fol.ParentID = par.UniqueID ) select pol.Name as 'PolicyName', ith.Name as 'FolderName', ith.Path as 'Folder Path' from POLICIES pol inner join ItemHierarchy ith on pol.ParentID = ith.UniqueID where pol.Name like '%RunbookName%'
Thursday, September 26, 2019
SCORCH: Find runbook in folder structure
When you know a runbook exists but you can't find the folder it resides in, you can try this SQL query.
To retrieve the complete path from the root of the folder structure down to the runbook, i used a Common Table Expression (CTE).
It works like a recursive function.
Labels:
Code,
Orchestrator,
SCORCH,
SQL
Wednesday, February 28, 2018
SCCM: Copy product categories from WSUS to SUP
For a project where Patch Management was migrated from WSUS to SCCM (SUP with new WSUS) i needed to create a list of product categories and sync them with the categories in SCCM.
This script retrieves a list of selected product categories from an WSUS server and searches for the corresponding category on your Software Update Point. It then will enable the subscription for this specific category.
As a precaution you have to change the scanOnly variable to actually enable this/
Before all this, the script will first create a backup of your current selected categories and export it to a timestamped CSV file.
#requirements
Script
This script retrieves a list of selected product categories from an WSUS server and searches for the corresponding category on your Software Update Point. It then will enable the subscription for this specific category.
As a precaution you have to change the scanOnly variable to actually enable this/
Before all this, the script will first create a backup of your current selected categories and export it to a timestamped CSV file.
#requirements
- SCCM console installed
- WSUS console installed
Script
Import-Module "$($ENV:SMS_ADMIN_UI_PATH)\..\ConfigurationManager.psd1" # Import the ConfigurationManager.psd1 module $SiteCode = Get-PSDrive -PSProvider CMSITE Set-Location "$($SiteCode.Name):\" $supCatBackupPath = "C:\SCCM_SUP_Subscribed_ProductCategories_$(Get-Date -Format 'yyyyMMdd_HHmmss').csv" $onlyScan = $true #Change this to $False if you are sure to change the Software Update Point categories. #get current selected SUP categories $subscribedCats = Get-CMSoftwareUpdateCategory -Fast | ? { $_.IsSubscribed -And $_.CategoryTypeName -eq "ProductFamily" -Or $_.CategoryTypeName -eq "Product" } #backup to CSV file $subscribedCats | Export-CSV -Path $supCatBackupPath -Delimiter ";" $wsusserver = "myWSUSserver" $wsusport = 8530 [reflection.assembly]::LoadWithPartialName("Microsoft.UpdateServices.Administration") | Out-Null $wsus = [Microsoft.UpdateServices.Administration.AdminProxy]::getUpdateServer($wsusserver,$False,$wsusport) $wsusSubscription = $wsus.GetSubscription() $selectedProducts = $wsusSubscription.GetUpdateCategories() | Select Title $selectedProducts $selectedProducts.Title | % { $_ $supCat = Get-CMSoftwareUpdateCategory -Fast -Name "$_" if($supCat.IsSubscribed -eq $False) { Write-Host -ForegroundColor Red "Not subscribed" if($onlyScan -eq $False) { $supCat.IsSubscribed = $true $supCat.Put() } else { Write-Host "scan only, not changing..." } } else { Write-Host "Already subscribed" } }
Labels:
PowerShell,
SCCM,
Software Updates,
WSUS
Thursday, January 18, 2018
SCCM - Download Software Updates with PowerShell
After you setup your new SCCM environment, it can be time consuming to download all Software Updates and place them in the right deployment package.
This is an example of how you could automate that with PowerShell.
It will download all software updates contained in the given Software Update Group and will save and download them to an already created deployment package.
$sugDeployment = "Deployment Group - 2017 - Microsoft Updates - Non-OS" $dpkg = "Deployment Package - 2017 - Microsoft Updates - Non-OS" $supLanguages = @("English","Dutch") Get-CMSoftwareUpdateGroup -Name $sugDeployment | Save-CMSoftwareUpdate -DeploymentPackageName $dpkg -Verbose -SoftwareUpdateLanguage $supLanguagesCreating a deployment package with PowerShell is also possible, but remember you need to create the path on the filesystem yourself.
Labels:
Code,
PowerShell,
SCCM,
Software Updates
Monday, December 18, 2017
SCCM: Query collection membership for specific system
Beneath you find a WQL query for listing all collection memberships for a specific system in SCCM
I got some examples of the internet which didn't work for me. This query does and uses a prompt asking for a system resource name.
select COL.Name from SMS_Collection as COL JOIN SMS_FullCollectionMembership as COLMEM on COL.CollectionID = COLMEM.CollectionID where COLMEM.Name = ##PRM:SMS_FullCollectionMembership.Name## order by COL.NameYou can use this query within SCCM solely as a Query or as a collection membership query. For SQL queries and reports take a look here
Labels:
Collections,
SCCM,
WQL
Thursday, December 14, 2017
PowerShell: Encapsulate 64 bit cmdlet in a 32 bit context
Found a rather old but nice tip from Neil Peterson to tackle a challenge executing Powershell 64 bit cmdlets from a 32 bit execution context (Orchestrator in his example).
Just wanted to share this and add it to my own blog for enhancing my toolbox.
#living in a 32 bit universe #starting PowerShell 'sysnative' version and thus 64 bit #more info: https://msdn.microsoft.com/en-us/library/windows/desktop/aa384187(v=vs.85).aspx $ClusterNodes = .$env:windir\sysnative\WindowsPowerShell\v1.0\powershell.exe { #living in a 64 bit universe Get-ClusterNode -Cluster clustername }
Labels:
Code,
Orchestrator,
PowerShell
Monday, July 3, 2017
SCOM: Using [bracket] NoteProperties in PowerShell
What are those brackets [] in PowerShell!!??
When you query class instances, you'll see a numer of properties available of the type 'NoteProperty'. Direct or inherited from parent classes. To use or filter on these class instance properties in PowerShell, you need to use a specific syntax.
You either enclose with single quotes or escape with backtick. In this example i'm using an instance of the 'Windows Server' class from one of the default SCOM management packs. This class inherits the property 'IPAddress' from the base class 'Windows Computer'.
Here are the useable options for PowerShell:
You either enclose with single quotes or escape with backtick. In this example i'm using an instance of the 'Windows Server' class from one of the default SCOM management packs. This class inherits the property 'IPAddress' from the base class 'Windows Computer'.
Here are the useable options for PowerShell:
#Get all available properties Get-SCOMClass -DisplayName "Windows Server" | Get-SCOMClassInstance | Get-Member ... [Microsoft.Windows.Computer].ActiveDirectoryObjectSid [Microsoft.Windows.Computer].ActiveDirectorySite [Microsoft.Windows.Computer].DNSName [Microsoft.Windows.Computer].DomainDnsName [Microsoft.Windows.Computer].ForestDnsName [Microsoft.Windows.Computer].HostServerName [Microsoft.Windows.Computer].IPAddress [Microsoft.Windows.Computer].IsVirtualMachine [Microsoft.Windows.Computer].LastInventoryDate [Microsoft.Windows.Computer].LogicalProcessors [Microsoft.Windows.Computer].NetbiosComputerName [Microsoft.Windows.Computer].NetbiosDomainName [Microsoft.Windows.Computer].NetworkName [Microsoft.Windows.Computer].OffsetInMinuteFromGreenwichTime [Microsoft.Windows.Computer].OrganizationalUnit [Microsoft.Windows.Computer].PhysicalProcessors [Microsoft.Windows.Computer].PrincipalName [Microsoft.Windows.Computer].VirtualMachineName ... # # Using a NoteProperty # ** Where-Object / ForEach-Object clause ** { $_.'[Microsoft.Windows.Computer].IPAddress'.Value } # ** Select-Object ** Select-Object ``[Microsoft.Windows.Computer`].IPAddress Select-Object *.IPAddress # ** Change column name or object property name ** # Looks like the syntax for Where-Object, but 'Value' subproperty is not specified! Select-Object @{Expression={$_.'[Microsoft.Windows.Computer].IPAddress'};Label="IP"}
Labels:
NoteProperty,
PowerShell,
SCOM,
Syntax
Wednesday, September 28, 2016
SCCM: Using Cmdlets from SCCM PowerShell Module
If you want to use PowerShell with SCCM, you can do two things.
You can verify this with the command Get-PSDrive command
When the CMSite PowerShell Drive is not available, you can create it yourself and connect to the SCCM site. The only parameter you need is the name of the Site Server which hosts the SMS Provider. This is usually the Primary Site Server.
How to add the CMSite PSDrive manually:
More info:
http://www.hasmug.com/2016/04/25/easily-document-configmgr-client-settings-with-powershell/ (thanks for the csv function!)
http://www.verboon.info/2013/05/powershell-script-to-retrieve-sccm-2012-client-settings/
https://blogs.technet.microsoft.com/enterprisemobility/2013/03/27/powershell-connecting-to-configuration-manager/
- Load the PowerShell through the SCCM Console (left top corner: Connect via Windows PowerShell)
- Load the PowerShell module manually
import-module($Env:SMS_ADMIN_UI_PATH.Substring(0,$Env:SMS_ADMIN_UI_PATH.Length-5) + '\ConfigurationManager.psd1')
You can verify this with the command Get-PSDrive command
Get-PSDrive -PSProvider CMSite
When the CMSite PowerShell Drive is not available, you can create it yourself and connect to the SCCM site. The only parameter you need is the name of the Site Server which hosts the SMS Provider. This is usually the Primary Site Server.
How to add the CMSite PSDrive manually:
New-PSDrive -Name "SCCM" -PSProvider CMSite -Root "<SCCM_SERVER>" cd SCCM:Of course you can use whatever name you want.
More info:
http://www.hasmug.com/2016/04/25/easily-document-configmgr-client-settings-with-powershell/ (thanks for the csv function!)
http://www.verboon.info/2013/05/powershell-script-to-retrieve-sccm-2012-client-settings/
https://blogs.technet.microsoft.com/enterprisemobility/2013/03/27/powershell-connecting-to-configuration-manager/
Labels:
Module,
PowerShell,
SCCM
Monday, May 30, 2016
SCOM: View Folder Path hierarchy (PowerShell)
So we're six years older and still there was a small thing that I still had not fixed.
It concerned the full view folder path of a SCOM Monitoring View.
In the past I wrote numerous PowerShell scripts which involved the views. Now I finally found some time to create a function to get the folder hierarchy of a given View by it's ID (guid).
Why I never thought about solving it like this, i don't know, but it appeared to be not that hard. It's a simple recursive function.
In the future I'll update the existing scripts concerning the User Scopes and will also upload a nice script which shows a complete report about all the dependancies between Management Packs and their objects used in User Roles and Notifications. This comes in handy, when you want to phase out management packs but don't know whether there are User Roles and Notification Subscriptions involved.
Enjoy.
In the past I wrote numerous PowerShell scripts which involved the views. Now I finally found some time to create a function to get the folder hierarchy of a given View by it's ID (guid).
Why I never thought about solving it like this, i don't know, but it appeared to be not that hard. It's a simple recursive function.
In the future I'll update the existing scripts concerning the User Scopes and will also upload a nice script which shows a complete report about all the dependancies between Management Packs and their objects used in User Roles and Notifications. This comes in handy, when you want to phase out management packs but don't know whether there are User Roles and Notification Subscriptions involved.
Enjoy.
- $computerName should have a name of a valid Management Server
- $viewId needs to have a valid guid of an existing view in your SCOM environment.
New-SCOMManagementGroupConnection -ComputerName $computerName $mg = Get-SCOMManagementGroup function GetFolderHierarchy($folderId,$folderpath) { $parentfolderid = $null $tmpfolder = $mg.GetMonitoringFolder($folderId) $tmpfolderdisplayname = $tmpfolder.DisplayName if ($folderpath -eq "" -Or $folderpath -eq $null) { $folderpath = $tmpfolderdisplayname } else { $folderpath = $tmpfolderdisplayname + "\" + $folderpath } $parentfolderid = $tmpfolder.ParentFolder.id.Guid if ($parentfolderid -ne "" -And $parentfolderid -ne $null -And $tmpfolder.name -ne "Microsoft.SystemCenter.Monitoring.ViewFolder.Root") { GetFolderHierarchy $parentfolderid $folderpath } else { return $folderpath } } function GetViewHierarchy($viewId) { $tmpview = $mg.GetMonitoringView($viewId) $parentfolderid = $tmpview.ParentFolderIds.Guid | Select -First 1 if($parentfolderid -ne "" -And $parentfolderid -ne $null) { $fullpath = GetFolderHierarchy $parentfolderid return $fullpath + "\" + $tmpview.DisplayName } } GetViewHierarchy $viewId
Labels:
Automation,
Code,
PowerShell,
SCOM,
User Role,
Views
Subscribe to:
Posts (Atom)