SharePoint – Create a JSON Field Customizer Extension

INTRODUCTION

SharePoint Field Customizers are client-side components that run inside the context of a SharePoint page. 

Currently, a React project is available out-of-the-box with the second option of “No JavaScript framework” for those ambitious enough to add their own preferred UI libraries.

SETUP YOUR DEVELOPMENT ENVIRONMENT

This article is not going to go into the details on how to set up your development environment. A good article can be found here.

My only recommendation is to use NVM instead of the default installation of NodeJS.

NVM allows you to install multiple versions of NodeJS and handles the heavy-lifting in swapping between them.

CREATE A FIELD CUSTOMIZER EXTENSION

As part of the article, I will demonstrate how to create a Field Customizer Extension that will display human-readable file size in Bytes, Kb, Mb, Gb, Tb, Pb, Eb, Zb, Yb from a number value store in a Sharepoint number column. In my project, I use a React component called file-bytes-formatter, but once you get the hang of it, you will be able to extend these to most React components.

Follow these steps to create the Field Customizer Extension template project. These steps have been adapted from the official Build your first Field Customizer extension article.

1) Create a new project directory in a convenient location

md Bytes-Field-Extension

2) Go to the project directory

cd Bytes-Field-Extension

3) Run the Yeoman SharePoint Generator 

yo @microsoft/sharepoint

4) When prompted, enter the following values (select the default option for all prompts omitted below): 

  • What is your solution name?: bytes-field-extension
  • Will the components in the solution require permissions to access web APIs that are unique and not shared with other components in the tenant?: (y/n) n
  • Which type of client-side component to create?: Extension
  • Which type of client-side extension to create?: Field Customizer
  • What is your Field Customizer name?: Bytes Field Extension
  • What is your Field Customizer description?: This Field Customizer Extension will return human-readable file size in Bytes, Kb, Mb, Gb, Tb, Pb, Eb, Zb, Yb
  • Which framework would you like to use?: React

At this point, Yeoman installs the required dependencies and scaffolds the solution files along with the HelloWorld extension. This might take a few minutes. When prompted, enter the following values (select the default option for all prompts omitted below):

5) Install file-bytes-formatter React component

npm install file-bytes-formatter 

6) Type the following into the console to start Visual Studio Code or open project in your favourite editor

code .

7) Within src\extensions\bytesFieldExtension\BytesFieldExtensionFieldCustomizer.ts add the following line under the last import command.

import Fsf from "file-bytes-formatter"

This will make the file-bytes-formatter component available in your project.

8) Within src\extensions\bytesFieldExtension\BytesFieldExtensionFieldCustomizer.ts change this line

const text: string = `${this.properties.sampleText}: ${event.fieldValue}`;

to

const text: string = `${Fsf(+(event.fieldValue).split(',').join(''))}`;

This will parse the text from the SharePoint column through the file-bytes-formatter component. The spit function is used to remove any commas that SharePoint might have added to style the number.

9) Make a note of the ClientSideComponentId in sharepoint\assets\elements.xml. You will need this later.

10) Save project and close project

PACKAGING FIELD CUSTOMIZER EXTENSION

1) In the console window, enter the following command to package your client-side solution that contains the extension so that we get the basic structure ready for packaging: 

gulp bundle --ship

2) Execute the following command so that the solution package is created: 

gulp package-solution --ship

The command creates the package: ./sharepoint/solution/bytes-field-extension.sppkg

DEPLOYING FIELD CUSTOMIZER EXTENSION

After deploying the bytes-field-extension.sppkg into your environment (see Deploy your client-side web part to a SharePoint page if you are unfamiliar with this process) you need to configure the field that you want this formatter to take effect on.

1) Using PnP PowerShell connect to your site

Connect-PnPOnline -Url "https://YOURTENANT.sharepoint.com/sites/YOURSITE" -UseWebLogin

2) Get a reference to the field object you want to configure to use this Field Customizer Extension

$field = Get-PnPField -List "YOURLIST" -Identity "YOURFIELDNAME"

3) Set the GUID from CREATE A FIELD CUSTOMIZER EXTENSION step 9 

$field.ClientSideComponentId = "YOURGUID"

4) Mark the field as updates

field.Update()

5) Execute the pending update

Invoke-PnPQuery

TEST FIELD CUSTOMIZER EXTENSION

If all sets are followed correctly any number column that has this Field Customizer Extension will take input like this.

and change it to a nice human-readable format like below

CONCLUSION

I hope you found this tutorial useful. You are encouraged to ask questions, report any bugs or make any other comments about it below. 

Tool to identify commercial MP3s

Introduction

Media files have become an important part of business content. Seeing that in some environments we cannot explicitly block MP3s, how do we identify what files are business recordings, and which are (possibly illegal) music?

IsMP3Song.exe is my attempt to address this issue. 

The idea is very simple, pass the filename to the tool and it returns a score indicating the confidence of it deeming the file as a commercial MP3.

The tool achieves this by doing a web-service call to iTunes and then scoring the iTunes results with the filename of the MP3.

Demo Execution

As you can see, the first couple of executions return a score because these are identified as commercial songs and the last one is scored as not commercial.

These scores are also returned by the application as an exit code, allowing return values to be used for other logic, such as email notifications or file classifications.

USING WITH POWERSHELL

Going a step further, the PowerShell snippet below can be used as a starting point to loop through folders and score all the MP3 files found.

$inputDir = "E:\SomeDataFolder";
$filterExt = "*.mp3";

$files = Get-ChildItem -Path $inputDir -Recurse -Filter $filterExt;
foreach ($file in $files)
{
$process = start-process .\IsMP3Song.exe -windowstyle Hidden -ArgumentList """$($file.Name)""" -PassThru -Wait
"$($file.Name) $($process.ExitCode)"
}

The C# code

using Newtonsoft.Json.Linq;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Net;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace IsMP3Song
{
public class Song
{
public string ArtistName;
public string TrackName;
public int Match;
}
class Program
{
static void Main(string[] args)
{
List<Song> songs = new List<Song>();

try
{
if (args.Length == 1)
{
string searchTerm = FormatSearchTerm(args[0]);
string jsonReturn = GetRequest($"https://itunes.apple.com/search?term={searchTerm}");
dynamic jsonCollection = JObject.Parse(jsonReturn);
foreach (var result in jsonCollection.results)
{
dynamic jsonObject = JObject.Parse(result.ToString());
double match = ((CalculateSimilarity(args[0], $"{jsonObject.artistName} - {jsonObject.trackName}")) * 100);

Song song = new Song();
song.ArtistName = jsonObject.artistName;
song.TrackName = jsonObject.trackName;
song.Match = (int)match;
songs.Add(song);
}

if (songs.Count > 0)
{
Song bestMatch = songs.OrderByDescending(s => s.Match).First();
Console.WriteLine($"{bestMatch.ArtistName} - {bestMatch.TrackName} [{bestMatch.Match}]");
Environment.Exit(bestMatch.Match);
}
else
{
Console.WriteLine($"Seems that is not a commercial MP3...");
Environment.Exit(0);
}

}
}
catch
{

}
}
// Returns JSON string
static string GetRequest(string url)
{
HttpWebRequest request = (HttpWebRequest)WebRequest.Create(url);
try
{
WebResponse response = request.GetResponse();
using (Stream responseStream = response.GetResponseStream())
{
StreamReader reader = new StreamReader(responseStream, System.Text.Encoding.UTF8);
return reader.ReadToEnd();
}
}
catch (WebException ex)
{
WebResponse errorResponse = ex.Response;
using (Stream responseStream = errorResponse.GetResponseStream())
{
StreamReader reader = new StreamReader(responseStream, System.Text.Encoding.GetEncoding("utf-8"));
String errorText = reader.ReadToEnd();
// log errorText
}
throw;
}
}
public static double CalculateSimilarity(string source, string target)
{
if ((source == null) || (target == null)) return 0.0;
if ((source.Length == 0) || (target.Length == 0)) return 0.0;
if (source == target) return 1.0;

int stepsToSame = ComputeLevenshteinDistance(source, target);
return (1.0 - ((double)stepsToSame / (double)Math.Max(source.Length, target.Length)));
}
public static int ComputeLevenshteinDistance(string source, string target)
{
if ((source == null) || (target == null)) return 0;
if ((source.Length == 0) || (target.Length == 0)) return 0;
if (source == target) return source.Length;

int sourceWordCount = source.Length;
int targetWordCount = target.Length;

// Step 1
if (sourceWordCount == 0)
return targetWordCount;

if (targetWordCount == 0)
return sourceWordCount;

int[,] distance = new int[sourceWordCount + 1, targetWordCount + 1];

// Step 2
for (int i = 0; i <= sourceWordCount; distance[i, 0] = i++) ;
for (int j = 0; j <= targetWordCount; distance[0, j] = j++) ;

for (int i = 1; i <= sourceWordCount; i++)
{
for (int j = 1; j <= targetWordCount; j++)
{
// Step 3
int cost = (target[j - 1] == source[i - 1]) ? 0 : 1;

// Step 4
distance[i, j] = Math.Min(Math.Min(distance[i - 1, j] + 1, distance[i, j - 1] + 1), distance[i - 1, j - 1] + cost);
}
}

return distance[sourceWordCount, targetWordCount];
}
public static string FormatSearchTerm(string fileName)
{
//Get only filename from full path
fileName = fileName.Split('\\')[fileName.Split('\\').Length - 1];
string searchTerm = fileName;

//Remove MP3 extension if present
searchTerm = Regex.Replace(searchTerm, ".mp3", "", RegexOptions.IgnoreCase).Trim();

//Remove all non-alpha characters
Regex regEx = new Regex("[^a-zA-Z ]");
searchTerm = regEx.Replace(searchTerm, "");

//Remove duplicate spaces
RegexOptions options = RegexOptions.None;
Regex regex = new Regex("[ ]{2,}", options);
searchTerm = regex.Replace(searchTerm, " ");

return searchTerm.Trim().Replace(" ", "+");
}
}
}

Download

Visual Studio project download

https://bitbucket.org/svermaak/ismp3song/downloads/

Binary download

https://e8eaed.a2cdn1.secureserver.net/wp-content/uploads/2018/03/IsMP3Song.zip

 

Windows Firewall as Code

Introduction

The Windows Firewall provides an important layer of protection and a rich interface to configure it. Unfortunately, at the time of writing, the Active Directory Group Policy section for Windows Firewall with Advanced Security does not allow for the concept of item level filter, resulting in a GPO for each policy with different filtering requirements.

For example, if a handful of servers require FTP ports to be open and another group requires RDP to be allowed, you will have to create two separate GPOs. 

Over time these GPOs really add up.

The solution to this is to do “Firewall as Code” using PowerShell.

Investigation

Well-behaved applications automatically register firewall rules which means that applications will happily continue to work when the firewall is enabled. 

Unfortunately, some applications do not include this as part of their install process. These applications require a manual rule to be configured.

Involve your environment application owners, especially for providing information of running processes and supplying network diagrams. I recommend, however, that you do not directly ask application owners which ports an application needs. I’ve found that if you do that, you will usually get a massive list of irrelevant ports back, mainly because application owners will just give you a list of open ports found on the application servers.

Always check that ports which are required are limited to a specific process. This way you can whitelist the application, allowing all the required ports without actually worrying which protocols or ports are required.

