Running WMI scripts in DTS package

Question

Post reply

Running WMI scripts in DTS package

Lynn Pettis

SSC Guru

Points: 442467
More actions
October 19, 2005 at 11:02 am

#68498

I am working on a WMI script using VBScript and running from a command line to obtain information regarding services on several servers (we recently had the MSSQLSERVER service disabled on a production server, and no one is admitting to it), so I want to monitor the state of specific services from another server. Can I run WMI scripts directly from a DTS package using VBScript in the package, or do I need to write the data to a file then import using DTS?

Any help is appreciated.

Lynn

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Don Staten SSC Eights! Points: 957 More actions · Answer 1

Lynn,

As long as you meet a few requirements, yes, you can use DTS to get the information through WMI.

Prerequisites:

Local and target servers must be running WMI.
The account used to execute the DTS package must have Administrator permissions on the target server.

I have included a sample script in this reply that you may use to get the information I believe you are looking for. Keep in mind that this script is very basic... I will leave it to you for error control and other coding requirements. I was not sure what you were going to do with the data after it was obtained (send email, update tables, etc.).

Hope this helps.

Don Staten

Software Engineer

Sentara Health Plan, Inc

'**********************************************************************

' Visual Basic ActiveX Script

'************************************************************************

Function Main()

Dim strComputer

Dim strQuery

Dim objWMI

Dim colService

Dim item

'* Update strComputer variable with target server name or IP address

strComputer = "."

'* WQL statement to select MSSQLSERVER service from WMI

strQuery = "Select State From Win32_Service Where Name = ""MSSQLSERVER"""

'* Attach to the WMI service of target machine

Set objWMI = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")

'* Get collect from WMI for the service

Set colService = objWMI.ExecQuery(strQuery)

'* Parse through collection results

For Each item In colService

'* Replace MsgBox with code to process results

MsgBox item.Name & ": " & item.State

End Function

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

My plan is to maintain a database of services and their status. If a change occurs to any services that are critical for a specific server then an email alert would be sent to those who need to know. For instance, if the setting for MSSQLSERVER on a production SQL Server System is changed from Automatic to Disabled, the DBA(S), and MIS Director would get an e-mail alerting them to this change. The database would have enough information to at least give us a clue as to when the change occurs so we can try and track down who made the change and why.

I hope to try setting up a DTS package (and subsequent job) to test this, and just wanted to be sure that it was possible. Didn't want to spin my wheels if others had tried and not succeeded.

Thank you for your response and I will let you know if it works.

Lynn Pettis