Windows Azure Pack PowerShell SQL Resource Provider Add SQL Server Hosting Group to Plan

This post equates to the 5th in the series of blog posts for Azure Pack, where we have gone over the process of obtaining, adding, and removing the core components of Azure Pack being the User, Plans and Subscription objects, then we moved onto the process of Adding & removing the SQL Server hosting groups, and there dependent SQL servers in the SQL Resource Provider. So in this post we will complete the circle on the administrative side by adding the newly created SQL Server group to a Plan.

Unlike the past posts where the concepts were quite self-explanatory when attaching the SQL Server hosting group to a Plan, it needs to be completed in a two-step process, the first is to associate the SQL Resource Provider to the Plan, then once it has been attached there is a requirement to allocate the actual resources by using a Quota.

From here your users will be able to create their own databases on the presented SQL Servers.

# query SQL function

function Invoke-SQL {

param([string] $connstring,[string] $sqlCommand)

$connectionString = $connstring

$connection = new-object system.data.SqlClient.SQLConnection($connectionString)

$command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)

$connection.Open()

$adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command

$dataset = New-Object System.Data.DataSet

$adapter.Fill($dataSet) | Out-Null

$connection.Close()

$dataSet.Tables

}

 

# get WAP connection string

$connstring = (Get-MgmtSvcSetting -Namespace adminsite | where {$_.name -eq ‘ApplicationServicesConnectionstring’}).value

$connstring = $connstring.Split(‘;’)[0] + ‘;’ + $connstring.Split(‘;’)[1] + “; Integrated Security=SSPI”

 

# get WAP websites