This PowerShell function can be helpful to identify ports used by applications (adapted from http://blogs.microsoft.co.il/scriptfanatic/2011/02/10/how-to-find-running-processes-and-their-port-number/)

function Get-NetworkStatistics 

    $properties = 'Protocol','LocalAddress','LocalPort' 
    $properties += 'RemoteAddress','RemotePort','State','ProcessName','ProcessPath','ProcessCompany','PID' 


    netstat -ano | Select-String -Pattern '\s+(TCP|UDP)' | ForEach-Object { 


        $item = $_.line.split(" ",[System.StringSplitOptions]::RemoveEmptyEntries) 


        if($item[1] -notmatch '^\[::') 
        {
            if (($la = $item[1] -as [ipaddress]).AddressFamily -eq 'InterNetworkV6') 
            { 
               $localAddress = $la.IPAddressToString 
               $localPort = $item[1].split('\]:')[-1] 
            } 
            else 
            { 
                $localAddress = $item[1].split(':')[0] 
                $localPort = $item[1].split(':')[-1] 
            }  


            if (($ra = $item[2] -as [ipaddress]).AddressFamily -eq 'InterNetworkV6') 
            { 
               $remoteAddress = $ra.IPAddressToString 
               $remotePort = $item[2].split('\]:')[-1] 
            } 
            else 
            { 
               $remoteAddress = $item[2].split(':')[0] 
               $remotePort = $item[2].split(':')[-1] 
            }  


            New-Object PSObject -Property @{ 
                PID = $item[-1] 
                ProcessName = (Get-Process -Id $item[-1] -ErrorAction SilentlyContinue).Name
                ProcessPath = (Get-Process -Id $item[-1] -ErrorAction SilentlyContinue).Path
                ProcessCompany = (Get-Process -Id $item[-1] -ErrorAction SilentlyContinue).Company
                Protocol = $item[0] 
                LocalAddress = $localAddress 
                LocalPort = $localPort 
                RemoteAddress =$remoteAddress 
                RemotePort = $remotePort 
                State = if($item[0] -eq 'tcp') {$item[3]} else {$null} 
            } | Select-Object -Property $properties 
        } 
    } 
}

To get a report, run the following after importing the function

Get-NetworkStatistics | Export-Csv -NoTypeInformation .\Ports.csv

Sample export from PowerShell function

Prestage rules

The key to enabling Windows Firewall involves pre-staging firewall rules well in advance of actually flipping the switch. This is achieved with a PowerShell script, executed at an interval on all devices. Conditions within the script selectively create the appropriate rule.

For example, you only want MSSQL firewall rule on servers running MSSQL,

Create PowerShell Script

Although many options exist that would be able to achieve this, I picked PowerShell because of its popularity amongst Windows Administrators.

The PowerShell script to achieve this consists of the following concepts;

  • Environmental Variables
  • Global Variables
  • Local Variables
  • Conditions
  • Rules

Environmental Variables

Specifically, to creating path conditions and rules, it is always better to use Environmental variables if the application is installed into one of these common locations.

To get these variable names, use the following PowerShell command

# List PowerShell's Environmental Variables
Get-Childitem -Path Env:* |Sort-Object Name

Global Variables

Global variables are variables that are used multiple times within the script thus, it makes sense to move these to the top of the file so that they are only evaluated once. The only downside to having multiple instances of these throughout the file is that the script will take longer to execute.

 

Some examples:

Computer account membership

$computerGroupMembers = ([adsisearcher]"(&(objectCategory=computer)(cn=$env:COMPUTERNAME))").FindOne().Properties.memberof -replace '^CN=([^,]+).+$','$1';

Application registry key

$SqlKey = Get-ChildItem -ErrorAction SilentlyContinue "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server";

Computer system information

$Computer = Get-WmiObject -Class Win32_ComputerSystem 

Local Variables

When a variable is changed from one condition to another and/or needs to be re-evaluated from one condition to the next, use a local variable.

Conditions

Conditions determine if a particular rule will be applied. That said, a rule without a condition is considered a global rule because it applies to all devices

 

Some examples:

Domain Roles

switch -regex ($computer.DomainRole)
{
    "0|1"
    { #Rules for workstations go here }
    "2|3"
    { #Rules for servers go here }
    "4|5"
    { #Rules for domain controllers go here }
}

Group membership

If ($computerGroupMembers -contains "Group01") { #Rules go here }

Multiple group membership

If ($computerGroupMembers -contains "Group01" -or $computerGroupMembers -contains "Group01") { #Rules go here }

Application binary exists at path using environmental variables

If (Test-Path -Path "$($Env:programfiles)\SomePath\SomeApp.exe"){ #Rules go here }
If (Test-Path -Path "$($Env:SystemDrive)\SomePath\SomeApp.exe "){ #Rules go here }
If (Test-Path -Path "$(${Env:ProgramFiles(x86)})\SomePath\SomeApp.exe") { #Rules go here }

Application binary running (notice the local variable)

$processPath = (Get-Process -Name SomeProcessName).path;
If (Test-Path -Path "$($processPath)") { #Rules go here }

Rules

Each rule name must be unique and descriptive and if applicable to a specific protocol, add the protocol to the rule name within parentheses.

Please note: PowerShell has a powerful CMDLet called New-NetFirewallRule that vastly simplifies the addition of firewall rule. Unfortunately, older versions of PowerShell do not contain it and as such, NETSH is used because it is compatible with more devices. The downside of NETSH is that we first need to delete the rule to ensure we do not create duplicate rules on subsequent executions.

 

Some examples:

Management range that bypass firewall completely

netsh advfirewall firewall delete rule name="Sample Rule Name"
netsh advfirewall firewall add rule name="Sample Rule Name" protocol=any action=allow dir=IN remoteip=10.1.1.0/24,10.2.2.0/24,10.3.3.0/24

TCP single port rule

netsh advfirewall firewall delete rule name="FTP (TCP)"
netsh advfirewall firewall add rule name="FTP (TCP)" protocol=6 action=allow dir=IN localport="21"

TCP multiple port ranges rule

netsh advfirewall firewall delete rule name="Sample Rule Name (TCP)"
netsh advfirewall firewall add rule name="Sample Rule Name (TCP)" protocol=6 action=allow dir=IN localport="900,1000-2000,3000-4000"

TCP and UDP port range rule

netsh advfirewall firewall delete rule name="Sample Rule Name (TCP)"
netsh advfirewall firewall delete rule name="Sample Rule Name (UDP)"
netsh advfirewall firewall add rule name="Sample Rule Name (TCP)" protocol=6 action=allow dir=IN localport="1000-2000"
netsh advfirewall firewall add rule name="Sample Rule Name (UDP)" protocol=17 action=allow dir=IN localport="1000-2000"

Absolute path application rule

netsh advfirewall firewall delete rule name="Sample Rule Name"
netsh advfirewall firewall add rule name="Sample Rule Name" dir=in action=allow program="C:\SomePath\SomeApp.exe"

Multiple combined environmental variable application rules

netsh advfirewall firewall delete rule name="Sample Rule Name 01"
netsh advfirewall firewall delete rule name="Sample Rule Name 02"
netsh advfirewall firewall delete rule name="Sample Rule Name 03"
netsh advfirewall firewall add rule name="Sample Rule Name 01" dir=in action=allow program="$($Env:programfiles)\SomePath\SomeApp.exe"
netsh advfirewall firewall add rule name="Sample Rule Name 02" dir=in action=allow program="$($Env:ProgramFiles(x86))\SomePath\SomeApp.exe"
netsh advfirewall firewall add rule name="Sample Rule Name 02" dir=in action=allow program="$($Env:SystemDrive)\SomePath\SomeApp.exe"

Using local variable

netsh advfirewall firewall delete rule name="Sample Rule Name"
netsh advfirewall firewall add rule name="Sample Rule Name" dir=in action=allow program="$($processPath)"

Predefined rules

netsh advfirewall firewall set rule group="remote desktop" new enable=yes

Firewall.ps1 example

$computerGroupMembers = ([adsisearcher]"(&(objectCategory=computer)(cn=$env:COMPUTERNAME))").FindOne().Properties.memberof -replace '^CN=([^,]+).+$','$1';
$SqlKey = Get-ChildItem -ErrorAction SilentlyContinue "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server";

If ($computerGroupMembers -contains "FirewallAllowFTP")
{
netsh advfirewall firewall delete rule name="FTP (TCP)"
netsh advfirewall firewall add rule name="FTP (TCP)" protocol=6 action=allow dir=IN localport="21"
}

If ($computerGroupMembers -contains "FirewallAllowRDP")
{
netsh advfirewall firewall set rule group="remote desktop" new enable=yes
}

If (Test-Path -Path "$($Env:SystemDrive)\App\bin\Engine.exe")
{
netsh advfirewall firewall delete rule name="App01"
netsh advfirewall firewall add rule name="App02" dir=in action=allow program="$($Env:SystemDrive)\App\bin\App.exe"
}

If ($SqlKey -ne $null)
{
netsh advfirewall firewall delete rule name="MSSQL - SQL Server"
netsh advfirewall firewall delete rule name="MSSQL - SQL Admin Connection"
netsh advfirewall firewall delete rule name="MSSQL - SQL Database Management"
netsh advfirewall firewall delete rule name="MSSQL - SQL Service Broker"
netsh advfirewall firewall delete rule name="MSSQL - SQL Debugger/RPC"
netsh advfirewall firewall delete rule name="MSSQL - SQL Server Browse Button Service"
netsh advfirewall firewall delete rule name="MSAS - SQL Analysis Services"
netsh advfirewall firewall delete rule name="MSAS - SQL Browser"
netsh advfirewall firewall delete rule name="MSRS - HTTP"
netsh advfirewall firewall delete rule name="MSRS - SSL"

netsh advfirewall firewall add rule name="MSSQL - SQL Server" dir=in action=allow protocol=TCP localport=1433
netsh advfirewall firewall add rule name="MSSQL - SQL Admin Connection" dir=in action=allow protocol=TCP localport=1434
netsh advfirewall firewall add rule name="MSSQL - SQL Database Management" dir=in action=allow protocol=UDP localport=1434
netsh advfirewall firewall add rule name="MSSQL - SQL Service Broker" dir=in action=allow protocol=TCP localport=4022
netsh advfirewall firewall add rule name="MSSQL - SQL Debugger/RPC" dir=in action=allow protocol=TCP localport=135
netsh advfirewall firewall add rule name="MSSQL - SQL Server Browse Button Service" dir=in action=allow protocol=UDP localport=1433
netsh advfirewall firewall add rule name="MSAS - SQL Analysis Services" dir=in action=allow protocol=TCP localport=2383
netsh advfirewall firewall add rule name="MSAS - SQL Browser" dir=in action=allow protocol=TCP localport=2382
netsh advfirewall firewall add rule name="MSRS - HTTP" dir=in action=allow protocol=TCP localport=80
netsh advfirewall firewall add rule name="MSRS - SSL" dir=in action=allow protocol=TCP localport=443

$SubKeys = Get-ChildItem "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server";
ForEach ($SubKey in $SubKeys)
{
If ($SubKey.Name.Contains("\MSSQL") -And !$SubKey.Name.Contains("\MSSQLServer"))
{
$InstanceName = $SubKey.Name.Split("\")[4]
$RegistryKey = Get-ItemProperty "HKLM:$($SubKey.Name)\Setup" -name SQLBinRoot;
$EXEPath = "$($RegistryKey.SQLBinRoot)\sqlservr.exe";

netsh advfirewall firewall delete rule name="MSSQL - $($InstanceName)"
netsh advfirewall firewall add rule name="MSSQL - $($InstanceName)" dir=in action=allow program="$($EXEPath)"
}
If ($SubKey.Name.Contains("\MSAS"))
{
$InstanceName = $SubKey.Name.Split("\")[4]
$RegistryKey = Get-ItemProperty "HKLM:$($SubKey.Name)\Setup" -name SQLBinRoot;
$EXEPath = "$($RegistryKey.SQLBinRoot)\msmdsrv.exe";

netsh advfirewall firewall delete rule name="MSAS - $($InstanceName)"
netsh advfirewall firewall add rule name="MSAS - $($InstanceName)" dir=in action=allow program="$($EXEPath)"
}
}
}

Implementation

1) Copy Firewall.ps1 to shared location (\\ittelligence.com\NETLOGON\Software\Scripts in this case)

2) Deploy file via GPO Files Preferences

3) Create a scheduled task with GPO Scheduled Tasks running as NT AUTHORITY\SYSTEM

4) Specify the following as options;

Program/scripts: PowerShell.exe

Add arguments (optional): -ExecutionPolicy Bypass -File “%CommonAppdataDir%\ITtelligence\Scripts\Firewall.ps1”

5) After running this GPO for a few days do some spot checks. If rules are creating successfully, enable the firewall.

 

Active Directory – Securely Set Local Account Passwords

How it works

A token is generated for a supplied account with the desired password. 

Example of a token: k8vVeIYZeI+6rkvlvw8eLOEnHK2yTcBfHQP4UEZrCgigcagy7+qt969LISkmHH/7CS5KfVWLEZh8cZMzCkVYGw==

This token (an AES-256 encrypted version of the username and the password) is passed to the SecurelySetPassword tool which is executed at start-up via an Active Directory Group Policy.

The token is decrypted and used to set the password for the specified account to the desired password.

Step 1: Download

Download SecurelySetPassword tool

Step 2: Create and Test Token

1) Run SecurelySetPassword.exe USERNAME PASSWORD (Note how the generated token is different on each run, this is because the value is salted for added security)

2) Copy token. It will be used in the implementation steps

3) To test the token, run SecurelySetPassword.exe TOKEN (Note for a successful test the user needs to exists)

Step 3: Copy SecurelySetPassword.exe to a network share

Copy SecurelySetPassword.exe to a network share accessible by all users (such as NETLOGON share)

Step 4: Implement Active Directory Group Policy

1) Start Microsoft Group Policy Management Console (GPMC.msc

2) Create and link a new Group Policy with the desired scope

3) Browse to Computer Configuration > Preferences > Windows Settings > Files and add a new file object

4) Set the Source files(s) path to the location of SecurelySetPassword.exe (\\ittelligence.com\NETLOGON\Software\SecurelySetPassword\SecurelySetPassword.exe in my case)

