The article below is about using Microsoft SQL Server Management Studio 2014 as SQL Server. Install it to proceed.

The scripts are here : PowerShell : Server's remote inventory, WMI, MSSQL Database

Introduction

This PowerShell tool allows to inventory a list of Windows server. 

The most of the work is made with Windows Management Instrumentation calls (WMI Architecture) to retrieve the systems configuration before being injected in a SQL database.

Exceptions are below :

  1. Install progams list is got through the registry.  It does not use the Win32_product  class which is not query optimized as Ed Wilson recommends

    "The Win32_product class is not query optimized. Queries such as "select * from Win32_Product where (name like 'Sniffer%')" require WMI to use the MSI provider to enumerate all of the installed products and then parse the full list sequentially to handle the "where" clause:

    This process initiates a consistency check of packages installed, and then verifying and repairing the installations. If you have an application that makes use of the Win32_Product class, you should contact the vendor to get an updated version that does not use this class."

  2. Scheduled tasks are queried through a COM obect "Schedule.Service"
  3. ODBC  is got through the registry
  4. Operating privileges are obtained via a WMI call to secedit.exe, and dropped into an XML file before injected into the database

A log file is created during the code execution. It allows to follow the INSERT statement into the database.

The result is a SQL Server database that you can query. It can be a good start for a Configuration Management Database or any inventory job.


Create database 

In order to create the database the script needs, you have to launch the following script : 

USE [master]

