Bookmark and Share

(Portions of this article previously appeared in the Microsoft Press book, Windows PowerShell Scripting Guide.)

Hey, Scripting Guy! Question

Hey, Scripting Guy! I am interested in collecting information about processes that are stopped on my systems. I would like to write the information to a Microsoft Access database using Windows PowerShell. Could you help me? If I have a database of information regarding stopped processes, I can write a report that will let me know when things are changing. I think this is important from a security perspective.

-- SW

Hey, Scripting Guy! Answer

Hello SW,

Microsoft Scripting Guy Ed Wilson here. My tea is cold. I hate it when my tea gets cold. I was so involved in tweeting, that I lost track of time. If you have been following the Scripting Guys on Twitter, you know there have been some interesting conversations going on. It is a great way to keep up to date with the changes going on in the world of Windows. There have been some cool questions from people trying to learn Windows PowerShell, as well as people who are still working with VBScript. I have been really happy with the bDule software that I have been using. The reason I like it is that it is based upon the .NET Framework and because Windows PowerShell uses the .NET Framework. I did not need to install any extra pieces of software other than the application itself.

Because my tea is cold, it means my tweeting has been interrupted. I think I will give some thought to your question while I go downstairs and boil some water for a fresh pot of Earl Grey tea.

In yesterday’s Hey Scripting Guy! Blog post, we looked at the process of developing a Windows PowerShell script that writes running services to a Microsoft Access database. After figuring out how to write running services to a Microsoft Access database, it is trivial to modify the script to store stopped service information. The process involves adding an additional table to the Access database, adding fields to the database, and creating an appropriate report. From the Windows PowerShell scripting side of the house, there is little to do other than make sure the fields line up with the database.

The bulk of the work has already been done in the WriteRunningServicesToAccess.ps1 script we developed yesterday. We can begin with it as the baseline and our template. The WriteRunningServicesToAccess.ps1 script is seen here.

WriteRunningServicesToAccess.ps1

$StrComputer = (New-Object -ComObject WScript.Network).computername
$StrDomain = (New-Object -ComObject WScript.Network).userDomain
$strWMIQuery = "Select * from win32_Service"
$objservice = get-wmiobject -query $strWMIQuery