5) Set the Destination file to %CommonAppdataDir%\SecurelySetPassword\SecurelySetPassword.exe

6) Browse to Computer Configuration > Preferences > Control Panel Settings > Scheduled Tasks and add a new scheduled task object 

7) On the Triggers tab create new trigger and set to At startup

8) On the Actions tab create a Start a program action to %CommonAppdataDir%\SecurelySetPassword\SecurelySetPassword.exe with token as argument

I hope you found this tutorial useful. You are encouraged to ask questions, report any bugs or make any other comments about it below.

Active Directory – Securely Set Local Account Passwords

Prerequisites: The following assumptions have been made in this tutorial. Readers should have a basic working knowledge of Microsoft Active Directory, SQL Server and Visual Studio software.

Step 1:  Create ACTIVE DIRECTORY SERVICE ACCOUNT

Create an Active directory service account with password reset as well as user account unlock permissions.

Step 2:  Download Visual Studio Project

1) Download the provided source zip file by clicking this link  (See below)

2) Extract and open the project in Visual Studio

Step 3:  Create database

Note: The basic steps for creating the database are listed below. Explaining MS SQL functionality is beyond the scope of this article, but I am happy to answer any questions in the comments section below.

1) From the Open Project in Visual Studio, open ModelSSPR.edmx

2) Right-click on white-space on the diagram page

3) Then select Generate Database from Model as shown below

4) Save the SQL script and use it on Microsoft SQL Server to build the database schema

5) Create an MS SQL user and grant it DB owner rights

step 4:  Modify config file

1) From the open project in Visual Studio

2) Replace the ADConnectionString connection string with the Active Directory LDAP string for the domain used in the Create Active Directory Service Account (Step 1)

3) Replace the SSPREntities connection string with the connection string of the database used in the Create Database (Step 3)

4) Configure ADMembershipProvider to the account created in the Create Active Directory Service Account (Step 1)

5) Replace the appSettings values with the correct information for the domain and account used in the Create Active Directory Service Account (Step 1)

Step 5:  Publish Site

Please Note: Explaining Visual Studio publishing is beyond the scope of this article, but I am happy to answer any questions in the comments section below.

1) From the open project in Visual Studio

2) Publish site with the Visual Studio Publishing wizard

step 6:  Testing Site

Registering password hints

1) Browse to site published in Publish Site (Step 5)

2) Click on Log in

3) Specify the Username and Password for the account to register for self-service password reset.

Note: Username must be in UPN format

4) Create password hints by adding questions and answers

Note: At least four hints need to be specified to utilize the self-service password reset function.

Self-Service Password Reset Request

1) Browse to the site published in the Publish Site (Step 5)

2) Click on Reset Password

3) Enter the Username for the account to reset the password for as shown below

Note: Username must be in UPN format

4) Enter answers to the security questions and provide new password

Note: Three random questions will be selected out of the hints configured

5) Click Reset Password

6) If the password was successfully reset, the following screen will display

I hope you found this tutorial useful. You are encouraged to ask questions, report any bugs or make any other comments about it below.

Using OpenTTD to create a realistic data stream

