Dynamic Infrastructure Documentation with Powershell – Part 3

gisliDocumentationLeave a Comment

Here we go again, in this part we are going to get all servers in Active Directory and then we are going to audit them for both applications and services. Note that the snippets in LinkedIn is not very code friendly so bare with me, I will post this blog also on my personal website that I am currently working on and it will have better code snippets. Create a new table in DynamicDocumentationDB named AuditServices this table will contain information on all servers, services that have specific user running i.e. Active Directory user. We will exclude all services that have log on as NT Authority, LocalSystem and if the service has an empty username. Here is the SQL query for creating the table

USE DynamicDocumentationDB;
CREATETABLE AuditServices(
	[ServerName] varchar(50) NOTNULL,
	[ServiceName] varchar(50) NOTNULL,
	[Caption] varchar(50) NOTNULL,
	[RunningAs] varchar(50) NOTNULL,
	[Notes] varchar(50) NOTNULL,

Open up the DEPMSSQLAction.psm1 and add the following line

function addMSSQLAuditServices($ServerName, $ServiceName, $Caption, $RunningAs, $Notes, $Environment){
   #Adds new row into the database
    $Insert_Query = "
    INSERT INTO AuditServices (ServerName, ServiceName, Caption, RunningAs, Notes) VALUES ('$ServerName','$ServiceName','$Caption','$RunningAs','$Notes')
    #Calls the Run-Query function
    RunQuery -Query $Insert_Query -Environment $Environment

Now we will create a new file named AuditServices.ps1 and save it to the tasks folder, this file will enumerate each server which is in Active Directory, check if the server is online and then capture the information about the services. We will also check if the specified service user has not changed his password within 90 days and will add it to the notes field.

As promised in Part 3 we will also enumerate all servers and get software installed. We can do the same as we do above but there is different approach to this so we cannot use the same code. But before we start we need to define what information we are going to collect, lets start with the server information and here is a list that I am going to collect.

  • Server Information
  • Bios Information
  • CPU Information

Create a new Table named OSInformation with this specific columns

USE DynamicDocumentationDB;
	[ServerName] varchar(50),
	[OS_BootDevice] varchar(50),
	[OS_BuildNumber] varchar(50),
	[OS_BuildType] varchar(50),
	[OS_Caption] varchar(255),
	[OS_CodeSet] varchar(50),
	[OS_CountryCode] varchar(50),
	[OS_CurrentTimeZone] varchar(50),
	[OS_Description] varchar(255),
	[OS_InstallDate] DATETIME,
	[OS_LastBootUpTime] DATETIME,
	[OS_Architecture] varchar(50),
	[OS_SerialNumber] varchar(50),
	[OS_ServicePackMajorVersion] varchar(50),
	[OS_ServicePackMinorVersion] varchar(50),
	[OS_SystemDrive] varchar(50),
	[OS_TotalVirtualMemorySize] varchar(50),
	[bios_SMBIOSBIOSVersion] varchar(50),
	[bios_Manufacturer] varchar(50),
	[bios_Name] varchar(50),
	[bios_SerialNumber] varchar(255),
	[bios_Version] varchar(50),
	[bios_ReleaseDate] DATETIME,
	[bios_Status] varchar(50),
	[cpu_Caption] varchar(50),
	[cpu_Manufacturer] varchar(50),
	[cpu_MaxClockSpeed] varchar(50),
	[cpu_Name] varchar(50),
	[cpu_CurrentVoltage] varchar(50),
	[cpu_NumberOfCores] varchar(50),
	[cpu_NumberOfEnabledCore] varchar(50),
	[cpu_NumberOfLogicalProcessors] varchar(50),
	[cpu_VirtualizationFirmwareEnabled] varchar(50),

Open up the DEPMSSQLActions.psm1 and add the following code. Note that the parameter for this function is pretty long

function addMSSQLOSInformation($OS_BootDevice,$OS_BuildNumber,$OS_BuildType,$OS_Caption,$OS_CodeSet,$OS_CountryCode,$OS_CurrentTimeZone,$OS_Description,$OS_InstallDate,$OS_LastBootUpTime,$OS_Architecture,$OS_SerialNumber,$OS_ServicePackMajorVersion,$OS_ServicePackMinorVersion,$OS_SystemDrive,$OS_TotalVirtualMemorySize,$bios_SMBIOSBIOSVersion,$bios_Manufacturer,$bios_Name,$bios_SerialNumber,$bios_Version,$bios_ReleaseDate,$bios_Status,$cpu_Caption,$cpu_Manufacturer,$cpu_MaxClockSpeed,$cpu_Name,$cpu_CurrentVoltage,$cpu_NumberOfCores,$cpu_NumberOfEnabledCore,$cpu_NumberOfLogicalProcessors,$cpu_VirtualizationFirmwareEnabled,$Environment){
    $Insert_Query = "
    INSERT INTO OSInformation (OS_BootDevice, OS_BuildNumber, OS_BuildType, OS_Caption, OS_CodeSet, OS_CountryCode, OS_CurrentTimeZone, OS_Description, OS_InstallDate, OS_LastBootUpTime, OS_Architecture, OS_SerialNumber, OS_ServicePackMajorVersion, OS_ServicePackMinorVersion, OS_SystemDrive, OS_TotalVirtualMemorySize, bios_SMBIOSBIOSVersion, bios_Manufacturer, bios_Name, bios_SerialNumber, bios_Version, bios_ReleaseDate, bios_Status, cpu_Caption, cpu_Manufacturer, cpu_MaxClockSpeed, cpu_Name, cpu_CurrentVoltage, cpu_NumberOfCores, cpu_NumberOfEnabledCore, cpu_NumberOfLogicalProcessors, cpu_VirtualizationFirmwareEnabled) VALUES ('$OS_BootDevice','$OS_BuildNumber','$OS_BuildType','$OS_Caption','$OS_CodeSet','$OS_CountryCode','$OS_CurrentTimeZone','$OS_Description','$OS_InstallDate','$OS_LastBootUpTime','$OS_Architecture','$OS_SerialNumber','$OS_ServicePackMajorVersion','$OS_ServicePackMinorVersion','$OS_SystemDrive','$OS_TotalVirtualMemorySize','$bios_SMBIOSBIOSVersion','$bios_Manufacturer','$bios_Name','$bios_SerialNumber','$bios_Version','$bios_ReleaseDate','$bios_Status','$cpu_Caption','$cpu_Manufacturer','$cpu_MaxClockSpeed','$cpu_Name','$cpu_CurrentVoltage','$cpu_NumberOfCores','$cpu_NumberOfEnabledCore','$cpu_NumberOfLogicalProcessors','$cpu_VirtualizationFirmwareEnabled')
    RunQuery -Query $Insert_Query -Environment $Environment

Now create a new file named AuditServers.ps1 and save it to the Task folder. Open up the file and add the following code.

Now for the Software, create a table named SoftwareInformation by using this query. We will get this information from the os of each computer in active directory.

USE DynamicDocumentationDB;
CREATE TABLE SoftwareInformation(
	[ServerName] varchar(255),
	[SoftName] varchar(255),
	[HelpLink] varchar(255),
	[InstallDate] DATE,
	[InstallLocation] varchar(255),
	[InstallSource] varchar(255),
	[PackageName] varchar(255),
	[Vendor] varchar(255),
	[SoftVersion] varchar(255),

Open up the DEPMSSQLActions.psm1 and add the following function to add information in the SoftwareInformation table

function addMSSQLSoftwareInformation($ServerName,$Name,$HelpLink,$InstallDate,$InstallLocation,$InstallSource,$PackageName,$Vendor,$Version,$Environment){
    $Insert_Query = "
    INSERT INTO SoftwareInformation (ServerName,SoftName,HelpLink,InstallDate,InstallLocation,InstallSource,PackageName,Vendor,SoftVersion) VALUES ('$ServerName','$Name','$HelpLink','$InstallDate','$InstallLocation','$InstallSource','$PackageName','$Vendor','$Version')
    RunQuery -Query $Insert_Query -Environment $Environment

Create a new file named AuditSoftware.ps1 and add the following code, note in this example I use the ParseExact date conversion, in AutidServers I used different method.

Now we have not updated our Debug-SQL.ps1 yet but we can do this now typing in this code

Now we have completed this part, in next part we will audit Microsoft Exchange server information and also the users.


Complete repository on


Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.