Get started with Azure Functions

A very quick post today, on the back of Experts Live Europe where I presented a session on using APIs and Azure Functions to develop the DevOps Toolkit of the Future. Azure Functions are one of the best ways to get automation into and around your datacentre. Why? Because you can use them in a full cloud infrastructure, hybrid scenarios, or as standalone automation tools. I recommend following Ryan’s blog at dftai.ch for advanced tips on this topic. I want to get you started on creating your first Azure Function App as a basis for upcoming posts on the subject.

Things you will need:

A Powershell script or module

An Azure subscription

For our EL session, Ryan developed a Spotify API module for Powershell and posted it to GitHub here. The premise was an idea to have more control of Spotify through their open API, for personal use, but I thought we could also use this to highlight what can be done in Azure in terms of tooling and automation. The Spotify module is an example of standalone automation.

So, the idea of this post is to show you how to quickly implement a simple Azure Function, using code you already have, and leveraging the functionality available in Azure.

Let’s get started, open Azure Portal and log in with your Azure subscription. Then click on +New, Search Marketplace for Function App and select this type, then click Create.

 

 

 

 

You need to give your Function App a unique name, then select a hosting plan depending on your subscription. I am using a Consumption Plan for this demo. Also, either create new or use existing storage. The configuration of your app needs to be saved into this storage. I pinned my app to the dashboard so I can find it easily.

 

 

 

 

If it creates successfully, I can open my Function App and see the Functions, Proxies and Slots. So I create a new Function here and I can add my powershell code. I add a HTTPTriggerPowershell function under the custom types.

 

 

 

Now, because I am not using Source Control (Advanced Topic) I can simply edit my powershell code in the directly portal.

 

 

I can also edit the Triggers (HTTP), the inputs (Cosmos DB for instance) and outputs (HTTP) in the Integrate Tab.

 

 

 

You should notice that the variables for the inputs and outputs are automatically inserted into the code.

Now copy the function URL

 

We are ready to test! Let’s create a powershell script on our local machine like this:

$functionurl = “”

$json = ‘{
“method”: “POST”,
“name”: “Andrew”
}’

Invoke-RestMethod -Uri $functionurl -Method POST -Body $json

Run you script and voila, Hello World, or Hello Andrew in my demo, triggered by HTTP POST, running in Azure and returning a result to your local machine. Serverless, hybrid automation.

That’s it. Check out Ryan’s blog for how to work with the Integrate tab (Triggers, Inputs and Outputs). You are ready to start implementing more powerful scripts, check out the Spotify Modules, think about connecting to other APIs, hmm, that sounds like the next post in the series. So, until then: happy serverless computing!
Andrew.

 

 

Advertisement

SCSM: Data Warehouse Troubleshooting Part 2 (Reinstall DW)

If all else fails: Reinstall the Data Warehouse (System Center Service Manager.)

IMPORTANT: Take a backup of SQL DBs before you reinstall DW. In case there are special SQL Objects that haven’t been exported or saved.

Reinstalling the DW is pretty straightforward and done in the main SCSM console. This post is targeted at individuals who know what they are doing with SCSM and DW. Hence I am not going to go through how to remove and reinstall. I though maybe more interesting to know what happens under the Hood.