A while back I was going through a refresher course on Azure Stream Analytics (https://www.pluralsight.com/courses/azure-stream-analytics-understanding by Alan Smith) and found the method used to generate data fascinating.

Basically, Alan used an opensource C# racing game to build a whole course on Azure Stream Analytics and the processing of telemetry data generated by it.

I took inspiration from it and manipulated a C++ opensource game OpenTTD to output JSON messages to disk which is then streamed to Azure via an Eventhub.

After scouring the code I found a procedure called OnNewDay which is called for each vehicle when a day is over.

This procedure was perfect for adding a function that would log out the vehicle information

Here is the full listing of LogVehicleEvent (bear in mind I primarily code in C#)

LogEvents.cpp

#include "stdafx.h"
#include <string>
#include "vehicle_base.h"
#include "station_base.h"
#include "strings_func.h"
#include "town_map.h"
#include "town.h"
#include "townname_func.h"
#include <iostream>
#include <fstream>
#include "LogEvents.h"
#include <ctime>

bool LogVehicleEvent(Vehicle *v)
{
/*
OwnerByte owner = v->owner;///< Which company owns the vehicle?
EngineID engine_type = v->engine_type;///< The type of engine used for this vehicle.
int CargoDaysInTransit = &v->cargo.DaysInTransit;///< Number of days cargo has been in transit
CargoID cargo_type = this->cargo_type;///< type of cargo this vehicle is carrying
byte cargo_subtype = this->cargo_subtype;///< Used for livery refits (NewGRF variations)
uint16 cargo_cap = this->cargo_cap;///< total capacity
uint16 refit_cap = this->refit_cap;///< Capacity left over from before last refit.
VehicleCargoList cargo = this->cargo;///< The cargo this vehicle is carrying
int8 trip_occupancy = this->trip_occupancy;///< NOSAVE: Occupancy of vehicle of the current trip (updated after leaving a station).
byte day_counter = this->day_counter;///< Increased by one for each day
byte vehstatus = this->vehstatus;///< Status
*/

Rect coord = v->coord;///< NOSAVE: Graphical bounding box of the vehicle, i.e. what to redraw on moves.
Money value = v->value;///< Value of the vehicle
Money profit_this_year = v->profit_this_year;///< Profit this year << 8, low 8 bits are fract
Money profit_last_year = v->profit_last_year;///< Profit last year << 8, low 8 bits are fract
Year build_year = v->build_year;///< Year the vehicle has been built.
Date max_age = v->max_age;///< Maximum age
Date age = v->age;///< Age in days
UnitID unitnumber = v->unitnumber;///< unit number, for display purposes only
uint16 cur_speed = v->cur_speed;///< current speed
Date date_of_last_service = v->date_of_last_service;///< Last date the vehicle had a service at a depot.
byte breakdowns_since_last_service = v->breakdowns_since_last_service;///< Counter for the amount of breakdowns.
Order current_order = v->current_order;///< The current order (+ status, like: loading)
StationID last_station_visited = v->last_station_visited;///< The last station we stopped at.
StationID last_loading_station = v->last_loading_station;///< Last station the vehicle has stopped at and could possibly leave from with any cargo loaded.  

if (unitnumber != 0)
{
string name;
string type;
time_t now = time(0);

switch (v->type) {
case VEH_TRAIN:    type = "Train"; break;
case VEH_ROAD:     type = "Road Vehicle"; break;
case VEH_SHIP:     type = "Ship"; break;
case VEH_AIRCRAFT: type = "Aircraft"; break;
default:           type = "Unknown Vehicle";
}

if (v->name != NULL)
{
name = v->name;
}
else
{
name = type + " " + to_string(unitnumber);
}

string lastStationVisited = GetTheStationName(last_station_visited);
string lastLoadingStation = GetTheStationName(last_loading_station);

//Build message
string message = "{";
message = message + "\"Name\":\"" + name + "\"";
message = message + ",";
message = message + "\"TimeStamp\":\"" + to_string(now) + "\"";
message = message + ",";
message = message + "\"Type\":\"" + type + "\"";
message = message + ",";
message = message + "\"CurrentSpeed\":" + to_string(cur_speed);
message = message + ",";
message = message + "\"LastStationVisited\":\"" + lastStationVisited + "\"";
message = message + ",";
message = message + "\"LastLoadingStation\":\"" + lastLoadingStation + "\"";
message = message + ",";
message = message + "\"ProfitLastYear\":" + to_string(profit_last_year);
message = message + ",";
message = message + "\"ProfitThisYear\":" + to_string(profit_this_year);
message = message + ",";
message = message + "\"Value\":" + to_string(value);
message = message + ",";
message = message + "\"BuildYear\":" + to_string(build_year);
message = message + ",";
message = message + "\"Age\":" + to_string(age);
message = message + ",";
message = message + "\"MaxAge\":" + to_string(max_age);
message = message + ",";
message = message + "\"DateOfLastService\":" + to_string(date_of_last_service);
message = message + ",";
message = message + "\"BreakdownsSinceLastService\":" + to_string(breakdowns_since_last_service);
message = message + "}";

//Write out message to temporary file
ofstream myfile;
string tempFolder = getenv("TEMP");
myfile.open(tempFolder + "\\" + GetRandomString(15, "abcdefghijklmnaoqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890") + ".txt");
myfile << message + "\n";
myfile.close();

return true;
}
}

//Get Station Name from Station ID
string GetTheStationName(StationID s)
{
string lastVisitedStation = "";
try
{
BaseStation *station = BaseStation::Get(s);

Town *town = station->town;

char buf1[(MAX_LENGTH_TOWN_NAME_CHARS + 1) * MAX_CHAR_LENGTH];
char buf2[(MAX_LENGTH_TOWN_NAME_CHARS + 1) * MAX_CHAR_LENGTH];

const char *buf = town->name;
if (buf == NULL) {
GetTownName(buf2, town, lastof(buf2));
buf = buf2;
}

StringID stationStringID = station->string_id;
string stationNamePart = GetStringPtr(stationStringID);
stationNamePart = stationNamePart.substr(stationNamePart.find(" ") + 1);

string townNameString(buf);

lastVisitedStation = townNameString + " " + stationNamePart;
}
catch (...)
{

}
return lastVisitedStation;
}

//Generate Random String
string GetRandomString(uint l = 15, std::string charIndex = "abcdefghijklmnaoqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890")
{
// l and charIndex can be customized, but I've also initialized them.

uint length = rand() % l + 1;
// length of the string is a random value that can be up to 'l' characters.

uint ri[15];
/* array of random values that will be used to iterate through random
indexes of 'charIndex' */

for (uint i = 0; i < length; ++i)
ri[i] = rand() % charIndex.length();
// assigns a random number to each index of "ri"

std::string rs = "";
// random string that will be returned by this function

for (uint i = 0; i < length; ++i)
rs += charIndex[ri[i]];
// appends a random amount of random characters to "rs"

if (rs.empty()) GetRandomString(l, charIndex);
// if the outcome is empty, then redo the generation (this doesn't work, help?)
else return rs;
}

LogEvents.h

#pragma once

#include "stdafx.h"
#include <string>
#include "vehicle_base.h"
#include "station_base.h"
#include "strings_func.h"
#include "town_map.h"
#include "town.h"
#include "townname_func.h"
#include <iostream>
#include <fstream>

using namespace std;

bool LogVehicleEvent(Vehicle *v);
string GetTheStationName(StationID s);
string GetRandomString(uint l, std::string charIndex);

The result is that a file is created every game day for each vehicle

And finally, here is an example after it passed through Azure Stream Analytics

Query

SELECT Name, Type, avg(CurrentSpeed) as AvgSpeed, max(CurrentSpeed) as MaxSpeed INTO [OpenTTDOutput] FROM [OpenTTDInput] GROUP BY Name, Type, TumblingWindow(minute,10)

Result

{"name":"Road 13","type":"Road","avgspeed":83.92307692307692,"maxspeed":176.0}
{"name":"Train 39","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Road 37","type":"Road","avgspeed":80.88,"maxspeed":176.0}
{"name":"Road 67","type":"Road","avgspeed":0.0,"maxspeed":0.0}
{"name":"Road 12","type":"Road","avgspeed":111.23076923076923,"maxspeed":176.0}
{"name":"Train 10","type":"Train","avgspeed":146.53846153846155,"maxspeed":264.0}
{"name":"Train 47","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Train 30","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Road 5","type":"Road","avgspeed":75.07692307692308,"maxspeed":176.0}
{"name":"Road 16","type":"Road","avgspeed":79.384615384615387,"maxspeed":176.0}
{"name":"Road 52","type":"Road","avgspeed":134.72,"maxspeed":176.0}
{"name":"Road 49","type":"Road","avgspeed":132.73076923076923,"maxspeed":176.0}
{"name":"Train 2","type":"Train","avgspeed":80.6,"maxspeed":264.0}
{"name":"Road 75","type":"Road","avgspeed":122.19230769230769,"maxspeed":176.0}
{"name":"Road 20","type":"Road","avgspeed":139.5,"maxspeed":176.0}
{"name":"Road 68","type":"Road","avgspeed":0.0,"maxspeed":0.0}
{"name":"Train 18","type":"Train","avgspeed":102.07692307692308,"maxspeed":198.0}
{"name":"Road 72","type":"Road","avgspeed":127.96153846153847,"maxspeed":176.0}
{"name":"Train 13","type":"Train","avgspeed":144.26923076923077,"maxspeed":264.0}
{"name":"Train 35","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Road 22","type":"Road","avgspeed":108.30769230769231,"maxspeed":176.0}
{"name":"Train 21","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Road 61","type":"Road","avgspeed":105.88461538461539,"maxspeed":176.0}
{"name":"Road 57","type":"Road","avgspeed":135.0,"maxspeed":176.0}
{"name":"Road 62","type":"Road","avgspeed":109.80769230769231,"maxspeed":176.0}
{"name":"Road 41","type":"Road","avgspeed":111.84615384615384,"maxspeed":176.0}
{"name":"Train 24","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Train 32","type":"Train","avgspeed":167.0,"maxspeed":264.0}
{"name":"Road 28","type":"Road","avgspeed":136.73076923076923,"maxspeed":176.0}
{"name":"Road 17","type":"Road","avgspeed":83.0,"maxspeed":176.0}
{"name":"Road 94","type":"Road","avgspeed":95.961538461538467,"maxspeed":201.0}
{"name":"Road 86","type":"Road","avgspeed":148.5,"maxspeed":176.0}
{"name":"Road 76","type":"Road","avgspeed":120.96,"maxspeed":176.0}
{"name":"Road 46","type":"Road","avgspeed":125.26923076923077,"maxspeed":176.0}
{"name":"Train 38","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Train 29","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Road 83","type":"Road","avgspeed":132.15384615384616,"maxspeed":176.0}
{"name":"Road 89","type":"Road","avgspeed":113.44,"maxspeed":176.0}
{"name":"Road 79","type":"Road","avgspeed":111.0,"maxspeed":176.0}
{"name":"Train 46","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Train 45","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Road 27","type":"Road","avgspeed":124.73076923076923,"maxspeed":176.0}
{"name":"Road 48","type":"Road","avgspeed":130.53846153846155,"maxspeed":176.0}
{"name":"Road 54","type":"Road","avgspeed":125.88461538461539,"maxspeed":176.0}
{"name":"Road 60","type":"Road","avgspeed":138.0,"maxspeed":176.0}
{"name":"Train 50","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Train 48","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Train 27","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Train 9","type":"Train","avgspeed":180.61538461538461,"maxspeed":264.0}
{"name":"Train 26","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Road 91","type":"Road","avgspeed":101.96,"maxspeed":224.0}
{"name":"Road 1","type":"Road","avgspeed":81.84,"maxspeed":176.0}
{"name":"Road 50","type":"Road","avgspeed":105.15384615384616,"maxspeed":176.0}
{"name":"Road 2","type":"Road","avgspeed":80.615384615384613,"maxspeed":176.0}
{"name":"Road 21","type":"Road","avgspeed":140.11538461538461,"maxspeed":176.0}
{"name":"Train 34","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Road 51","type":"Road","avgspeed":128.65384615384616,"maxspeed":176.0}
{"name":"Train 36","type":"Train","avgspeed":1.0,"maxspeed":1.0}
{"name":"Train 28","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Road 69","type":"Road","avgspeed":136.76923076923077,"maxspeed":176.0}
{"name":"Road 25","type":"Road","avgspeed":108.96153846153847,"maxspeed":176.0}
{"name":"Road 24","type":"Road","avgspeed":115.46153846153847,"maxspeed":176.0}
{"name":"Train 12","type":"Train","avgspeed":152.5,"maxspeed":264.0}
{"name":"Road 47","type":"Road","avgspeed":135.07692307692307,"maxspeed":176.0}
{"name":"Train 14","type":"Train","avgspeed":193.0,"maxspeed":264.0}
{"name":"Road 6","type":"Road","avgspeed":81.6923076923077,"maxspeed":176.0}
{"name":"Road 59","type":"Road","avgspeed":131.11538461538461,"maxspeed":176.0}
{"name":"Road 19","type":"Road","avgspeed":135.11538461538461,"maxspeed":176.0}
{"name":"Road 56","type":"Road","avgspeed":129.38461538461539,"maxspeed":176.0}
{"name":"Road 18","type":"Road","avgspeed":138.92307692307693,"maxspeed":176.0}
{"name":"Train 37","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Train 8","type":"Train","avgspeed":126.24,"maxspeed":264.0}
{"name":"Road 10","type":"Road","avgspeed":109.46153846153847,"maxspeed":176.0}
{"name":"Road 87","type":"Road","avgspeed":134.73076923076923,"maxspeed":176.0}
{"name":"Road 11","type":"Road","avgspeed":132.46153846153845,"maxspeed":176.0}
{"name":"Train 5","type":"Train","avgspeed":126.5,"maxspeed":264.0}
{"name":"Road 93","type":"Road","avgspeed":107.07692307692308,"maxspeed":224.0}
{"name":"Road 82","type":"Road","avgspeed":122.61538461538461,"maxspeed":176.0}
{"name":"Train 4","type":"Train","avgspeed":90.44,"maxspeed":264.0}
{"name":"Road 45","type":"Road","avgspeed":108.42307692307692,"maxspeed":176.0}
{"name":"Road 44","type":"Road","avgspeed":112.2,"maxspeed":176.0}
{"name":"Road 34","type":"Road","avgspeed":106.76923076923077,"maxspeed":176.0}
{"name":"Road 74","type":"Road","avgspeed":123.73076923076923,"maxspeed":176.0}
{"name":"Train 42","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Train 41","type":"Train","avgspeed":1.0,"maxspeed":1.0}
{"name":"Road 3","type":"Road","avgspeed":79.34615384615384,"maxspeed":176.0}
{"name":"Road 9","type":"Road","avgspeed":78.3076923076923,"maxspeed":176.0}
{"name":"Road 7","type":"Road","avgspeed":79.44,"maxspeed":176.0}
{"name":"Train 25","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Train 44","type":"Train","avgspeed":3.0,"maxspeed":3.0}
{"name":"Road 35","type":"Road","avgspeed":98.384615384615387,"maxspeed":176.0}
{"name":"Road 78","type":"Road","avgspeed":100.15384615384616,"maxspeed":176.0}
{"name":"Road 66","type":"Road","avgspeed":0.0,"maxspeed":0.0}
{"name":"Road 14","type":"Road","avgspeed":74.15384615384616,"maxspeed":176.0}
{"name":"Road 81","type":"Road","avgspeed":105.15384615384616,"maxspeed":176.0}
{"name":"Road 64","type":"Road","avgspeed":106.03846153846153,"maxspeed":176.0}
{"name":"Train 7","type":"Train","avgspeed":184.61538461538461,"maxspeed":264.0}
{"name":"Road 63","type":"Road","avgspeed":93.84615384615384,"maxspeed":176.0}
{"name":"Train 40","type":"Train","avgspeed":2.0,"maxspeed":2.0}
{"name":"Train 31","type":"Train","avgspeed":3.0,"maxspeed":3.0}
{"name":"Road 31","type":"Road","avgspeed":131.26923076923077,"maxspeed":176.0}
{"name":"Road 36","type":"Road","avgspeed":84.269230769230774,"maxspeed":176.0}
{"name":"Ship 1","type":"Ship","avgspeed":48.0,"maxspeed":48.0}
{"name":"Road 90","type":"Road","avgspeed":116.23076923076923,"maxspeed":176.0}
{"name":"Train 23","type":"Train","avgspeed":2.0,"maxspeed":2.0}
{"name":"Road 42","type":"Road","avgspeed":114.4,"maxspeed":176.0}
{"name":"Road 88","type":"Road","avgspeed":132.19230769230768,"maxspeed":176.0}
{"name":"Road 43","type":"Road","avgspeed":119.69230769230769,"maxspeed":176.0}
{"name":"Road 30","type":"Road","avgspeed":103.84,"maxspeed":176.0}
{"name":"Road 26","type":"Road","avgspeed":116.84615384615384,"maxspeed":176.0}
{"name":"Train 6","type":"Train","avgspeed":166.42307692307693,"maxspeed":264.0}
{"name":"Train 49","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Train 43","type":"Train","avgspeed":2.0,"maxspeed":2.0}
{"name":"Road 32","type":"Road","avgspeed":98.52,"maxspeed":176.0}
{"name":"Road 15","type":"Road","avgspeed":79.84615384615384,"maxspeed":176.0}
{"name":"Train 22","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Road 38","type":"Road","avgspeed":114.53846153846153,"maxspeed":176.0}
{"name":"Road 8","type":"Road","avgspeed":81.32,"maxspeed":176.0}
{"name":"Road 71","type":"Road","avgspeed":110.73076923076923,"maxspeed":176.0}
{"name":"Road 29","type":"Road","avgspeed":125.69230769230769,"maxspeed":176.0}
{"name":"Road 40","type":"Road","avgspeed":108.96153846153847,"maxspeed":176.0}
{"name":"Train 19","type":"Train","avgspeed":176.34615384615384,"maxspeed":264.0}
{"name":"Road 92","type":"Road","avgspeed":107.76923076923077,"maxspeed":224.0}
{"name":"Road 23","type":"Road","avgspeed":122.88461538461539,"maxspeed":176.0}
{"name":"Train 20","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Train 17","type":"Train","avgspeed":201.88461538461539,"maxspeed":264.0}
{"name":"Road 80","type":"Road","avgspeed":96.52,"maxspeed":176.0}
{"name":"Train 16","type":"Train","avgspeed":89.384615384615387,"maxspeed":185.0}
{"name":"Road 85","type":"Road","avgspeed":124.84,"maxspeed":176.0}
{"name":"Road 4","type":"Road","avgspeed":77.615384615384613,"maxspeed":176.0}
{"name":"Road 77","type":"Road","avgspeed":90.96,"maxspeed":176.0}
{"name":"Train 11","type":"Train","avgspeed":161.42307692307693,"maxspeed":264.0}
{"name":"Train 3","type":"Train","avgspeed":142.2,"maxspeed":264.0}
{"name":"Road 84","type":"Road","avgspeed":125.65384615384616,"maxspeed":176.0}
{"name":"Road 73","type":"Road","avgspeed":119.96153846153847,"maxspeed":176.0}
{"name":"Road 53","type":"Road","avgspeed":118.46153846153847,"maxspeed":176.0}
{"name":"Road 33","type":"Road","avgspeed":101.23076923076923,"maxspeed":176.0}
{"name":"Road 58","type":"Road","avgspeed":134.69230769230768,"maxspeed":176.0}
{"name":"Road 39","type":"Road","avgspeed":83.92307692307692,"maxspeed":176.0}
{"name":"Train 51","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Train 33","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Train 1","type":"Train","avgspeed":115.38461538461539,"maxspeed":264.0}
{"name":"Road 65","type":"Road","avgspeed":90.961538461538467,"maxspeed":176.0}
{"name":"Train 15","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Road 70","type":"Road","avgspeed":129.30769230769232,"maxspeed":176.0}
{"name":"Road 55","type":"Road","avgspeed":120.48,"maxspeed":176.0}
{"name":"Road 37","type":"Road","avgspeed":102.08,"maxspeed":176.0}
{"name":"Road 13","type":"Road","avgspeed":84.88,"maxspeed":176.0}
{"name":"Train 39","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Road 67","type":"Road","avgspeed":0.0,"maxspeed":0.0}
{"name":"Road 12","type":"Road","avgspeed":92.2,"maxspeed":176.0}
{"name":"Train 10","type":"Train","avgspeed":144.16,"maxspeed":264.0}
{"name":"Train 47","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Train 30","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Road 5","type":"Road","avgspeed":79.64,"maxspeed":176.0}
{"name":"Road 16","type":"Road","avgspeed":91.0,"maxspeed":176.0}
{"name":"Road 52","type":"Road","avgspeed":137.6,"maxspeed":176.0}
{"name":"Train 2","type":"Train","avgspeed":88.96,"maxspeed":264.0}
{"name":"Road 49","type":"Road","avgspeed":132.66666666666666,"maxspeed":176.0}
{"name":"Road 75","type":"Road","avgspeed":122.16,"maxspeed":176.0}
{"name":"Road 20","type":"Road","avgspeed":135.44,"maxspeed":176.0}
{"name":"Road 68","type":"Road","avgspeed":0.0,"maxspeed":0.0}
{"name":"Train 18","type":"Train","avgspeed":113.2,"maxspeed":250.0}
{"name":"Train 35","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Road 72","type":"Road","avgspeed":108.6,"maxspeed":176.0}
{"name":"Train 13","type":"Train","avgspeed":136.66666666666666,"maxspeed":252.0}
{"name":"Road 22","type":"Road","avgspeed":112.84,"maxspeed":176.0}
{"name":"Train 21","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Road 61","type":"Road","avgspeed":104.12,"maxspeed":176.0}
{"name":"Road 57","type":"Road","avgspeed":135.84,"maxspeed":176.0}
{"name":"Road 62","type":"Road","avgspeed":83.44,"maxspeed":176.0}
{"name":"Road 41","type":"Road","avgspeed":102.88,"maxspeed":176.0}
{"name":"Train 24","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Train 32","type":"Train","avgspeed":164.84,"maxspeed":264.0}
{"name":"Road 28","type":"Road","avgspeed":126.12,"maxspeed":176.0}
{"name":"Road 17","type":"Road","avgspeed":83.88,"maxspeed":176.0}
{"name":"Road 94","type":"Road","avgspeed":116.20833333333333,"maxspeed":224.0}
{"name":"Road 86","type":"Road","avgspeed":131.08,"maxspeed":176.0}
{"name":"Road 76","type":"Road","avgspeed":129.16,"maxspeed":176.0}
{"name":"Train 38","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Road 46","type":"Road","avgspeed":139.04,"maxspeed":176.0}
{"name":"Train 29","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Road 83","type":"Road","avgspeed":123.875,"maxspeed":176.0}
{"name":"Road 89","type":"Road","avgspeed":123.2,"maxspeed":176.0}
{"name":"Train 46","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Train 45","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Road 79","type":"Road","avgspeed":110.96,"maxspeed":176.0}
{"name":"Road 27","type":"Road","avgspeed":134.2,"maxspeed":176.0}
{"name":"Road 48","type":"Road","avgspeed":128.04166666666666,"maxspeed":176.0}
{"name":"Road 54","type":"Road","avgspeed":128.76,"maxspeed":176.0}
{"name":"Road 60","type":"Road","avgspeed":146.28,"maxspeed":176.0}
{"name":"Train 50","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Road 1","type":"Road","avgspeed":64.36,"maxspeed":176.0}
{"name":"Train 27","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Road 91","type":"Road","avgspeed":115.0,"maxspeed":224.0}
{"name":"Train 9","type":"Train","avgspeed":176.44,"maxspeed":264.0}
{"name":"Train 48","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Train 26","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Road 50","type":"Road","avgspeed":130.625,"maxspeed":176.0}
{"name":"Road 2","type":"Road","avgspeed":66.32,"maxspeed":176.0}
{"name":"Train 28","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Train 34","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Road 21","type":"Road","avgspeed":125.44,"maxspeed":176.0}
{"name":"Road 51","type":"Road","avgspeed":120.70833333333333,"maxspeed":176.0}
{"name":"Train 36","type":"Train","avgspeed":1.0,"maxspeed":1.0}
{"name":"Road 69","type":"Road","avgspeed":139.36,"maxspeed":176.0}
{"name":"Road 25","type":"Road","avgspeed":117.96,"maxspeed":176.0}
{"name":"Road 24","type":"Road","avgspeed":109.72,"maxspeed":176.0}
{"name":"Train 12","type":"Train","avgspeed":154.68,"maxspeed":264.0}
{"name":"Road 47","type":"Road","avgspeed":129.28,"maxspeed":176.0}
{"name":"Train 14","type":"Train","avgspeed":160.6,"maxspeed":264.0}
{"name":"Road 6","type":"Road","avgspeed":79.08,"maxspeed":176.0}
{"name":"Road 59","type":"Road","avgspeed":132.68,"maxspeed":176.0}
{"name":"Road 19","type":"Road","avgspeed":138.875,"maxspeed":176.0}
{"name":"Road 56","type":"Road","avgspeed":130.6,"maxspeed":176.0}
{"name":"Road 18","type":"Road","avgspeed":128.4,"maxspeed":176.0}
{"name":"Train 37","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Train 8","type":"Train","avgspeed":114.08,"maxspeed":264.0}
{"name":"Road 10","type":"Road","avgspeed":98.72,"maxspeed":176.0}
{"name":"Road 87","type":"Road","avgspeed":135.36,"maxspeed":176.0}
{"name":"Road 11","type":"Road","avgspeed":68.166666666666671,"maxspeed":176.0}
{"name":"Train 5","type":"Train","avgspeed":93.291666666666671,"maxspeed":239.0}
{"name":"Road 93","type":"Road","avgspeed":107.2,"maxspeed":224.0}
{"name":"Road 82","type":"Road","avgspeed":125.8,"maxspeed":176.0}
{"name":"Train 4","type":"Train","avgspeed":66.32,"maxspeed":264.0}
{"name":"Road 45","type":"Road","avgspeed":112.125,"maxspeed":176.0}
{"name":"Road 44","type":"Road","avgspeed":116.72,"maxspeed":176.0}
{"name":"Road 34","type":"Road","avgspeed":104.4,"maxspeed":176.0}
{"name":"Road 74","type":"Road","avgspeed":116.95833333333333,"maxspeed":176.0}
{"name":"Train 42","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Train 41","type":"Train","avgspeed":1.0,"maxspeed":1.0}
{"name":"Road 3","type":"Road","avgspeed":84.96,"maxspeed":176.0}
{"name":"Road 9","type":"Road","avgspeed":82.375,"maxspeed":176.0}
{"name":"Road 7","type":"Road","avgspeed":80.92,"maxspeed":176.0}
{"name":"Train 25","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Train 44","type":"Train","avgspeed":3.0,"maxspeed":3.0}
{"name":"Road 35","type":"Road","avgspeed":96.32,"maxspeed":176.0}
{"name":"Road 78","type":"Road","avgspeed":83.36,"maxspeed":176.0}
{"name":"Road 66","type":"Road","avgspeed":0.0,"maxspeed":0.0}
{"name":"Road 14","type":"Road","avgspeed":71.92,"maxspeed":176.0}
{"name":"Road 81","type":"Road","avgspeed":116.16,"maxspeed":176.0}
{"name":"Road 64","type":"Road","avgspeed":94.88,"maxspeed":176.0}
{"name":"Train 7","type":"Train","avgspeed":196.66666666666666,"maxspeed":264.0}
{"name":"Road 63","type":"Road","avgspeed":115.0,"maxspeed":176.0}
{"name":"Train 40","type":"Train","avgspeed":2.0,"maxspeed":2.0}
{"name":"Train 31","type":"Train","avgspeed":3.0,"maxspeed":3.0}
{"name":"Road 31","type":"Road","avgspeed":124.84,"maxspeed":176.0}
{"name":"Road 36","type":"Road","avgspeed":97.28,"maxspeed":176.0}
{"name":"Road 42","type":"Road","avgspeed":109.44,"maxspeed":176.0}
{"name":"Ship 1","type":"Ship","avgspeed":48.0,"maxspeed":48.0}
{"name":"Road 90","type":"Road","avgspeed":112.16666666666667,"maxspeed":176.0}
{"name":"Train 23","type":"Train","avgspeed":2.0,"maxspeed":2.0}
{"name":"Road 88","type":"Road","avgspeed":130.04,"maxspeed":176.0}
{"name":"Road 43","type":"Road","avgspeed":119.6,"maxspeed":176.0}
{"name":"Road 30","type":"Road","avgspeed":102.36,"maxspeed":176.0}
{"name":"Road 26","type":"Road","avgspeed":114.33333333333333,"maxspeed":176.0}
{"name":"Road 32","type":"Road","avgspeed":100.32,"maxspeed":176.0}
{"name":"Train 49","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Train 43","type":"Train","avgspeed":2.0,"maxspeed":2.0}
{"name":"Train 6","type":"Train","avgspeed":180.08333333333334,"maxspeed":264.0}
{"name":"Road 15","type":"Road","avgspeed":70.8,"maxspeed":176.0}
{"name":"Train 22","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Road 38","type":"Road","avgspeed":93.48,"maxspeed":176.0}
{"name":"Road 8","type":"Road","avgspeed":80.64,"maxspeed":176.0}
{"name":"Road 71","type":"Road","avgspeed":101.84,"maxspeed":176.0}
{"name":"Road 29","type":"Road","avgspeed":141.68,"maxspeed":176.0}
{"name":"Road 40","type":"Road","avgspeed":56.64,"maxspeed":176.0}
{"name":"Train 19","type":"Train","avgspeed":173.04,"maxspeed":264.0}
{"name":"Road 92","type":"Road","avgspeed":110.48,"maxspeed":224.0}
{"name":"Road 23","type":"Road","avgspeed":116.125,"maxspeed":176.0}
{"name":"Road 80","type":"Road","avgspeed":104.8,"maxspeed":176.0}
{"name":"Train 20","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Train 17","type":"Train","avgspeed":199.28,"maxspeed":264.0}
{"name":"Train 16","type":"Train","avgspeed":107.0,"maxspeed":180.0}
{"name":"Road 85","type":"Road","avgspeed":124.64,"maxspeed":176.0}
{"name":"Road 77","type":"Road","avgspeed":82.68,"maxspeed":176.0}
{"name":"Train 3","type":"Train","avgspeed":138.96,"maxspeed":264.0}
{"name":"Road 4","type":"Road","avgspeed":77.32,"maxspeed":176.0}
{"name":"Train 11","type":"Train","avgspeed":112.64,"maxspeed":264.0}
{"name":"Road 84","type":"Road","avgspeed":93.0,"maxspeed":176.0}
{"name":"Road 73","type":"Road","avgspeed":108.88,"maxspeed":176.0}
{"name":"Road 53","type":"Road","avgspeed":129.20833333333334,"maxspeed":176.0}
{"name":"Road 33","type":"Road","avgspeed":93.16,"maxspeed":176.0}
{"name":"Road 58","type":"Road","avgspeed":137.36,"maxspeed":176.0}
{"name":"Road 39","type":"Road","avgspeed":113.64,"maxspeed":176.0}
{"name":"Train 51","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Train 33","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Train 1","type":"Train","avgspeed":91.56,"maxspeed":264.0}
{"name":"Road 65","type":"Road","avgspeed":106.32,"maxspeed":176.0}
{"name":"Train 15","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Road 70","type":"Road","avgspeed":130.32,"maxspeed":176.0}
{"name":"Road 55","type":"Road","avgspeed":119.88,"maxspeed":176.0}
{"name":"Road 37","type":"Road","avgspeed":149.26923076923077,"maxspeed":176.0}
{"name":"Road 13","type":"Road","avgspeed":76.92307692307692,"maxspeed":176.0}
{"name":"Train 39","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Road 67","type":"Road","avgspeed":0.0,"maxspeed":0.0}
{"name":"Road 12","type":"Road","avgspeed":122.53846153846153,"maxspeed":176.0}
{"name":"Train 10","type":"Train","avgspeed":157.6,"maxspeed":264.0}
{"name":"Train 47","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Train 30","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Road 5","type":"Road","avgspeed":65.6923076923077,"maxspeed":176.0}
{"name":"Road 16","type":"Road","avgspeed":70.56,"maxspeed":176.0}
{"name":"Road 52","type":"Road","avgspeed":124.23076923076923,"maxspeed":176.0}
{"name":"Road 49","type":"Road","avgspeed":110.30769230769231,"maxspeed":176.0}
{"name":"Train 2","type":"Train","avgspeed":95.3076923076923,"maxspeed":253.0}
{"name":"Road 75","type":"Road","avgspeed":106.5,"maxspeed":176.0}
{"name":"Road 20","type":"Road","avgspeed":138.30769230769232,"maxspeed":176.0}
{"name":"Road 68","type":"Road","avgspeed":0.0,"maxspeed":0.0}
{"name":"Train 18","type":"Train","avgspeed":121.32,"maxspeed":245.0}
{"name":"Train 13","type":"Train","avgspeed":132.42307692307693,"maxspeed":264.0}
{"name":"Train 35","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Road 72","type":"Road","avgspeed":128.80769230769232,"maxspeed":176.0}
{"name":"Road 22","type":"Road","avgspeed":116.56,"maxspeed":176.0}
{"name":"Train 21","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Road 61","type":"Road","avgspeed":113.26923076923077,"maxspeed":176.0}
{"name":"Road 57","type":"Road","avgspeed":139.5,"maxspeed":176.0}
{"name":"Road 62","type":"Road","avgspeed":46.28,"maxspeed":176.0}
{"name":"Road 41","type":"Road","avgspeed":115.2,"maxspeed":176.0}
{"name":"Train 24","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Train 32","type":"Train","avgspeed":162.07692307692307,"maxspeed":264.0}
{"name":"Road 28","type":"Road","avgspeed":132.65384615384616,"maxspeed":176.0}
{"name":"Road 17","type":"Road","avgspeed":79.84,"maxspeed":176.0}
{"name":"Road 94","type":"Road","avgspeed":99.461538461538467,"maxspeed":224.0}
{"name":"Road 86","type":"Road","avgspeed":128.56,"maxspeed":176.0}
{"name":"Road 76","type":"Road","avgspeed":114.84615384615384,"maxspeed":176.0}
{"name":"Train 38","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Road 46","type":"Road","avgspeed":121.73076923076923,"maxspeed":176.0}
{"name":"Train 29","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Road 83","type":"Road","avgspeed":135.0,"maxspeed":176.0}
{"name":"Road 89","type":"Road","avgspeed":114.96153846153847,"maxspeed":176.0}
{"name":"Train 46","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Train 45","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Road 79","type":"Road","avgspeed":96.1923076923077,"maxspeed":176.0}
{"name":"Road 27","type":"Road","avgspeed":128.72,"maxspeed":176.0}
{"name":"Road 48","type":"Road","avgspeed":122.38461538461539,"maxspeed":176.0}
{"name":"Road 54","type":"Road","avgspeed":129.88,"maxspeed":176.0}
{"name":"Road 60","type":"Road","avgspeed":133.28,"maxspeed":176.0}
{"name":"Train 50","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Road 1","type":"Road","avgspeed":70.038461538461533,"maxspeed":176.0}
{"name":"Train 27","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Road 91","type":"Road","avgspeed":101.69230769230769,"maxspeed":224.0}
{"name":"Train 9","type":"Train","avgspeed":168.46153846153845,"maxspeed":264.0}
{"name":"Train 48","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Train 26","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Road 50","type":"Road","avgspeed":118.96153846153847,"maxspeed":176.0}
{"name":"Train 36","type":"Train","avgspeed":1.0,"maxspeed":1.0}
{"name":"Train 28","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Road 51","type":"Road","avgspeed":127.0,"maxspeed":176.0}
{"name":"Train 34","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Road 2","type":"Road","avgspeed":85.269230769230774,"maxspeed":176.0}
{"name":"Road 21","type":"Road","avgspeed":131.80769230769232,"maxspeed":176.0}
{"name":"Road 69","type":"Road","avgspeed":139.73076923076923,"maxspeed":176.0}
{"name":"Road 25","type":"Road","avgspeed":121.34615384615384,"maxspeed":176.0}
{"name":"Road 24","type":"Road","avgspeed":122.88461538461539,"maxspeed":176.0}
{"name":"Train 12","type":"Train","avgspeed":151.69230769230768,"maxspeed":264.0}
{"name":"Road 47","type":"Road","avgspeed":119.23076923076923,"maxspeed":176.0}
{"name":"Train 14","type":"Train","avgspeed":195.28,"maxspeed":264.0}
{"name":"Road 6","type":"Road","avgspeed":86.538461538461533,"maxspeed":176.0}
{"name":"Road 59","type":"Road","avgspeed":131.96153846153845,"maxspeed":176.0}
{"name":"Road 19","type":"Road","avgspeed":136.84615384615384,"maxspeed":176.0}
{"name":"Road 56","type":"Road","avgspeed":133.26923076923077,"maxspeed":176.0}
{"name":"Road 18","type":"Road","avgspeed":131.44,"maxspeed":176.0}
{"name":"Train 37","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Train 8","type":"Train","avgspeed":122.88461538461539,"maxspeed":264.0}
{"name":"Road 10","type":"Road","avgspeed":132.19230769230768,"maxspeed":176.0}
{"name":"Road 87","type":"Road","avgspeed":138.69230769230768,"maxspeed":176.0}
{"name":"Road 11","type":"Road","avgspeed":84.57692307692308,"maxspeed":174.0}
{"name":"Train 5","type":"Train","avgspeed":123.34615384615384,"maxspeed":264.0}
{"name":"Road 93","type":"Road","avgspeed":108.08,"maxspeed":224.0}
{"name":"Road 82","type":"Road","avgspeed":130.23076923076923,"maxspeed":176.0}
{"name":"Train 4","type":"Train","avgspeed":71.42307692307692,"maxspeed":264.0}
{"name":"Road 45","type":"Road","avgspeed":106.65384615384616,"maxspeed":176.0}
{"name":"Road 44","type":"Road","avgspeed":113.69230769230769,"maxspeed":176.0}
{"name":"Road 34","type":"Road","avgspeed":106.56,"maxspeed":176.0}
{"name":"Road 74","type":"Road","avgspeed":118.84615384615384,"maxspeed":176.0}
{"name":"Train 42","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Road 9","type":"Road","avgspeed":69.0,"maxspeed":176.0}
{"name":"Train 41","type":"Train","avgspeed":1.0,"maxspeed":1.0}
{"name":"Road 3","type":"Road","avgspeed":81.36,"maxspeed":176.0}
{"name":"Road 7","type":"Road","avgspeed":83.65384615384616,"maxspeed":176.0}
{"name":"Train 25","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Train 44","type":"Train","avgspeed":3.0,"maxspeed":3.0}
{"name":"Road 35","type":"Road","avgspeed":108.80769230769231,"maxspeed":176.0}
{"name":"Road 78","type":"Road","avgspeed":100.03846153846153,"maxspeed":176.0}
{"name":"Road 66","type":"Road","avgspeed":0.0,"maxspeed":0.0}
{"name":"Train 7","type":"Train","avgspeed":177.23076923076923,"maxspeed":264.0}
{"name":"Road 14","type":"Road","avgspeed":85.42307692307692,"maxspeed":176.0}
{"name":"Road 64","type":"Road","avgspeed":144.92307692307693,"maxspeed":176.0}
{"name":"Road 81","type":"Road","avgspeed":110.0,"maxspeed":176.0}
{"name":"Road 63","type":"Road","avgspeed":130.15384615384616,"maxspeed":176.0}
{"name":"Train 40","type":"Train","avgspeed":2.0,"maxspeed":2.0}
{"name":"Train 23","type":"Train","avgspeed":2.0,"maxspeed":2.0}
{"name":"Train 31","type":"Train","avgspeed":3.0,"maxspeed":3.0}
{"name":"Road 31","type":"Road","avgspeed":135.30769230769232,"maxspeed":176.0}
{"name":"Road 42","type":"Road","avgspeed":95.57692307692308,"maxspeed":176.0}
{"name":"Road 90","type":"Road","avgspeed":116.11538461538461,"maxspeed":176.0}
{"name":"Road 36","type":"Road","avgspeed":102.69230769230769,"maxspeed":176.0}
{"name":"Ship 1","type":"Ship","avgspeed":48.0,"maxspeed":48.0}
{"name":"Road 88","type":"Road","avgspeed":131.53846153846155,"maxspeed":176.0}
{"name":"Road 43","type":"Road","avgspeed":114.15384615384616,"maxspeed":176.0}
{"name":"Road 30","type":"Road","avgspeed":96.884615384615387,"maxspeed":176.0}
{"name":"Road 26","type":"Road","avgspeed":111.69230769230769,"maxspeed":176.0}
{"name":"Train 6","type":"Train","avgspeed":145.30769230769232,"maxspeed":264.0}
{"name":"Road 32","type":"Road","avgspeed":97.42307692307692,"maxspeed":176.0}
{"name":"Train 49","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Train 43","type":"Train","avgspeed":2.0,"maxspeed":2.0}
{"name":"Road 15","type":"Road","avgspeed":79.15384615384616,"maxspeed":176.0}
{"name":"Train 22","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Road 38","type":"Road","avgspeed":115.48,"maxspeed":176.0}
{"name":"Road 8","type":"Road","avgspeed":82.884615384615387,"maxspeed":176.0}
{"name":"Road 71","type":"Road","avgspeed":95.56,"maxspeed":176.0}
{"name":"Road 29","type":"Road","avgspeed":126.88,"maxspeed":176.0}
{"name":"Road 40","type":"Road","avgspeed":123.23076923076923,"maxspeed":176.0}
{"name":"Train 19","type":"Train","avgspeed":177.61538461538461,"maxspeed":264.0}
{"name":"Road 92","type":"Road","avgspeed":122.48,"maxspeed":224.0}
{"name":"Road 23","type":"Road","avgspeed":108.61538461538461,"maxspeed":176.0}
{"name":"Road 80","type":"Road","avgspeed":106.0,"maxspeed":176.0}
{"name":"Train 20","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Train 17","type":"Train","avgspeed":176.76923076923077,"maxspeed":264.0}
{"name":"Train 16","type":"Train","avgspeed":134.34615384615384,"maxspeed":240.0}
{"name":"Road 85","type":"Road","avgspeed":106.46153846153847,"maxspeed":176.0}
{"name":"Road 77","type":"Road","avgspeed":87.42307692307692,"maxspeed":165.0}
{"name":"Train 3","type":"Train","avgspeed":138.0,"maxspeed":264.0}
{"name":"Road 4","type":"Road","avgspeed":65.57692307692308,"maxspeed":176.0}
{"name":"Train 11","type":"Train","avgspeed":131.57692307692307,"maxspeed":264.0}
{"name":"Road 84","type":"Road","avgspeed":114.61538461538461,"maxspeed":176.0}
{"name":"Road 73","type":"Road","avgspeed":115.23076923076923,"maxspeed":176.0}
{"name":"Road 53","type":"Road","avgspeed":137.07692307692307,"maxspeed":176.0}
{"name":"Road 33","type":"Road","avgspeed":99.884615384615387,"maxspeed":176.0}
{"name":"Road 58","type":"Road","avgspeed":139.92,"maxspeed":176.0}
{"name":"Road 39","type":"Road","avgspeed":153.96153846153845,"maxspeed":176.0}
{"name":"Train 51","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Train 33","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Train 1","type":"Train","avgspeed":101.15384615384616,"maxspeed":264.0}
{"name":"Road 65","type":"Road","avgspeed":122.46153846153847,"maxspeed":176.0}
{"name":"Train 15","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Road 70","type":"Road","avgspeed":144.61538461538461,"maxspeed":176.0}
{"name":"Road 55","type":"Road","avgspeed":134.61538461538461,"maxspeed":176.0}
{"name":"Road 37","type":"Road","avgspeed":114.19230769230769,"maxspeed":176.0}
{"name":"Road 13","type":"Road","avgspeed":87.5,"maxspeed":176.0}
{"name":"Train 39","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Road 67","type":"Road","avgspeed":0.0,"maxspeed":0.0}
{"name":"Road 12","type":"Road","avgspeed":117.44,"maxspeed":176.0}
{"name":"Train 10","type":"Train","avgspeed":151.53846153846155,"maxspeed":264.0}
{"name":"Train 47","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Train 30","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Road 5","type":"Road","avgspeed":73.2,"maxspeed":176.0}
{"name":"Road 16","type":"Road","avgspeed":81.384615384615387,"maxspeed":176.0}
{"name":"Road 52","type":"Road","avgspeed":127.11538461538461,"maxspeed":176.0}
{"name":"Road 49","type":"Road","avgspeed":119.03846153846153,"maxspeed":176.0}
{"name":"Train 2","type":"Train","avgspeed":79.461538461538467,"maxspeed":264.0}
{"name":"Road 75","type":"Road","avgspeed":120.48,"maxspeed":176.0}
{"name":"Road 20","type":"Road","avgspeed":125.84615384615384,"maxspeed":176.0}
{"name":"Road 68","type":"Road","avgspeed":0.0,"maxspeed":0.0}
{"name":"Train 18","type":"Train","avgspeed":129.19230769230768,"maxspeed":240.0}
{"name":"Train 13","type":"Train","avgspeed":129.07692307692307,"maxspeed":264.0}
{"name":"Train 35","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Road 72","type":"Road","avgspeed":112.61538461538461,"maxspeed":176.0}
{"name":"Road 22","type":"Road","avgspeed":109.65384615384616,"maxspeed":176.0}
{"name":"Train 21","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Road 61","type":"Road","avgspeed":114.80769230769231,"maxspeed":176.0}
{"name":"Road 57","type":"Road","avgspeed":144.03846153846155,"maxspeed":176.0}
{"name":"Road 62","type":"Road","avgspeed":97.0,"maxspeed":176.0}
{"name":"Road 41","type":"Road","avgspeed":111.30769230769231,"maxspeed":176.0}
{"name":"Train 24","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Train 32","type":"Train","avgspeed":171.53846153846155,"maxspeed":264.0}
{"name":"Road 28","type":"Road","avgspeed":139.28,"maxspeed":176.0}
{"name":"Road 17","type":"Road","avgspeed":88.42307692307692,"maxspeed":176.0}
{"name":"Road 94","type":"Road","avgspeed":122.5,"maxspeed":224.0}
{"name":"Road 86","type":"Road","avgspeed":138.65384615384616,"maxspeed":176.0}
{"name":"Road 76","type":"Road","avgspeed":113.42307692307692,"maxspeed":176.0}
{"name":"Train 38","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Road 46","type":"Road","avgspeed":129.08,"maxspeed":176.0}
{"name":"Train 29","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Road 83","type":"Road","avgspeed":116.19230769230769,"maxspeed":176.0}
{"name":"Road 89","type":"Road","avgspeed":116.42307692307692,"maxspeed":176.0}
{"name":"Train 46","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Train 45","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Road 79","type":"Road","avgspeed":105.61538461538461,"maxspeed":176.0}
{"name":"Road 27","type":"Road","avgspeed":134.80769230769232,"maxspeed":176.0}
{"name":"Road 48","type":"Road","avgspeed":128.30769230769232,"maxspeed":176.0}
{"name":"Road 54","type":"Road","avgspeed":132.84615384615384,"maxspeed":176.0}
{"name":"Road 60","type":"Road","avgspeed":135.80769230769232,"maxspeed":176.0}
{"name":"Train 50","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Train 48","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Train 27","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Train 26","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Road 91","type":"Road","avgspeed":112.61538461538461,"maxspeed":224.0}
{"name":"Road 1","type":"Road","avgspeed":81.84615384615384,"maxspeed":176.0}
{"name":"Train 9","type":"Train","avgspeed":176.19230769230768,"maxspeed":264.0}
{"name":"Road 50","type":"Road","avgspeed":134.46153846153845,"maxspeed":176.0}
{"name":"Train 36","type":"Train","avgspeed":1.0,"maxspeed":1.0}
{"name":"Train 28","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Road 51","type":"Road","avgspeed":139.19230769230768,"maxspeed":176.0}
{"name":"Train 34","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Road 2","type":"Road","avgspeed":80.72,"maxspeed":176.0}
{"name":"Road 21","type":"Road","avgspeed":122.48,"maxspeed":176.0}
{"name":"Road 69","type":"Road","avgspeed":137.34615384615384,"maxspeed":176.0}
{"name":"Road 25","type":"Road","avgspeed":117.44,"maxspeed":176.0}
{"name":"Road 24","type":"Road","avgspeed":107.57692307692308,"maxspeed":176.0}
{"name":"Train 12","type":"Train","avgspeed":164.5,"maxspeed":264.0}
{"name":"Road 47","type":"Road","avgspeed":133.46153846153845,"maxspeed":176.0}
{"name":"Train 14","type":"Train","avgspeed":184.19230769230768,"maxspeed":264.0}
{"name":"Road 6","type":"Road","avgspeed":72.96,"maxspeed":176.0}
{"name":"Road 59","type":"Road","avgspeed":126.5,"maxspeed":176.0}
{"name":"Road 19","type":"Road","avgspeed":150.5,"maxspeed":176.0}
{"name":"Road 56","type":"Road","avgspeed":134.92307692307693,"maxspeed":176.0}
{"name":"Road 18","type":"Road","avgspeed":127.19230769230769,"maxspeed":176.0}
{"name":"Train 37","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Train 8","type":"Train","avgspeed":141.73076923076923,"maxspeed":264.0}
{"name":"Road 10","type":"Road","avgspeed":99.16,"maxspeed":176.0}
{"name":"Road 87","type":"Road","avgspeed":142.0,"maxspeed":176.0}
{"name":"Road 11","type":"Road","avgspeed":83.1923076923077,"maxspeed":176.0}
{"name":"Train 5","type":"Train","avgspeed":110.34615384615384,"maxspeed":264.0}
{"name":"Road 93","type":"Road","avgspeed":135.38461538461539,"maxspeed":224.0}
{"name":"Road 82","type":"Road","avgspeed":144.73076923076923,"maxspeed":176.0}
{"name":"Train 4","type":"Train","avgspeed":79.384615384615387,"maxspeed":264.0}
{"name":"Road 45","type":"Road","avgspeed":106.5,"maxspeed":176.0}
{"name":"Road 44","type":"Road","avgspeed":117.65384615384616,"maxspeed":176.0}
{"name":"Road 34","type":"Road","avgspeed":110.73076923076923,"maxspeed":176.0}
{"name":"Road 74","type":"Road","avgspeed":119.92307692307692,"maxspeed":176.0}
{"name":"Train 41","type":"Train","avgspeed":1.0,"maxspeed":1.0}
{"name":"Road 3","type":"Road","avgspeed":75.0,"maxspeed":176.0}
{"name":"Road 9","type":"Road","avgspeed":74.42307692307692,"maxspeed":176.0}
{"name":"Train 42","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Road 7","type":"Road","avgspeed":82.5,"maxspeed":176.0}
{"name":"Train 25","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Train 44","type":"Train","avgspeed":3.0,"maxspeed":3.0}
{"name":"Road 66","type":"Road","avgspeed":0.0,"maxspeed":0.0}
{"name":"Road 35","type":"Road","avgspeed":99.384615384615387,"maxspeed":176.0}
{"name":"Road 78","type":"Road","avgspeed":81.07692307692308,"maxspeed":155.0}
{"name":"Road 81","type":"Road","avgspeed":109.34615384615384,"maxspeed":176.0}
{"name":"Train 7","type":"Train","avgspeed":185.46153846153845,"maxspeed":264.0}
{"name":"Road 14","type":"Road","avgspeed":81.1923076923077,"maxspeed":176.0}
{"name":"Road 64","type":"Road","avgspeed":78.769230769230774,"maxspeed":176.0}
{"name":"Road 63","type":"Road","avgspeed":88.115384615384613,"maxspeed":176.0}
{"name":"Train 40","type":"Train","avgspeed":2.0,"maxspeed":2.0}
{"name":"Train 23","type":"Train","avgspeed":2.0,"maxspeed":2.0}
{"name":"Train 31","type":"Train","avgspeed":3.0,"maxspeed":3.0}
{"name":"Road 31","type":"Road","avgspeed":127.30769230769231,"maxspeed":176.0}
{"name":"Road 42","type":"Road","avgspeed":111.15384615384616,"maxspeed":176.0}
{"name":"Road 90","type":"Road","avgspeed":128.11538461538461,"maxspeed":176.0}
{"name":"Road 36","type":"Road","avgspeed":95.96,"maxspeed":176.0}
{"name":"Ship 1","type":"Ship","avgspeed":48.0,"maxspeed":48.0}
{"name":"Road 88","type":"Road","avgspeed":124.69230769230769,"maxspeed":176.0}
{"name":"Road 43","type":"Road","avgspeed":108.53846153846153,"maxspeed":176.0}
{"name":"Road 30","type":"Road","avgspeed":111.88461538461539,"maxspeed":176.0}
{"name":"Road 26","type":"Road","avgspeed":117.76923076923077,"maxspeed":176.0}
{"name":"Train 6","type":"Train","avgspeed":146.76923076923077,"maxspeed":264.0}
{"name":"Road 32","type":"Road","avgspeed":105.92307692307692,"maxspeed":176.0}
{"name":"Train 49","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Train 43","type":"Train","avgspeed":2.0,"maxspeed":2.0}
{"name":"Road 15","type":"Road","avgspeed":87.34615384615384,"maxspeed":176.0}
{"name":"Train 22","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Road 38","type":"Road","avgspeed":119.0,"maxspeed":176.0}
{"name":"Road 8","type":"Road","avgspeed":75.115384615384613,"maxspeed":176.0}
{"name":"Road 71","type":"Road","avgspeed":103.92307692307692,"maxspeed":176.0}
{"name":"Road 29","type":"Road","avgspeed":129.26923076923077,"maxspeed":176.0}
{"name":"Road 40","type":"Road","avgspeed":114.32,"maxspeed":176.0}
{"name":"Train 19","type":"Train","avgspeed":196.15384615384616,"maxspeed":264.0}
{"name":"Road 92","type":"Road","avgspeed":113.03846153846153,"maxspeed":224.0}
{"name":"Road 23","type":"Road","avgspeed":127.69230769230769,"maxspeed":176.0}
{"name":"Road 80","type":"Road","avgspeed":118.03846153846153,"maxspeed":176.0}
{"name":"Train 20","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Train 17","type":"Train","avgspeed":232.68,"maxspeed":264.0}
{"name":"Train 16","type":"Train","avgspeed":106.92307692307692,"maxspeed":222.0}
{"name":"Road 85","type":"Road","avgspeed":131.46153846153845,"maxspeed":176.0}
{"name":"Road 77","type":"Road","avgspeed":98.038461538461533,"maxspeed":176.0}
{"name":"Train 3","type":"Train","avgspeed":143.26923076923077,"maxspeed":264.0}
{"name":"Road 4","type":"Road","avgspeed":82.615384615384613,"maxspeed":176.0}
{"name":"Train 11","type":"Train","avgspeed":131.52,"maxspeed":264.0}
{"name":"Road 84","type":"Road","avgspeed":117.24,"maxspeed":176.0}
{"name":"Road 73","type":"Road","avgspeed":131.46153846153845,"maxspeed":176.0}
{"name":"Road 53","type":"Road","avgspeed":122.80769230769231,"maxspeed":176.0}
{"name":"Road 33","type":"Road","avgspeed":111.69230769230769,"maxspeed":176.0}
{"name":"Road 58","type":"Road","avgspeed":144.46153846153845,"maxspeed":176.0}
{"name":"Train 51","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Road 39","type":"Road","avgspeed":106.15384615384616,"maxspeed":176.0}
{"name":"Train 33","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Train 1","type":"Train","avgspeed":97.07692307692308,"maxspeed":264.0}
{"name":"Road 65","type":"Road","avgspeed":82.34615384615384,"maxspeed":176.0}
{"name":"Train 15","type":"Train","avgspeed":0.0,"maxspeed":0.0}
{"name":"Road 70","type":"Road","avgspeed":127.88,"maxspeed":176.0}
{"name":"Road 55","type":"Road","avgspeed":123.5,"maxspeed":176.0}

And an example of PowerBI quick insights

Resources

GIT Repo of the modified OpenTTD project

https://svermaak@bitbucket.org/svermaak/openttd.git

OpenTTD Compiling steps

https://wiki.openttd.org/Compiling_on_Windows_using_Microsoft_Visual_C%2B%2B_2012

UserResourceCleanup

INTRODUCTION

A GPO exist that can be configured to automatically delete old user profiles and a process such as CircularLogArchivercan be used to clean up log or old data but what about user data?

UserResouceCleanup can take care of this by monitoring the user data folders and Active Directory.

CONFIGURING USERRESOURCECLEANUP

a) Download and extract UserResourceCleanup.zip (here is VirusTotal scan) to a folder of your choice on the computer which it will be scheduled to run on.

b) Run Configurator.exe (Configurator Editor).

