September 29th, 2009

Hey, Scripting Guy! Can I Create Microsoft Access Reports of Running Services?

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 would like to be able to write a listing of the services that are running to a Microsoft Access database. I would then be able to use the report writer from Microsoft Access to produce some nice professional looking reports. Is this something I can do using Windows PowerShell?

<

p style=”MARGIN: 0in 0in 8pt” class=”MsoNormal”>– DN

Hey, Scripting Guy! Answer

Hello DN,

Microsoft Scripting Guy Ed Wilson here. It is evening as I write this article, and the sky is clear and the moon shines brightly through the few remaining wafting clouds. I am sitting in my swing on my front porch, checking the scripter@microsoft.com inbox, and just relaxing and enjoying the onset of fall in Charlotte, North Carolina, in the United States. Some of the leaves are already changing color, and it will not be too long before the trick-or-treaters will be making their way through the neighborhood extorting candy from the various householders. I love the fall season, the changing colors, the homemade apple pies, and apple cider. It is a relaxing time of the year. Because I am in such a mellow and relaxed mood, it seems that I remember having written something that is close to what you are asking. Let me dig it out, dust it off, and adapt it a bit. Kind of an environmental approach to Hey, Scripting Guy! Blog posts.

By writing to a database, we give ourselves the opportunity to store the data in a more permanent fashion. We can produce reports that not only provide pertinent information, but also are easy to read and understand. Additionally, because databases are designed for concurrent access, they are a more robust solution for storing data than are text files, which typically are limited to one user at a time. By using the report writer in Access, the process of developing a report is as easy as clicking through a wizard. After we are finished, the report has automatic grouping and sorting, which greatly facilitate the navigation of the information. The report looks professional and could easily be given to upper management. An example of such a report is seen here:

Image of an example Access report

In the WriteRunningServicesToAccess.ps1 script, we illustrate the process of writing to a Microsoft Access database by using Active X Data Objects (ADO) technology.  On the first line of the script, we retrieve the current computer name by using the wshNetwork object, which is created by using the New-Object cmdlet, specifying the –comobject parameter, and using the wscript.network program ID. We enclose the entire statement in a set of smooth parentheses, and then choose only the computer name property from the object. We assign this computer name to the variable $strComputer.

On the second line of the script, we use exactly the same object and the same procedure. The only difference is that, instead of choosing the computername property, we choose the domain name instead. The two lines of code that work with the wshNetwork object are seen here:

$StrComputer = (New-Object -ComObject WScript.Network).computername
$StrDomain = (New-Object -ComObject WScript.Network).Domain

To retrieve the computername and the domain properties from the wshNetwork object, we created the same object twice. This saved us a little bit of typing. One other way to have done it would have looked like the following:

$wshNetwork = (New-Object -ComObject WScript.Network)
$StrComputer = $wshNetwork.computername
$strDomain = $wshNetwork.domain

On the next line, we define the WMI query we will use. It is an unabashed WMI Query Language (WQL) statement “Select * from WIN32_Service. When we use this query with the Get-WmiObject cmdlet, we will retrieve every property from every service that is defined on the machine. We hold this WQL statement in the variable $strQuery.

If WQL “looks like” SQL, it is for good reason. WQL is considered to be a subset of the Transact SQL query language.

We query the WMI service on the machine by using the Get-WmiObject cmdlet. We call the Get-WmiObject cmdlet and specify the –query parameter. The string contained in the $strWMIQuery variable is passed as the query. The resulting object is held in the $objService variable. The two lines of code that define the WMI query and make the connection into WMI are seen here:

$strWMIQuery = “Select * from win32_Service”
$objservice = get-wmiobject -query $strWMIQuery

After we have made the connection into WMI and retrieved our information, we use the Write-Host cmdlet to display a status message. We use the –foregroundcolor to print the message in yellow. The string “Obtaining service info …” is hard-coded into the call for the Write-Host cmdlet. The Write-Host line of code is seen here:

write-host -foreGroundColor yellow “Obtaining service info …”

The Get-WmiObject cmdlet returns a collection of WMI objects, each representing a different service that is defined on the machine. To deal with all the data, we use the foreach statement to walk through the collection. $strservice is a variable we define to hold each individual service out of the collection services stored in the $objService variable.

