{"id":52363,"date":"2009-09-29T03:01:00","date_gmt":"2009-09-29T03:01:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2009\/09\/29\/hey-scripting-guy-can-i-create-microsoft-access-reports-of-running-services\/"},"modified":"2009-09-29T03:01:00","modified_gmt":"2009-09-29T03:01:00","slug":"hey-scripting-guy-can-i-create-microsoft-access-reports-of-running-services","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/hey-scripting-guy-can-i-create-microsoft-access-reports-of-running-services\/","title":{"rendered":"Hey, Scripting Guy! Can I Create Microsoft Access Reports of Running Services?"},"content":{"rendered":"

\"Bookmark<\/a><\/p>\n

(Portions of this article previously appeared in the Microsoft Press book, Windows PowerShell Scripting Guide<\/font><\/a>.)<\/p>\n

\"Hey,<\/p>\n

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>\n

<<\/p>\n

p style=”MARGIN: 0in 0in 8pt” class=”MsoNormal”>– DN<\/p>\n

\"Hey,<\/p>\n

Hello DN, <\/p>\n

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<\/font><\/span><\/a> inbox, and just relaxing and enjoying the onset of fall in Charlotte, North Carolina<\/font><\/a>, in the United States. Some of the leaves are already changing color, and it will not be too long before the trick-or-treaters<\/font><\/a> 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<\/font><\/a> to Hey, Scripting Guy! Blog posts. <\/p>\n

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:<\/p>\n

\"Image
<\/p>\n

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

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<\/b> property, we choose the domain name instead. The two lines of code that work with the wshNetwork<\/b> object are seen here:<\/p>\n

$StrComputer = (New-Object -ComObject WScript.Network).computername
$StrDomain = (New-Object -ComObject WScript.Network).Domain<\/p>\n

<\/font><\/span><\/p>\n

To retrieve the computername<\/b> and the domain properties from the wshNetwork<\/b> 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:<\/p>\n

$wshNetwork = (New-Object -ComObject WScript.Network)
$StrComputer = $wshNetwork.computername
$strDomain = $wshNetwork.domain<\/p>\n

<\/font><\/span><\/p>\n

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<\/b>. When we use this query with the Get-WmiObject<\/b> cmdlet, we will retrieve every property from every service that is defined on the machine. We hold this WQL statement in the variable $strQuery<\/b>. <\/p>\n

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

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

$strWMIQuery = “Select * from win32_Service”
$objservice = get-wmiobject -query $strWMIQuery<\/p>\n

<\/font><\/span><\/p>\n

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

write-host -foreGroundColor yellow “Obtaining service info …”<\/p>\n

<\/font><\/span><\/p>\n

The Get-WmiObject<\/b> 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<\/b> statement to walk through the collection. $strservice<\/b> is a variable we define to hold each individual service out of the collection services stored in the $objService<\/b> variable. <\/p>\n

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

foreach ($service in $objService)
 <\/span>{
  <\/span>if ($service.state -eq “running”)
  <\/span>{<\/p>\n

<\/font><\/span><\/p>\n

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

$strServiceName = $service.name
$strStatus = $service.State<\/p>\n

<\/font><\/span><\/p>\n

On the next line we create a variable called $adOpenStatic<\/b> 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<\/b> and set it equal to 3 as well. This value will also be used when opening the connection to the database. <\/p>\n

The complete path to the database is stored in the variable $strDB<\/b>. The $strTable<\/b> 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<\/b> table. This is the string we assign to the $strTable<\/b> variable. The four variables that will be used by ADO are seen here:<\/p>\n

$adOpenStatic = 3
$adLockOptimistic = 3
$strDB = “c:fsoservices.mdb”
$strTable = “runningServices”<\/p>\n

<\/font><\/span><\/p>\n

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<\/b> object and a RecordSet<\/b> object. To create the Connection<\/b> object we use the New-Object<\/b> cmdlet to specify the –comobject<\/b> parameter and feed it the program ID ADODB.Connection<\/b>. We store the Connection<\/b> object in the $objConnection<\/b>. <\/p>\n

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

$objConnection = New-Object -ComObject ADODB.Connection
$objRecordSet = new-object -ComObject ADODB.Recordset<\/p>\n

<\/font><\/span><\/p>\n

Now that we have the two ADODB objects created, we can begin the process of “wiring up” the ADO connection to the services.mdb<\/b> 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<\/b> variable. We use the Open<\/b> method from the Connection<\/b> object, and specify the provider as the Microsoft.Jet.OLEDB.4.0<\/b> provider. We separate the provider from the data source, which is specified as the database whose path is stored in the $strDB<\/b> 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: <\/p>\n

$objConnection.Open(“Provider = Microsoft.Jet.OLEDB.4.0; `
Data Source= $strDB”)<\/p>\n

<\/font><\/span><\/p>\n

When the connection to the database is open, it is time to use the Open method from the RecordSet<\/b> 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: <\/p>\n

$objRecordSet.Open(“SELECT * FROM runningServices”, `
$objConnection, $adOpenStatic, $adLockOptimistic)<\/p>\n

<\/font><\/span><\/p>\n

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

\"Image<\/a><\/p>\n

 <\/strong><\/p>\n

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

$objRecordSet.AddNew()
   <\/span>$objRecordSet.Fields.item(“TimeStamp”) = Get-Date
   <\/span>$objRecordSet.Fields.item(“strComputer”) = $strComputer
   <\/span>$objRecordSet.Fields.item(“strDomain”) = $strDomain
   <\/span>$objRecordSet.Fields.item(“strService”) = $strServiceName
   <\/span>$objRecordSet.Fields.item(“strStatus”) = $strStatus<\/p>\n

<\/font><\/span><\/p>\n

To write the data back to the database, we use the Update<\/b> method from the RecordSet<\/b> object. This is seen here: <\/p>\n

$objRecordSet.Update()<\/p>\n

<\/font><\/span><\/p>\n

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

write-host -foregroundColor yellow “\/” -noNewLine<\/p>\n

<\/font><\/span><\/p>\n

The output from the above Write-Host<\/b> 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:<\/p>\n

\"Image<\/p>\n

 <\/p>\n

After all the records have been written to the database, we close both the Connection<\/b> object and the RecordSet<\/b> object. These two lines of code are seen here:<\/p>\n

$objRecordSet.Close()
$objConnection.Close()<\/p>\n

<\/font><\/span><\/p>\n

The complete WriteRunningServicesToAccess.ps1 script is seen here. <\/p>\n

WriteRunningServicesToAccess.ps1<\/p>\n

<\/strong><\/p>\n

$StrComputer = (New-Object -ComObject WScript.Network).computername
$StrDomain = (New-Object -ComObject WScript.Network).Domain
$strWMIQuery = “Select * from win32_Service”
$objservice = get-wmiobject -query $strWMIQuery<\/p>\n

write-host -foreGroundColor yellow “Obtaining service info …”<\/p>\n

foreach ($service in $objService)
 <\/span>{
  <\/span>if ($service.state -eq “running”)
  <\/span>{
   <\/span>$strServiceName = $service.name
   <\/span>$strStatus = $service.State
   <\/span>$adOpenStatic = 3
   <\/span>$adLockOptimistic = 3
   <\/span>$strDB = “c:fsoservices.mdb”
   <\/span>$strTable = “runningServices”
   <\/span>$objConnection = New-Object -ComObject ADODB.Connection
   <\/span>$objRecordSet = new-object -ComObject ADODB.Recordset
   <\/span>$objConnection.Open(“Provider = Microsoft.Jet.OLEDB.4.0; `
     <\/span>Data Source= $strDB”)
   <\/span>$objRecordSet.Open(“SELECT * FROM runningServices”, `
     <\/span>$objConnection, $adOpenStatic, $adLockOptimistic)<\/p>\n

   <\/span>$objRecordSet.AddNew()
   <\/span>$objRecordSet.Fields.item(“TimeStamp”) = Get-Date
   <\/span>$objRecordSet.Fields.item(“strComputer”) = $strComputer
   <\/span>$objRecordSet.Fields.item(“strDomain”) = $strDomain
   <\/span>$objRecordSet.Fields.item(“strService”) = $strServiceName
   <\/span>$objRecordSet.Fields.item(“strStatus”) = $strStatus
   <\/span>$objRecordSet.Update()
   <\/span>write-host -foregroundColor yellow “\/” -noNewLine
  <\/span>}
 <\/span>}<\/p>\n

$objRecordSet.Close()
$objConnection.Close()<\/p>\n

<\/font><\/font><\/span><\/p>\n

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

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

 <\/p>\n

Ed Wilson and Craig Liebendorfer, Scripting Guys<\/span><\/b><\/p>\n

<\/span><\/b> <\/p>\n","protected":false},"excerpt":{"rendered":"

(Portions of this article previously appeared in the Microsoft Press book, Windows PowerShell Scripting Guide.) 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 […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[31,3,39,45],"class_list":["post-52363","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-operating-system","tag-scripting-guy","tag-services","tag-windows-powershell"],"acf":[],"blog_post_summary":"

(Portions of this article previously appeared in the Microsoft Press book, Windows PowerShell Scripting Guide.) 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 […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/52363","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=52363"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/52363\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=52363"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=52363"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=52363"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}