c) On the Encrypt tab, enter the password for the account that will be performing the automated placement task. Encrypt it with key bRK92kDpCqpnPMEtFp1cdJXixgqOqSKFUZ and record encrypted password

d) On the Settings tab, enter the domain information, connection username and the encrypted password recorded in step c. Configure UserNameMatch to a RegEx query that will match user account format. If you do not have a specific format, use .*

e) On the UserFolderLocations tab, specify folders to monitor to redundant user data

f) On the UsersToSkip tab, specify user folders to skip

g) Save configuration files

h) Schedule UserResourceCleanup.ConsoleApp.exe as a scheduled tasks

CONCLUSION

Using this process will keep recover space by removing old/redundant user data, just make sure that you have backups to cheap/slower storage in case you need to recover data.

Automated object placement using AutoAD

IMPLEMENTATION

1) Computer Description Update Process

a) Delegation

To be able to update computer descriptions you need to delegate rights.

Add the following permissions to Active Directory either to the root of the domain or any other Organizational Unit. You would add it to an Organizational Unit if you only want to use this process for some computers

b) Powershell Script

Below is the Powershell script used to update the computer description.

It is important to note that you should not change the format of the message if you are planning to use my automated object placement process.

This script will be used within a group policy in step c

try
    {
        # Get current user name
        $strUserName = $env:username;

        # Get current computer name
        $strComputerName = $env:computername;

        $objADSystemInfo = New-Object -ComObject ADSystemInfo;
        $objType = $objADSystemInfo.GetType();

        # Get current site name
        $strSiteName = $objType.InvokeMember('SiteName', 'GetProperty', $null, $objADSystemInfo, $null);

        # Get current date and time
        $strLogonDate = Get-Date -Format "dd-MM-yyyy HH:mm:ss";

        # Build message
        $strMessage = "$($strUserName) logged in on $($strLogonDate) at $($strSiteName) site";

        # Get computer object from Active Directory
        $strFilter = "(&(objectCategory=Computer)(name=$strComputerName))"
        $objSearcher = New-Object System.DirectoryServices.DirectorySearcher
        $objSearcher.Filter = $strFilter
        $objPath = $objSearcher.FindOne()
        $objComputer = $objPath.GetDirectoryEntry()

        # Update computer object description with message in Active Directory
        $objComputer.InvokeSet("Description", $strMessage)
        $objComputer.CommitChanges()
    }