Before DW is functional, it needs to run a number of (#53) deployment jobs in SQL (only visible in SQL). This has to happen before the DW can be registered in SCSM. (I don’t know, it seems that the DW will regulate this itself, so that if you register the DW directly after install the sync jobs will be queued. I am doing this very granularly)

So, while these deployment jobs are running the DWMaintenance job is stuck in Waiting status. (consequently all other DW Jobs are also “stuck” or “queued”). Notice the DWMaintenance is not just Waiting but also has Errors in the ErrorSummary (this prevents sync jobs from running)

Once these deployment jobs are finished, DW should create a new Batch job for DWMaintenance which will be released from Waiting and should then run ok.

Step-by-step

Immediately after installation has completed, SQL will look like this:

 

 

Query:

select WI.BatchId, WI.StatusId, WI.ErrorSummary from infra.WorkItem(nolock) WI

join infra.Batch BAT on WI.BatchId = BAT.BatchId

join infra.Process PRO on BAT.ProcessId = PRO.ProcessId

where PRO.ProcessName = ‘DWMaintenance’

select * from DeploySequenceView where DeploymentStatusId != 6

select * from DeploySequenceStaging

 

Until all the jobs in DeploySequenceView disappear. Ca 1 ½ hours

SQL will then look like this:

 

 

 

With a new Batch job for DWMaintenance.

In Powershell we can now see the DW_EXTRACT, Transform and Load jobs. Ready for registration.

You can check the Deployment with: select * from DeployItemView

All Items should be completed.

The Extract, Load and Transform jobs all then run, even though the DW is not registered it is an initialization of the jobs. They don’t take long to run through.

Now we can try to register the DW.

After successful Registration the Extract Job will run. All jobs can now be seen in Powershell via the Warehouse Cmdlets.

Start (Resume) the MPSyncJob if you can’t wait.

The jobs run in the following order (sort of):

 

MPSyncJob/Disable Deployment Jobs

MPSyncJob/Synchronize ServiceManger MPs

MPSyncJob/Create ServiceManagerExtracts

Extract_DW_/*

MPSyncJob/Associate Imported MP Vertex

 

Wait for a long time until all these jobs finish……. probably overnight.

SCSM: Data Warehouse Troubleshooting Part 1 (Jobs Fail on Missing Primary Keys)

Symptoms: The Load.Common or Transform.Common Jobs are failing in SCSM DW (Service Manager)

To find out why run this query against DWDataMart:

select WI.WorkItemId,WI.BatchId, WI.StatusId, WI.ErrorSummary from infra.WorkItem(nolock) WI where WI.ErrorSummary is not null

If you see Errors referring to missing Primary keys, like this example:

Message: UNION ALL view ‘[dbo].PowerActivityDayFactvw’ is not updatable because a primary key was not found on table ‘[dbo].[PowerActivityDayFact_2013_Jun]’.

Then you need to either rebuild the DW or re-create these Primary Keys. I have no idea why tables suddenly lose their Primary Key. This Problem however usually affects Relationship Tables and Views (Facts). With a few exceptions the Primary Key is composed of the first 3x Columns (according to Ordinal). These columns are usually the DimKey, the related item DimKey and the DateKey however if it is a “Duration” or “measure” relationship then the third column will be something like a StartDate or TimeKey. In this case you Need the first 3x columns and then the DateKey, making 4x columns in total to create the Primary key. This eventuality is covered in the script. What is not covered is the EntityManagedType and EntityRelatesToEntity relationship tables which have extra columns in the Primary Key. Also the SLAInstanceInformation relationship table has a Special Primary Key. These exceptions must be dealt with separately.

Happily though, for everything else there’s a script:

<#
Fix Data Warehouse Primary Keys Issue
#>
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection
$sQLServer=’SCSMServer’
$sQLDBName = ‘DWDataMart’
$sQLStagingDBName = “DWStagingAndConfig”
function SQLCommand($sQLCommand,$sQLDB){
$sqlConnection.ConnectionString = “Server = $sQLServer; Database = $sQLDB;Integrated Security = True”
$sqlConnection.Open()
$sQLCmd = New-Object System.Data.SqlClient.SqlCommand
$sQLCmd.CommandText = $sQLCommand
$sQLCmd.Connection = $sqlConnection
$sQLCmd.ExecuteNonQuery()
$sqlConnection.Close()
}
function QueryTable($sQLQuery,$sQLDB){
$sqlConnection.ConnectionString = “Server = $sQLServer; Database = $sQLDB;Integrated Security = True”
$sqlConnection.Open()
$sQLCmd = New-Object System.Data.SqlClient.SqlCommand
$sQLCmd.CommandText = $sQLQuery
$sQLCmd.Connection = $sqlConnection
$sQLAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$sQLAdapter.SelectCommand = $sQLCmd
$dataSet = New-Object System.Data.DataSet
$sQLAdapter.Fill($dataSet)
$sqlConnection.Close()
return $dataSet
}
cls
$allMay2014FactTables = QueryTable “select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_NAME like ‘%Fact_2014_Jun%'” $sQLDBName
foreach($table in $allMay2014FactTables[1].Tables[0]){
$tableName = $table.TABLE_NAME
$priKeyExists = QueryTable “SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_NAME), ‘IsPrimaryKey’) = 1
AND
TABLE_NAME = ‘$tableName'” $sQLDBName
if($priKeyExists[1].Tables[0] -ne $null){
“Primary Key Exists in $tableName”
}else{
“Primary Key MISSING: $tableName”
$columns = QueryTable “select COLUMN_NAME,ORDINAL_POSITION
from INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME = ‘$tableName’
ORDER BY ORDINAL_POSITION” $sQLDBName
$priKey1 = $columns[1].Tables[0].Rows[0].COLUMN_NAME
$priKey2 = $columns[1].Tables[0].Rows[1].COLUMN_NAME
$priKey3 = $columns[1].Tables[0].Rows[2].COLUMN_NAME
if($priKey3 -ne ‘DateKey’){
$alterTableCMD = “ALTER TABLE [dbo].[$tableName] ADD  CONSTRAINT [PK_$tableName] PRIMARY KEY NONCLUSTERED
(
[$priKey1] ASC,
[$priKey2] ASC,
[$priKey3] ASC,
[DateKey] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [FileGroup2_Facts1]”
}else{
$alterTableCMD = “ALTER TABLE [dbo].[$tableName] ADD  CONSTRAINT [PK_$tableName] PRIMARY KEY NONCLUSTERED
(
[$priKey1] ASC,
[$priKey2] ASC,
[$priKey3] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [FileGroup2_Facts1]”
}
SQLCommand $alterTableCMD $sQLDBName | out-null
}
}
SQLCommand “update infra.WorkItem set ErrorSummary = NULL,StatusId=3 where ErrorSummary is not null” $sQLStagingDBName | out-null

After running this script, try resuming the Load.Common Job and check for Errors. I recommend using Mihai’s script just to clean everything up:

http://blogs.technet.com/b/mihai/archive/2013/07/03/resetting-and-running-the-service-manager-data-warehouse-jobs-separately.aspx

EDIT: MS already have a SQL Script which will do the same thing.. 🙂

https://technet.microsoft.com/en-us/library/dn299381.aspx

Quick Tip: Complex Powershell in Run Commandline Step of ConfigMgr TS

I am using this more and more now, maybe just because i think it’s kinda cool. I wanted to use some Powershell in a Run Commandline Step in a TS, which used a couple of lines of code, had some double-quote characters, and i didn’t want (or was too lazy) to create a script in a package, update the DPs and use that. I just wanted to test the script, demo what it did, before i went down the package route.

Also, using double-quotes in the Commandline step annoys me , escape characters and so on; so i found this neat little trick.

Take the code you want to use, e.g.

$tsenv=new-object microsoft.sms.tsenvironment;$tsenv.Value(“SMSTSErrorDialogTimeout”)=0

and parse this to a string variable using something like a Here-String

$script = {$tsenv=new-object microsoft.sms.tsenvironment;$tsenv.Value(“SMSTSErrorDialogTimeout”)}.ToString()

then use the System Convert and Text Encoding classes to create a Base64String

$encCmd = [System.Convert]::ToBase64String([System.Text.Encoding]::Unicode.GetBytes($script))

which gives

JAB0AHMAZQBuAHYAPQBuAGUAdwAtAG8AYgBqAGUAYwB0ACAAbQBpAGMAcgBvAHMAbwBmAHQALgBzAG0AcwAuAHQAcwBlAG4AdgBpAHIAbwBuAG0AZQBuAHQAOwAkAHQAcwBlAG4AdgAuAFYAYQBsAHUAZQAoACIAUwBNAFMAVABTAEUAcgByAG8AcgBEAGkAYQBsAG8AZwBUAGkAbQBlA
G8AdQB0ACIAKQA=

Now use that with Powershell in a Commandline step like this

powershell.exe -EncodedCommand JAB0AHMAZQBuAHYAPQBuAGUAdwAtAG8AYgBqAGUAYwB0ACAAbQBpAGMAcgBvAHMAbwBmAHQALgBzAG0AcwAuAHQAcwBlAG4AdgBpAHIAbwBuAG0AZQBuAHQAOwAkAHQAcwBlAG4AdgAuAFYAYQBsAHUAZQAoACIAUwBNAFMAVABTAEUAcgByAG8AcgBEAGkAYQBsAG8AZwBUAGkAbQBlAG8AdQB0ACIAKQA=

 

Watch for line breaks when you copy the Base64String. Otherwise, it works, and if you find it useful or just cool then i’m glad.

Incidentally, to Change the Base64String back to readable text:

[System.Text.Encoding]::Unicode.GetString([System.Convert]::FromBase64String($encCmd))

Write logs in CMTrace tool format

as recently promised at CMCE here is a powershell module to write your logfiles in cmtrace format:

http://gallery.technet.microsoft.com/Write-CMTrace-0330216e

Please remember to rate

 

Accessing the Task Sequence Environment in ConfigMgr

I had a question at CMCE Switzerland about how to get acces to the SMSTS variables through Powerhell, so here it is:

In either a script or at a Powershell command window, create a ComObject:

$tsenv = New-Object -ComObject Microsoft.SMS.TSEnvironment

you can use this to access and set Task Sequence variables:

$tsenv.Value(“OSDComputerName”)

will return the value of the OSDComputerName action variable

$tsenv.Value(“SMSTSErrorDialogTimeout”) = 0

sets the timeout on the error message box to something like 6 years (in seconds)

WSUS Post Configuration Failing

If WSUS Post Configuration tasks fail, check the log for a missing ContentDir parameter:
Start: LoadSettingsFromXml
Start: GetConfigValue with filename=UpdateServices-Services.xml item=ContentLocal
Value is true
End: GetConfigValue
Start: GetConfigValue with filename=UpdateServices-Services.xml item=ContentDirectory
Config file did not contain a value “ContentDirectory”
Microsoft.UpdateServices.Administration.CommandException: A required configuration value was not found in the system. This is usually caused by installing WSUS through PowerShell and not specifying a configuration file. Review the article Managing WSUS Using PowerShell at TechNet Library (http://go.microsoft.com/fwlink/?LinkId=235499) for more information on the recommended steps to perform WSUS installation using PowerShell.

This can happen if WSUS is added as a feature via Powershell, but also sometimes when using server manager.
The WSUS setup uses the UpdateServices-Services.xml from the ServerManager module. If the ContentDirectory node is empty ServerManager will use a root folder on the drive with most space (e:\wsus) and create a content directory there (e:\wsus\wsuscontent)
If this happens, even if you define a content folder in the wizard, then run the post install tasks with the following command lines (elevated) depending on whether using a WID or SQL Server

For installations using WID:
wsusutil postinstall CONTENT_DIR=E:\WSUS (or whatever parent folder for WSUSContent you’ve specified)

For installations using SQL Server:
wsusutil postinstall CONTENT_DIR=E:\WSUS SQL_INSTANCE_NAME=databaseServer[\instanceName]

Policypv Unknown SQL Error

Came across this nice little nugget today. The site seemed generally in order, mostly green, but the Policy Provider was in a Warning state. Looking in the status messages I found hundreds of Unknown SQL Errors. The policypv.log didn’t shed much more light on the supposed problem. Probably had the site been in a more intensive operational state someone would have noticed that they couldn’t add new Software Update packages, for instance.
The problem itself is a common one in the SQL Server world, stored procedures cannot be remotely executed. In this case the sp_updpolicyresmap.
And the most common cause is a SQL DBA or a Server Admin being proactive and improving the server by adding or reconfiguring new disks. To do this they detach the databases, make the changes and reattach. What sometimes happens though, is that for reasons unknown to me the Trustworthy state of the database reverts to false and the sa loses ownership.
Easy to fix though:
USE [databasename]
GO
EXECUTE sp_changedbowner ‘sa’

ALTER DATABASE [databasename]
SET TRUSTWORTHY ON

BitLocker in SCCM with 2nd HDD – NEW and REFRESH Scenarios

There are a couple of challenges when using BitLocker in ConfigMgr 2012. Using Pre-Provisioning and locking a 2nd HDD in REFRESH Scenarios is one such challenge.

Here is how i handle it.

Scenario 1: NEW – Single Disk

Background and overview: New PC or Laptop, single hard drive.

1. Create a PreInstall partition on the disk if there is no available partitions

2. Configure BIOS and TPM (see previous post)

3. Format and Partition Disk0 for use with BitLocker. Create a BDE partition with fixed size 500Mb, NTFS and store the drive letter as a variable BOOTPART. Create a System partition of 100% remaining space, NTFS, store drive letter as OSPART.

4. Pre-Provision BitLocker to “Logical drive letter stored in a a variable” – OSPART

5. At the end of the Task Sequence Enable BitLocker on “Current operating system drive”. Choose to wait for BitLocker to complete before continuing.

Scenario 2: REFRESH – Single Disk

Background and overview: Refresh PC or Laptop, single hard drive.

1. When started from Software Center, disable BitLocker on current operating system drive and reboot to WinPE.

2. If started from USB or PXE, use a script to unlock the operating system drive.

3. continue from step 2, Scenario 1.

Scenario 3: NEW – Additional Disk

Background and overview: New PC or Laptop, multiple hard drives.

1. Out of the box means started from USB or PXE, use a script to unlock OS and data drives.

2. continue from step 2, Scenario 1.

3. When finalizing BitLocker on the OS disk choose to continue on error. This because the attributes will be inconsistent after C, D and E drive letters are reassigned but the BitLocker process will finalize ok.

4. Enable BitLocker on the additional drive, choose either to wait for BitLocker to finish or continue and allow the drive to encrypt in the background. The machine will be usable but the 2nd disk will have limited availability until the process is finished which could be 20 – 50 minutes.

Scenario 4: REFRESH – Additional Disk

Background and overview: Refresh PC or Laptop, multiple hard drives.

1. When started from Software Center, disable BitLocker on current operating system drive and data drives and reboot to WinPE.

2. If started from USB or PXE, use a script to unlock the operating system drive and data drives.

3. continue from step 2, Scenario 1.

3. continue from step 2, Scenario 3.

Here are some screenshots of the TS:

Configuration Manager Community Event Jan 2014

Check all the slides from the event at www.configmgr.ch

I’d like to thank everyone who attended my session, they were a lovely bunch and I had some great discussions with people afterwards, here is a slide deck which I have since extended with my demo notes and script examples.

Hopefully we’ll be able to put up some web sessions, I will certainly post up here some follow ups on Pre-Execution Environment.

Big thanks to Mirko Colemberg for organising, to all the guys who travelled through: Daniel Bühlmann, Roman Andres, Thomas Kürth, Jürg Koller, Pascal Berger, my very esteemed colleague Roger Zander and not to forget David O’Brien and Wally Mead who delivered great sessions.

https://drive.google.com/file/d/0B7oCZsxx9d5dUDhaekoyeUhweDA/edit?usp=sharing