How It Works

Components

The diagram below illustrates the components of The Source of Truth Computer Information System. This comprises of:

A configuration utility
A Powershell script and .config file
A Group Policy
An SQL Server
An IIS Server

A Powershell script and config file is places on the Sysvol share where it is deployed by Group Policy which copies the files to a local folder on the end device. The script runs, updates the SQL server and displayed on a web page, served by the IIS server.

The Powershell script and config file is placed on the Sysvol share and deployed by GPO.

The GPO creates a folder on the end user machine in C:\Program Files, into which the script and config files are copied.

When it applies, the GPO can either create an immediate scheduled task which will run the script at every GPO update, or it can schedule it for however often is desired.

The script has an auto-update mechanism, so if this is scheduled for every X hours/days on the client, then if there is an update to the script, the client will pick it up before executing it.

The client script writes logs to a folder in C:\ProgramData for troubleshooting any code that has been added and is not working as expected. Only up to 11 logs of around 8kb are kept.

The next action is to check if there is an updated config file or script on the Sysvol share. If there is, it will replace the existing file(s) and re-execute, meaning that both the script and config files are self-updating.

When the script has run and collected all the data, it will connect to the SQL server and either create a new record if one does not exist, or update the existing record with new data.

The IIS server hosts a simple .aspx page that displays the MainDetails table (where all the data is stored) as a web page in table format. This can be exported to Excel (.xlsx format) with one button on the web page.

An example of what the output looks like on the web page can be seen below: (additional columns cropped from the screenshot)

You can see that this is listed by machine name and shows some basic, default details that would be pertinent to most engineers, administrators and support personnel, with the LastDBUpdate column being the last time seen on the domain.

Minimum Requirements & Prerequisites

Source of Truth requires the following requirements and prerequisites to be met for installation and operation:

Server:

Windows Server 2016 or later (physical or virtual)
16GB RAM (64GB or greater recommended)
10GB free disk space at a minimum. This requirement will grow with large client bases.
Dual core processor (4 cores or greater recommended)
Powershell enabled in Full Language mode.
Windows Firewall ports TCP/UDP 1433,1434 opened (for SQL Database)
HTTP Port 80 for the IIS web page
- If this is set by GPO at an organisational level, these will need to be excluded from the SoT server.
- The SoT Configurator will open the SQL ports and the IIS installation opens the HTTP port.
If Applocker is enabled, then scripts from C:\Program Files\FFPSoT allowed.

Clients:

Windows 7 or later with Powershell 3.0 or higher
- Whilst the client script MAY work on Windows XP or Vista, these operating systems MUST have at least Powershell 3.0 installed, but even then, this has not been tested and is not warranted.
No particular RAM, CPU or Disk requirements.
Line-of-Sight to the Domain Controllers and SoT DB Server.

Customisations

This is where the extensible nature of this system really comes into it’s own; the ability to add Powershell code to the script to produce additional data to report.

As this is added to the master script on the Sysvol share, due to the self-updating nature of the files and how it is deployed via GPO, at it’s fastest, it can have every machine in the environment update it’s records within the hour, depending on GPO frequency.

To add customisations to the system, there are several steps necessary.

For this example, reporting on a software version is used; Microsoft Silverlight (in case a vulnerability has been found in a certain version of a software product and that version needs to be reported quickly)

Add code to the script. The key part is to ensure that the result is stored in a variable. This variable will be used in the SQL Insert/Update statement to push into the database.

2. Add the $SilverlightVersion variable to the SQL Insert command. This just needs to be placed at the end of the string command as well as the update command.

3. Add the column SilverLightVersion to the end of the SQL dbo.MainDetails table, before the VERSION column.

4. Repeat this process for each new item of data that needs to be collected.

Operation & Usage

Once Source of Truth has been installed, configured and the Group Policies deployed, it will, to a large extent run itself and work quietly in the background, collecting the data from your environment.

Extracting Data:

In order to view the data held by Source of Truth, you will need to open a browser from within your environment and enter HTTP://%SoTServerName% (Where %SoTServerName% is the name of the server where SoT is installed). This will bring you to the Source Of Truth web page for your environment.

Click the Export to Excel button and this exports the grid on the web page to an xlsx file for use in Excel.

To access the Dev data, enter HTTP://%SoTServerName%/Default-Dev.aspx . This will bring back the data from the Dev database:

The data is exported to Excel in the same manner.

Explanation of the columns:

Security

Upon installation, both the Prod and Dev instances of the databases are permissioned for Domain Computers to have Read/Write permissions, along with the user installing it having the SA role assigned to them. The reason for this is that all domain computers will need to be able to both check to see if they have an entry in the databases and write their data to the databases.

The website (both prod and dev instances) are openly permissioned, so any user on your domain can access them.

Whilst it is possible to further customise the database and web page access and security according to the needs of your organisation, this is something that is entirely down to you and FFP Software accepts no liability for security misconfiguration after installation.