catch
    {
        throw
    }

c) Group Policy Object

Create a GPO and link it to the root of a domain or Organizational Unit used in step a

Add the PowerShell script from step b as a User Logon script

d) Result

After these steps, notice how the computer descriptions are automatically populated once the users log on to their computers

2) AutoAD

a) Download and extract AutoAD.zip (here is VirusTotal scan) to a folder of your choice on the computer which it will be scheduled to run on.

b) Run Configurator.exe (Configurator Editor).

c) On the Encrypt tab, enter the password for the account that will be performing the automated placement task. Encrypt it with key 2xCJvezFBYWQPBeHy7USdajK55M8skww and record encrypted password

d) On the Settings tab, enter the domain information, connection user name and the encrypted password recorded in step 2c.

Specify which objects AutoAD should create automatically

e) Specify Active Directory information. The format for these are Subnet/Bit Mask|AD Site Name|Computer DN|User DN

Subnet/Bit Mask: The subnet and mask (in bit format) for the specific entry

AD Site: The Active Directory site to which the subnet belongs

Computer DN: The distinguished name of the organizational unit where to move computers to for computer objects in this subnet

User DN: The distinguished name of the organizational unit where to move users to for user objects in this subnet

Please Note: Ensure that you do not allow users/admins to gain any additional permissions by moving users from one container to another. The reason for this is that a user move might be forced to an incorrect OU if descriptions are tampered with. 