We open the script block for the foreach cmdlet by using curly brackets. The first thing we do inside the foreach code block is use the if statement to determine if the service is running or not. We use the $service.state property and see if it is equal to “running.” The foreach and opening code block for the if statement are seen here:

foreach ($service in $objService)
 {
  if ($service.state -eq “running”)
  {

If the service is running, we enter another code block and store the servicename property in the variable $strServiceName. We retrieve service state and assign it in the $strStatus variable. The two WMI value assignments are seen here:

$strServiceName = $service.name
$strStatus = $service.State

On the next line we create a variable called $adOpenStatic and assign the number 3 to it. This will be used when opening the connection to the database. We also create a variable called $adLockOptimistic and set it equal to 3 as well. This value will also be used when opening the connection to the database.

The complete path to the database is stored in the variable $strDB. The $strTable variable is used to hold the name of the table we wish to access. In this script, we are going to connect to the runningServices table. This is the string we assign to the $strTable variable. The four variables that will be used by ADO are seen here:

$adOpenStatic = 3
$adLockOptimistic = 3
$strDB = “c:fsoservices.mdb”
$strTable = “runningServices”

With the “preliminaries” out of the way, we are ready to get into some nitty-gritty ADO. We need to create two objects: a Connection object and a RecordSet object. To create the Connection object we use the New-Object cmdlet to specify the –comobject parameter and feed it the program ID ADODB.Connection. We store the Connection object in the $objConnection.

The next object we need to create is a RecordSet object. To do this we also use the New-Object cmdlet with the –ComObject parameter. We use the program ID ADODB.Recordset and store the resulting RecordSet object in the variable $objRecordSet. The code used to create the two ADODB objects is seen here:

$objConnection = New-Object -ComObject ADODB.Connection
$objRecordSet = new-object -ComObject ADODB.Recordset

Now that we have the two ADODB objects created, we can begin the process of “wiring up” the ADO connection to the services.mdb database. The first thing that must be done is to open the connection to the database. To do this, we must use the Connection object that is contained in the $objConnection variable. We use the Open method from the Connection object, and specify the provider as the Microsoft.Jet.OLEDB.4.0 provider. We separate the provider from the data source, which is specified as the database whose path is stored in the $strDB variable. This command is a single, logical line. The grave character is used after the semicolon to indicate line continuation. This line of code is seen here:

$objConnection.Open(“Provider = Microsoft.Jet.OLEDB.4.0; `
Data Source= $strDB”)

When the connection to the database is open, it is time to use the Open method from the RecordSet object. To do this, we first specify a SQL query. We then list the connection and how we wish to open the database. All these parameters are seen in the code shown here:

$objRecordSet.Open(“SELECT * FROM runningServices”, `
$objConnection, $adOpenStatic, $adLockOptimistic)

After the RecordSet is opened, we can add new records to it. To do this, we use the AddNew method from the  RecordSet object. To add data to the database, we use the Fields.item property of the  RecordSet object. The fieldnames for the Microsoft Access database can be found easily by looking at the database table in design view. This is seen here:

Image of database table in Design view

 

The fieldname in quotation marks comes from the database itself. We use the variables we assigned earlier. The exception is the use of the Get-Date cmdlet to retrieve the current date-time stamp. The code to do this is seen here:

$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

To write the data back to the database, we use the Update method from the RecordSet object. This is seen here:

$objRecordSet.Update()

To provide feedback to the user on the progress of writing the data back to the database, we use the Write-Host cmdlet and display a series of /,each of which indicates one service. To indicate continuity, we use the –noNewLine switch:

write-host -foregroundColor yellow “/” -noNewLine

The output from the above Write-Host cmdlet, though maybe not impressive, does provide a good visual representation that the script is running and indicates progress. The completed output is seen here:

Image of completed output

 

After all the records have been written to the database, we close both the Connection object and the RecordSet object. These two lines of code are seen here:

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

The complete WriteRunningServicesToAccess.ps1 script is seen here.

WriteRunningServicesToAccess.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 “running”)
  {
   $strServiceName = $service.name
   $strStatus = $service.State
   $adOpenStatic = 3
   $adLockOptimistic = 3
   $strDB = “c:fsoservices.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)

   $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()

DN, that is how you can record the running services to a Microsoft Access database. Join us tomorrow as Service Week continues.

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

 

Author

0 comments

Discussion are closed.