Raspberry Pi Kitchen Wall Display Screen

Posted on August 25, 2017by Tom Pearson with 7 comments

Ok, so not really System Center related but I had to share this little project! My wife works as cabin crew for a UK airline so has crazy shift patterns which are hard to keep track of, I wanted to make a display screen for the kitchen wall which contained our shared calendars plus some other useful stuff, hopefully this will serve as inspiration for other kitchen wall display screen geeks!

The screen currently shows the following:

DakBoard linked to multiple Google calendars
The Guardian scrolling RSS news feed.
FlightRadar24 - Used to track the aircraft my wife is flying on (to show the kids.. honest!)
MetOffice local weather.
Travel reports in a 50 mile radius.

**2018 Update**

4 cryptocurrency price charts
Strava activity widget

What I also wanted to be able to do was somehow temporarily display a recipe while cooking in the kitchen without manually connecting to the Pi and opening a webpage. I looked into running an AirPlay server on the Pi (http://www.instructables.com/id/AirPlay-Mirroring-without-Apple-TV-using-RPi/) so we could mirror one of our iOS devices. This didn't work as expected due to Apple restrictions. But I did get a bit further when using AirParrot from the PC, I was able to successfully stream audio and video to the Pi, but mirroring the PC's display only worked for about 4 seconds before bombing out. I've given up on this for now.

The bulk of the work to get the Raspberry Pi and calendar display set up is covered on either of these two posts:
http://dakboard.com/blog/diy-wall-display/
https://www.raspberrypi.org/magpi/dakboard-wall-display/

My customisation is achieved using the configuration below:

DakBoard

I used this guide to get everything set up and ready for wall mounting, but disabled all other features of DakBoard except for the calendar feature. I then added multiple Google calendars with different colours.

To link your DakBoard to the custom page below you'll need to use the Private URL shown on the DakBoard site under Account Settings.

Webpage

Additional things you'll need:

Somewhere to host a webpage (PHP enabled)
An IMAP email account specifically for the purpose of this board.

To take things further and get all the other funky stuff on the screen I needed to create a custom webpage to embed all of the other sources of information using HTML iframes. The main page is constructed using iframes and consists of my DakBoard calendar, a locally hosted PHP webpage (side.php) for the right hand section, weather.html for the weather, and bottom.html for the news feed section.

I'm not a web developer by any means and I'm sure what I've thrown together here will get some serious frowns from the web-dev community, but it's locally hosted so I'm not worrying too much!

index.html

<body style="margin:0px;">

<div class="box">

<iframe src="URL to DakBoard Screen" frameborder="0" scrolling="no" height="88%" width="55%" align="left"></iframe></div>

<div class="box">

<iframe src="URL to side.php" frameborder="0" scrolling="no" height="100%" width="45%" align="right"></iframe>

<div class="box"><br>

<iframe src="URL to bottom.html" frameborder="0" scrolling="no" height="12%" width="55%" align="center"></iframe>

</body>

The iframes are sized using height and width percentages so it will still fit nicely if I eventually get a larger monitor of the same aspect ratio.

side.php

Now, this is where the fun starts. I've configured this page to refresh every 10 minutes to ensure the aircraft tracking, weather and traffic is up to date. Each of these sources are pulled in using more HTML iframes.

The page needs to be PHP because at the point of the page refreshing it uses PHP to check an IMAP mailbox for the most recent email and extract the contents of the subject line. The subject line is used to determine what aircraft registration number FlightRadar24 should be tracking. So I just send an email with the subject line of G-TAWB for example, and the next time the page refreshes it will begin tracking it.

The subject line from the latest email is used as a variable ($reg) and tagged on the end of the FlightRader24 URL like this : https://www.flightradar24.com/simple?reg=<?php echo $reg; ?>

The page HTML looks like this:

<head><META http-equiv="refresh" content="600;URL="URL to side.php"></head>
<body style="margin:0px;">
<?php
/* connect to server */
$hostname = '{IP/Hostname of Mailserver/pop3/novalidate-cert}INBOX';
$username = 'Email Username';
$password = 'Email Password';

/* try to connect */
$mbox = imap_open($hostname,$username,$password) or die('Cannot connect to Tiriyo: ' . imap_last_error());

// get information about the current mailbox (INBOX in this case)
$mboxCheck = imap_check($mbox);

// get the total amount of messages
$totalMessages = $mboxCheck->Nmsgs;

// select how many messages you want to see
$showMessages = 1;

// get those messages
$result = array_reverse(imap_fetch_overview($mbox,($totalMessages-$showMessages+1).":".$totalMessages));

// iterate trough those messages
foreach ($result as $mail) {

//print_r($mail);

// if you want the mail body as well, do it like that. Note: the '1.1' is the section, if a email is a multi-part message in MIME format, you'll get plain text with 1.1
//$mailBody = imap_fetchbody($mbox, $mail->msgno, '1.1');

// but if the email is not a multi-part message, you get the plain text in '1'
// if(trim($mailBody)=="") {
$var = imap_fetchbody($mbox, $mail->msgno, '2');
//get email subject
$reg = "{$mail->subject}\n";

//}

// just an example output to view it
//echo $reg;
}

imap_close($mbox);
?>
<div class="box">
<iframe src="https://www.flightradar24.com/simple?reg=<?php echo $reg; ?>" frameborder="0" scrolling="no" height="65%" width="100%"></iframe>
</div>

<div class="box">
<iframe src="URL to Weather.html" frameborder="0" scrolling="no" height="35%" width="30%" align="left"></iframe>
</div>

<div> <iframe src="http://www.trafficnews.co/widget/?w=100%&l=Postcode&val=YourPostcode&r=50" height="35%" width="70%" scrolling="no" marginheight="0" marginwidth="0" frameborder="0" align="right"></iframe></div>
<div class="box">

weather.html

I've used the MetOffice weather widget to make this because the weather provided with DakBoard wasn't good enough in my opinion.

You can embed the weather however you want, but here's the html for my page.

<head></head>

<body style="margin:0px;" bgcolor="#000000">

<div class="box">

</body>

2018 Updates

Crypto-widgets

Because I've been wasting my hard earned cash on cryptocurrencies, I wanted a way to monitor the prices while I was cooking breakfast - so I used widgets from this website.

I was able to insert a few graphs onto the board using a separate HTML page containing HTLM tables with each graph in, otherwise they wouldn't sit evenly.

Strava Widget

I've been using Map My Ride for logging my cycling workouts for ages and have resisted making the switch to Strava because I saw no reason to. But, then I stumbled across the Strava activity widget which I thought would be cool to display on the screen. It was very easy to set up, here is a guide.

The only problem I had was that the background colour is white and there's no way of changing this within the embed code. I ended up using some CSS to invert the colours.

Put this in <head>

<head><link rel="stylesheet" href="./style.css"></head>

Then this in body tag:
<body style="margin:0px;" bgcolor="#000000" class="invert">

Create a style.css file with:
.invert {
filter: invert(100%);
-webkit-filter: invert(100%);
}

Finished Product

Overall I'm pretty impressed with the outcome and it's definitely a source of amusement when we have visitors!

Dynamic SCCM report for installed software, altered by Orchestrator

Posted on August 21, 2017by Tom Pearson with No comments

Although this report might not be useful to everyone, the method of altering the data within the report might be of use.

The report essentially provides a list of all systems that have certain software installed, and presents the CPU and Operating System information for each.

The software titles to include in the report are retrieved from a separate database which can be edited by end-users with Orchestrator runbooks.

(Removed server/domain data)

The Report

The recipients of the report wanted to retrieve a list of systems based on a predefined software list (Microsoft SQL Server, Symantec NetBackup Client etc etc). I was keen not to hard code this list of software into the report query because A: there were nearly 100 software titles to report on, which would look pretty messy in the query, and B: Software titles needed to be added and removed easily.

I created a couple of data tables on a separate database (CM_Runtime) to hold the lists of software they wanted to report on, one for Windows (dbo.reportsoftware) and the other for Linux (dbo.reportapplications).

SCCM inventory data for Linux applications is stored in 'System_ADD_REMOVE_PROGRAMS0.DisplayName00' where-as the Windows software we wanted to retrieve was in 'System_INSTALLED_SOFTWARE0.ARPDisplayName00'

The query to retrieve the system lists based on the contents of dbo.reportsoftware and dbo.reportapplications is below.

select distinct

SMS_R_System.Name0 AS [Server],

SMS_R_System.Resource_Domain_OR_Workgr0 AS Domain,

___System_INSTALLED_SOFTWARE0.ARPDisplayName00 AS [Application Name],

SMS_G_System_PROCESSOR.NumberOfCores00 AS [Number of Cores],

SMS_G_System_COMPUTER_SYSTEM.NumberOfProcessors00 AS [Number of Processors],

SMS_G_System_PROCESSOR.NumberOfLogicalProcessors00 AS [Number of Logical CPUs],

SMS_G_System_OPERATING_SYSTEM.Caption00 AS [Operating System]

from

vSMS_R_System AS SMS_R_System

INNER JOIN INSTALLED_SOFTWARE_DATA AS ___System_INSTALLED_SOFTWARE0 ON ___System_INSTALLED_SOFTWARE0.MachineID = SMS_R_System.ItemKey

INNER JOIN Processor_DATA AS SMS_G_System_PROCESSOR ON SMS_G_System_PROCESSOR.MachineID = SMS_R_System.ItemKey

INNER JOIN Computer_System_DATA AS SMS_G_System_COMPUTER_SYSTEM ON SMS_G_System_COMPUTER_SYSTEM.MachineID = SMS_R_System.ItemKey

INNER JOIN Operating_System_DATA AS SMS_G_System_OPERATING_SYSTEM ON SMS_G_System_OPERATING_SYSTEM.MachineID = SMS_R_System.ItemKey

INNER JOIN _RES_COLL_SMSDM003 AS SMS_CM_RES_COLL_SMSDM003 ON SMS_CM_RES_COLL_SMSDM003.MachineID = SMS_R_System.ItemKey

where

___System_INSTALLED_SOFTWARE0.ARPDisplayName00 in

( SELECT InstalledSoftware

FROM CM_RunTime.dbo.reportsoftware )

UNION

select distinct

SMS_R_System.Name0 AS [Server],

SMS_R_System.Resource_Domain_OR_Workgr0 AS Domain,

__System_ADD_REMOVE_PROGRAMS0.DisplayName00 AS [Application Name],

SMS_G_System_PROCESSOR.NumberOfCores00 AS [Number of Cores],

SMS_G_System_COMPUTER_SYSTEM.NumberOfProcessors00 AS [Number of Processors],

SMS_G_System_PROCESSOR.NumberOfLogicalProcessors00 AS [Number of Logical CPUs],

SMS_G_System_OPERATING_SYSTEM.Caption00 AS [Operating System]

from

vSMS_R_System AS SMS_R_System

INNER JOIN Add_Remove_Programs_DATA AS __System_ADD_REMOVE_PROGRAMS0 ON __System_ADD_REMOVE_PROGRAMS0.MachineID = SMS_R_System.ItemKey

INNER JOIN Processor_DATA AS SMS_G_System_PROCESSOR ON SMS_G_System_PROCESSOR.MachineID = SMS_R_System.ItemKey

INNER JOIN Computer_System_DATA AS SMS_G_System_COMPUTER_SYSTEM ON SMS_G_System_COMPUTER_SYSTEM.MachineID = SMS_R_System.ItemKey

INNER JOIN Operating_System_DATA AS SMS_G_System_OPERATING_SYSTEM ON SMS_G_System_OPERATING_SYSTEM.MachineID = SMS_R_System.ItemKey

where __System_ADD_REMOVE_PROGRAMS0.DisplayName00

( SELECT InstalledApplications

FROM CM_RunTime.dbo.reportapplications )

Adding and Removing Software in Report

I made an initial bulk import of software to the data tables using PowerShell, but for ongoing management I've configured Orchestrator to remove or add rows to the tables using a couple of runbooks:

(Remove Software title)

(Add Software title)

Each runbook initialize data properties are as follows:

To add a software title the 'Write to Database' activity is used.

To remove a software title the 'Query Database' activity is used.

A list of the current software titles held in the data tables is sent each time the tables are edited using a PowerShell script to retrieve the data and create a CSV, and then it's emailed.

# Set script parameters from runbook data bus and Orchestrator global variables

# Define any inputs here and then add to the $argsArray and script block parameters below

$ReportPath = "D:\SCORCH Data\SoftwareReports\softwarereport-$(Get-Date -Format yyyyMMddHHmmss).csv"

$Username = "#SQLUSER#"

$Password = "#PASSWORD#"

$Database = "CM_Runtime"

$SQLServer = "#SQLSERVERNAME#"

#-----------------------------------------------------------------------

## Initialize result and trace variables

# $ResultStatus provides basic success/failed indicator

# $ErrorMessage captures any error text generated by script

# $Trace is used to record a running log of actions

$ResultStatus = ""

$ErrorMessage = ""

$Trace = (Get-Date).ToString() + "`t" + "Runbook activity script started" + " `r`n"

# Create argument array for passing data bus inputs to the external script session

$argsArray = @()

$argsArray += $ReportPath

$argsArray += $Username

$argsArray += $Password

$argsArray += $Database

$argsArray += $SQLServer

# Establish an external session (to localhost) to ensure 64bit PowerShell runtime using the latest version of PowerShell installed on the runbook server

# Use this session to perform all work to ensure latest PowerShell features and behavior available

$Session = New-PSSession -ComputerName localhost

# Invoke-Command used to start the script in the external session. Variables returned by script are then stored in the $ReturnArray variable

$ReturnArray = Invoke-Command -Session $Session -Argumentlist $argsArray -ScriptBlock {

# Define a parameter to accept each data bus input value. Recommend matching names of parameters and data bus input variables above

Param(

[ValidateNotNullOrEmpty()]

[string]$ReportPath,

[ValidateNotNullOrEmpty()]

[string]$Username,

[ValidateNotNullOrEmpty()]

[string]$Password,

[ValidateNotNullOrEmpty()]

[string]$Database,

[ValidateNotNullOrEmpty()]

[string]$SQLServer

)

# Define function to add entry to trace log variable

function AppendLog ([string]$Message)

{

$script:CurrentAction = $Message

$script:TraceLog += ((Get-Date).ToString() + "`t" + $Message + " `r`n")

}

# Set external session trace and status variables to defaults

$ResultStatus = ""

$ErrorMessage = ""

$script:CurrentAction = ""

$script:TraceLog = ""

try

{

# Add startup details to trace log

AppendLog "Script now executing in external PowerShell version [$($PSVersionTable.PSVersion.ToString())] session in a [$([IntPtr]::Size * 8)] bit process"

AppendLog "Running as user [$([Environment]::UserDomainName)\$([Environment]::UserName)] on host [$($env:COMPUTERNAME)]"

AppendLog "Parameter values received:ReportPath=[$ReportPath] Database=[$Database] SQLServer=[$SQLServer]"

# The actual work the script does goes here

AppendLog "SQL Query to retrieve"

$Array = @(Invoke-SQLCmd -query "SELECT InstalledApplications AS [Software] FROM dbo.reportapplications UNION SELECT InstalledSoftware AS [Software] FROM dbo.reportsoftware" -Server $SQLServer -Username $Username -Password $Password -Database $Database)

AppendLog "Writing to CSV File"

$Array | ConvertTo-Csv -NoTypeInformation |

% {$_.Replace('"','')} |

Out-File $ReportPath -Encoding ascii

# Validate results and set return status

AppendLog "Finished work, determining result"

$EverythingWorked = $true

if($EverythingWorked -eq $true)

{

$ResultStatus = "Success"

}

else

{

$ResultStatus = "Failed"

}

catch

{

# Catch any errors thrown above here, setting the result status and recording the error message to return to the activity for data bus publishing

$ResultStatus = "Failed"

$ErrorMessage = $error[0].Exception.Message

AppendLog "Exception caught during action [$script:CurrentAction]: $ErrorMessage"

}

finally

{

# Always do whatever is in the finally block. In this case, adding some additional detail about the outcome to the trace log for return

if($ErrorMessage.Length -gt 0)

{

AppendLog "Exiting external session with result [$ResultStatus] and error message [$ErrorMessage]"

}

else

{

AppendLog "Exiting external session with result [$ResultStatus]"

}

# Return an array of the results. Additional variables like "myCustomVariable" can be returned by adding them onto the array

$resultArray = @()

$resultArray += $ResultStatus

$resultArray += $ErrorMessage

$resultArray += $script:TraceLog

$resultArray += $myCustomVariable

return $resultArray

}#End Invoke-Command

# Get the values returned from script session for publishing to data bus

$ResultStatus = $ReturnArray[0]

$ErrorMessage = $ReturnArray[1]

$Trace += $ReturnArray[2]

$MyCustomVariable = $ReturnArray[3]

# Record end of activity script process

$Trace += (Get-Date).ToString() + "`t" + "Script finished" + " `r`n"

# Close the external session

Remove-PSSession $Session

Check the Status of ConfigMgr MP Replica's with Orchestrator

Posted on August 17, 2017by Tom Pearson with No comments

I recently identified the need to receive an alert when one of the SCCM Management Point SQL replicas became un-healthy. (e.g. replication with subscriber fails).

A simple SQL query can be run on the CMDistribution database to bring back the status of the Replication Agents. Using this information an email alert can be triggered using Orchestrator.
I've scheduled the parent runbook to execute once a day using a Windows scheduled task and SCOJobrunner.exe.

The resulting email will look something like this (if you have 4 SCCM MP's):

Overview

(Parent runbook)

(Child Runbook)

Runbook Activity's

The first step invokes the child runbook, this is done so that the data retrieved using the SQL query is flattened to prevent sending an email per MPReplica.

Run SQL query to retrieve the status of each MPReplica from CMDistribution DB. Status values are as follows:

Started
Succeeded
In Progress
Idle
Retrying
Failed

SELECT status AS ReplicationAgentsStatus

FROM dbo.MSReplication_monitordata

WHERE publication = 'ConfigMgr_MPReplica'

We only need to alert when the status = 5 or 6. So the links from the SQL query will split based on this.

(Go to 'Error' when SQL string contains 5 or 6)

(Go to "OK" when SQL string contains 1,2,3 or 4)

In order to create a Returned Data value and generate some events (just in case SCOM needed to collect the status in the future) I used 'Send Event Log Message' activity's.

The 'Return Data' activity's include the activity name from either 'Send Event Log Message'

(Return Activity Name from 'OK')

(Return Activity Name from 'Error")

(Parent runbook Returned Data from child runbook)

Using the flattened return data, determine whether to send an email alert or not. If there are 4 SCCM MPReplica's with no issues the data will look like: "OK,OK,OK,OK". If there was an issue with one of the servers it would look like: "OK,Error,OK,OK". The links to the subsequent activity look like this:

(Go to 'Send Email' if returned data contains the word 'Error')

(Go to OK if returned data doesn't contain 'Error')

If there is an error, use PowerShell script to generate an email with the current status of MPReplica's in HTML format. This PS script uses the SCORCH best practice template (More info) because PS v.2 doesn't support the SQL functions needed.

The SQL and SMTP details are stored as Orchestrator variables which are called in the PS script.

$Username = "\`d.T.~Vb/{9995A45F-87F0-40B9-8D7A-AA113E4EB251}\`d.T.~Vb/"

$Password = "\`d.T.~Vb/{AC9E30A6-7F4F-4E2E-BA15-82ECE7D58301}\`d.T.~Vb/"

$Database = "CMDistribution"

$SQLServer = "\`d.T.~Vb/{68A5FAA6-AFEC-4DE0-A40F-D06DEB80BCA6}\`d.T.~Vb/"

$FROM="\`d.T.~Vb/{E755220C-D529-46B9-B72E-D105CE15C5BC}\`d.T.~Vb/"

$SMTP = "\`d.T.~Vb/{806E12A2-90B9-41BE-9F2F-7E32BAEF57B0}\`d.T.~Vb/"

$smtpUsername = "TFL\\`d.T.~Vb/{3E209AB6-5BE8-4D67-AB26-6A797DC68F91}\`d.T.~Vb/"

$smtpPassword = "\`d.T.~Vb/{A2168CE3-4E2A-404B-9A1B-2D245165A526}\`d.T.~Vb/"

#-----------------------------------------------------------------------

## Initialize result and trace variables
# $ResultStatus provides basic success/failed indicator
# $ErrorMessage captures any error text generated by script
# $Trace is used to record a running log of actions
$ResultStatus = ""
$ErrorMessage = ""
$Trace = (Get-Date).ToString() + "`t" + "Runbook activity script started" + " `r`n"

# Create argument array for passing data bus inputs to the external script session
$argsArray = @()
$argsArray += $Username
$argsArray += $Password
$argsArray += $Database
$argsArray += $SQLServer
$argsArray += $FROM
$argsArray += $SMTP
$argsArray += $smtpUsername
$argsArray += $smtpPassword

# Establish an external session (to localhost) to ensure 64bit PowerShell runtime using the latest version of PowerShell installed on the runbook server
# Use this session to perform all work to ensure latest PowerShell features and behavior available
$Session = New-PSSession -ComputerName localhost

# Invoke-Command used to start the script in the external session. Variables returned by script are then stored in the $ReturnArray variable
$ReturnArray = Invoke-Command -Session $Session -Argumentlist $argsArray -ScriptBlock {
# Define a parameter to accept each data bus input value. Recommend matching names of parameters and data bus input variables above
Param(
[ValidateNotNullOrEmpty()]
[string]$Username,

[ValidateNotNullOrEmpty()]
[string]$Password,

[ValidateNotNullOrEmpty()]
[string]$Database,

[ValidateNotNullOrEmpty()]
[string]$SQLServer,

[ValidateNotNullOrEmpty()]
[string]$FROM,

[ValidateNotNullOrEmpty()]
[string]$SMTP,

[ValidateNotNullOrEmpty()]
[string]$smtpUsername,

[ValidateNotNullOrEmpty()]
[string]$smtpPassword
)

# Define function to add entry to trace log variable
function AppendLog ([string]$Message)
{
$script:CurrentAction = $Message
$script:TraceLog += ((Get-Date).ToString() + "`t" + $Message + " `r`n")
}

# Set external session trace and status variables to defaults
$ResultStatus = ""
$ErrorMessage = ""
$script:CurrentAction = ""
$script:TraceLog = ""

try
{
# Add startup details to trace log
AppendLog "Script now executing in external PowerShell version [$($PSVersionTable.PSVersion.ToString())] session in a [$([IntPtr]::Size * 8)] bit process"
AppendLog "Running as user [$([Environment]::UserDomainName)\$([Environment]::UserName)] on host [$($env:COMPUTERNAME)]"
AppendLog "Parameter values received:Username=[$Username] Database=[$Database] SQLServer=[$SQLServer] FROM=[$FROM] SMTP=[$SMTP] smtpUsername=[$smtpUsername]"

# The actual work the script does goes here
AppendLog "SQL Query to retrieve accounts"

$pass = ConvertTo-SecureString -AsPlainText $smtpPassword -Force
$Cred = New-Object System.Management.Automation.PSCredential -ArgumentList $smtpUsername,$pass

$Array = @(Invoke-SQLCmd -query "SELECT agent_name,status,lastrefresh,last_distsync,time_stamp,cur_latency FROM dbo.MSReplication_monitordata WHERE publication = 'ConfigMgr_MPReplica'" -Server $SQLServer -Username $Username -Password $Password -Database $Database) | ConvertTo-Html -Fragment

# Build and send email for each contact.
AppendLog "Build and send email"

# Config at top of script
# Param for sending mail

$smtpsettings = @{
To = "\`d.T.~Vb/{57C5F4D7-D2FA-46E5-8D10-A31FACFE16EB}\`d.T.~Vb/"
From = $From
SmtpServer = $SMTP
Subject = "ConfigMgr MPReplica Status Error"
Port = "587"
Credential = $cred
}

$date = (Get-Date).AddDays(-60)
$htmlhead = "<html>
<style>
BODY{font-family: Calibri; font-size: 11pt;}
H1{font-size: 22px; font-family: 'Segoe UI Light','Segoe UI','Lucida Grande',Verdana,Arial,Helvetica,sans-serif;}
H2{font-size: 18px; font-family: 'Segoe UI Light','Segoe UI','Lucida Grande',Verdana,Arial,Helvetica,sans-serif;}
H3{font-size: 16px; font-family: 'Segoe UI Light','Segoe UI','Lucida Grande',Verdana,Arial,Helvetica,sans-serif;}
TABLE{border: 1px solid black; border-collapse: collapse; font-size: 8pt;}
TH{border: 1px solid #969595; background: #dddddd; padding: 5px; color: #000000;}
TD{border: 1px solid #969595; padding: 5px; }
td.pass{background: #B7EB83;}
td.warn{background: #FFF275;}
td.fail{background: #FF2626; color: #ffffff;}
td.info{background: #85D4FF;}
</style>
<body>
<p>Hello<br>There is an issue with replication on the SCCM MP Replica's. Please see below for more information.<br><br>
Yours Faithfully,<br>
Orchestrator</p>"

$htmltail = "</body></html>"

$body = $htmlhead + $array + $htmltail

Send-MailMessage @smtpsettings -body $body -BodyAsHtml

# Validate results and set return status
AppendLog "Finished work, determining result"
$EverythingWorked = $true
if($EverythingWorked -eq $true)
{
$ResultStatus = "Success"
}
else
{
$ResultStatus = "Failed"
}
}
catch
{
# Catch any errors thrown above here, setting the result status and recording the error message to return to the activity for data bus publishing
$ResultStatus = "Failed"
$ErrorMessage = $error[0].Exception.Message
AppendLog "Exception caught during action [$script:CurrentAction]: $ErrorMessage"
}
finally
{
# Always do whatever is in the finally block. In this case, adding some additional detail about the outcome to the trace log for return
if($ErrorMessage.Length -gt 0)
{
AppendLog "Exiting external session with result [$ResultStatus] and error message [$ErrorMessage]"
}
else
{
AppendLog "Exiting external session with result [$ResultStatus]"
}

}

# Return an array of the results. Additional variables like "myCustomVariable" can be returned by adding them onto the array
$resultArray = @()
$resultArray += $ResultStatus
$resultArray += $ErrorMessage
$resultArray += $script:TraceLog
$resultArray += $myCustomVariable
return $resultArray

}#End Invoke-Command

# Get the values returned from script session for publishing to data bus
$ResultStatus = $ReturnArray[0]
$ErrorMessage = $ReturnArray[1]
$Trace += $ReturnArray[2]
$MyCustomVariable = $ReturnArray[3]

# Record end of activity script process
$Trace += (Get-Date).ToString() + "`t" + "Script finished" + " `r`n"

# Close the external session
Remove-PSSession $Session

Export Multiple PFX Certificates using Orchestrator

Posted on August 15, 2017by Tom Pearson with No comments

Following on from my last post, I had to throw this runbook together in order to create a batch of PFX files ready for manual SCCM client installations on *nix systems. The certificate requests needed to be targeted at a CA in the relevant Active Directory forest. (PreProd, Production, or ST).

Overview

The parent runbook imports a list of servers from a CSV containing the hostname and domain name, then invokes a child runbook on the relevant Orchestrator Runbook Server, depending on the domain name.

The CSV is formatted as follows:

Server,Domain
HOST1,PROD
HOST2,PREPROD

Runbook Activity's

Initialize Data: Nothing configured here yet.
Read Line: Pointed at the location of the CSV file.

GetRecords: PowerShell to ingest each line from the CSV and published to the Orchestrator data bus with $ArrayList variable.

$ArrayList = @()

{Line text from "Read Line"}

"@.Split("'`n") | foreach {

$ArrayList += "$_"

}

ProcessRecords: PowerShell to split the $ArrayList string into individual variables ready to be used for subsequent activity's.

$Computer= "[Field({ArrayList from "GetRecords"},',',1)]"

$Domain= "[Field({ArrayList from "GetRecords"},',',2)]"

Invoke the Request PFX Runbook: This runbook will be run on the corresponding Orchestrator Runbook Server depending on the 'Domain' value.

(The Server name is translated into the CN parameter.)

Request and Export PFX Certificate

The 'Domain' and 'Server' values from the CSV are parsed into the script which ultimately exports a PFX file to D:\SCORCH ready to be transferred to the target Linux system and used during SCCM client installation.

This is the contents of the PowerShell 'Run .Net Script' activity.

Define variables using values from the Orchestrator data bus:

Determine the issuing CA server and Certificate Template name using the 'Domain' Initialize Data value.

if ($Domain -like "PREPROD")

{

$CAName = "#SERVERNAME#\PreProd CA"

$TemplateName = "ConfigMgrClientCertificateforExport"

}

elseif ($Domain -like "PROD")

{

$CAName = "#SERVERNAME#\IssuingCA"

$TemplateName = "ConfigMgrClientCertificateforExport"

}

elseif ($Domain -like "ST")

{

$CAName = "#SERVERNAME#\ST CA"

$TemplateName = "ConfigMgrClientCertificateforExport"

}

Define a function to remove temporary certificate request files.

function Remove-ReqTempfiles()

{

param(

[String[]]$tempfiles

)

Write-Verbose "Cleanup temp files and pending requests"

#delete pending request (if a request exists for the CN)

$certstore = new-object system.security.cryptography.x509certificates.x509Store('REQUEST', 'LocalMachine')

$certstore.Open('ReadWrite')

foreach($certreq in $($certstore.Certificates))

{

if($certreq.Subject -eq "CN=$CN")

{

$certstore.Remove($certreq)

}

$certstore.close()

foreach($file in $tempfiles){remove-item ".\$file" -ErrorAction silentlycontinue}

}

Remove temporary request files (in case left over from previous requests)

Remove-ReqTempfiles -tempfiles "certreq.inf","certreq.req","$CN.cer","$CN.rsp"

Build certificate request file

$file = @"

[NewRequest]

Subject = "CN=$CN"

MachineKeySet = TRUE

KeyLength = 2048

Exportable = TRUE

[RequestAttributes]

CertificateTemplate = "$TemplateName"

Set-Content .\certreq.inf $file

Submit certificate request and add to local cert store.

Invoke-Expression -Command "certreq -new certreq.inf certreq.req"

Invoke-Expression -Command "certreq -submit -config `"$CAName`" certreq.req $CN.cer"

Invoke-Expression -Command "certreq -accept $CN.cer"

Export PFX file to D:\SCORCH directory

$certpath = "d:\SCORCH\$CN.pfx"

Invoke-Expression -Command "certutil -privatekey -exportPFX -p 'Password' my $CN $certpath"

Remove certificate from local cert store, and remove temporary files.

$cert = Get-Childitem "cert:\LocalMachine\My" | where-object {$_.Thumbprint -eq (New-Object System.Security.Cryptography.X509Certificates.X509Certificate2((Get-Item "$CN.cer").FullName,"")).Thumbprint}

$certstore = new-object system.security.cryptography.x509certificates.x509Store('My', 'LocalMachine')

$certstore.Open('ReadWrite')

$certstore.Remove($cert)

$certstore.close()

Remove-ReqTempfiles -tempfiles "certreq.inf","certreq.req","$CN.cer","$CN.rsp"

The PFX file will be located on each Orchestrator server in the D:\SCORCH directory, which then needs to be manually copied to where ever it's needed.

Using Orchestrator to install a ConfigMgr Linux Client with PKI Certificate

Posted on August 14, 2017by Tom Pearson with No comments

My first blog post will be an extension of a great post by Laurie Rhodes who constructed an Orchestrator runbook to install the Linux SCCM client. HERE
I won't go into much detail about all of the steps in the runbook, but I will detail some of the bits I've added. My plan is to create some other blog posts about running the SCCM client on Linux to provide some more context.

A brief overview of the environment:

Multiple Windows forests and logical network locations. (Prod, PreProd, & SystemTest)
An SCCM Management Point serving each forest.
A SCORCH Runbook Server in each domain.
SCCM Management Points communicate on HTTPS.
A Windows CA in each forest.

I needed to add the following functionality to this work flow:

Execute the SSH commands from an Orchestrator server situated logically close to the target Linux server.
Request and export a certificate from the local CA to be used to secure communication from the target Linux server SCCM client and the HTTPS SCCM Management Point.
Append the hostname / IP address of the local SCCM management point to /etc/hosts on each Linux system.

Initialize Data

Linux Username: Input a user with root privileges on the target Linux system
Linux Password: User password.
Target IP Address: IP address of target Linux system.
Domain: The domain / environment the target Linux system is situated in.
System Name: The hostname of the target Linux system

Runbooks

The parent runbook is used to determine which Orchestrator Runbook Server executes the 'ConfigMgr - Install Universal Client' runbook by using the 'Domain' Initialize Data.

The runbook server is defined in the Invoke Runbook activity property 'Runbook Server'.

The 'ConfigMgr - Install Universal Client' runbook (below) contains the following activities.

The 'Request PFX Certificate' runbook is invoked on the runbook server currently being used by parsing the 'Runbook Server' Published Data to it:

The 'Request PFX Certificate' runbook contains a simple PowerShell script, as described below.

Request and Export a PFX for Linux SCCM Client.

The 'Domain' and 'System Name' (shown as CN) Initialize Data values are parsed into the script which ultimately exports a PFX file to D:\SCORCH ready to be transferred to the target Linux system and used during SCCM client installation.

This is the contents of the PowerShell 'Run .Net Script' activity.

Define variables using values from the Orchestrator data bus:

Determine the issuing CA server and Certificate Template name using the 'Domain' Initialize Data value.

if ($Domain -like "PREPROD")

{

$CAName = "#SERVERNAME#\PreProd CA"

$TemplateName = "ConfigMgrClientCertificateforExport"

}

elseif ($Domain -like "PROD")

{

$CAName = "#SERVERNAME#\IssuingCA"

$TemplateName = "ConfigMgrClientCertificateforExport"

}

elseif ($Domain -like "ST")

{

$CAName = "#SERVERNAME#\ST CA"

$TemplateName = "ConfigMgrClientCertificateforExport"

}

Define a function to remove temporary certificate request files.

function Remove-ReqTempfiles()

{

param(

[String[]]$tempfiles

)

Write-Verbose "Cleanup temp files and pending requests"

#delete pending request (if a request exists for the CN)

$certstore = new-object system.security.cryptography.x509certificates.x509Store('REQUEST', 'LocalMachine')

$certstore.Open('ReadWrite')

foreach($certreq in $($certstore.Certificates))

{

if($certreq.Subject -eq "CN=$CN")

{

$certstore.Remove($certreq)

}

$certstore.close()

foreach($file in $tempfiles){remove-item ".\$file" -ErrorAction silentlycontinue}

}

Remove temporary request files (in case left over from previous requests)

Remove-ReqTempfiles -tempfiles "certreq.inf","certreq.req","$CN.cer","$CN.rsp"

Build certificate request file

$file = @"

[NewRequest]

Subject = "CN=$CN"

MachineKeySet = TRUE

KeyLength = 2048

Exportable = TRUE

[RequestAttributes]

CertificateTemplate = "$TemplateName"

Set-Content .\certreq.inf $file

Submit certificate request and add to local cert store.

Invoke-Expression -Command "certreq -new certreq.inf certreq.req"

Invoke-Expression -Command "certreq -submit -config `"$CAName`" certreq.req $CN.cer"

Invoke-Expression -Command "certreq -accept $CN.cer"

Export PFX file to D:\SCORCH directory

$certpath = "d:\SCORCH\$CN.pfx"

Invoke-Expression -Command "certutil -privatekey -exportPFX -p 'Password' my $CN $certpath"

Remove certificate from local cert store, and remove temporary files.

$certstore = new-object system.security.cryptography.x509certificates.x509Store('My', 'LocalMachine')

$certstore.Open('ReadWrite')

$certstore.Remove($cert)

$certstore.close()

Remove-ReqTempfiles -tempfiles "certreq.inf","certreq.req","$CN.cer","$CN.rsp"

The resulting PFX file is transferred to the target Linux system in the 'Transfer Client Files' activity.

I added the following lines to the existing PowerShell script.

$certpath = "{certpath from "Request PFX Certificate"}"

$p = Start-Process cmd.exe -ArgumentList "/c D:\SCORCH\pscp.exe -l $linuxUsername -pw $linuxPassword -C -scp $certpath $linuxUsername`@$Target`:/tmp/" -wait -WindowStyle Hidden -PassThru

Append /etc/hosts with SCCM Management Point

Using an SSH activity, the /etc/hosts file is appended with the hostname and IP address of the corresponding SCCM Management Point for the target Linux system.

The hostname and IP is determined using PowerShell ('Set MP' activity) and the 'Domain' Initialized Data.

$Domain = "#INITIALIZE-DATA#"

if ($Domain -like "PREPROD")

{

$MP= "#PREPROD-MP-FQDN#"

$MPNetbios = "#PREPROD-MP#"

$IP = "#IP ADDRESS#"

}

elseif ($Domain -like "PROD")

{

$MP= "#PROD-MP-FQDN#"

$MPNetbios = "#PROD-MP#"

$IP = "#IP ADDRESS#"

}

elseif ($Domain -like "ST")

{

$MP= "ST-MP-FQDN#"

$MPNetbios = "#ST-MP#"

$IP = "#IP-ADDRESS#"

}

The published data from this script is as follows:

The SSH command to use this data looks like this:

echo '{IP from "Set MP"} {MPNetBios from "Set MP"} {MP from "Set MP"}' >> /etc/hosts

SCCM Client Installation

Using the resulting PFX file and Published Data, the final installation command looks something like the following:

-t sudo /tmp/./install -mp {MP from "Set MP"} /tmp/ccm-Universalx64.tar -sitecode ### -UsePKICert /tmp/{CN from "Request PFX Certificate"}.pfx -NoCRLCheck -certpw #Password# -keepdb -fsp #FSP-Hostname#

Conclusion

The fundamental functions in this runbook were kindly put together by someone else, but with a few extra PowerShell scripts and some clever runbook invocation I was able to achieve my end goal.

Menu

Device Management Ramblings

A blog focused on Microsoft technologies, predominantly Intune, ConfigMgr, M365 and Azure.

Raspberry Pi Kitchen Wall Display Screen

DakBoard

Webpage

index.html

side.php

weather.html

2018 Updates

Crypto-widgets

Strava Widget

Finished Product

Dynamic SCCM report for installed software, altered by Orchestrator

The Report

Adding and Removing Software in Report

Check the Status of ConfigMgr MP Replica's with Orchestrator

Overview

Runbook Activity's

Export Multiple PFX Certificates using Orchestrator

Overview

Runbook Activity's

Using Orchestrator to install a ConfigMgr Linux Client with PKI Certificate

Initialize Data

Runbooks

Request and Export a PFX for Linux SCCM Client.

Append /etc/hosts with SCCM Management Point

SCCM Client Installation

Conclusion

About Me

Popular Posts

Search This Blog

Categories

Blog Archive

About

DakBoard

Webpage

index.html

side.php

weather.html

**2018 Updates**

Crypto-widgets

Strava Widget

Finished Product

The Report

Adding and Removing Software in Report

Overview

Runbook Activity's

Overview

Runbook Activity's

Initialize Data

Runbooks

Request and Export a PFX for Linux SCCM Client.

Append /etc/hosts with SCCM Management Point

SCCM Client Installation

Conclusion

About Me

Popular Posts

Search This Blog

Categories

Blog Archive

About

2018 Updates