f) Specify any user DNs that should be skipped

g) Specify any computer DNs that should be skipped

DEMO EXECUTION

After implementing ComputerDescriptionUpdate.ps1 notice how computer descriptions are automatically updated

AutoAD.exe output

Sites and subnets automatically created by AutoAD

Organizational Units automatically created by AutoAD

Object placement (example 1)

Object placement (example 2)

Object placement (example 3)

Object placement (example 4)

CONCLUSION

Using this process will keep Active Directory organized and objects in the correct Organizational Units

Active Directory Password Reset Tool

BENEFITS

  • Sets a unique, secure password on each password reset
  • Helpdesk employee users do not need to use or install RSAT (at least not for those only resetting passwords)
  • End-users do not get passwords such as Password1 or Company1 and continue with this bad practice by continuing with passwords such as Password2 or Company2

IMPLEMENTATION

1) Download and extract PasswordResetTool.zip (here is VirusTotal scan) to a folder of your choice.

2) Run Configurator.exe (Configurator Editor).

a) On the Settings tab, enter the FQDN and NetBIOS for the domain on for which Password Reset Tool needs to reset passwords for

b) Specify length that passwords should be reset to for user and administrator accounts

c) Save the configuration file

DEMO EXECUTION

Once configuration has been completed, the Password Reset Tool can be executed