GO
/****** Object:  Database [PowerShellServerInventory]    Script Date: 2015-07-22 09:12:22 ******/
CREATE DATABASE [PowerShellServerInventory]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'PowerShellServerInventory', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\PowerShellServerInventory.mdf' , SIZE = 14336KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'PowerShellServerInventory_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\PowerShellServerInventory_log.ldf' , SIZE = 16576KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [PowerShellServerInventory] SET COMPATIBILITY_LEVEL = 120
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [PowerShellServerInventory].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [PowerShellServerInventory] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [PowerShellServerInventory] SET ANSI_NULLS OFF
GO
ALTER DATABASE [PowerShellServerInventory] SET ANSI_PADDING OFF
GO
ALTER DATABASE [PowerShellServerInventory] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [PowerShellServerInventory] SET ARITHABORT OFF
GO
ALTER DATABASE [PowerShellServerInventory] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [PowerShellServerInventory] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [PowerShellServerInventory] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [PowerShellServerInventory] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [PowerShellServerInventory] SET CURSOR_DEFAULT  GLOBAL
GO
ALTER DATABASE [PowerShellServerInventory] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [PowerShellServerInventory] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [PowerShellServerInventory] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [PowerShellServerInventory] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [PowerShellServerInventory] SET  DISABLE_BROKER
GO
ALTER DATABASE [PowerShellServerInventory] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [PowerShellServerInventory] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [PowerShellServerInventory] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [PowerShellServerInventory] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [PowerShellServerInventory] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [PowerShellServerInventory] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [PowerShellServerInventory] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [PowerShellServerInventory] SET RECOVERY SIMPLE
GO
ALTER DATABASE [PowerShellServerInventory] SET  MULTI_USER
GO
ALTER DATABASE [PowerShellServerInventory] SET PAGE_VERIFY CHECKSUM 
GO
ALTER DATABASE [PowerShellServerInventory] SET DB_CHAINING OFF
GO
ALTER DATABASE [PowerShellServerInventory] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )
GO
ALTER DATABASE [PowerShellServerInventory] SET TARGET_RECOVERY_TIME = 0 SECONDS
GO
ALTER DATABASE [PowerShellServerInventory] SET DELAYED_DURABILITY = DISABLED
GO
USE [PowerShellServerInventory]
GO
/****** Object:  Table [dbo].[DriveAudited]    Script Date: 2015-07-22 09:12:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DriveAudited](
    [diskID] [int] IDENTITY(1,1) NOT NULL,
    [serverID] [int] NOT NULL,
    [diskType] [varchar](25) NULL,
    [driveLetter] [varchar](5) NULL,
    [capacity] [int] NULL,
    [freeSpace] [int] NULL,
PRIMARY KEY CLUSTERED
(
    [diskID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[InstalledProgramAudited]    Script Date: 2015-07-22 09:12:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[InstalledProgramAudited](
    [installedProgramID] [int] IDENTITY(1,1) NOT NULL,
    [serverID] [int] NOT NULL,
    [displayName] [varchar](150) NULL,
    [displayVersion] [varchar](80) NULL,
    [installLocation] [varchar](200) NULL,
    [publisher] [varchar](100) NULL,
PRIMARY KEY CLUSTERED
(
    [installedProgramID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[LocalGroupAudited]    Script Date: 2015-07-22 09:12:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[LocalGroupAudited](
    [localGroupAuditedID] [int] IDENTITY(1,1) NOT NULL,
    [serverID] [int] NOT NULL,
    [localGroup] [varchar](200) NULL,
    [userNested] [varchar](200) NULL,
PRIMARY KEY CLUSTERED
(
    [localGroupAuditedID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[MemoryAudited]    Script Date: 2015-07-22 09:12:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MemoryAudited](
    [memoryID] [int] IDENTITY(1,1) NOT NULL,
    [serverID] [int] NOT NULL,
    [Label] [varchar](50) NULL,
    [Capacity] [int] NULL,
    [Form] [int] NULL,
    [TypeM] [int] NULL,
PRIMARY KEY CLUSTERED
(
    [memoryID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[NetworkAudited]    Script Date: 2015-07-22 09:12:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[NetworkAudited](
    [networkID] [int] IDENTITY(1,1) NOT NULL,
    [serverID] [int] NOT NULL,
    [networkCard] [varchar](50) NULL,
    [dhcpEnabled] [varchar](5) NULL,
    [ipAddress] [varchar](50) NULL,
    [subnetMask] [varchar](50) NULL,
    [defaultGateway] [varchar](50) NULL,
    [dnsServers] [varchar](50) NULL,
    [dnsReg] [varchar](5) NULL,
    [primaryWins] [varchar](50) NULL,
    [secondaryWins] [varchar](50) NULL,
    [winsLookup] [varchar](5) NULL,
PRIMARY KEY CLUSTERED
(
    [networkID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[ODBCConfiguredAudited]    Script Date: 2015-07-22 09:12:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ODBCConfiguredAudited](
    [odbcConfiguredAuditedID] [int] IDENTITY(1,1) NOT NULL,
    [serverID] [int] NOT NULL,
    [dsn] [varchar](150) NULL,
    [serverName] [varchar](150) NULL,
    [port] [int] NULL,
    [dataBaseFile] [varchar](150) NULL,
    [dataBaseName] [varchar](150) NULL,
    [odbcUID] [varchar](150) NULL,
    [odbcPWD] [varchar](150) NULL,
    [start] [varchar](150) NULL,
    [lastUser] [varchar](150) NULL,
    [odbcDatabase] [varchar](150) NULL,
    [defaultLibraries] [varchar](150) NULL,
    [defaultPackage] [varchar](150) NULL,
    [defaultPkgLibrary] [varchar](150) NULL,
    [odbcSystem] [varchar](150) NULL,
    [driver] [varchar](150) NULL,
    [odbcDescription] [varchar](200) NULL,
PRIMARY KEY CLUSTERED
(
    [odbcConfiguredAuditedID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[ODBCInstalledAudited]    Script Date: 2015-07-22 09:12:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ODBCInstalledAudited](
    [odbcInstalledAuditedID] [int] IDENTITY(1,1) NOT NULL,
    [serverID] [int] NOT NULL,
    [driver] [varchar](150) NULL,
    [driverODBCVer] [varchar](150) NULL,
    [fileExtns] [varchar](150) NULL,
    [setup] [varchar](150) NULL,
PRIMARY KEY CLUSTERED
(
    [odbcInstalledAuditedID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[OSPrivilegeAudited]    Script Date: 2015-07-22 09:12:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[OSPrivilegeAudited](
    [privilegeID] [int] IDENTITY(1,1) NOT NULL,
    [serverID] [int] NOT NULL,
    [strategy] [varchar](100) NULL,
    [securityParameter] [varchar](500) NULL,
PRIMARY KEY CLUSTERED
(
    [privilegeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[PrinterAudited]    Script Date: 2015-07-22 09:12:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PrinterAudited](
    [printerAuditedID] [int] IDENTITY(1,1) NOT NULL,
    [serverID] [int] NOT NULL,
    [name] [varchar](100) NULL,
    [location] [varchar](100) NULL,
    [printerState] [int] NULL,
    [printerStatus] [int] NULL,
    [shareName] [varchar](100) NULL,
    [systemName] [varchar](100) NULL,
PRIMARY KEY CLUSTERED
(
    [printerAuditedID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[ProcessAudited]    Script Date: 2015-07-22 09:12:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ProcessAudited](
    [processAuditedID] [int] IDENTITY(1,1) NOT NULL,
    [serverID] [int] NOT NULL,
    [name] [varchar](100) NOT NULL,
    [location] [varchar](150) NOT NULL,
    [sessionID] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
    [processAuditedID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[ProcessorAudited]    Script Date: 2015-07-22 09:12:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ProcessorAudited](
    [processorAuditedID] [int] IDENTITY(1,1) NOT NULL,
    [serverID] [int] NOT NULL,
    [Name] [varchar](100) NULL,
    [TypeP] [varchar](100) NULL,
    [Family] [varchar](10) NULL,
    [Speed] [int] NULL,
    [CacheSize] [int] NULL,
    [Interface] [int] NULL,
    [SocketNumber] [varchar](20) NULL,
PRIMARY KEY CLUSTERED
(
    [processorAuditedID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[ScheduledTaskAudited]    Script Date: 2015-07-22 09:12:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ScheduledTaskAudited](
    [scheduledTaskAuditedID] [int] IDENTITY(1,1) NOT NULL,
    [serverID] [int] NOT NULL,
    [name] [varchar](150) NULL,
    [runAs] [varchar](150) NULL,
    [scheduledAction] [varchar](200) NULL,
    [nextRunTime] [datetime] NOT NULL,
    [lastRunTime] [datetime] NOT NULL,
    [pathName] [varchar](200) NULL,
PRIMARY KEY CLUSTERED
(
    [scheduledTaskAuditedID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[ServerAudited]    Script Date: 2015-07-22 09:12:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ServerAudited](
    [serverID] [int] NOT NULL,
    [serverName] [varchar](100) NULL,
    [domain] [varchar](100) NULL,
    [role] [varchar](50) NULL,
    [HW_Make] [varchar](100) NULL,
    [HW_Model] [varchar](100) NULL,
    [HW_Type] [varchar](100) NULL,
    [cpuCount] [int] NULL,
    [memoryGB] [int] NULL,
    [operatingSystem] [varchar](100) NULL,
    [servicePackLevel] [varchar](50) NULL,
    [biosName] [varchar](100) NULL,
    [biosVersion] [varchar](100) NULL,
    [hardwareSerial] [varchar](100) NULL,
    [timeZone] [varchar](50) NULL,
    [wmiVersion] [varchar](20) NULL,
    [virtualMemoryName] [varchar](50) NULL,
    [virtualMemoryCurrentUsage] [int] NULL,
    [virtualMermoryPeakUsage] [int] NULL,
    [virtualMemoryAllocatedBaseSize] [int] NULL,
PRIMARY KEY CLUSTERED
(
    [serverID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[ServiceAudited]    Script Date: 2015-07-22 09:12:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ServiceAudited](
    [serviceAuditedID] [int] IDENTITY(1,1) NOT NULL,
    [serverID] [int] NOT NULL,
    [displayName] [varchar](150) NULL,
    [name] [varchar](150) NULL,
    [startName] [varchar](150) NULL,
    [startMode] [varchar](10) NOT NULL,
    [servicePathName] [varchar](150) NULL,
    [serviceDescription] [varchar](1000) NULL,
PRIMARY KEY CLUSTERED
(
    [serviceAuditedID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[ShareAudited]    Script Date: 2015-07-22 09:12:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ShareAudited](
    [shareAuditedID] [int] IDENTITY(1,1) NOT NULL,
    [serverID] [int] NOT NULL,
    [shareName] [varchar](150) NULL,
PRIMARY KEY CLUSTERED
(
    [shareAuditedID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[ShareRightsAudited]    Script Date: 2015-07-22 09:12:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ShareRightsAudited](
    [shareRightsAuditedID] [int] IDENTITY(1,1) NOT NULL,
    [shareAuditedID] [int] NOT NULL,
    [account] [varchar](100) NOT NULL,
    [rights] [varchar](200) NOT NULL,
    [aceFlags] [varchar](100) NOT NULL,
    [aceType] [varchar](20) NOT NULL,
PRIMARY KEY CLUSTERED
(
    [shareRightsAuditedID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
SET ANSI_PADDING OFF
GO
/****** Object:  View [dbo].[vw_localGroupOnServer]    Script Date: 2015-07-22 09:12:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
CREATE VIEW [dbo].[vw_localGroupOnServer]
   AS
SELECT sa.[serverID]
      ,[serverName]
      ,[domain]
      ,[role]
      ,[HW_Make]
      ,[HW_Model]
      ,[HW_Type]
      ,[cpuCount]
      ,[memoryGB]
      ,[operatingSystem]
      ,[servicePackLevel]
      ,[biosName]
      ,[biosVersion]
      ,[hardwareSerial]
      ,[timeZone]
      ,[wmiVersion]
      ,[virtualMemoryName]
      ,[virtualMemoryCurrentUsage]
      ,[virtualMermoryPeakUsage]
      ,[virtualMemoryAllocatedBaseSize]
      ,[localGroupAuditedID]
      ,[localGroup]
      ,[userNested]
  FROM [PowerShellServerInventory].[dbo].[LocalGroupAudited] lga JOIN [PowerShellServerInventory].[dbo].[ServerAudited] sa
      ON lga.serverID = sa.serverID
 
 
GO
/****** Object:  View [dbo].[vw_NLDOM01LocalGroupOnServers]    Script Date: 2015-07-22 09:12:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vw_NLDOM01LocalGroupOnServers]
   AS
SELECT distinct [userNested] as userNestedToronto
      ,sa.[serverName]
      ,sa.[domain]
      ,[localGroup]
       
  FROM [PowerShellServerInventory].[dbo].[ServerAudited] sa JOIN [PowerShellServerInventory].[dbo].[LocalGroupAudited] lga
  ON lga.serverID = sa.serverID
  WHERE [userNested] LIKE '%NLDOM%'
 
 
GO
ALTER TABLE [dbo].[DriveAudited]  WITH CHECK ADD  CONSTRAINT [FK_Drive_ServerAudited] FOREIGN KEY([serverID])
REFERENCES [dbo].[ServerAudited] ([serverID])
GO
ALTER TABLE [dbo].[DriveAudited] CHECK CONSTRAINT [FK_Drive_ServerAudited]
GO
ALTER TABLE [dbo].[InstalledProgramAudited]  WITH CHECK ADD  CONSTRAINT [FK_InstalledProgramAudited_ServerAudited] FOREIGN KEY([serverID])
REFERENCES [dbo].[ServerAudited] ([serverID])
GO
ALTER TABLE [dbo].[InstalledProgramAudited] CHECK CONSTRAINT [FK_InstalledProgramAudited_ServerAudited]
GO
ALTER TABLE [dbo].[LocalGroupAudited]  WITH CHECK ADD  CONSTRAINT [FK_LocalGroupAudited_ServerAudited] FOREIGN KEY([serverID])
REFERENCES [dbo].[ServerAudited] ([serverID])
GO
ALTER TABLE [dbo].[LocalGroupAudited] CHECK CONSTRAINT [FK_LocalGroupAudited_ServerAudited]
GO
ALTER TABLE [dbo].[MemoryAudited]  WITH CHECK ADD  CONSTRAINT [FK_Memory_ServerAudited] FOREIGN KEY([serverID])
REFERENCES [dbo].[ServerAudited] ([serverID])
GO
ALTER TABLE [dbo].[MemoryAudited] CHECK CONSTRAINT [FK_Memory_ServerAudited]
GO
ALTER TABLE [dbo].[NetworkAudited]  WITH CHECK ADD  CONSTRAINT [FK_NetworkAudited_ServerAudited] FOREIGN KEY([serverID])
REFERENCES [dbo].[ServerAudited] ([serverID])
GO
ALTER TABLE [dbo].[NetworkAudited] CHECK CONSTRAINT [FK_NetworkAudited_ServerAudited]
GO
ALTER TABLE [dbo].[ODBCConfiguredAudited]  WITH CHECK ADD  CONSTRAINT [FK_ODBCConfiguredAudited_ServerAudited] FOREIGN KEY([serverID])
REFERENCES [dbo].[ServerAudited] ([serverID])
GO
ALTER TABLE [dbo].[ODBCConfiguredAudited] CHECK CONSTRAINT [FK_ODBCConfiguredAudited_ServerAudited]
GO
ALTER TABLE [dbo].[ODBCInstalledAudited]  WITH CHECK ADD  CONSTRAINT [FK_ODBCInstalledAudited_ServerAudited] FOREIGN KEY([serverID])
REFERENCES [dbo].[ServerAudited] ([serverID])
GO
ALTER TABLE [dbo].[ODBCInstalledAudited] CHECK CONSTRAINT [FK_ODBCInstalledAudited_ServerAudited]
GO
ALTER TABLE [dbo].[OSPrivilegeAudited]  WITH CHECK ADD  CONSTRAINT [FK_OSPrivilege_ServerAudited] FOREIGN KEY([serverID])
REFERENCES [dbo].[ServerAudited] ([serverID])
GO
ALTER TABLE [dbo].[OSPrivilegeAudited] CHECK CONSTRAINT [FK_OSPrivilege_ServerAudited]
GO
ALTER TABLE [dbo].[PrinterAudited]  WITH CHECK ADD  CONSTRAINT [FK_PrinterAudited_ServerAudited] FOREIGN KEY([serverID])
REFERENCES [dbo].[ServerAudited] ([serverID])
GO
ALTER TABLE [dbo].[PrinterAudited] CHECK CONSTRAINT [FK_PrinterAudited_ServerAudited]
GO
ALTER TABLE [dbo].[ProcessAudited]  WITH CHECK ADD  CONSTRAINT [FK_ProcessAudited_ServerAudited] FOREIGN KEY([serverID])
REFERENCES [dbo].[ServerAudited] ([serverID])
GO
ALTER TABLE [dbo].[ProcessAudited] CHECK CONSTRAINT [FK_ProcessAudited_ServerAudited]
GO
ALTER TABLE [dbo].[ProcessorAudited]  WITH CHECK ADD  CONSTRAINT [FK_ProcessorAudited_ServerAudited] FOREIGN KEY([serverID])
REFERENCES [dbo].[ServerAudited] ([serverID])
GO
ALTER TABLE [dbo].[ProcessorAudited] CHECK CONSTRAINT [FK_ProcessorAudited_ServerAudited]
GO
ALTER TABLE [dbo].[ScheduledTaskAudited]  WITH CHECK ADD  CONSTRAINT [FK_ScheduledTaskAudited_ServerAudited] FOREIGN KEY([serverID])
REFERENCES [dbo].[ServerAudited] ([serverID])
GO
ALTER TABLE [dbo].[ScheduledTaskAudited] CHECK CONSTRAINT [FK_ScheduledTaskAudited_ServerAudited]
GO
ALTER TABLE [dbo].[ServiceAudited]  WITH CHECK ADD  CONSTRAINT [FK_ServicesAudited_ServerAudited] FOREIGN KEY([serverID])
REFERENCES [dbo].[ServerAudited] ([serverID])
GO
ALTER TABLE [dbo].[ServiceAudited] CHECK CONSTRAINT [FK_ServicesAudited_ServerAudited]
GO
ALTER TABLE [dbo].[ShareAudited]  WITH CHECK ADD  CONSTRAINT [FK_ShareAudited_ServerAudited] FOREIGN KEY([serverID])
REFERENCES [dbo].[ServerAudited] ([serverID])
GO
ALTER TABLE [dbo].[ShareAudited] CHECK CONSTRAINT [FK_ShareAudited_ServerAudited]
GO
ALTER TABLE [dbo].[ShareRightsAudited]  WITH CHECK ADD  CONSTRAINT [FK_ShareRightsAudited_ShareAudited] FOREIGN KEY([shareAuditedID])
REFERENCES [dbo].[ShareAudited] ([shareAuditedID])
GO
ALTER TABLE [dbo].[ShareRightsAudited] CHECK CONSTRAINT [FK_ShareRightsAudited_ShareAudited]
GO
USE [master]
GO
ALTER DATABASE [PowerShellServerInventory] SET  READ_WRITE
GO

Connect to the database

To connect to the created database, we use the following method in the PowerShell script :

# ???????????????????????????????????????????????????????????????????????
# Function Name 'Connect-Database' - connect to a SQL database
# ________________________________________________________________________
Function Connect-Database($connString){
    $sqlConnection = new-object System.Data.SqlClient.SqlConnection
    $sqlConnection.ConnectionString = $connString
    return $sqlConnection
}


Insert record into the database

To insert record into the database, we use the following method in the PowerShell script :

# ????????????????????????????????????????????????????????????????????????
# Function Name 'Insert-IntoDatabase' - insert record in a SQL table
# ________________________________________________________________________
Function Insert-IntoDatabase($sqlCommand, $query){       
    $sqlCommand.CommandText = $query
    try{
        $sqlCommand.executenonquery() | Out-Null
    }
    catch {
        $_.Exception
    }
}


Select record from database

To select a record from the database, we use the following method in the PowerShell script :

# ????????????????????????????????????????????????????????????????????????
# Function Name 'Select-Record' - count number of record about a query
# ________________________________________________________________________
Function Select-FromDatabase($query) {
    $queryText = $query
    $sqlCommand = $sqlConnection.CreateCommand()
    $sqlCommand.CommandText = $QueryText
    $dataAdapter = new-object System.Data.SqlClient.SqlDataAdapter $sqlCommand
    $dataset = new-object System.Data.Dataset
    $dataAdapter.Fill($dataset) | Out-Null
    $record = ($dataset.Tables[0])
    return $record
}


Prepare your .csv file

ServerName

server1 

server2

server3

server4

...


Launch the script

To launch the PowerShell script, hit "F5" on your keyboard. You see an Open File Dialog box is displayed in which you can select the .csv file you created in the previous step


Follow execution of the script 

You can follow the execution of the script 


Script result 

The result of the script is a SQL Server database where the following information is collected

  1. General (domain, role in the domain, hardware manufacturer, type and model, CPU number, memory capacity, operating system and service pack level)   
  2. System (BIOS name, BIOS version, hardware serial number, time zone, WMI version, virtual memory file location, virtual memory current usage, virtual memory peak usage and virtual memory allocated)   
  3. Processor (processor(s), processor type, family, speed in MHz, cache size in GB and socket number)   
  4. Memory (bank number, label, capacity in GB, form and type)   
  5. Disk (disk type, letter, capacity in GB, free space in GB)   
  6. Network (network card, DHCP enable or not, IP address, subnet mask, default gateway, DNS servers, Dns registered or not, primary and secondary wins and wins lookup or not)    
  7. Installed Programs (display name, version, install location and publisher)    
  8. Share swith NTFS rights (share name, user account, rights, ace flags and ace type)    
  9. Services (display name, name, start by, start mode and path name)   
  10. Scheduled Tasks (name, last run time, next run time and run as)   
  11. Printers (location, name, printer state and status, share name and system name)   
  12. Process (name, path and sessionID)   
  13. Local Users (groups, users)   
  14. ODBC Configured (dsn, server, port, databaseFile, databaseName, UID, PWD, start, lastUser, database, defaultLibraries, defaultPackage, defaultPkgLibrary, system, driver, description)   
  15. ODBC Drivers Installed (driver, driverODBCVer, fileExtns, setup)   
  16. Operating System Privileges (strategy, securityParameters)      

Database : 


Raw script

<#
#requires -version 4
 
.SYNOPSIS        
    Name : Server Inventory (Get-ServerInventorySQL.ps1)
    Description : Get informations from remote servers with WMI and ouput in an SQL Database
  
    Author : Pierre-Alexandre Braeken
     
    * Select list of servers from a CSV file with an OpenFileDialog
    * Get remotely Servers informations with WMI and Powershell :
    * General (Domain, role in the domain, hardware manufacturer, type and model, cpu number, memory capacity, operating system and sp level)
    * System (BIOS name, BIOS version, hardware serial number, time zone, WMI version, virtual memory file location, virtual memory current usage, virtual memory peak usage and virtual memory allocated)
    * Processor (Processor(s), processor type, family, speed in Mhz, cache size in GB and socket number)
    * Memory (Bank number, label, capacity in GB, form and type)
    * Disk (Disk type, letter, capacity in GB, free space in GB + display a chart Excel)
    * Network (Network card, DHCP enable or not, Ip address, subnet mask, default gateway, Dns servers, Dns registered or not, primary and secondary wins and wins lookup or not)
    * Installed Programs (Display name, version, install location and publisher)
    * Share swith NTFS rights (Share name, user account, rights, ace flags and ace type)
    * Services (Display name, name, start by, start mode and path name)
    * Scheduled Tasks (Name, last run time, next run time and run as)
    * Printers (Locationm, name, printer state and status, share name and system name)
    * Process (Name, Path and sessionID)
    * Local Users (Groups, users)
    * ODBC Configured (dsn, Server, Port, DatabaseFile, DatabaseName, UID, PWD, Start, LastUser, Database, DefaultLibraries, DefaultPackage, DefaultPkgLibrary, System, Driver, Description)
    * ODBC Drivers Installed (Driver, DriverODBCVer, FileExtns, Setup)
    * Operating System Privileges (Strategy, SecurityParameters)  
    * MB to GB conversion
    * Display of the progress of the script
 
.INPUT
    .csv file with servers to activate
 
.OUTPUTS
    Console outputs : server ok or not ok
    Log file
 
.NOTES
    Version:        1.0
    Author:         Pierre-Alexandre Braeken
    Creation Date:  2015-05-12
    Purpose/Change: Initial script development
   
.EXAMPLE
    .\Get-ServerInventorySQL.ps1
#>
 
#---------------------------------------------------------[Initialisations]--------------------------------------------------------
 
#Set Error Action to Silently Continue
$ErrorActionPreference = "SilentlyContinue"
 
$scriptPath = split-path -parent $myInvocation.MyCommand.Definition
$loggingFunctions = "$scriptPath\logging\Logging_Functions.ps1"
. $loggingFunctions
 
$tc = [System.Management.ManagementDateTimeconverter]
 
$start =$tc::ToDmtfDateTime((Get-Date).AddDays(-1).Date)
 
#----------------------------------------------------------[Declarations]----------------------------------------------------------
 
#Script version will be write in the log file
$sScriptName = "Get-ServerInventorySQL"
$sScriptVersion = "1.0"
 
#Log File Info
$scriptPath = split-path -parent $MyInvocation.MyCommand.Definition
 
$launchDate = get-date -f "yyyyMMdd"
$sLogPath = $scriptPath + "\" + $launchDate
$logDate = get-date -f "yyyyMMddHHmm"
 
if(!(Test-Path $sLogPath)) {
    New-Item $sLogPath -type directory
}
 
$sLogName = $sScriptName + $logDate + ".log"
$sLogFile = Join-Path -Path $sLogPath -ChildPath $sLogName
 
$returnValue = ""
 
#-----------------------------------------------------------[Functions]------------------------------------------------------------
 
# ????????????????????????????????????????????????????????????????????????
# Function Name 'Connect-Database' - connect to a SQL database
# ________________________________________________________________________
Function Connect-Database($connString){
    $sqlConnection = new-object System.Data.SqlClient.SqlConnection
    $sqlConnection.ConnectionString = $connString
    return $sqlConnection
}
 
# ????????????????????????????????????????????????????????????????????????
# Function Name 'Count-Record' - count number of record about a query
# ________________________________________________________________________
Function Count-Record($query) {
    $queryText = $query
    $sqlCommand = $sqlConnection.CreateCommand()
    $sqlCommand.CommandText = $QueryText
    $dataAdapter = new-object System.Data.SqlClient.SqlDataAdapter $sqlCommand
    $dataset = new-object System.Data.Dataset
    $dataAdapter.Fill($dataset) | Out-Null
    $nbRecord = ($dataset.Tables[0].recordCount)
    return $nbRecord
}
 
# ????????????????????????????????????????????????????????????????????????
# Function Name 'Select-Record' - count number of record about a query
# ________________________________________________________________________
Function Select-FromDatabase($query) {
    $queryText = $query
    $sqlCommand = $sqlConnection.CreateCommand()
    $sqlCommand.CommandText = $QueryText
    $dataAdapter = new-object System.Data.SqlClient.SqlDataAdapter $sqlCommand
    $dataset = new-object System.Data.Dataset
    $dataAdapter.Fill($dataset) | Out-Null
    $record = ($dataset.Tables[0])
    return $record
}
 
# ????????????????????????????????????????????????????????????????????????
# Function Name 'Insert-IntoDatabase' - insert record in a SQL table
# ________________________________________________________________________
Function Insert-IntoDatabase($sqlCommand, $query){       
    $sqlCommand.CommandText = $query
    try{
        $sqlCommand.executenonquery() | Out-Null
    }
    catch {
        $_.Exception
    }
}
 
# ????????????????????????????????????????????????????????????????????????
# Function Name 'Clean-CharacterChain' remove spaces, quotes and accents
# from a string
# ________________________________________________________________________
Function Clean-CharacterChain {
param ([String]$src = [String]::Empty)
    $normalized = $src.Normalize( [Text.NormalizationForm]::FormD )
    $normalized = $normalized.replace(" ","")   
    $normalized = $normalized.replace("'","")   
    $sb = new-object Text.StringBuilder
    $normalized.ToCharArray() | % {
        if( [Globalization.CharUnicodeInfo]::GetUnicodeCategory($_) -ne [Globalization.UnicodeCategory]::NonSpacingMark) {
            [void]$sb.Append($_)
        }
    }
 
    return $sb.ToString()       
}
 
# ????????????????????????????????????????????????????????????????????????
# Function Name 'Read-OpenFileDialog' - Open an open File Dialog box
# ________________________________________________________________________
Function Read-OpenFileDialog([string]$InitialDirectory, [switch]$AllowMultiSelect) {     
    [System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms") | Out-Null
    $openFileDialog = New-Object System.Windows.Forms.OpenFileDialog       
    $openFileDialog.ShowHelp = $True    # http://www.sapien.com/blog/2009/02/26/primalforms-file-dialog-hangs-on-windows-vista-sp1-with-net-30-35/
    $openFileDialog.initialDirectory = $initialDirectory
    $openFileDialog.filter = "csv files (*.csv)|*.csv|All files (*.*)| *.*"
    $openFileDialog.FilterIndex = 1
    $openFileDialog.ShowDialog() | Out-Null
    return $openFileDialog.filename
}
# ????????????????????????????????????????????????????????????????????????
# Function Name 'Translate-AccessMask' - Translate integer value in string
# ________________________________________________________________________
Function Translate-AccessMask($val) {
    Switch ($val)
    {
        2032127 {"FullControl"; break}
        1179785 {"Read"; break}
        1180063 {"Read, Write"; break}
        1179817 {"ReadAndExecute"; break}
        -1610612736 {"ReadAndExecuteExtended"; break}
        1245631 {"ReadAndExecute, Modify, Write"; break}
        1180095 {"ReadAndExecute, Write"; break}
        268435456 {"FullControl (Sub Only)"; break}
        default {$AccessMask = $val; break}
    }
}
# ??????????????????????????????????????????? ????????????????????????????
# Function Name 'Translate-AceType' - Translate integer value in string
# ________________________________________________________________________
Function Translate-AceType($val) {
    Switch ($val)
    {
        0 {"Allow"; break}
        1 {"Deny"; break}
        2 {"Audit"; break}
    }
}
# ????????????????????????????????????????????????????????????????????????
# Function Name 'Translate-AceFlagse' - Translate integer value in string
# ________________________________________________________________________
<#  OBJECT_INHERIT_ACE
    1 (0x1)
    Noncontainer child objects inherit the ACE as an effective ACE.
    For child objects that are containers, the ACE is inherited as an inherit-only ACE unless the NO_PROPAGATE_INHERIT_ACE bit flag is also set.
    CONTAINER_INHERIT_ACE
    2 (0x2)
    Child objects that are containers, such as directories, inherit the ACE as an effective ACE. The inherited ACE is inheritable unless the NO_PROPAGATE_INHERIT_ACE bit flag is also set.
    NO_PROPAGATE_INHERIT_ACE
    4 (0x4)
    If the ACE is inherited by a child object, the system clears the OBJECT_INHERIT_ACE and CONTAINER_INHERIT_ACE flags in the inherited ACE. This prevents the ACE from being inherited by subsequent generations of objects.
    INHERIT_ONLY_ACE
    8 (0x8)
    Indicates an inherit-only ACE which does not control access to the object to which it is attached. If this flag is not set, the ACE is an effective ACE which controls access to the object to which it is attached.
    Both effective and inherit-only ACEs can be inherited depending on the state of the other inheritance flags.
    INHERITED_ACE
    16 (0x10)
    The system sets this bit when it propagates an inherited ACE to a child object.
    Access these the same way. You can break them out using the bitwise AND operator or just test for the totals #>
Function Translate-AceFlags($val) {
    Switch ($val)
    {
        0 {"0"}
        1 {"Noncontainer child objects inherit"; break}
        2 {"Containers will inherit and pass on"; break}
        3 {"Containers AND Non-containers will inherit and pass on"; break}      
    }
}
# ????????????????????????????????????????????????????????????????????????
# Function Name 'Get-NtfsRights' - Enumerates NTFS rights of a folder
# ________________________________________________________________________
Function Get-NtfsRights($name,$path,$comp) {
    $path = [regex]::Escape($path)
    $share = "\\$comp\\$name"
    $wmi = gwmi Win32_LogicalFileSecuritySetting -filter "path='$path'" -ComputerName $comp
    $wmi.GetSecurityDescriptor().Descriptor.DACL | where {$_.AccessMask -as [Security.AccessControl.FileSystemRights]} |select `
                @{name="ShareName";Expression={$share}},
                @{name="Principal";Expression={"{0}\{1}" -f $_.Trustee.Domain,$_.Trustee.name}},
                @{name="Rights";Expression={Translate-AccessMask $_.AccessMask }},
                @{name="AceFlags";Expression={Translate-AceFlags $_.AceFlags }},
                @{name="AceType";Expression={Translate-AceType $_.AceType }}
                 
}
# ????????????????????????????????????????????????????????????????????????
# Function Name 'listProgramsInstalled' - get info in registry
# ________________________________________________________________________
Function listProgramsInstalled ($uninstallKey) {
    $array = @()
 
    $computername = $strComputer          
    $remoteBaseKeyObject = [microsoft.win32.registrykey]::OpenRemoteBaseKey('LocalMachine',$computername)    
    if($remoteBaseKeyObject) {
        $remoteBaseKey = $remoteBaseKeyObject.OpenSubKey($uninstallKey)            
        if($remoteBaseKey) {
            $subKeys = $remoteBaseKey.GetSubKeyNames()           
            foreach($key in $subKeys){           
                $thisKey=$UninstallKey+"\\"+$key         
                $thisSubKey=$remoteBaseKeyObject.OpenSubKey($thisKey)
                $psObject = New-Object PSObject       
                $psObject | Add-Member -MemberType NoteProperty -Name "DisplayName" -Value $($thisSubKey.GetValue("DisplayName"))
                $psObject | Add-Member -MemberType NoteProperty -Name "DisplayVersion" -Value $($thisSubKey.GetValue("DisplayVersion"))
                $psObject | Add-Member -MemberType NoteProperty -Name "InstallLocation" -Value $($thisSubKey.GetValue("InstallLocation"))
                $psObject | Add-Member -MemberType NoteProperty -Name "Publisher" -Value $($thisSubKey.GetValue("Publisher"))
                $array += $psObject
            }   
        }    
    
    $array
}
# ????????????????????????????????????????????????????????????????????????
# Function Name 'getTasks' - get scheduled tasks on remote server
# ________________________________________________________________________
Function getTasks($path) {
    $out = @()
    # Get root tasks
    $schedule.GetFolder($path).GetTasks(0) | % {
        $xml = [xml]$_.xml
        $out += New-Object psobject -Property @{
            "Name" = $_.Name
            "Path" = $_.Path
            "LastRunTime" = $_.LastRunTime
            "NextRunTime" = $_.NextRunTime
            "Actions" = ($xml.Task.Actions.Exec | % { "$($_.Command) $($_.Arguments)" }) -join "`n"
            "RunAs" = ($xml.Task.Principals.principal.userID)
        }
    }
    # Get tasks from subfolders
    $schedule.GetFolder($path).GetFolders(0) | % {
        $out += getTasks($_.Path)
    }   
    $out
}
# ????????????????????????????????????????????????????????????????????????
# Function Name 'getLocalUsersInGroup' - get local users in groups
# ________________________________________________________________________
function getLocalUsersInGroup {
    if($saveIntDomainRole -le 3) {
        $serverADSIObject = [ADSI]"WinNT://$strComputer,computer"
        $localUserinGroups=@()
        $serverADSIObject.psbase.children | Where { $_.psbase.schemaClassName -eq 'group' } |`
            foreach {
                $group =[ADSI]$_.psbase.Path
                $group.psbase.Invoke("Members") | `
                foreach {$localUserinGroups += New-Object psobject -property @{Group = $group.Name;User=(($_.GetType().InvokeMember("Adspath", 'GetProperty', $null, $_, $null)) -replace "WinNT://","")}}
            }
    }
    else {
        $localUserinGroups = @()
    }
    $localUserinGroups
}
# ????????????????????????????????????????????????????????????????????????
# Function Name 'listODBCConfigured' - get ODBC connections configured
# ________________________________________________________________________
Function listODBCConfigured ($odbcConfigured) {
    $computername = $strComputer
    $arrayConfigured = @()          
    $remoteBaseKeyObject = [microsoft.win32.registrykey]::OpenRemoteBaseKey('LocalMachine',$computername)    
    $remoteBaseKey = $remoteBaseKeyObject.OpenSubKey($odbcConfigured)            
    $subKeys = $remoteBaseKey.GetSubKeyNames()           
    foreach($key in $subKeys){           
        $thisKey=$odbcConfigured+"\\"+$key         
        $thisSubKey=$remoteBaseKeyObject.OpenSubKey($thisKey)        
        $psObjectConfigured = New-Object PSObject
        $psObjectConfigured | Add-Member -MemberType NoteProperty -Name "ComputerName" -Value $computername
        $psObjectConfigured | Add-Member -MemberType NoteProperty -Name "DSN" -Value $($thisSubKey.GetValue("dsn"))
        $psObjectConfigured | Add-Member -MemberType NoteProperty -Name "Server" -Value $($thisSubKey.GetValue("Server"))
        $psObjectConfigured | Add-Member -MemberType NoteProperty -Name "Port" -Value $($thisSubKey.GetValue("Port"))
        $psObjectConfigured | Add-Member -MemberType NoteProperty -Name "DatabaseFile" -Value $($thisSubKey.GetValue("DatabaseFile"))
        $psObjectConfigured | Add-Member -MemberType NoteProperty -Name "DatabaseName" -Value $($thisSubKey.GetValue("DatabaseName"))
        $psObjectConfigured | Add-Member -MemberType NoteProperty -Name "UID" -Value $($thisSubKey.GetValue("UID"))
        $psObjectConfigured | Add-Member -MemberType NoteProperty -Name "PWD" -Value $($thisSubKey.GetValue("PWD"))
        $psObjectConfigured | Add-Member -MemberType NoteProperty -Name "Start" -Value $($thisSubKey.GetValue("Start"))
        $psObjectConfigured | Add-Member -MemberType NoteProperty -Name "LastUser" -Value $($thisSubKey.GetValue("LastUser"))
        $psObjectConfigured | Add-Member -MemberType NoteProperty -Name "Database" -Value $($thisSubKey.GetValue("Database"))
        $psObjectConfigured | Add-Member -MemberType NoteProperty -Name "DefaultLibraries" -Value $($thisSubKey.GetValue("DefaultLibraries"))
        $psObjectConfigured | Add-Member -MemberType NoteProperty -Name "DefaultPackage" -Value $($thisSubKey.GetValue("DefaultPackage"))
        $psObjectConfigured | Add-Member -MemberType NoteProperty -Name "DefaultPkgLibrary" -Value $($thisSubKey.GetValue("DefaultPkgLibrary"))
        $psObjectConfigured | Add-Member -MemberType NoteProperty -Name "System" -Value $($thisSubKey.GetValue("System"))
        $psObjectConfigured | Add-Member -MemberType NoteProperty -Name "Driver" -Value $($thisSubKey.GetValue("Driver"))
        $psObjectConfigured | Add-Member -MemberType NoteProperty -Name "Description" -Value $($thisSubKey.GetValue("Description"))
        $arrayConfigured += $psObjectConfigured
    }          
    $arrayConfigured   
}
# ????????????????????????????????????????????????????????????????????????
# Function Name 'listODBCInstalled' - get ODBC connections installed
# ________________________________________________________________________
Function listODBCInstalled ($odbcDriversInstalled) {
    $computername = $strComputer
    $arrayInstalled = @()      
    $remoteBaseKeyObject = [microsoft.win32.registrykey]::OpenRemoteBaseKey('LocalMachine',$computername)    
    $remoteBaseKey = $remoteBaseKeyObject.OpenSubKey($odbcDriversInstalled)            
    $subKeys = $remoteBaseKey.GetSubKeyNames()           
    foreach($key in $subKeys){           
        $thisKey=$odbcDriversInstalled+"\\"+$key         
        $thisSubKey=$remoteBaseKeyObject.OpenSubKey($thisKey)        
        $psObjectInstalled = New-Object PSObject
        $psObjectInstalled | Add-Member -MemberType NoteProperty -Name "ComputerName" -Value $computername
        $psObjectInstalled | Add-Member -MemberType NoteProperty -Name "Driver" -Value $($thisSubKey.GetValue("Driver"))
        $psObjectInstalled | Add-Member -MemberType NoteProperty -Name "DriverODBCVer" -Value $($thisSubKey.GetValue("DriverODBCVer"))
        $psObjectInstalled | Add-Member -MemberType NoteProperty -Name "FileExtns" -Value $($thisSubKey.GetValue("FileExtns"))
        $psObjectInstalled | Add-Member -MemberType NoteProperty -Name "Setup" -Value $($thisSubKey.GetValue("Setup"))
        $arrayInstalled += $psObjectInstalled
    }          
    $arrayInstalled   
}
 
# ????????????????????????????????????????????????????????????????????????
# Function Name 'ListFile' - get server based on a CSV file
# ________________________________________________________________________
Function ListFile {
    $fileOpen = Read-OpenFileDialog
    if($fileOpen -ne '') { 
        $colComputers = Import-Csv $fileOpen
    }
    $colComputers
}
# Run-WmiRemoteProcess
Function Run-WmiRemoteProcess
{
    Param(
        [string]$computername=$env:COMPUTERNAME,
        [string]$cmd=$(Throw "You must enter the full path to the command which will create the process."),
        [int]$timeout = 0
    )
  
    Write-Host "Process to create on $computername is $cmd"
    [wmiclass]$wmi="\\$computername\root\cimv2:win32_process"
    # Exit if the object didn't get created
    if (!$wmi) {return}
  
    try{
    $remote=$wmi.Create($cmd)
    }
    catch{
        $_.Exception
    }
    $test =$remote.returnvalue
    if ($remote.returnvalue -eq 0) {
        Write-Host ("Successfully launched $cmd on $computername with a process id of " + $remote.processid)
    } else {
        Write-Host ("Failed to launch $cmd on $computername. ReturnValue is " + $remote.ReturnValue)
    }
  
    # Wait for the process to complete or to reach timeout
    $processId = $remote.processid
    $processActive = 1
    while ( $processActive -ge 1) {
        $process = Get-Wmiobject -class win32_process `
        -namespace "rootcimv2" -computerName $computername -Filter "ProcessId = $processId"
        if ($process -ne $null) {
            if ($processActive -ge $timeout -and $timeout -ne 0){
                Write-Host "Remote process execution is taking too long and timed out"
                return
            }
            $processActive++
            Start-Sleep -Seconds 1
        } else {
            Write-Host "Remote process finished"
            $processActive = 0
        }
    }
}
 
function Get-NameFromSid
{
    Param (
        [String]$currentSid
    )
  
    $objSID = $null
    $objUser = $null
  
    try {
        $sid = $currentSid.Replace("`*","")
        $objSID = New-Object System.Security.Principal.SecurityIdentifier ($sid)
        $objUser = $objSID.Translate( [System.Security.Principal.NTAccount])
        Write-Host "SID $sid translated to $objUser.Value"
        return $objUser.Value
    } catch {
        Write-Host "SID $sid could not be translated"
        return $currentSid
    }
}
  
# to find the index of an element in an array
function Get-IndexOf {
    Param (
        [object[]]$array, $element
    )
  
    $line = 0..($array.length - 1) | where {$array[$_] -eq $element}
    return $line
}
  
# Parse the text file from the secdump and outputs an array of policies
function Parse-SecdumpFileToObject {
    Param (
        [String]$file
    )
  
    # The array that will be returned
    $policies = @()
  
    # put the text file to an array
    $fileContent = Get-Content $file
  
    # Find the delimitations of the security policies
    $start = IndexOf $fileContent "[Privilege Rights]"
    $end = IndexOf $fileContent "[Version]"
  
    # Extract the security policies between those delimitations
    For ($i = $start+1; $i -lt $end; $i++) {
        $policy = New-Object Object
        $line = $fileContent[$i].split(" =")
  
        # Add policy name to the policy
        Add-Member -memberType NoteProperty -name name -value $line[0] -inputObject $policy
        # Extract array of members, translate the SIDs, and add the members array to the policy
        $members = $line[3].split(",")
        For ($j = 0; $j -lt $members.Count; $j++) {
            if ($members[$j] -like "``**") {
                $members[$j] = Get-NameFromSid $members[$j]
            }
        }
        Add-Member -memberType NoteProperty -name members -value $members -inputObject $policy
  
        # Add the policy to the "policies" array
        $policies += $policy
    }
    return $policies
}
#-----------------------------------------------------------[Execution]------------------------------------------------------------
 
Log-Start -LogPath $sLogPath -LogName $sLogName -ScriptName $sScriptName -ScriptVersion $sScriptVersion
 
# open database connection
 
$connString = "Data Source=Q19466\SQLEXPRESS; Initial Catalog=PowerShellServerInventory; Integrated Security=True"
$sqlConnection = Connect-Database $connString
Log-Write -LogPath $sLogFile -LineValue "Database connection to $connString"
$sqlConnection.Open()
$sqlCommand = $sqlConnection.CreateCommand()
Log-Write -LogPath $sLogFile -LineValue "SqlCommand objec created"
$colComputers = ListFile   
$computerCount = $colComputers.Count
Log-Write -LogPath $sLogFile -LineValue "List of $computerCount computers to query collected" #`n"
 
$serversNotResponding = ""
$nbError = 0
$nbSuccess = 0
$nbTot = $computerCount
 
foreach ($strComputer in $colComputers){   
    $items = ""
    $queryText = "Select count(*) as recordCount FROM ServerAudited"
    $nbServer = Count-Record($queryText)
    $strComputer = $strComputer.ServerName
    $serverName = $strComputer
    Write-Progress -Activity "Getting general information ($strComputer)" -status "Running..." -id 1                
    $items = gwmi Win32_ComputerSystem -Comp $strComputer | Select-Object Domain, DomainRole, Manufacturer, Model, SystemType, NumberOfProcessors, TotalPhysicalMemory
    if($items) {
        $domain = $items.Domain
        $domainRole = $items.DomainRole
        $manufacturer = $items.Manufacturer
        $model = $items.Model
        $systemType = $items.SystemType
        $numberOfProcessors = $items.NumberOfProcessors
        $totalPhysicalMemory = [math]::round(($items.TotalPhysicalMemory)/1024/1024/1024, 0)   
        Write-Progress -Activity "Getting systems information ($strComputer)" -status "Running..." -id 1
        $items = ""
        $items = gwmi Win32_OperatingSystem -Comp $strComputer | Select-Object Caption, csdversion  
        $operatingSystem = $items.Caption
        $servicePackLevel = $items.csdversion
        $items = ""
        $items = gwmi Win32_BIOS -Comp $strComputer | Select-Object Name, SMBIOSbiosVersion, SerialNumber
        $biosName = $items.Name
        $biosVersion = $items.SMBIOSbiosVersion
        $hardwareSerial = $items.SerialNumber
        $items = ""
        $items = gwmi Win32_TimeZone -Comp $strComputer | Select-Object Caption
        $timeZone = $items.Caption
        $items = ""
        $items = gwmi Win32_WmiSetting -Comp $strComputer | Select-Object BuildVersion   
        $wmiVersion = $items.BuildVersion                    
        $items = ""
        $items = gwmi Win32_PageFileUsage -Comp $strComputer | Select-Object Name, CurrentUsage, PeakUsage, AllocatedBaseSize   
        $virtualMemoryName = $items.Name
        $virtualMemoryCurrentUsage = $items.CurrentUsage
        $virtualMermoryPeakUsage = $items.PeakUsage
        $virtualMemoryAllocatedBaseSize = $items.AllocatedBaseSize
 
        $saveIntDomainRole = $domainRole
 
        Switch($domainRole) {
            0{$domainRole = "Stand Alone Workstation"}
            1{$domainRole = "Member Workstation"}
            2{$domainRole = "Stand Alone Server"}
            3{$domainRole = "Member Server"}
            4{$domainRole = "Back-up Domain Controller"}
            5{$domainRole = "Primary Domain Controller"}
            default{"Undetermined"}
        }
 
        $serverQueryInsert = "INSERT INTO ServerAudited (serverID,serverName,domain,role,HW_Make,HW_Model,HW_Type,cpuCount,memoryGB,operatingSystem,servicePackLevel,
                    biosName,biosVersion,hardwareSerial,timeZone,wmiVersion,virtualMemoryName,virtualMemoryCurrentUsage,virtualMermoryPeakUsage,
                    virtualMemoryAllocatedBaseSize) VALUES('$nbServer','$serverName','$domain','$domainRole','$manufacturer','$model','$systemType','$numberOfProcessors',
                    '$totalPhysicalMemory','$operatingSystem','$servicePackLevel','$biosName','$biosVersion','$hardwareSerial','$timeZone','$wmiVersion','$virtualMemoryName',
                    '$virtualMemoryCurrentUsage','$virtualMermoryPeakUsage','$virtualMemoryAllocatedBaseSize')"
        Write-Progress -Activity "Inserting server information ($strComputer)" -status "Running..." -id 1 
        Insert-IntoDatabase $sqlCommand $serverQueryInsert
        Log-Write -LogPath $sLogFile -LineValue "$serverQueryInsert"
 
        Write-Progress -Activity "Getting processor information ($strComputer)" -status "Running..." -id 1    
        $items = ""
        $items = gwmi Win32_Processor -Comp $strComputer | Select-Object DeviceID, Name, Description, family, currentClockSpeed, l2cacheSize, UpgradeMethod, SocketDesignation
        Write-Progress -Activity "Inserting processor information ($strComputer)" -status "Running..." -id 1 
        foreach($item in $items) {
            $deviceLocator = $item.DeviceID
            $processorName = $item.Name
            $processorDescription = $item.Description
            $processorFamily = $item.family
            $currentClockSpeed = $item.currentClockSpeed
            $l2cacheSize = $item.l2cacheSize
            $upgradeMethod = $item.UpgradeMethod
            $socketDesignation = $item.SocketDesignation
            $processorQueryInsert =  "INSERT INTO ProcessorAudited (serverID,Name,TypeP,Family,Speed,CacheSize,Interface,SocketNumber) VALUES
                                ('$nbServer','$deviceLocator','$processorName','$processorFamily','$currentClockSpeed','$l2cacheSize','$upgradeMethod','$socketDesignation')"           
            Insert-IntoDatabase $sqlCommand $processorQueryInsert
            Log-Write -LogPath $sLogFile -LineValue "$processorQueryInsert"           
        }
 
 
        Write-Progress -Activity "Getting memory information ($strComputer)" -status "Running..." -id 1
        $items = ""
        $items = gwmi Win32_PhysicalMemory -Comp $strComputer | Select-Object DeviceLocator, Capacity, FormFactor, TypeDetail
        Write-Progress -Activity "Inserting memory information ($strComputer)" -status "Running..." -id 1
        foreach($item in $items) {
            $deviceLocator = $item.DeviceLocator
            $capacity = [math]::round(($item.Capacity)/1024/1024/1024, 0)
            $formFactor = $item.FormFactor
            $typeDetail = $item.TypeDetail
            $memoryQueryInsert = "INSERT INTO MemoryAudited (serverID,Label,Capacity,Form,TypeM) VALUES ('$nbServer','$deviceLocator','$capacity','$formFactor','$typeDetail')"
            Insert-IntoDatabase $sqlCommand $memoryQueryInsert
            Log-Write -LogPath $sLogFile -LineValue "$memoryQueryInsert"
        }
 
        Write-Progress -Activity "Getting disks information ($strComputer)" -status "Running..." -id 1     
        $items = ""      
        $items = gwmi Win32_LogicalDisk -Comp $strComputer | Select-Object DriveType, DeviceID, Size, FreeSpace
        Write-Progress -Activity "Inserting disk information ($strComputer)" -status "Running..." -id 1 
        foreach($item in $items) {
            $driveType = $item.DriveType
            $deviceID = $item.DeviceID
            $size = [math]::round(($item.Size)/1024/1024/1024, 0)  
            $freeSpace = [math]::round(($item.FreeSpace)/1024/1024/1024, 0)   
     
            Switch($driveType) {
                2{$driveType = "Floppy"}
                3{$driveType = "Fixed Disk"}
                5{$driveType = "Removable Media"}
                default{"Undetermined"}
            }
     
            $diskQueryInsert = "INSERT INTO DriveAudited (serverID,diskType,driveLetter,capacity,freeSpace) VALUES ('$nbServer','$driveType','$deviceID','$size','$freeSpace')"
            Insert-IntoDatabase $sqlCommand $diskQueryInsert
            Log-Write -LogPath $sLogFile -LineValue "$diskQueryInsert"
        }
 
        Write-Progress -Activity "Getting network information ($strComputer)" -status "Running..." -id 1
        $items = ""
        $items = gwmi Win32_NetworkAdapterConfiguration -Comp $strComputer | Where{$_.IPEnabled -eq "True"} | Select-Object Caption, DHCPEnabled, IPAddress, IPSubnet, DefaultIPGateway, DNSServerSearchOrder, FullDNSRegistrationEnabled, WINSPrimaryServer, WINSSecondaryServer, WINSEnableLMHostsLookup
        Write-Progress -Activity "Inserting network information ($strComputer)" -status "Running..." -id 1 
        foreach($item in $items) {
            $caption = $item.Caption
            $dhcpEnabled = $item.DHCPEnabled
            $ipAddress = $item.IPAddress
            $ipSubnet = $item.IPSubnet
            $defaultIPGateway = $item.DefaultIPGateway
            $dnsServerSearchOrder = $item.DNSServerSearchOrder
            $fullDNSRegistrationEnabled = $item.FullDNSRegistrationEnabled
            $winsPrimaryServer = $item.WINSPrimaryServer
            $winsSecondaryServer = $item.WINSSecondaryServer
            $winsEnableLMHostsLookup = $item.WINSEnableLMHostsLookup
            $networkQueryInsert = "INSERT INTO NetworkAudited (serverID,networkCard,dhcpEnabled,ipAddress,subnetMask,defaultGateway,dnsServers,dnsReg,primaryWins,secondaryWins,winsLookup)
            VALUES ('$nbServer','$caption','$dhcpEnabled','$ipAddress','$ipSubnet','$defaultIPGateway','$dnsServerSearchOrder','$fullDNSRegistrationEnabled',
            '$winsPrimaryServer','$winsSecondaryServer','$winsEnableLMHostsLookup')"
            Insert-IntoDatabase $sqlCommand $networkQueryInsert   
            Log-Write -LogPath $sLogFile -LineValue "$networkQueryInsert"
        }
 
        Write-Progress -Activity "Getting programs installed information ($strComputer)" -status "Running..." -id 1      
        # Populate Installed Programs          
        $arrayprogramsInstalled = listProgramsInstalled "SOFTWARE\\Microsoft\\Windows\\CurrentVersion\\Uninstall"       
        $arrayprogramsInstalled2 = listProgramsInstalled "SOFTWARE\\Wow6432Node\\Microsoft\\Windows\\CurrentVersion\\Uninstall"     
        $items = ""     
        $items = $arrayprogramsInstalled + $arrayprogramsInstalled2     
        Write-Progress -Activity "Inserting installed programs information ($strComputer)" -status "Running..." -id 1 
        foreach($item in $items) {
            $displayName = $item.DisplayName
            $displayVersion = $item.DisplayVersion
            $installLocation = $item.InstallLocation
            $publisher = $item.Publisher   
            if(!([string]::IsNullOrEmpty($displayName))) {   
                $installedProgramQueryInsert = "INSERT INTO InstalledProgramAudited (serverID,displayName,displayVersion,installLocation,publisher) VALUES ('$nbServer','$displayName','$displayVersion','$installLocation','$publisher')"
                Insert-IntoDatabase $sqlCommand $installedProgramQueryInsert
                Log-Write -LogPath $sLogFile -LineValue "$installedProgramQueryInsert"
            }
        }
 
        # Populate Shares
        Write-Progress -Activity "Getting shares information ($strComputer)" -status "Running..." -id 1
        if ($shares = Get-WmiObject Win32_Share -ComputerName $strComputer) {       
            $items = @()
            $shares | Foreach {$items += Get-NtfsRights $_.Name $_.Path $_.__Server}
        }
        else {$shares = "Failed to get share information from {0}." -f $($_.ToUpper())}           
        Write-Progress -Activity "Inserting shares information  ($strComputer)" -status "Running..." -id 1
        $shareName = ""
        $shareNameSave = ""
        foreach ($item in $items) {
            $shareName = $item.ShareName
            if($shareName -ne $shareNameSave) {
                $sharesQueryInsert = "INSERT INTO ShareAudited (serverID,shareName) VALUES ('$nbServer','$shareName')"       
                Insert-IntoDatabase $sqlCommand $sharesQueryInsert
                $shareNameSave = $shareName
                Log-Write -LogPath $sLogFile -LineValue "$sharesQueryInsert"
            }
            $principal = $item.Principal
            $rights = $item.Rights
            $aceFlags = $item.AceFlags
            $aceType = $item.AceType
     
            $queryText = "Select shareAuditedID as shareAuditedID FROM ShareAudited WHERE shareName LIKE '$shareName'"
            $recordReturned = Select-FromDatabase($queryText)
            $shareAuditedID = $recordReturned.shareAuditedID
            $sharesRightsQueryInsert = "INSERT INTO ShareRightsAudited (shareAuditedID,account,rights,aceFlags,aceType) VALUES ('$shareAuditedID','$principal','$rights','$aceFlags','$aceType')"       
            Insert-IntoDatabase $sqlCommand $sharesRightsQueryInsert
            Log-Write -LogPath $sLogFile -LineValue "$sharesRightsQueryInsert"
        }
 
        # Populate Services  
        Write-Progress -Activity "Getting services information ($strComputer)" -status "Running..." -id 1  
        $items = ""
        $items = Get-WmiObject win32_service -Comp $strComputer | Select-Object DisplayName, Name, StartName, StartMode, PathName, Description                
        Write-Progress -Activity "Inserting services information ($strComputer)" -status "Running..." -id 1
        foreach ($item in $items) {
            $displayName = $item.DisplayName
            $name = $item.Name
            $startName = $item.StartName
            $startMode = $item.StartMode
            $pathName = $item.PathName
            $description = $item.Description
            $description = $description.replace("'","")   
 
            $servicesQueryInsert = "INSERT INTO ServiceAudited (serverID,displayName,name,startName,startMode,servicePathName,serviceDescription) VALUES ('$nbServer','$displayName','$name','$startName','$startMode','$pathName','$description')"   
            Insert-IntoDatabase $sqlCommand $servicesQueryInsert    
            Log-Write -LogPath $sLogFile -LineValue "$servicesQueryInsert"  
        }
 
        # Populate Scheduled Tasks      
        Write-Progress -Activity "Getting tasks information ($strComputer)" -status "Running..." -id 1    
        $items = @()       
        try { $schedule = new-object -comobject "Schedule.Service" ; $schedule.Connect($strComputer) }
        catch [System.Management.Automation.PSArgumentException] { throw $_ }         
        $items += getTasks("\")
        # Close com
        [System.Runtime.Interopservices.Marshal]::ReleaseComObject($schedule) | Out-Null
        Remove-Variable schedule       
        Write-Progress -Activity "Inserting Scheduled Tasks information ($strComputer)" -status "Running..." -id 1    
        foreach ($item in $items) {
            $name = $item.Name
            $name = $name.replace("'","")   
            $path = $item.Path
            $path = $path.replace("'","")   
            $lastRunTime = $item.LastRunTime
            $nextRunTime = $item.NextRunTime
            $actions = $item.Actions
            $runAs = $item.RunAs
 
            $scheduledTasksQueryInsert = "INSERT INTO ScheduledTaskAudited (serverID,name,pathName,lastRunTime,nextRunTime,scheduledAction,runAs) VALUES ('$nbServer','$name','$path','$lastRunTime','$nextRunTime','$actions','$runAs')"
            Insert-IntoDatabase $sqlCommand $scheduledTasksQueryInsert
            Log-Write -LogPath $sLogFile -LineValue "$scheduledTasksQueryInsert"
        }
 
        # Populate Printers    
        Write-Progress -Activity "Getting printers information ($strComputer)" -status "Running..." -id 1
        $items = ""
        $items = gwmi Win32_Printer -Comp $strComputer | Select-Object Location, Name, PrinterState, PrinterStatus, ShareName, SystemName          
        Write-Progress -Activity "Inserting Printers information ($strComputer)" -status "Running..." -id 1 
        foreach ($item in $items) { 
            $name = $item.Name
            $location = $item.Location
            $printerState = $item.PrinterState
            $printerStatus = $item.PrinterStatus
            $shareName = $item.ShareName
            $systemName = $item.SystemName
 
            $printerQueryInsert = "INSERT INTO PrinterAudited (serverID,name,location,printerState,printerStatus,shareName,systemName) VALUES ('$nbServer','$name','$location','$printerState','$printerStatus','$shareName','$systemName')"
            Insert-IntoDatabase $sqlCommand $printerQueryInsert   
            Log-Write -LogPath $sLogFile -LineValue "$printerQueryInsert"
        }                
        # Populate Process worksheet      
        Write-Progress -Activity "Getting process information ($strComputer)" -status "Running..." -id 1    
        $items = ""
        $items = gwmi win32_process -ComputerName $strComputer | select-object Name, Path, SessionId
        Write-Progress -Activity "Inserting Process information ($strComputer)" -status "Running..." -id 1 
        foreach ($item in $items) { 
            $name = $item.Name
            $location = $item.Location
            $sessionID = $item.sessionID
 
            $processQueryInsert = "INSERT INTO ProcessAudited (serverID,name,location,sessionID) VALUES ('$nbServer','$name','$location','$sessionID')"
            Insert-IntoDatabase $sqlCommand $processQueryInsert   
            Log-Write -LogPath $sLogFile -LineValue "$processQueryInsert"
        }   
 
        # Populate ODBC Configured
        Write-Progress -Activity "Getting ODBC connections Configured ($strComputer)" -status "Running..." -id 1  
        if($systemType -eq "x86-based PC") {
            $odbcConfigured = "SOFTWARE\\odbc\\odbc.ini"
            $odbcDriversInstalled = "SOFTWARE\\odbc\\odbcinst.ini"
        }
        else {
            $odbcConfigured = "SOFTWARE\\wow6432Node\\odbc\\odbc.ini"
            $odbcDriversInstalled = "SOFTWARE\\wow6432Node\\odbc\\odbcinst.ini"
        }    
        Write-Progress -Activity "Formating the output - ODBC connections Configured ($strComputer)" -status "Running..." -id 1
        $items = ""
        $items = listODBCConfigured $odbcConfigured       
        foreach ($item in $items) { 
            $dsn = $item.dsn
            $serverName = $item.serverName
            $port = $item.port
            $dataBaseFile = $item.dataBaseFile
            $dataBaseName = $item.dataBaseName
            $odbcUID = $item.odbcUID
            $odbcPWD = $item.odbcPWD
            $start = $item.start
            $lastUser = $item.lastUser
            $odbcDatabase = $item.odbcDatabase
            $defaultLibraries = $item.defaultLibraries
            $defaultPackage = $item.defaultPackage
            $defaultPkgLibrary = $item.defaultPkgLibrary
            $odbcSystem = $item.odbcSystem
            $driver = $item.driver
            $odbcDescription = $item.odbcDescription
 
            $odbcConfiguredQueryInsert = "INSERT INTO ODBCConfiguredAudited (serverID,dsn,serverName,port,dataBaseFile,dataBaseName,odbcUID,odbcPWD,start,lastUser,odbcDatabase,defaultLibraries,defaultPackage,defaultPkgLibrary,odbcSystem,driver,odbcDescription)
            VALUES ('$nbServer','$dsn','$serverName','$port','$dataBaseFile','$dataBaseName','$odbcUID','$odbcPWD','$start','$lastUser','$odbcDatabase','$defaultLibraries','$defaultPackage','$defaultPkgLibrary','$odbcSystem','$driver','$odbcDescription')"   
            Insert-IntoDatabase $sqlCommand $odbcConfiguredQueryInsert
            Log-Write -LogPath $sLogFile -LineValue "$odbcConfiguredQueryInsert"
        }                  
        # Populate ODBC Drivers Installed              
        Write-Progress -Activity "Getting ODBC Drivers Installed ($strComputer)" -status "Running..." -id 1
        $items = ""
        $items = listODBCInstalled $odbcDriversInstalled  
        Write-Progress -Activity "Formating the output - ODBC Drivers Installed ($strComputer)" -status "Running..." -id 1
        foreach ($item in $items) {     
            $driver = $item.Driver
            $driverODBCVer = $item.DriverODBCVer
            $fileExtns = $item.FileExtns
            $setup = $item.Setup
 
            $odbcInstalledQueryInsert = "INSERT INTO ODBCInstalledAudited (serverID,driver,driverODBCVer,fileExtns,setup) VALUES ('$nbServer','$driver','$driverODBCVer','$fileExtns','$setup')"
            Insert-IntoDatabase $sqlCommand $odbcInstalledQueryInsert   
            Log-Write -LogPath $sLogFile -LineValue "$odbcInstalledQueryInsert"  
        }  
 
        Write-Progress -Activity "Getting local users information ($strComputer)" -status "Running..." -id 1                
        $items = ""
        $items = getLocalUsersInGroup 
        Write-Progress -Activity "Inserting local users information ($strComputer)" -status "Running..." -id 1 
        foreach($item in $items) {
            $group = $item.Group
            $user = $item.User
            $localUsersQueryInsert = "INSERT INTO LocalGroupAudited (serverID,localGroup,userNested) VALUES ('$nbServer','$group','$user')"
            Insert-IntoDatabase $sqlCommand $localUsersQueryInsert
            Log-Write -LogPath $sLogFile -LineValue "$localUsersQueryInsert"
        }
 
        Write-Progress -Activity "Getting OS Privileges information ($strComputer)" -status "Running..." -id 1  
        Run-WmiRemoteProcess $computername 'secedit.exe /export /cfg c:\secdump.txt' | Wait-Process
        Start-Sleep -Seconds 3  # wait for file to be created
 
        [string]$strScriptPath = Split-Path $MyInvocation.MyCommand.Pathwhoami
        $file = ($strScriptPath + "secdump.txt")
        try {
 
        $fileTocopy = "\\$computername\c$\secdump.txt"
 
        Copy-Item $fileTocopy $file
        }
        catch{
            $_.Exception
        }
 
        $dumpResult = Parse-SecdumpFileToObject $file
        Start-Sleep -Seconds 1
 
        Remove-Item \\$computername\c$\secdump.txt
        Remove-Item $file
 
        # convert the dump to XML to a test file
        $XMLDump = $dumpResult | ConvertTo-XML -NoTypeInformation
        # Save Dump Data in the Output File
        $XMLDump.Save("secdump.xml")
 
        $xmlPath = "$scriptPath\logging\secdump.xml"
        $nodes = ""
        $nodes = Select-Xml -Path $xmlPath -XPath "//Property" | Select-Object -ExpandProperty Node
 
        $arrayPrivilege = @{}
 
        $nbNode = 0
        $nodes | ForEach-Object {
            $name = ""
            $name = $_.Name  
            if($name -eq "name") {
                $privilegeName = $_ | Select '#text'
                $privilegeName = $privilegeName.'#text'
            }
            if($name -eq "members") {
                $members = $_ | Select 'Property'       
                $members = $members.property
                $arrayPrivilege.Add($privilegeName, $members)
            }   
          
            $nbNode++
        }
         
        foreach($privilege in $arrayPrivilege.keys) {
            $strategy = $privilege
            $securityParameters = $arrayPrivilege.item($privilege)       
            $OSPrivilegeQueryInsert = "INSERT INTO OSPrivilegeAudited (serverID,strategy,securityParameter) VALUES ('$nbServer','$strategy','$securityParameters')"             
            Insert-IntoDatabase $sqlCommand $OSPrivilegeQueryInsert
            Log-Write -LogPath $sLogFile -LineValue "$localUsersQueryInsert"
        }
 
        $nbSuccess++          
    }
    else {
 
        Log-Write -LogPath $sLogFile -LineValue "WMI connection to $strComputer failed`n"
         
 
        $serversNotResponding += "$strComputer `r`n"
        $nbError++
    }
}
$sqlConnection.Close()
 
$printErrorEncountered = ""
 
if($nbError -gt 0) {
    $printErrorEncountered = "`r`n The script encountered $nbError error `r`n $serversNotResponding"      
}
 
Log-Write -LogPath $sLogFile -LineValue "`r`n****************************************************** $printErrorEncountered `r`n $nbSuccess / $nbTot server(s) answer WMI requests `r`n $nbError / $nbTot server(s) NOT answer WMI requests `r`n ****************************************************** `n "
 
Log-Write -LogPath $sLogFile -LineValue "Database connection closed"
 
 
 
Log-Finish -LogPath $sLogFile


See Also