$adOpenStatic = 3
$adLockOptimistic = 3
$strDB = "c:\fso\services.mdb"
$strTable = "runningServices"
$objConnection = New-Object -ComObject ADODB.Connection
$objRecordSet = new-object -ComObject ADODB.Recordset
$objConnection.Open("Provider = Microsoft.Jet.OLEDB.4.0; `
  Data Source= $strDB")
$objRecordSet.Open("SELECT * FROM runningServices", `
  $objConnection, $adOpenStatic, $adLockOptimistic)

write-host -foreGroundColor yellow "Obtaining service info ..."

foreach ($service in $objService)
 {
  if ($service.state -eq "running")
  {
   $strServiceName = $service.name
   $strStatus = $service.State
   $objRecordSet.AddNew()
   $objRecordSet.Fields.item("TimeStamp") = Get-Date
   $objRecordSet.Fields.item("strComputer") = $strComputer
   $objRecordSet.Fields.item("strDomain") = $strDomain
   $objRecordSet.Fields.item("strService") = $strServiceName
   $objRecordSet.Fields.item("strStatus") = $strStatus
   $objRecordSet.Update()
   write-host -foregroundColor yellow "/\" -noNewLine
  }
 }

$objRecordSet.Close()
$objConnection.Close()

We first need to change the if statement from looking for running services to looking for stopped services. The modified line of code is seen here.

if ($service.state -eq "stopped")

After we have modified the if filter, we change the name of the database table stored in the $strTable variable to the “StoppedServices” table. We also need to modify the access query that is hardcoded in the Open method call on the RecordSet object. The modified Open method call is seen here:

$objRecordSet.Open("SELECT * FROM StoppedServices", `
$objConnection, $adOpenStatic, $adLockOptimistic)

Interestingly enough, because of the design of our database, which is seen in the following image, we do not need to change anything in the section where we write to the database.

Image of the database


Because we are collecting exactly the same information, it makes sense to use the same field names for the StoppedServices table in the Services database. The completed WriteStoppedServicesToAccess.ps1 is seen here.

WriteStoppedServicesToAccess.ps1

$StrComputer = (New-Object -ComObject WScript.Network).computername
$StrDomain = (New-Object -ComObject WScript.Network).Domain
$strWMIQuery = "Select * from win32_Service"
$objservice = get-wmiobject -query $strWMIQuery

write-host -foreGroundColor yellow "Obtaining service info ..."

foreach ($service in $objService)
 {
  if ($service.state -eq "stopped")
  {
   $strServiceName = $service.name
   $strStatus = $service.State
   $adOpenStatic = 3
   $adLockOptimistic = 3
   $strDB = "c:\fso\services.mdb"
   $strTable = "StoppedServices"
   $objConnection = New-Object -ComObject ADODB.Connection
   $objRecordSet = new-object -ComObject ADODB.Recordset
   $objConnection.Open("Provider = Microsoft.Jet.OLEDB.4.0; `
     Data Source= $strDB")
   $objRecordSet.Open("SELECT * FROM StoppedServices", `
     $objConnection, $adOpenStatic, $adLockOptimistic)

   $objRecordSet.AddNew()
   $objRecordSet.Fields.item("TimeStamp") = Get-Date
   $objRecordSet.Fields.item("strComputer") = $strComputer
   $objRecordSet.Fields.item("strDomain") = $strDomain
   $objRecordSet.Fields.item("strService") = $strServiceName
   $objRecordSet.Fields.item("strStatus") = $strStatus
   $objRecordSet.Update()
   write-host -foregroundColor yellow "/\" -noNewLine
  }
 }

$objRecordSet.Close()
$objConnection.Close()


In the WriteServiceConfigToAccess.ps1 script, we decide to make a couple of changes to the structure of the previous WriteRunningServicesToAccess.ps1 script, and then add a few supplemental fields. The first change we need to make to the script is to remove the if filter. This is because we want the configuration of all services, whether they are running or not. This requires deleting not only the line of code with the if statement, but also deleting two curly brackets as well.

After we delete the if filter, we need to add some new variables and collect some new information from our WMI service object. We use the variable $strStartName to hold the account name that the service will start with. It is contained in the WMI property StartName on the Win32_service class. This new line of code is seen here:

$strStartName = $service.StartName

The next piece of information we wish to collect is the start mode of the service. The StartMode property reports how the service is configured to start up. The values that may be reported by the WMI StartMode property of the WIN32_Service class are listed in Table 1.

Table 1 Service Start Modes

Start mode

Meaning

"Boot"

Device driver started by operating system loader

"System"

Device driver started by operating system initialization process

"Auto"

Service started automatically by service control manager (SCM) during system startup

"Manual"

Service started by SCM when a process calls StartService method

"Disabled"

Service cannot be started


The new line of code that collects the StartMode information is seen here:

$strStartMode = $service.StartMode

The next two pieces of information we wish to collect refer to whether or not we can stop or pause a service. Though it is not unusual to be able to stop a service, there are several processes that if stopped would lead to system instability; therefore, those services do not accept a stop command. However, it is very unusual that a service will accept a pause command. In fact, on my Windows Vista laptop, only eight services report the ability to accept a pause. A quick script is seen here that will reveal which services can be stopped:

AcceptPause.ps1

Get-WmiObject -Class win32_service |
Where-Object { $_.acceptpause -eq "true" } |
Select-Object name

The services that will accept a pause command on my Windows Vista Professional laptop (your results may be different depending on which options you have selected and which version of Windows Vista you have installed) are seen here:

name
----
LanmanServer
LanmanWorkstation
Netlogon
seclogon
stisvc
TapiSrv
WerSvc
Winmgmt

If you want to document whether a service can be paused or stopped, you will need to work with two different properties of the Win32_Service WMI class: AcceptPause and AcceptStop. Because the properties return a Boolean (true/false) value, we called our variables $blnAcceptPause and $blnAcceptStop. The code that collects these values and stores them in the appropriate variables is seen here:

$blnAcceptPause = $service.AcceptPause
$blnAcceptStop = $service.AcceptStop

To write these values to the database is not a very hard task. The only thing we need to do is follow the pattern we have previously established for writing to the database. We keep the variable names and the database field names similar to avoid confusion:

$objRecordSet.Fields.item("strStartMode") = $strStartMode
$objRecordSet.Fields.item("blnAcceptPause") = $blnAcceptPause
$objRecordSet.Fields.item("blnAcceptStop") = $blnAcceptStop

After having made all the changes to the script, we end up with the code for the WriteServiceConfigToAccess.ps1 script. The completed script is seen here.

WriteServiceConfigToAccess.ps1

$StrComputer = (New-Object -ComObject WScript.Network).computername
$StrDomain = (New-Object -ComObject WScript.Network).Domain
$strWMIQuery = "Select * from win32_Service"
$objservice = get-wmiobject -query $strWMIQuery

write-host -foreGroundColor yellow "Obtaining service info ..."

foreach ($service in $objService)
{
   $strServiceName = $service.name
   $strStartName = $service.StartName
   $strStartMode = $service.StartMode
   $blnAcceptPause = $service.AcceptPause
   $blnAcceptStop = $service.AcceptStop
   $adOpenStatic = 3
   $adLockOptimistic = 3
   $strDB = "c:\fso\services.mdb"
   $strTable = "ServiceConfiguration"
   $strAccessQuery = "Select * from $strTable"
   $objConnection = New-Object -ComObject ADODB.Connection
   $objRecordSet = new-object -ComObject ADODB.Recordset
   $objConnection.Open("Provider = Microsoft.Jet.OLEDB.4.0; `
     Data Source= $strDB")
   $objRecordSet.Open($strAccessQuery, `
     $objConnection, $adOpenStatic, $adLockOptimistic)

   $objRecordSet.AddNew()
   $objRecordSet.Fields.item("TimeStamp") = Get-Date
   $objRecordSet.Fields.item("strComputer") = $strComputer
   $objRecordSet.Fields.item("strDomain") = $strDomain
   $objRecordSet.Fields.item("strService") = $strServiceName
   $objRecordSet.Fields.item("strStartName") = $strStartName
   $objRecordSet.Fields.item("strStartMode") = $strStartMode
   $objRecordSet.Fields.item("blnAcceptPause") = $blnAcceptPause
   $objRecordSet.Fields.item("blnAcceptStop") = $blnAcceptStop
   $objRecordSet.Update()
   write-host -foregroundColor yellow "/\" -noNewLine
}

$objRecordSet.Close()
$objConnection.Close()

As you can see, SW, it is easy to modify an existing script and use it to write to a Microsoft Access Database.

If you want to know exactly what we will be scripting tomorrow, follow us on Twitter or Facebook. If you have any questions, send e-mail to us at scripter@microsoft.com or post your questions on the Official Scripting Guys Forum. See you tomorrow. Until then, peace.

 

Ed Wilson and Craig Liebendorfer, Scripting Guys