Once the Reset Password button is pressed, the specified user account password is reset to a pronounceable, random password

After the process, the connection password and user field are cleared.

RELEVANT CODE

DirectoryEntry directionEntry = new DirectoryEntry(domainPath, domainName + "\\" + connectionUserName, @connectionPassword);
    if (directionEntry != null)
    {
        DirectorySearcher search = new DirectorySearcher(directionEntry);
        search.Filter = "(SAMAccountName=" + userToReset + ")";
        SearchResult result = search.FindOne();
        if (result != null)
        {
            DirectoryEntry userEntry = result.GetDirectoryEntry();
            if (userEntry != null)
            {
                userEntry.Invoke("SetPassword", new object[] { password });
                userEntry.Properties["pwdLastSet"].Value = 0;
                userEntry.Properties["LockOutTime"].Value = 0x0000;
                userEntry.CommitChanges();
            }
        }
    }
    return password;

CONCLUSION

Using initial secure and unique passwords during reset contributes to a far more secure environment.

How to manage local account passwords from Active Directory without LAPS

Introduction

Active Directory provided a mechanism to set account password via GPO preferences. Unfortunately this was done with an encrypted cpassword value for which the decryption key was published and subsequently the functionality was blocked.

Later, Local Administrator Password Solution (LAPS), was adopted by Microsoft and it is well suited for the task.

The approach described in this article is an alternative method to LAPS which can be used if the schema extension requirement of LAPS cannot be done. It also allows for multiple account management.

For this solution you will need:

  • File Share
  • SQL Server
  • Group Policy
  • PasswordImporter
  • PasswordViewer

Both PasswordImport and PasswordViewer is available within the same download here

Create share

Create a folder on a server which will hold the passwords reset output files from computers that get the GPO.

It is important to only allow administrators to view files within this folder. Everyone (authenticated users) should only have create/write permission (as shown below). The reason for this is that password will be stored here in clear-text until the PasswordImporter
tool processed it.

Create GPO

Create a GPO and filter it to the computer objects for the computers you wish to manage passwords for.

 

The startup script needs to be set to save password text files to the share created in an earlier step.

Create SQL Table

Use the following SQL script to create a table within SQL to store passwords. Assign a group to this group to SQL and grant it SELECT rights on the Passwords table. Add all your administrators and support personnel into this group.

CREATE TABLE [dbo].[Passwords](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ComputerName] [varchar](255) NOT NULL,
[Password] [varchar](255) NOT NULL,
CONSTRAINT [PK_Passwords] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Below is an example of the passwords stored encrypted within SQL

Setup PasswordImporter

PasswordImporter is an application that reads password files from the share and import is, encrypted, into SQL. After a successful import, the password file is deleted.

Open Configurator and specify Location, ConnectionString and LogFile values. Note that Location and ConnectionString needs to be encrypted with encryption key wL!PIQj%EeWj%L^e$nEpdjFzL0d9%Y1#

 

Schedule this application to run every few minutes to import passwords to SQL database

Setup PasswordViewer

PasswordViewer is an application that shows the specified computers password. This application should be distributed to all administrators and support personnel.

Open Configurator and specify FQDN, NetBIOS and ConnectionString values. Note that ConnectionString needs to provide placeholder values for Domain, Username and
Password, for example:

Data source=YOURSQLSERVER;initial catalog=YOURDATABASE;Integrated Security=SSPI; User Id = [DOMAINNAME\USERNAME]; Password = [PASSWORD]