$adminsite = (Invoke-SQL -sqlCommand “SELECT value FROM [Config].[Settings] where Namespace = ‘AdminSite’ and Name = ‘Authentication.Fqdn'” -connstring $connstring).value

$TenantSite = (Invoke-SQL -sqlCommand “SELECT value FROM [Config].[Settings] where Namespace = ‘TenantSite’ and Name = ‘Authentication.Fqdn'” -connstring $connstring).value

$AuthSite = (Invoke-SQL -sqlCommand “SELECT value FROM [Config].[Settings] where Namespace = ‘AuthSite’ and Name = ‘Authentication.Fqdn'” -connstring $connstring).value

$windowsauthsite = (Invoke-SQL -sqlCommand “SELECT value FROM [Config].[Settings] where Namespace = ‘WindowsAuthSite’ and Name = ‘Authentication.Fqdn'” -connstring $connstring).value

$AdminAPI = (Invoke-SQL -sqlCommand “SELECT value FROM [Config].[Settings] where Namespace = ‘AdminSite’ and Name = ‘Microsoft.Azure.Portal.Configuration.OnPremPortalConfiguration.RdfeAdminUri'” -connstring $connstring).value

$TenantAPI = (Invoke-SQL -sqlCommand “SELECT value FROM [Config].[Settings] where Namespace = ‘TenantSite’ and Name = ‘Microsoft.Azure.Portal.Configuration.AppManagementConfiguration.RdfeUnifiedManagementServiceUri'” -connstring $connstring).value

$ClientRealm = (Invoke-SQL -sqlCommand “SELECT value FROM [Config].[Settings] where Namespace = ‘AdminSite’ and Name = ‘Authentication.RelyingParty'” -connstring $connstring).value.Split(‘,’)[1].replace(‘”Realm”:”‘,).replace(‘”‘,)

 

# Check is cert is signed

$admin = $adminsite.Split(‘:’)

$cert = !(New-Object System.Net.Security.SslStream((New-Object System.Net.Sockets.TcpClient($admin[1].Replace(‘/’,),$admin[2])).GetStream())).IsSigned

 

# get token

$token = Get-MgmtSvcToken -AuthenticationSite $windowsauthsite -ClientRealm $ClientRealm -Type Windows -DisableCertificateValidation:$cert

 

# Add SQL Server Group to Plan

$ResProviderName = ‘sqlservers’

$PlanName = ‘steve’

$plan = Get-MgmtSvcPlan -AdminUri $AdminAPI -Token $token -DisableCertificateValidation:$cert -DisplayName $PlanName

$ResourceProvider = Get-MgmtSvcResourceProvider -AdminUri $AdminAPI -Token $token -DisableCertificateValidation:$cert -Name $ResProviderName

Add-MgmtSvcPlanService -AdminUri $AdminAPI -Token $token -DisableCertificateValidation:$cert -InstanceId $ResourceProvider.InstanceId -ServiceName $ResourceProvider.Name -PlanId $plan.ID

 

# Add SQL Quota to Plan

$ServiceName = ‘sqlservers’

$QuotaList = New-MgmtSvcQuotaList

$ResProvider = Get-MgmtSvcResourceProvider -AdminUri $AdminAPI -Token $token -DisableCertificateValidation:$cert -Name $servicename

$SQLQuota = Add-MgmtSvcListQuota -QuotaList $QuotaList -ServiceName $ServiceName -ServiceInstanceId $ResProvider.InstanceId

$sqlquotares = Add-MgmtSvcQuotaSetting -Quota $SqlQuota -Key Editions -Value ‘[{“displayName”:”Laptop”, “groupName”:”Laptop”, “resourceCount”:”1″, “resourceSize”:”1024″, “resourceSizeLimit”:”1024″, “offerEditionId”:”081313063701″, “groupType”:null}]’

Update-MgmtSvcPlanQuota -AdminUri $AdminAPI -Token $token -QuotaList $QuotaList -PlanId $plan.Id -DisableCertificateValidation:$cert

 

# Remove SQL Server group from plan

$ResProviderName = ‘sqlservers’

$PlanName = ‘steve’

$plan = Get-MgmtSvcPlan -AdminUri $AdminAPI -Token $token -DisableCertificateValidation:$cert -DisplayName $PlanName

remove-MgmtSvcPlanService -adminuri $adminapi -token $token -disablecertificatevalidation:$cert -ServiceName $ResProviderName -PlanId $plan.id

 

# Get list of Services attached to Plan

$PlanName = ‘steve’

$plan = Get-MgmtSvcPlan -AdminUri $AdminAPI -Token $token -DisableCertificateValidation:$cert -DisplayName $PlanName

$plan.ServiceQuotas.settings.Value

I hope this helps and makes sense, please let me know via twitter @steve_hosko if you would like to know any more information around this.

Good Luck

Steve

Windows Azure Pack PowerShell SQL Resource Provider Add Servers & Hosting Group

Here we are again to go over some more commands for automation of Azure Pack, and in this case the addition and or removal of new/old SQL servers to your existing infrastructure, in my previous posts I have gone over the Get, Add, and Removal of Users, Plans, and Subscriptions which are the core components of Azure Pack. For this post I will go over the process of obtaining, the SQL Server Hosting Group, in this case it is a standalone group as Microsoft has not exposed the ability to add a highly available group via PowerShell.

Once we have spent our time working on the SQL Server Hosting group we will move on to adding a new SQL server to the Group, along with the removal of the SQL Server.

Much like the last posts I have included the first 33 lines where we obtain all of the required details for Azure Pack to execute the commands.

# query SQL function

function Invoke-SQL {

param([string] $connstring,[string] $sqlCommand)

$connectionString = $connstring

$connection = new-object system.data.SqlClient.SQLConnection($connectionString)

$command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)

$connection.Open()

$adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command

$dataset = New-Object System.Data.DataSet

$adapter.Fill($dataSet) | Out-Null

$connection.Close()

$dataSet.Tables

}

 

# get WAP connection string

$connstring = (Get-MgmtSvcSetting -Namespace adminsite | where {$_.name -eq ‘ApplicationServicesConnectionstring’}).value

$connstring = $connstring.Split(‘;’)[0] + ‘;’ + $connstring.Split(‘;’)[1] + “; Integrated Security=SSPI”

 

# get WAP websites

$adminsite = (Invoke-SQL -sqlCommand “SELECT value FROM [Config].[Settings] where Namespace = ‘AdminSite’ and Name = ‘Authentication.Fqdn'” -connstring $connstring).value

$TenantSite = (Invoke-SQL -sqlCommand “SELECT value FROM [Config].[Settings] where Namespace = ‘TenantSite’ and Name = ‘Authentication.Fqdn'” -connstring $connstring).value

$AuthSite = (Invoke-SQL -sqlCommand “SELECT value FROM [Config].[Settings] where Namespace = ‘AuthSite’ and Name = ‘Authentication.Fqdn'” -connstring $connstring).value

$windowsauthsite = (Invoke-SQL -sqlCommand “SELECT value FROM [Config].[Settings] where Namespace = ‘WindowsAuthSite’ and Name = ‘Authentication.Fqdn'” -connstring $connstring).value

$AdminAPI = (Invoke-SQL -sqlCommand “SELECT value FROM [Config].[Settings] where Namespace = ‘AdminSite’ and Name = ‘Microsoft.Azure.Portal.Configuration.OnPremPortalConfiguration.RdfeAdminUri'” -connstring $connstring).value

$TenantAPI = (Invoke-SQL -sqlCommand “SELECT value FROM [Config].[Settings] where Namespace = ‘TenantSite’ and Name = ‘Microsoft.Azure.Portal.Configuration.AppManagementConfiguration.RdfeUnifiedManagementServiceUri'” -connstring $connstring).value

$ClientRealm = (Invoke-SQL -sqlCommand “SELECT value FROM [Config].[Settings] where Namespace = ‘AdminSite’ and Name = ‘Authentication.RelyingParty'” -connstring $connstring).value.Split(‘,’)[1].replace(‘”Realm”:”‘,).replace(‘”‘,)

 

# Check is cert is signed

$admin = $adminsite.Split(‘:’)

$cert = !(New-Object System.Net.Security.SslStream((New-Object System.Net.Sockets.TcpClient($admin[1].Replace(‘/’,),$admin[2])).GetStream())).IsSigned

 

# get token

$token = Get-MgmtSvcToken -AuthenticationSite $windowsauthsite -ClientRealm $ClientRealm -Type Windows -DisableCertificateValidation:$cert

 

# Get SQL Server Hosting Group

Get-MgmtSvcSqlServerGroup -AdminUri $AdminAPI -Token $token -DisableCertificateValidation:$cert

 

# Remove SQL Server Hosting Group

$SQLSvrGroupName = ‘Laptop’

$SQLSvrGroup = Get-MgmtSvcSqlServerGroup -AdminUri $AdminAPI -Token $token -DisableCertificateValidation:$cert -GroupName $SQLSvrGroupName

Remove-MgmtSvcSqlServerGroup -AdminUri $AdminAPI -Token $token -DisableCertificateValidation:$cert -ServerGroupId $SQLSvrGroup.GroupId

 

# Add SQL Server Hosting Group

$GRPNAME = ‘Laptop’

Add-MgmtSvcSqlServerGroup -AdminUri $AdminAPI -Token $token -DisableCertificateValidation:$cert -GroupName $GRPNAME -ResourceGovernorEnabled

 

# Get SQL Server

Get-MgmtSvcSqlHostingServer -AdminUri $adminapi -Token $token -DisableCertificateValidation:$cert

 

# Remove SQL Server

$Svrname = ‘desktop-ojt0lg6’

$sqlsvr = Get-MgmtSvcSqlHostingServer -AdminUri $adminapi -Token $token -DisableCertificateValidation:$cert | Where-Object {$_.Name -eq $Svrname}

Remove-MgmtSvcSqlHostingServer -AdminUri $adminapi -token $token -DisableCertificateValidation:$cert -HostingServerId $sqlsvr.ServerId

 

# Add SQL Server

$cred = Get-Credential -UserName ‘SA’ -Message ‘Enter the SA Password for the SQL Server’

$GRPNAME = ‘Laptop’

$SQLSize = 1024

$SQLCPU = 4

$SQLMEM = 4

$SQLIOPS = 2000

$SQLMAXResourcePools = 10

$SQLSVRName = ‘desktop-ojt0lg6’

$svrgrp = Get-MgmtSvcSqlServerGroup -AdminUri $AdminAPI -token $token -DisableCertificateValidation:$cert | Where-Object {$_.GroupName -eq $GRPNAME}

Add-MgmtSvcSqlHostingServer -adminuri $adminapi -Token $token -DisableCertificateValidation:$cert -SqlUser $cred -Name $SQLSVRName -TotalSpaceMB $SQLSize -ServerGroupId $svrgrp.GroupId -NumberOfCpuCores $SQLCPU -TotalMemoryGB $SQLMEM -SupportedIopsPerVolume $SQLIOPS -MaximumResourcePools $SQLMAXResourcePools

This is where I will leave you on this post, but stay turned for future posts around adding the SQL Server hosting group to a Plan.

Good Luck

Steve

Windows Azure Pack PowerShell sample commands – Remove Commands

To close out the series of the simple sample commands for Azure Pack the first being to Get the Second to Add and this one will go over the process of Removing objects from Azure Pack. Much like the first two posts the first 33 lines are the same which obtains the connection details for your Azure Pack environment, along with an authentication token.

Once we have obtained these details we will have the starting point to remove each of the objects we created in the second blog post, this needs to be completed in reverse where the Subscription is required to be removed before the Plan can be removed. Once both have been removed then the User object can be removed.

You will note that for both the subscription and the plan we need to obtain the Subscription ID and Plan ID respectively. Rather than typing these in I have added the command to obtain it from the name of the Subscription and the Plan.

# query SQL function

function Invoke-SQL {

param([string] $connstring,[string] $sqlCommand)

$connectionString = $connstring

$connection = new-object system.data.SqlClient.SQLConnection($connectionString)

$command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)

$connection.Open()

$adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command

$dataset = New-Object System.Data.DataSet

$adapter.Fill($dataSet) | Out-Null

$connection.Close()

$dataSet.Tables

}

 

# get WAP connection string

$connstring = (Get-MgmtSvcSetting -Namespace adminsite | where {$_.name -eq ‘ApplicationServicesConnectionstring’}).value

$connstring = $connstring.Split(‘;’)[0] + ‘;’ + $connstring.Split(‘;’)[1] + “; Integrated Security=SSPI”

 

# get WAP websites

$adminsite = (Invoke-SQL -sqlCommand “SELECT value FROM [Config].[Settings] where Namespace = ‘AdminSite’ and Name = ‘Authentication.Fqdn'” -connstring $connstring).value

$TenantSite = (Invoke-SQL -sqlCommand “SELECT value FROM [Config].[Settings] where Namespace = ‘TenantSite’ and Name = ‘Authentication.Fqdn'” -connstring $connstring).value

$AuthSite = (Invoke-SQL -sqlCommand “SELECT value FROM [Config].[Settings] where Namespace = ‘AuthSite’ and Name = ‘Authentication.Fqdn'” -connstring $connstring).value

$windowsauthsite = (Invoke-SQL -sqlCommand “SELECT value FROM [Config].[Settings] where Namespace = ‘WindowsAuthSite’ and Name = ‘Authentication.Fqdn'” -connstring $connstring).value

$AdminAPI = (Invoke-SQL -sqlCommand “SELECT value FROM [Config].[Settings] where Namespace = ‘AdminSite’ and Name = ‘Microsoft.Azure.Portal.Configuration.OnPremPortalConfiguration.RdfeAdminUri'” -connstring $connstring).value

$TenantAPI = (Invoke-SQL -sqlCommand “SELECT value FROM [Config].[Settings] where Namespace = ‘TenantSite’ and Name = ‘Microsoft.Azure.Portal.Configuration.AppManagementConfiguration.RdfeUnifiedManagementServiceUri'” -connstring $connstring).value

$ClientRealm = (Invoke-SQL -sqlCommand “SELECT value FROM [Config].[Settings] where Namespace = ‘AdminSite’ and Name = ‘Authentication.RelyingParty'” -connstring $connstring).value.Split(‘,’)[1].replace(‘”Realm”:”‘,).replace(‘”‘,)

 

# Check is cert is signed

$admin = $adminsite.Split(‘:’)

$cert = !(New-Object System.Net.Security.SslStream((New-Object System.Net.Sockets.TcpClient($admin[1].Replace(‘/’,),$admin[2])).GetStream())).IsSigned

 

# get token

$token = Get-MgmtSvcToken -AuthenticationSite $windowsauthsite -ClientRealm $ClientRealm -Type Windows -DisableCertificateValidation:$cert

 

# Remove Subscription

$SubscriptionName = ‘Steve’

$subscriptionID = (Get-MgmtSvcSubscription -AdminUri $AdminAPI -Token $token -DisableCertificateValidation:$cert | Where-Object {$_.SubscriptionName -eq $SubscriptionName}).SubscriptionID

Remove-MgmtSvcSubscription -AdminUri $AdminAPI -Token $token -DisableCertificateValidation:$cert -SubscriptionId $subscriptionID

 

# remove Plan

$planName = ‘steve’

$planid = (Get-MgmtSvcPlan -AdminUri $adminapi -Token $token -DisableCertificateValidation:$cert -DisplayName $planName).id

Remove-MgmtSvcPlan -AdminUri $AdminAPI -Token $token -DisableCertificateValidation:$cert -PlanId $planid

 

# remove User

$name = ‘Steve’

Remove-MgmtSvcUser -AdminUri $adminapi -Token $token -DisableCertificateValidation:$cert -Name $name

This concludes the Blog posts on the Simple administrative tasks for Azure Pack, if you have followed each of the blogs that we have found, created and deleted each of the 3 object types, but once the Subscription is associated to a user there are no resources allocated to it. In the next group of Blog posts we will go over the process to allocate resources to the Plan/Subscription. But until then.

Good Luck.

Steve

Windows Azure Pack PowerShell sample commands – Add Commands

To follow on from my last post where I detailed the process of obtaining the basic details from Azure Pack, for this post I will detail the process to Add objects into Azure Pack, for example new user’s, plans & subscriptions.

In the PowerShell script below, you’ll note that the 33 lines are identical to the first 33 lines of the previous post, this ensures that all of the variables we are using will be accessible, as always with WordPress sites make sure that you check the quotation makes when copying and pasting into the ISE. The last 13 lines we create a user object, the email account supplied does not need to exist when using the native Azure Pack tenant portal authentication, this should be swapped out to ADFS or equivalent authentication solutions when in production.

You will also note that when creating the subscription we obtain a variable called $Plan which we then use the ID from for the new Subscription, this saves us the requirement of remembering the ID which looks something like this: 1fc6dc79 which isn’t very easy to recall.

# query SQL function

function Invoke-SQL {

param([string] $connstring,[string] $sqlCommand)

$connectionString = $connstring

$connection = new-object system.data.SqlClient.SQLConnection($connectionString)

$command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)

$connection.Open()

$adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command

$dataset = New-Object System.Data.DataSet

$adapter.Fill($dataSet) | Out-Null

$connection.Close()

$dataSet.Tables

}

 

# get WAP connection string

$connstring = (Get-MgmtSvcSetting -Namespace adminsite | where {$_.name -eq ‘ApplicationServicesConnectionstring’}).value

$connstring = $connstring.Split(‘;’)[0] + ‘;’ + $connstring.Split(‘;’)[1] + “; Integrated Security=SSPI”

 

# get WAP websites

$adminsite = (Invoke-SQL -sqlCommand “SELECT value FROM [Config].[Settings] where Namespace = ‘AdminSite’ and Name = ‘Authentication.Fqdn'” -connstring $connstring).value

$TenantSite = (Invoke-SQL -sqlCommand “SELECT value FROM [Config].[Settings] where Namespace = ‘TenantSite’ and Name = ‘Authentication.Fqdn'” -connstring $connstring).value

$AuthSite = (Invoke-SQL -sqlCommand “SELECT value FROM [Config].[Settings] where Namespace = ‘AuthSite’ and Name = ‘Authentication.Fqdn'” -connstring $connstring).value

$windowsauthsite = (Invoke-SQL -sqlCommand “SELECT value FROM [Config].[Settings] where Namespace = ‘WindowsAuthSite’ and Name = ‘Authentication.Fqdn'” -connstring $connstring).value

$AdminAPI = (Invoke-SQL -sqlCommand “SELECT value FROM [Config].[Settings] where Namespace = ‘AdminSite’ and Name = ‘Microsoft.Azure.Portal.Configuration.OnPremPortalConfiguration.RdfeAdminUri'” -connstring $connstring).value

$TenantAPI = (Invoke-SQL -sqlCommand “SELECT value FROM [Config].[Settings] where Namespace = ‘TenantSite’ and Name = ‘Microsoft.Azure.Portal.Configuration.AppManagementConfiguration.RdfeUnifiedManagementServiceUri'” -connstring $connstring).value

$ClientRealm = (Invoke-SQL -sqlCommand “SELECT value FROM [Config].[Settings] where Namespace = ‘AdminSite’ and Name = ‘Authentication.RelyingParty'” -connstring $connstring).value.Split(‘,’)[1].replace(‘”Realm”:”‘,).replace(‘”‘,)

 

# Check is cert is signed

$admin = $adminsite.Split(‘:’)

$cert = !(New-Object System.Net.Security.SslStream((New-Object System.Net.Sockets.TcpClient($admin[1].Replace(‘/’,),$admin[2])).GetStream())).IsSigned

 

# get token

$token = Get-MgmtSvcToken -AuthenticationSite $windowsauthsite -ClientRealm $ClientRealm -Type Windows -DisableCertificateValidation:$cert

 

# Add new User

$Email = ‘steve@laptop.com’ #user email address

$name = ‘Steve’ # Name of user

Add-MgmtSvcUser -AdminUri $AdminAPI -Email $Email -name $name -Token $token -DisableCertificateValidation:$cert

 

# Add Plan

$displayname = ‘steve’ # Name of the Plan

Add-MgmtSvcPlan -AdminUri $adminapi -Token $token -DisableCertificateValidation:$cert -DisplayName $displayname

 

# Add Subscription

$SubscriptionName = ‘Steve’ # Name of the Subscription

$PlanName = ‘Steve’ # Name of the Plan

$Email = ‘steve@laptop.com’ #user email address

$uname = ‘Steve’ # Name of user

$plan = get-MgmtSvcPlan -AdminUri $adminapi -Token $token -DisableCertificateValidation:$cert -DisplayName $Planname

Add-MgmtSvcSubscription -AdminUri $AdminAPI -Token $token -DisableCertificateValidation:$cert -AccountAdminLiveEmailId $Email -AccountAdminLivePuid $uname -PlanId $plan.id -FriendlyName $SubscriptionName

 

Like previously this isn’t an exhaustive list of commands but a starting point.

Good Luck Steve

Windows Azure Pack PowerShell sample commands – Get Commands

Over the past 12 months I’ve been working less on SCCM and more in Windows Azure Pack, to help integrate a complex database as a service solution. One of the things I have found over this time is there is limited information around how to automate using PowerShell, along with quite a number assumptions made in provided documentation. To this end I have spent a couple of hours writing out some useful PowerShell commands to obtain the commonly used settings of a Azure Pack environment for example the websites and ports which go with each of the websites. I’ll note that while the native Azure Pack PowerShell can obtain this information on a WAP express install, it doesn’t return the information in a dispersed configuration which is the recommended deployment for production usage of Azure Pack. So I have written up the following code to help out everybody.

# query SQL function

function Invoke-SQL {

param([string] $connstring,[string] $sqlCommand)

$connectionString = $connstring

$connection = new-object system.data.SqlClient.SQLConnection($connectionString)

$command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)

$connection.Open()

$adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command

$dataset = New-Object System.Data.DataSet

$adapter.Fill($dataSet) | Out-Null

$connection.Close()

$dataSet.Tables

}

 

# get WAP connection string

$connstring = (Get-MgmtSvcSetting -Namespace adminsite | where {$_.name -eq ‘ApplicationServicesConnectionstring’}).value

$connstring = $connstring.Split(‘;’)[0] + ‘;’ + $connstring.Split(‘;’)[1] + “; Integrated Security=SSPI”

 

# get WAP websites

$adminsite = (Invoke-SQL -sqlCommand “SELECT value FROM [Config].[Settings] where Namespace = ‘AdminSite’ and Name = ‘Authentication.Fqdn'” -connstring $connstring).value

$TenantSite = (Invoke-SQL -sqlCommand “SELECT value FROM [Config].[Settings] where Namespace = ‘TenantSite’ and Name = ‘Authentication.Fqdn'” -connstring $connstring).value

$AuthSite = (Invoke-SQL -sqlCommand “SELECT value FROM [Config].[Settings] where Namespace = ‘AuthSite’ and Name = ‘Authentication.Fqdn'” -connstring $connstring).value

$windowsauthsite = (Invoke-SQL -sqlCommand “SELECT value FROM [Config].[Settings] where Namespace = ‘WindowsAuthSite’ and Name = ‘Authentication.Fqdn'” -connstring $connstring).value

$AdminAPI = (Invoke-SQL -sqlCommand “SELECT value FROM [Config].[Settings] where Namespace = ‘AdminSite’ and Name = ‘Microsoft.Azure.Portal.Configuration.OnPremPortalConfiguration.RdfeAdminUri'” -connstring $connstring).value

$TenantAPI = (Invoke-SQL -sqlCommand “SELECT value FROM [Config].[Settings] where Namespace = ‘TenantSite’ and Name = ‘Microsoft.Azure.Portal.Configuration.AppManagementConfiguration.RdfeUnifiedManagementServiceUri'” -connstring $connstring).value

$ClientRealm = (Invoke-SQL -sqlCommand “SELECT value FROM [Config].[Settings] where Namespace = ‘AdminSite’ and Name = ‘Authentication.RelyingParty'” -connstring $connstring).value.Split(‘,’)[1].replace(‘”Realm”:”‘,).replace(‘”‘,)

 

# Check is cert is signed

$admin = $adminsite.Split(‘:’)

$cert = !(New-Object System.Net.Security.SslStream((New-Object System.Net.Sockets.TcpClient($admin[1].Replace(‘/’,),$admin[2])).GetStream())).IsSigned

 

# get token

$token = Get-MgmtSvcToken -AuthenticationSite $windowsauthsite -ClientRealm $ClientRealm -Type Windows -DisableCertificateValidation:$cert

 

# get admin users

Get-MgmtSvcAdminUser -ConnectionString $connstring.Replace(“PortalConfigStore”,“Store”)

 

# get users

Get-MgmtSvcUser -AdminUri $AdminAPI -Token $token -DisableCertificateValidation:$cert

 

# get plans

Get-MgmtSvcPlan -AdminUri $adminapi -Token $token -DisableCertificateValidation:$cert

 

# get Subscriptions

Get-MgmtSvcSubscription -AdminUri $AdminAPI -Token $token -DisableCertificateValidation:$cert

*make sure you check the quotes when copying the PowerShell commands

While this isn’t an exhaustive list of commands it covers off the basics which will get anybody new to the platform and PowerShell a leg up to obtain the information of the solution.

You will note that I’m using SQL queries to obtain the websites and ports as I found it was more robust to complete the process this way.

Good luck

Steve

Automated updates of Devolution’s Remote Desktop Manager using SCCM

Recently I have been spending time going back to basic’s around automation in SCCM with PowerShell to brush up on my skills. As part of this I spent a few days playing around with migrating to use Visual Studio as my development space for PowerShell with Adam Driscoll’s PowerShell Tools for Visual Studio add in, which I can’t recommend highly enough when tied to TFS online, Version becomes a super simple task of checking your code in rather than saving different versions.

So I have my Dev space all setup, and went to connect to my Lab Servers Via Devolutions Remote Desktop Manager and got this Prompt

Which annoyed me to no end as I only installed it 3 weeks ago and it appears there had already been 2 version changes. So rather than just disabling the prompt to check for updates as I would normally do on RDM, I started investigating how I can auto mate the updates of RDM without paying for a Third Party tool, and still have the latest version available from my SCCM Server.

So first things first we need to find out how the Application checks to see if there are any updated versions available, for this we can use Fiddler which is a great tool that allows to capture the Http/Https traffic leaving your computer.

Which presented us with these links:

http://remotedesktopmanager.com/products.htm

http://remotedesktopmanager.com/data/RdmChangeHistoryUpdate.htm

From here we browse to each and can see that http://remotedesktopmanager.com/products.htm returns this:

Which we can obtain the current version for each of the versions of RDM, along with where to download the application exe file from. Right about now I was thinking this is going to be a cake walk we can just step through each of the lines in the htm file and select only the ones which we need.

So we grab the web address and use the following script to obtain the htm page into a PowerShell Variable:

$wc = New-Object Net.WebClient

$srdm = $wc.DownloadString(“http://remotedesktopmanager.com/products.htm”)

And when we run $srdm in the ISE to see the list is looks like this:

This should be super easy, I’m still thinking we can use a simple foreach on $srdm like I would on a normal multi line string. I try it, and I get the all of the information back, which I must say threw me for a minute, so I tried

$srdm.count

With the following result:

I dig deeper into $srdm and find that the end of the line is a line break rather than a carriage Return as I was expecting, so after some quick research found that we can split $srdm using $srdm.Split(“`r`n”) which will separate it on the line break’s rather than the carriage returns. You can see if we now run the following command $srdm.Split(“`r`n”).count that the number is vastly larger than 1 which was the previous result

So we can now use the simple foreach to step through each line in the variable, which will look like this:

$RDMVerChk = “RDMEnterprise”

$wc = New-Object Net.WebClient

$srdm = $wc.DownloadString(“http://remotedesktopmanager.com/products.htm”)

$out = @()

foreach($rdm in $srdm.Split(“`r`n”).count)

{

$obj = New-Object PSObject

if ($rdm -like “*$RDMVerChk*”)

{

$s = $rdm.Split(“=”)

$obj | Add-Member Noteproperty Name $s[0].split(“.”)[1]

$obj | Add-Member Noteproperty Value $s[1]

$out += $obj

}

}

You will see that I have also added a check only return the strings which contain RDMEnterprise as this is the version of RDM we use, you can change the $RDMVerChk variable to the required version. The next step is to create new PSobject to capture the results into a simple Array for use later in the script.

Now the next step is to check in SCCM to check if the version available from the RDM website is currently available, and if it isn’t to publish it.

To start we need to import the Configuration Manager PowerShell module with the following command:

Import-Module $env:SMS_ADMIN_UI_PATH.Replace(“i386”,“ConfigurationManager.psd1”) -Force

Once imported we need to set the current PowerShell location to the SCCM SIteCode for this example P01

Set-Location “P01:”

Now we can completed a simple Get-CMApplication to see if the RDM Version exists, we will use the expected name of the application we create during running this script which is “Remote_Desktop_Manager-$rdmver where $rdmver is the RDM version from the Website.

$rdmver = $out | ForEach-Object {if ($_.Name -eq “Version”){$_.Value}}

if((Get-CMApplication -Name “Remote_Desktop_Manager-$rdmver))

{

write-output “Already Available”

}

else

{

write-output “Needs to be created”

}

Great now we just need to add the application to SCCM, and make it available to users via the Application Catalog. To do this we use the following script:

#download File

$wc.DownloadFile(($out | ForEach-Object {if ($_.Name -eq “exe”){$_.Value}}), $path\RDM.exe”)

#Create RDM folder and Extract MSI from exe

if(!(Test-Path -path $Path\RDM”)){New-Item -Path $Path\RDM” -Force -ItemType Directory | Out-Null}

Start-Process -FilePath $path\RDM.exe” -ArgumentList “/extract:$path\RDM” -Wait

# Extract .ico file from the RDM.exe

[System.Drawing.Icon]::ExtractAssociatedIcon($path\RDM.exe”).ToBitmap().save($path\RDM\RDM.ico”)

$path = $path\RDM”

$InstallFile = (Get-ChildItem -Path $path | where {$_.name -Like “*.msi”}).name

# Create Folders and move files around

if(!(Test-Path -path $DestPath\applications”)){New-Item -Path $DestPath\applications” -Force -ItemType Directory | Out-Null}

if(!(Test-Path -path $DestPath\applications\$prodman)){New-Item -Path $DestPath\applications\$prodman -Force -ItemType Directory | Out-Null}

if(!(Test-Path -path $DestPath\applications\$prodman\$prodname$rdmver)){New-Item -Path $DestPath\applications\$prodman\$prodname$rdmver -Force -ItemType Directory | Out-Null}

Copy-Item $path\*” $DestPath\applications\$prodman\$prodname$rdmver

# Create SCCM Application

Set-Location $Sitecode`:”

# get existing Deployment Types to be SuperSeeded

$oldappdt = Get-CMDeploymentType -ApplicationName $prodname*”

$newapp = New-CMApplication -Name $prodname$rdmver -Publisher ($prodman.replace(” “,“_”)) -SoftwareVersion $rdmver -IconLocationFile $path\rdm.ico” -LocalizedApplicationName ($prodname.replace(” “,“_”))

Add-CMDeploymentType -MsiInstaller -InstallationFileLocation $DestPath\applications\$prodman\$prodname$rdmver\$installfile -Application $newapp -ForceForUnknownPublisher:$true

# Create SuperSeedence for each existing Deployment Type

foreach ($dt in $oldappdt)

{

# You need to get the new Deployment Type each time as it changes when new SuperSeedences are added.

$appdt = Get-CMDeploymentType -ApplicationName $prodname$rdmver

Add-CMDeploymentTypeSupersedence -SupersedingDeploymentType $dt -IsUninstall $true -SupersededDeploymentType $appdt

}

# Disribution Content to a DP (this can be changed to DP Group as needed)

Start-CMContentDistribution -Application $newapp -DistributionPointName $DP

# Create New User collection for deployment, defineing the limiting collection and the collection to include (Can be changed to Query As required)

$newcol = New-CMUserCollection -Name “Install $ProdMan $Prodname $rdmver -LimitingCollectionName $LimitingCol

Add-CMUserCollectionIncludeMembershipRule -Collection $newcol -IncludeCollectionName $IncCol

# deploy New Application to New Collection

Start-CMApplicationDeployment -CollectionName ($newcol.Name) -Name ($newapp.LocalizedDisplayName)

As you can see it is pretty much broken up into 3 phases, Download & Prepare the file, Confirm if folders exist on Media Store & copy extracted files up to the new folders, And create SCCM Application, Supersede old versions & deploy the new application to a New Collection. I will focus on the first 2 phase’s as they contain the interesting parts, while the last phase is using the default create collection & deployment commands for SCCM.

Download phase, we obtain a copy of the file using this command:

$wc.DownloadFile(($out | ForEach-Object {if ($_.Name -eq “exe”){$_.Value}}), $path\RDM.exe”)

Where $wc & $out still exist from earlier use scripts

The next step is to create a scratch folder to target the exe’s /Extract command at, this will give us the following 2 files

The next step is to create the .ico we can use in the Application Catalog to make it look pretty for the end user

[System.Drawing.Icon]::ExtractAssociatedIcon($path\RDM.exe”).ToBitmap().save($path\RDM\RDM.ico”)

Which we are saving into the scratch folder to copy up to the SCCM server. We also define $InstallFile

From the MSI that exists in the scratch folder rather than using assuming that Devolutions will continue to use the same naming convention for the msi file.

The creation of the folders is pretty self-explanatory.

The next phase is to create the SCCM Applications & supersede the old versions.

We obtain the list of existing Application Deployment Types as the first step to ensure it doesn’t include the application we are creating.

With New-CMApplication in addition to the mandatory parameters we also see -LocalizedApplicationName & -IconLocationFile these are used to define the appearance of the application when published in the Application Catalog, the IconLocationFile parameter have the requirement of either a .ico or image file to work, while LocalizedApplicationName is a free text field.

With Add-CMDeploymentType we have included the following parameter -ForceForUnknownPublisher:$true which ensure that if the MSI file is not signed it will still add the deployment type, You should only use this on MSI’s you know & trust the origin of.

To Supersede the existing versions of Remote Desktop Manager we are using the Add-CMDeploymentTypeSupersedence command, as noted above, every time you add a new Superseded deployment type to the new Deployment type you will need reload the variable as it has changed.

So that’s the breakdown, the Whole script looks like this (Make sure you fix the Quotes when copying from the internet when not using PowerShell 5):

Set-Location “c:”

$Sitecode = “” #SCCM SiteCode

$path = “C:\data”

$Destpath = “” #Path to Central media store eg: \\<servername>\Source$

$dp = “” #FQDN of DP to deploy too (single DP at this point)

$LimitingCol = “” # name of limiting Collection

$IncCol = “” # name of collection to include

$RDMVerChk = “RDMEnterprise” # version of RDM

Import-Module $env:SMS_ADMIN_UI_PATH.Replace(“i386”,“ConfigurationManager.psd1”) -Force | Out-Null

$curdrv = (Get-Location).path

$wc = New-Object Net.WebClient

$srdm = $wc.DownloadString(“http://remotedesktopmanager.com/products.htm”)

$out = @()

foreach($rdm in $srdm.Split(“`r`n”))

{

$obj = New-Object PSObject

if ($rdm -like “*$RDMVerChk*”)

{

$s = $rdm.Split(“=”)

$obj | Add-Member Noteproperty Name $s[0].split(“.”)[1]

$obj | Add-Member Noteproperty Value $s[1]

$out += $obj

}

}

$rdmver = $out | ForEach-Object {if ($_.Name -eq “Version”){$_.Value}}

Set-Location $Sitecode`:”

if((Get-CMApplication -Name “Remote_Desktop_Manager-$rdmver))

{

Set-Location $curdrv

}

else

{

Set-Location $curdrv

#download File

$wc.DownloadFile(($out | ForEach-Object {if ($_.Name -eq “exe”){$_.Value}}), $path\RDM.exe”)

#Create RDM folder and Extract MSI from exe

if(!(Test-Path -path $Path\RDM”)){New-Item -Path $Path\RDM” -Force -ItemType Directory | Out-Null}

Start-Process -FilePath $path\RDM.exe” -ArgumentList “/extract:$path\RDM” -Wait

# Extract .ico file from the RDM.exe

[System.Drawing.Icon]::ExtractAssociatedIcon($path\RDM.exe”).ToBitmap().save($path\RDM\RDM.ico”)

$path = $path\RDM”

$InstallFile = (Get-ChildItem -Path $path | where {$_.name -Like “*.msi”}).name

# Create Folders and move files around

if(!(Test-Path -path $DestPath\applications”)){New-Item -Path $DestPath\applications” -Force -ItemType Directory | Out-Null}

if(!(Test-Path -path $DestPath\applications\$prodman)){New-Item -Path $DestPath\applications\$prodman -Force -ItemType Directory | Out-Null}

if(!(Test-Path -path $DestPath\applications\$prodman\$prodname$rdmver)){New-Item -Path $DestPath\applications\$prodman\$prodname$rdmver -Force -ItemType Directory | Out-Null}

Copy-Item $path\*” $DestPath\applications\$prodman\$prodname$rdmver

# Create SCCM Application

Set-Location $Sitecode`:”

# get existing Deployment Types to be SuperSeeded

$oldappdt = Get-CMDeploymentType -ApplicationName $prodname*”

$newapp = New-CMApplication -Name $prodname$rdmver -Publisher ($prodman.replace(“_”,” “)) -SoftwareVersion $rdmver -IconLocationFile $path\rdm.ico” -LocalizedApplicationName ($prodname.replace(“_”,” “))

Add-CMDeploymentType -MsiInstaller -InstallationFileLocation $DestPath\applications\$prodman\$prodname$rdmver\$installfile -Application $newapp -ForceForUnknownPublisher:$true

# Create SuperSeedence for each existing Deployment Type

foreach ($dt in $oldappdt)

{

# You need to get the new Deployment Type each time as it changes when new SuperSeedences are added.

}

# Disribution Content to a DP (this can be changed to DP Group as needed)

Start-CMContentDistribution -Application $newapp -DistributionPointName $DP

# Create New User collection for deployment, defineing the limiting collection and the collection to include (Can be changed to Query As required)

$newcol = New-CMUserCollection -Name “Install $ProdMan $Prodname $rdmver -LimitingCollectionName $LimitingCol

Add-CMUserCollectionIncludeMembershipRule -Collection $newcol -IncludeCollectionName $IncCol

# deploy New Application to New Collection

Start-CMApplicationDeployment -CollectionName ($newcol.Name) -Name ($newapp.LocalizedDisplayName)

# return to original Path

Set-Location $curdrv

}

Good Luck

Steve

Create a Device Collection based on a User Collection

I had an interesting discussion with a past colleague the other day where he was asking around to find out if it was possible to create a Device Collection based off a User Collection using the Primary Device option. After a bit of back and forth and him providing the query he was using for the user collection we started playing around with SubSelect queries to see if we can even reference User objects in the device collection.

So to start off we create the simple user query of get user x when in domain y like so:

select *

from SMS_R_User

where SMS_R_User.UserPrincipalName Like ‘%hosking%’

and SMS_R_USER.WindowsNTDomain like ‘%domain%’

Which will return my user account into a User Collection let’s call “Steves User Account” with a collection ID of “P0100024” from here we will move over to the device collection area of the console and create a collection called “Steve Primary Computers” and for this collection we will use the following query:

select *

from SMS_R_System

where SMS_R_System.resourceID in

(select SMS_UserMachineRelationship.resourceID

from SMS_R_User

join SMS_UserMachineRelationship on SMS_UserMachineRelationship.UniqueUserName = SMS_R_User.UniqueUserName

where SMS_R_User.UserPrincipalName Like ‘%hosking%’

and SMS_R_USER.WindowsNTDomain like ‘%domain%’)

So what we are doing here is using the SMS_UserMachineRelationship table which holds the information for Primary Computers for each user, and limiting the search for my username and Domain, which we then return the ResourceID, and finally we then return the SMS_R_system Resources which are in the query into the Device collection.

So this works great, but it does mean that I now have the same query in 2 different locations so if I want to change the query in the User Collection, I will then need to update the query in the Computer Collection.

So we looked at just including the User Collection itself in the query, this information is actually captured in a table accessible via WMI in tables that share this type of naming convention: SMS_CM_RES_COLL_<CollectionID> so from here we wrote this query:

select sms_r_system.name

from SMS_R_System

where SMS_R_System.resourceID in

(select SMS_UserMachineRelationship.resourceID

from SMS_CM_RES_COLL_P0100024

join SMS_UserMachineRelationship on SMS_UserMachineRelationship.UniqueUserName = SMS_CM_RES_COLL_P0100024.SMSID)

This query is even easy then the middle one as all we are doing using the UniqueUserName field from the SMS_UserMachineRelationship table and joining it with the SMSID from the collection table and returning the ResourceID like we did in the previous query.

Good Luck

Steve

Capturing installed Windows Features from Client OS into SCCM without touching the MOF

Recently I was asked how to capture the Enabled Windows features from client machines, to help identify the crazy cats who have Hyper-V turned on just so they can run an extra OS or 2 on there Surface Pro 3. So this one was an interesting question in the server OS there is the Win32_ServerFeatures WMI class which is great on servers, but doesn’t exist on client OS’s. All of the information is captured in DSIM right, but how do you turn that into a WMI Class for reporting purposes.

I started off by creating a PowerShell Script with plans to run it in DCM to create the Registry keys which we have traditionally used to capture the information in SCCM, which looks like this:

$f = “”

$feature = dism /online /get-features

foreach ($svc in $feature)

{

if ($svc -like “*Feature Name *”)

{

$f = $f + $svc.Replace(“Feature Name : “, “”) + “,”

}

elseif ($svc -like “*State : *”)

{

$f = $f + $svc.Replace(“State : “,“”) + “;”

}

}

 

Push-Location

Set-Location HKLM:

if ((Test-Path .\software\clientFeatures) -eq $false){New-Item -Path .\software -name clientFeatures | out-null}

foreach ($r in $f.Split(“;”))

{

if ($r -ne “”)

{

$feat = $r.Split(“,”)[0].ToString()

$featset = $r.Split(“,”)[1].ToString()

New-ItemProperty .\software\clientFeatures -Name $feat -Value $featset -PropertyType “string” -Force | Out-Null

$feat = “”

$featset = “”

}

}

Pop-Location

So let’s breakdown the script and explain what each of the sections are doing

$feature = dism /online /get-features

This puts the default DISM result into the $feature variable

I know your sitting there like me and wondering how you can turn that into a registry entry.

Well that’s where the next phase of the script comes in and creates a single string

foreach ($svc in $feature)

{

if ($svc -like “*Feature Name *”)

{

$f = $f + $svc.Replace(“Feature Name : “, “”) + “,”

}

elseif ($svc -like “*State : *”)

{

$f = $f + $svc.Replace(“State : “,“”) + “;”

}

}

Which steps through each of the lines in the result of the DISM command, and finds the lines which have “Feature Names” and “State” then writes into string called $f which we will use later. As we have put the , after the Feature name and the ; after the state, we can then split these in the next phase of the script. Which I will now explain what we are doing there:

Push-Location

Set-Location HKLM:

if ((Test-Path .\software\clientFeatures) -eq $false){New-Item -Path .\software -name clientFeatures | out-null}

foreach ($r in $f.Split(“;”))

{

if ($r -ne “”)

{

$feat = $r.Split(“,”)[0].ToString()

$featset = $r.Split(“,”)[1].ToString()

New-ItemProperty .\software\clientFeatures -Name $feat -Value $featset -PropertyType “string” -Force | Out-Null

$feat = “”

$featset = “”

}

}

Pop-Location

At a high level we are connecting to HKEY_LOCAL_MACHINE and created a new Key called ClientFeatures under the Software key, then creating a new REG_SZ for each of the features with the states as the value of the key. At this point I was really happy I had all of the features in the registry in a format I could then create a MOF from, this was short lived as I noticed I had over 100 features in the client OS which would create a heartache when creating the MOF file as it would be around 300 lines of boring code. So I went to myself, self why don’t you just create the WMI class with PowerShell then you don’t have to worry about getting the MOF file right, which I responded to self with that’s a great idea I’m amazed I didn’t think of it myself. Below you will see the resulting script:

if ((Get-WmiObject -Class Win32_ClientFeatures -ErrorAction SilentlyContinue).__PROPERTY_COUNT -lt 1)

{

$newClass = New-Object System.Management.ManagementClass(“root\cimv2”, [String]::Empty, $null)

$newClass[“__CLASS”] = “Win32_ClientFeatures”

$newClass.Qualifiers.Add(“Static”, $true)

$newClass.Properties.Add(“key”,[System.Management.CimType]::String, $false)

$newClass.Properties[“key”].Qualifiers.Add(“Key”, $true)

foreach ($r in $f.Split(“;”))

{

if ($r -ne “”)

{

$feat = $r.Split(“,”)[0].ToString()

$featset = $r.Split(“,”)[1].ToString()

$newClass.Properties.Add($feat,[System.Management.CimType]::String, $false)

$newClass.Put() | Out-Null

$feat = “”

$featset = “”

}

}

}

if ((Get-WmiObject -Class Win32_ClientFeatures).__path -eq $null){$new = $true} else {$inst = (Get-WmiObject -Class Win32_ClientFeatures).key}

if ($new) {$classinstance = $newClass.CreateInstance()}

foreach ($r in $f.Split(“;”))

{

if ($r -ne “”)

{

$feat = $r.Split(“,”)[0].ToString()

$featset = $r.Split(“,”)[1].ToString()

if ($new)

{

$classinstance.$feat = $featset

$classinstance.put() | Out-Null

}

else

{

$fe = Get-WmiObject -Class Win32_clientFeatures

$fe.$feat = $featset

$fe.put() | Out-Null

}

$feat = “”

$featset = “”

}

}

Let’s dive into the breakdown of the slab of code,

if ((Get-WmiObject -Class Win32_ClientFeatures -ErrorAction SilentlyContinue).__PROPERTY_COUNT -lt 1)

This if statement is checking to see if the WMI Class called Win32_ClientFeatures has any properties assigned to it, if the count is less than 1 then we will go ahead and create the WMI Class and create a Property for each of the Windows Features which is this slab of code:

{

$newClass = New-Object System.Management.ManagementClass(“root\cimv2”, [String]::Empty, $null)

$newClass[“__CLASS”] = “Win32_ClientFeatures”

$newClass.Qualifiers.Add(“Static”, $true)

$newClass.Properties.Add(“key”,[System.Management.CimType]::String, $false)

$newClass.Properties[“key”].Qualifiers.Add(“Key”, $true)

foreach ($r in $f.Split(“;”))

{

if ($r -ne “”)

{

$feat = $r.Split(“,”)[0].ToString()

$featset = $r.Split(“,”)[1].ToString()

$newClass.Properties.Add($feat,[System.Management.CimType]::String, $false)

$newClass.Put() | Out-Null

$feat = “”

$featset = “”

}

}

}

We have also created a key called “key” inventive I know, but it works. So we now have a WMI Class with all of the properties the next step is to create a WMI instance of the state of each of the Windows Features like so:

if ((Get-WmiObject -Class Win32_ClientFeatures).__path -eq $null){$new = $true} else {$inst = (Get-WmiObject -Class Win32_ClientFeatures).key}

if ($new) {$classinstance = $newClass.CreateInstance()}

foreach ($r in $f.Split(“;”))

{

if ($r -ne “”)

{

$feat = $r.Split(“,”)[0].ToString()

$featset = $r.Split(“,”)[1].ToString()

if ($new)

{

$classinstance.$feat = $featset

$classinstance.put() | Out-Null

}

else

{

$fe = Get-WmiObject -Class Win32_clientFeatures

$fe.$feat = $featset

$fe.put() | Out-Null

}

$feat = “”

$featset = “”

}

}

The if statement is checking to see if the there is an existing instance in the WMI object and selects that instance to update, otherwise we will go ahead and create a new instance. As you can see in the script these are 2 different groups of code.

So I now have a great WMI class which I can query with PowerShell and it returns like so:

We are now sitting there really happy with our new WMI class which captures all of the Windows Features settings for the computer great. The next step is to import the new WMI class into SCCM Hardware Inventory, and this is where it became interesting, as I got this error message:

I started doing some digging into this and found that the Remote Server Administration Tools features all started the same along with the language packs, so I tweaked the script to handle this and is still didn’t correct the issue. At about this point I came to the realisation that as easy as it would be from a reporting point of view I would be next to impossible to maintain as each OS version has different features, and then you need to include the RSAT features, and Language packs, which would result of in hundreds of properties required in WMI for each OS just to capture the information.

To get around this we then need to change the WMI class to have 2 properties called Feature which we will make as the Key for the instances, and value which will contain the state for the Feature. To complete this we run the following script:

$f = “”

$new = $true

$feature = dism /online /get-features

foreach ($svc in $feature)

{

if ($svc -like “*Feature Name *”)

{

$f = $f + $svc.Replace(“Feature Name : “, “”) + “,”

}

elseif ($svc -like “*State : *”)

{

$f = $f + $svc.Replace(“State : “,“”) + “;”

}

}

if ((Get-WmiObject -Class ClientFeatures -ErrorAction SilentlyContinue).__PROPERTY_COUNT -lt 1)

{

$newClass = New-Object System.Management.ManagementClass(“root\cimv2”, [String]::Empty, $null)

$newClass[“__CLASS”] = “Win32_ClientFeatures”

$newClass.Qualifiers.Add(“Static”, $true)

$newClass.Properties.Add(“Feature”,[System.Management.CimType]::String, $false)

$newClass.Properties[“Feature”].Qualifiers.Add(“Key”, $true)

$newClass.Properties.Add(“value”,[System.Management.CimType]::String, $false)

$newClass.Put() | out-null

}

 

foreach ($r in $f.Split(“;”))

{

 

if ($r -ne “”)

{

$feat = $r.Split(“,”)[0].ToString()

$featset = $r.Split(“,”)[1].ToString()

if ((Get-WmiObject -Class Win32_ClientFeatures).feature -eq $feat)

{

$fe = Get-WmiObject -query “select * from win32_clientFeatures where feature = ‘$feat‘”

$fe.value = $featset

$fe.put() | Out-Null

}

else

{

$classinstance = $newClass.CreateInstance()

$classinstance.feature = $feat

$classinstance.value = $featset

$classinstance.put() | Out-Null

}

$feat = “”

$featset = “”

}

}

The first part is the same as above, but once we get to defining the WMI class it changes a little, now rather than stepping through each of windows features and creating a property for each feature we are now just creating 2 properties called “Feature” and “Value”, only if the WMI class doesn’t exist, as defined in the below script:

if ((Get-WmiObject -Class ClientFeatures -ErrorAction SilentlyContinue).__PROPERTY_COUNT -lt 1)

{

$newClass = New-Object System.Management.ManagementClass(“root\cimv2”, [String]::Empty, $null)

$newClass[“__CLASS”] = “Win32_ClientFeatures”

$newClass.Qualifiers.Add(“Static”, $true)

$newClass.Properties.Add(“Feature”,[System.Management.CimType]::String, $false)

$newClass.Properties[“Feature”].Qualifiers.Add(“Key”, $true)

$newClass.Properties.Add(“value”,[System.Management.CimType]::String, $false)

$newClass.Put() | out-null

}

The next step is to create a new instance for each features, defining the feature name and the state like so:

foreach ($r in $f.Split(“;”))

{

 

if ($r -ne “”)

{

$feat = $r.Split(“,”)[0].ToString()

$featset = $r.Split(“,”)[1].ToString()

if ((Get-WmiObject -Class Win32_ClientFeatures).feature -eq $feat)

{

$fe = Get-WmiObject -query “select * from win32_clientFeatures where feature = ‘$feat‘”

$fe.value = $featset

$fe.put() | Out-Null

}

else

{

$classinstance = $newClass.CreateInstance()

$classinstance.feature = $feat

$classinstance.value = $featset

$classinstance.put() | Out-Null

}

$feat = “”

$featset = “”

}

}

We are also checking to see if the instance already exists for the feature, if it does exist, we will need to update the value, otherwise we create a new class instance.

Now when we import the WMI class into the SCCM console it doesn’t throw an error, and we can see the v_GS_ClientFeatures in SQL Reporting Services as an option to report upon now, so how do I get the list of computers which have Hyper-V Enabled, well you create a SQL Report with the following query:

select rsys.Name0

from v_R_System as rsys join

v_GS_CLIENT_FEATURES as feat on rsys.ResourceID = feat.ResourceID

where Feature0 = ‘Microsoft-Hyper-V’ and

value0 = ‘enabled’

And the WMI Query for a collection looks like this:

select * from SMS_R_System inner join SMS_G_System_CLIENT_FEATURES on SMS_G_System_CLIENT_FEATURES.ResourceId = SMS_R_System.ResourceId where SMS_G_System_CLIENT_FEATURES.Feature = “Microsoft-Hyper-V” and SMS_G_System_CLIENT_FEATURES.value = “Enabled”

The DCM detection PowerShell script looks like this:

$compliance = “Compliant”

$f = “”

$feature = dism /online /get-features

foreach ($svc in $feature)

{

if ($svc -like “*Feature Name *”)

{

$f = $f + $svc.Replace(“Feature Name : “, “”) + “,”

}

elseif ($svc -like “*State : *”)

{

$f = $f + $svc.Replace(“State : “,“”) + “;”

}

}

if ((Get-WmiObject -Class win32_ClientFeatures -ErrorAction SilentlyContinue).__PROPERTY_COUNT -eq 2)

{

foreach ($r in $f.Split(“;”))

{

if ($r -ne “”)

{

$feat = $r.Split(“,”)[0].ToString()

$featset = $r.Split(“,”)[1].ToString()

$cls = Get-WmiObject -query “select * from win32_clientFeatures where feature = ‘$feat‘”

if ($cls.value -ne $featset) {$compliance = “Non-Compliant”}

$feat = “”

$featset = “”

}

}

}

else

{

$compliance = “Non-Compliant”

}

$compliance

And the DCM remediation PowerShell script looks like this:

$f = “”

$new = $true

$feature = dism /online /get-features

foreach ($svc in $feature)

{

if ($svc -like “*Feature Name *”)

{

$f = $f + $svc.Replace(“Feature Name : “, “”) + “,”

}

elseif ($svc -like “*State : *”)

{

$f = $f + $svc.Replace(“State : “,“”) + “;”

}

}

if ((Get-WmiObject -Class ClientFeatures -ErrorAction SilentlyContinue).__PROPERTY_COUNT -lt 1)

{

$newClass = New-Object System.Management.ManagementClass(“root\cimv2”, [String]::Empty, $null)

$newClass[“__CLASS”] = “Win32_ClientFeatures”

$newClass.Qualifiers.Add(“Static”, $true)

$newClass.Properties.Add(“Feature”,[System.Management.CimType]::String, $false)

$newClass.Properties[“Feature”].Qualifiers.Add(“Key”, $true)

$newClass.Properties.Add(“value”,[System.Management.CimType]::String, $false)

$newClass.Put() | out-null

}

 

foreach ($r in $f.Split(“;”))

{

 

if ($r -ne “”)

{

$feat = $r.Split(“,”)[0].ToString()

$featset = $r.Split(“,”)[1].ToString()

if ((Get-WmiObject -Class Win32_ClientFeatures).feature -eq $feat)

{

$fe = Get-WmiObject -query “select * from win32_clientFeatures where feature = ‘$feat‘”

$fe.value = $featset

$fe.put() | Out-Null

}

else

{

$classinstance = $newClass.CreateInstance()

$classinstance.feature = $feat

$classinstance.value = $featset

$classinstance.put() | Out-Null

}

$feat = “”

$featset = “”

}

}

$compliance = “Compliant”

$compliance

For the DCM you will need to do a detection on a string that equals “Compliant” and remediate if it not.

As an recap of the whole blog, you can see there is a couple of ways to capture the information, depending upon the amount of properties it might make sense to have a single WMI instance in the WMI class, but in other cases it might make sense to have a large number of WMI instances in the WMI class, each have their advantages and disadvantages for the methods. The nice part of this solution is we don’t need to worry about making any changes to the MOF which makes life so much easier.

Good Luck

Steve

Get SCCM 2012 R2 Application Deep link

So over the weekend i was reviewing some posts up on the Facebook groups for SCCM and i can across this blog around sharing a link to the AppCatalog which also references this blog which details the actual creation of the application string. Which for me looks great as it resolves a big bug bear i have had where i couldn’t share the link to customers, a comment on the Blog post questioned where the right click tool was for this, which got me thinking i can do that.

I’ve added a few lines of code to Nick’s code which gets the encoded string, pops up a new draft email from Outlook (sorry if you have notes. no no really I’m so so sorry if you have to live with lotus notes) along with Copying the string to the clip board.

The added code is:

function
sendemail([string]$appstring,[string]$appnamestring)
{
$ol = New-Object –comObject Outlook.Application

$mail = $ol.CreateItem(0)

$mail.BodyFormat = 2

$mail.Subject = “How to Install $appnamestring

$mail.Body = $appstring

$mail.save()

$inspector = $mail.GetInspector

$inspector.Display()
}
$appname = $args[0]
Import-Module ($env:SMS_ADMIN_UI_PATH).Replace(“i386”, “ConfigurationManager.psd1”)$sitecode = ((gwmi –Namespace root\ccm –Class SMS_Authority).Name.split(“:”)[1])
Set-Location $SiteCode“:\”
$scopeidlong = (Get-CMApplication –Name $appname).CI_UniqueID
$scopeid = $scopeidlong.split(“/”)[0] + “/” +$scopeidlong.split(“/”)[1]
$encoded = encode($scopeid)
$appcat = (gwmi –Namespace root\ccm\LocationServices –Class SMS_PortalInformation).Url
$encoded = $appcat + “/#/SoftwareCatalog/AppDetails/” + $encoded
sendemail $encoded $appname
Add-Type –AssemblyName ‘System.Windows.Forms’
[Windows.Forms.Clipboard]::SetText($encoded)

The first part of the script is a function to create an email, then we handle the Arguments coming into the script and launch, and importing the SCCM 2012 PowerShell module from your computer. Then we get the sitecode and connect to path.

Now we are connected to your site in PowerShell we will get the CI_UniqueID for the application you are looking for, which is then split to remove the version number off the end, then we Encode the string using Nick’s code which we then get the AppCatalog’s URL to provide a full path for the email which is created next, then copies the code to ClipBoard.

So I have created a simple zip file which you can download from my OneDrive here

Please let me know if you find any issues.

Enjoy

Steve

Technorati Tags: SCCM,Self Service,AppCatalog

Actual Installed Application Reporting from SCCM

Having worked on SCCM for many years now I have lost count of how many times I have been asked, “Since you guys have an agent on all of the computers can I get you to provide me a list of ALL software that is installed on all of the computers?” after which there is a long draw out discussion explaining that when SCCM returns the information from Programs and Features it doesn’t discriminate between System install applications which are not visible in Programs and Features in which you will typically end up with double the amount of applications per computer, then what actually appears in Programs and Features. To this end I’ve had some spare time recently, so I have done some investigating, and found that with a simple update of the MOF we can typically get within +/-98% accuracy of only the applications that appear in Programs and Features appearing in the SCCM reports, which for me it a huge win.

As we know the Registry is the master of what appears in the Programs and Features list, so for example you can change the Display Name of applications in Programs and Features by changing the following registry Item:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\<Application Identifier>|DisplayName

This Key also includes such things as the Publisher, Version numbers, and Uninstallation strings. The Item’s which we are interested in for this solution are the following:
    SystemComponent
    ParentDisplayName

The SystemComponent item will appear as 1 if the application is to be hidden from Programs and Features, so for example the SCCM Client will have SystemComponent = 1 which appears to hide it from Programs and Features, if this item is not defined it is assumed to be a 0 (This is also used by Microsoft Office to hide each of the components from Programs and Features).

Whereas the ParentDisplayName item is used for patches to applications and Operating system, an example of can be seen in Windows XP where you could select the check box to hide updates. When not referring to the install Operating System it will refer to the DisplayName of the parent, for example “Microsoft .NET Framework 4 Client Profile” will have all of the updates that are dependent upon it.

Now we have explained what we are doing and why let’s step through the process of actually capturing the details in SCCM.

  1. Browse to \\<SCCMSERVER>\SMS_<SITECODE>\inboxes\clifiles.src\hinv
  2. Copy Configuration.mof to c:\data\mofs
  3. Open Configuration.mof with Notepad
  4. Find the Follow:
    class Win32Reg_AddRemovePrograms
    {
    [key]
    string ProdID;
    [PropertyContext(“DisplayName”)]
    string DisplayName;
    [PropertyContext(“InstallDate”)]
    string InstallDate;
    [PropertyContext(“Publisher”) ]
    string Publisher;
    [PropertyContext(“DisplayVersion”)]
    string Version;
    }
    and Replace with the following:
    class Win32Reg_AddRemovePrograms
    {
    [key]
    string ProdID;
    [PropertyContext(“DisplayName”)]
    string DisplayName;
    [PropertyContext(“InstallDate”)]
    string InstallDate;
    [PropertyContext(“Publisher”) ]
    string Publisher;
    [PropertyContext(“DisplayVersion”)]
    string Version;
    [PropertyContext(“SystemComponent”)]
    string SystemComponent;
    [PropertyContext(“ParentDisplayName”)]
    string ParentDisplayName;
    }

  5. Then find the following directly below:
    class Win32Reg_AddRemovePrograms64
    {
    [key]
    string ProdID;
    [PropertyContext(“DisplayName”)]
    string DisplayName;
    [PropertyContext(“InstallDate”)]
    string InstallDate;
    [PropertyContext(“Publisher”) ]
    string Publisher;
    [PropertyContext(“DisplayVersion”)]
    string Version;
    }
    and Replace with the following:
    class Win32Reg_AddRemovePrograms64
    {
    [key]
    string ProdID;
    [PropertyContext(“DisplayName”)]
    string DisplayName;
    [PropertyContext(“InstallDate”)]
    string InstallDate;
    [PropertyContext(“Publisher”) ]
    string Publisher;
    [PropertyContext(“DisplayVersion”)]
    string Version;
    [PropertyContext(“SystemComponent”)]
    string SystemComponent;
    [PropertyContext(“ParentDisplayName”)]
    string ParentDisplayName;
    }

  6. Close and Save Configuration.mof
  7. In an elevated command prompt run the following commands:
    1. Mofcomp c:\data\configuration.mof
      the result should look like this:
  8. Browse back to: \\<SCCMSERVER>\SMS_<SITECODE>\inboxes\clifiles.src\hinv and rename the existing file to match this naming convention:
    1. configurationYYYYMMDD.mof
  9. Copy configuration.mof from c:\data to \\<SCCMSERVER>\SMS_<SITECODE>\inboxes\clifiles.src\hinv
  10. Open SCCM 2012 Console and browse Administration à Client Settings
  11. Right click on Default Client Settings and select Properties
  12. Select Set Classes under the Hardware Inventory tab on the Default Settings Form
  13. Select Export on the Hardware Inventory Classes form
  14. Save the exported file to c:\data\export.mof
  15. Open C:\data\export.mof in notepad
  16. Find the following:
    class Win32Reg_AddRemovePrograms : SMS_Class_Template
    {
    [SMS_Report (TRUE), key ]
    string ProdID;
    [SMS_Report (TRUE) ]
    string DisplayName;
    [SMS_Report (TRUE) ]
    string InstallDate;
    [SMS_Report (TRUE) ]
    string Publisher;
    [SMS_Report (TRUE) ]
    string Version;
    };

    and replace with:
    class Win32Reg_AddRemovePrograms : SMS_Class_Template
    {
    [SMS_Report (TRUE), key ]
    string ProdID;
    [SMS_Report (TRUE) ]
    string DisplayName;
    [SMS_Report (TRUE) ]
    string InstallDate;
    [SMS_Report (TRUE) ]
    string Publisher;
    [SMS_Report (TRUE) ]
    string Version;
    [SMS_Report (TRUE) ]
    string SystemComponent;
    [SMS_Report (TRUE) ]
    string ParentDisplayName;
    };

  17. Then find the following directly below:
    class Win32Reg_AddRemovePrograms64 : SMS_Class_Template
    {
    [SMS_Report (TRUE), key ]
    string ProdID;
    [SMS_Report (TRUE) ]
    string DisplayName;
    [SMS_Report (TRUE) ]
    string InstallDate;
    [SMS_Report (TRUE) ]
    string Publisher;
    [SMS_Report (TRUE) ]
    string Version;
    };

    and replace with:
    class Win32Reg_AddRemovePrograms64 : SMS_Class_Template
    {
    [SMS_Report (TRUE), key ]
    string ProdID;
    [SMS_Report (TRUE) ]
    string DisplayName;
    [SMS_Report (TRUE) ]
    string InstallDate;
    [SMS_Report (TRUE) ]
    string Publisher;
    [SMS_Report (TRUE) ]
    string Version;
    [SMS_Report (TRUE) ]
    string SystemComponent;
    [SMS_Report (TRUE) ]
    string ParentDisplayName;
    };
  18. Close and Save c:\data\export.mof
  19. On the Hardware Inventory Classes form select Import
  20. Review the Message to ensure everything is correct, and Select Import.
  21. On the Hardware Inventory Classes form search for Win32reg_addremoveprograms and ensure that SystemComponent & ParentDisplayName are selected, then click on OK, and OK, back to the SCCM Console

To test ensure that you have completed a Policy refresh on a targeted computer, wait a few minutes then trigger a hardware inventory, you can see if the policy has updated by reviewing the InventoryAgent.log on the client computer and filter with CMTrace for Win32reg_addremoveprograms, you should see something like this:

And on the server side you can confirm the new inventory has been loaded by reviewing the DataLdr.log file on the Site server, looking for the line
“Done: Machine=<Computername>(GUID:<ComputerGUID>) code=0 (1473 stored procs in <InventoryMIF>.MIF)”
to ensure that the data has been loaded into the SQL database.

From here we can create a report using the following example SQL query:
Count of all applications installed on all computers:

SELECT
*
FROM
(

SELECT
DISTINCT

v_GS_ADD_REMOVE_PROGRAMS_64.DisplayName0 AS “Product Name”, v_GS_ADD_REMOVE_PROGRAMS_64.Publisher0 AS “Publisher”,
count(*)
as
‘Install count’

FROM v_GS_ADD_REMOVE_PROGRAMS_64

INNER
JOIN v_R_System_Valid ON v_R_System_Valid.ResourceID = v_GS_ADD_REMOVE_PROGRAMS_64.ResourceID

JOIN v_GS_OPERATING_SYSTEM ON v_GS_ADD_REMOVE_PROGRAMS_64.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID

WHERE v_GS_ADD_REMOVE_PROGRAMS_64.SystemComponent0 is
null
and v_GS_ADD_REMOVE_PROGRAMS_64.parentdisplayname0 is
null
and
not v_GS_ADD_REMOVE_PROGRAMS_64.Displayname0 is
null

group
by v_GS_ADD_REMOVE_PROGRAMS_64.DisplayName0, v_GS_ADD_REMOVE_PROGRAMS_64.Publisher0

UNION ALL

(


SELECT
DISTINCT

v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 AS “Product Name”, v_GS_ADD_REMOVE_PROGRAMS.Publisher0 AS “Publisher”,count(*)
as
‘Install count’


FROM v_GS_ADD_REMOVE_PROGRAMS


INNER
JOIN v_R_System_Valid ON v_R_System_Valid.ResourceID = v_GS_ADD_REMOVE_PROGRAMS.ResourceID


JOIN v_GS_OPERATING_SYSTEM ON v_GS_ADD_REMOVE_PROGRAMS.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID


WHERE v_GS_ADD_REMOVE_PROGRAMS.SystemComponent0 is
null
and v_GS_ADD_REMOVE_PROGRAMS.parentdisplayname0 is
null
and
not v_GS_ADD_REMOVE_PROGRAMS.Displayname0 is
null

    group
by v_GS_ADD_REMOVE_PROGRAMS.DisplayName0, v_GS_ADD_REMOVE_PROGRAMS.Publisher0

))
AS u

ORDER
BY “Product Name”, Publisher

Installed applications on a Single Computer:

DECLARE @compname VARCHAR(MAX) = ‘Computer Name’

SELECT
*
FROM
(

SELECT
DISTINCT

v_GS_ADD_REMOVE_PROGRAMS_64.DisplayName0 AS “Product Name”, v_GS_ADD_REMOVE_PROGRAMS_64.Publisher0 AS “Publisher”, v_GS_ADD_REMOVE_PROGRAMS_64.Version0 AS “Version”

FROM v_GS_ADD_REMOVE_PROGRAMS_64

INNER
JOIN v_R_System_Valid ON v_R_System_Valid.ResourceID = v_GS_ADD_REMOVE_PROGRAMS_64.ResourceID

JOIN v_GS_OPERATING_SYSTEM ON v_GS_ADD_REMOVE_PROGRAMS_64.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID

WHERE v_GS_ADD_REMOVE_PROGRAMS_64.SystemComponent0 is
null
and v_GS_ADD_REMOVE_PROGRAMS_64.parentdisplayname0 is
null
and
not v_GS_ADD_REMOVE_PROGRAMS_64.Displayname0 is
null

and v_R_System_Valid.Netbios_Name0 = @compname

UNION ALL

(


SELECT
DISTINCT

v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 AS “Product Name”, v_GS_ADD_REMOVE_PROGRAMS.Publisher0 AS “Publisher”, v_GS_ADD_REMOVE_PROGRAMS_64.Version0 AS “Version”


FROM v_GS_ADD_REMOVE_PROGRAMS


INNER
JOIN v_R_System_Valid ON v_R_System_Valid.ResourceID = v_GS_ADD_REMOVE_PROGRAMS.ResourceID


JOIN v_GS_OPERATING_SYSTEM ON v_GS_ADD_REMOVE_PROGRAMS.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID


WHERE v_GS_ADD_REMOVE_PROGRAMS.SystemComponent0 is
null
and v_GS_ADD_REMOVE_PROGRAMS.parentdisplayname0 is
null
and
not v_GS_ADD_REMOVE_PROGRAMS.Displayname0 is
null

and v_R_System_Valid.Netbios_Name0 = @compname

))
AS u

ORDER
BY “Product Name”, Publisher

You will notice that we are not using the “v_add_remove_programs” view, this appears to be a derived view which doesn’t appear to be updated when new columns added to the Class, but we can get around this by using the Union function in SQL.

Good Luck

Steve