How To Query Windows OS with vbscripts, SQL and xp_cmdshell

klanguedoc @ 2010

xp_cmdshell is a system stored procedure in SQL Server that allows users to write queries exactly like using the Windows Command Shell. This jewel of technology opens immense doors of possibility for anyone who needs to extend T-SQL queries into the OS or network. A user could gather information open available printers, files, running processes, open ports start or stop Windows services all from a T-SQL query. The results could also be combined with other information and stored in a table.

This article is demonstrate using xp_cmdshell along with cscript.exe to execute vbscript or jscript scripts from a T-SQL query. If you haven’t already enabled xp_cmdshell, here are a set of instructions to do so.

Say Hello World

This is a simple example to print the proverbial “Hello World” from vbscript. If we issue the following command:

execute xp_cmdshell 'cscript.exe "C:\temp\script\helloworld.vbs"'

You will get the following output in SSMS:

Microsoft (R) Windows Script Host Version 5.7
Copyright (C) Microsoft Corporation. All rights reserved.
NULL
Hello, world!
NULL

The helloworld.vbs script file contains the following bit of code:

Wscript.echo("Hello, world!")

Very simple.


Gather Information on your Operating System

Suppose you want to do something more elaborate such as gathering information on your computer or as an Administrator or Developer you would need to gather information on the computers on your network. This would be very straightforward with the Windows Management Instrumentation API (WMI). To demonstrate you would need to issue query like this one in SSMS:

execute xp_cmdshell 'cscript.exe "C:\temp\script\OSInfo.vbs"'

The OSInfo.vbs script would look something like this:

' OSInfo.vbs
' Gather information on your OS
' -------------------------------------------------------'
Option Explicit

‘Declare the variables
Dim oWMIService ‘ This represents the WMI Service
Dim queryRes ‘ This represents the query result
Dim oItem ‘ This represents each item of information in the query
Dim strComputer ‘ The ciomputer name where the script is executing

On Error Resume Next
strComputer = "."

' Get the CIM object with a WMI Connection.
‘ The CIM is the Common Management Interface
Set oWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")

' Query the Win32_OperatingSystem with WMI
Set queryRes= oWMIService.ExecQuery ("Select * from Win32_OperatingSystem")

' For Each... In Loop (Next at the very end)
For Each objItem in queryRes
WScript.Echo "Computer Name: " & oItem.CSName & VbCr & _
"===================================" & vbCr & _
"Processor: " & objItem.Description & VbCr & _
"Manufacturer: " & objItem.Manufacturer & VbCr & _
"Operating System: " & objItem.Caption & VbCr & _
"Version: " & objItem.Version & VbCr & _
"Service Pack: " & objItem.CSDVersion & VbCr & _
"CodeSet: " & objItem.CodeSet & VbCr & _
"CountryCode: " & objItem.CountryCode & VbCr & _
"OSLanguage: " & objItem.OSLanguage & VbCr & _
"CurrentTimeZone: " & objItem.CurrentTimeZone & VbCr & _
"Locale: " & objItem.Locale & VbCr & _
"SerialNumber: " & objItem.SerialNumber & VbCr & _
"SystemDrive: " & objItem.SystemDrive & VbCr & _
"WindowsDirectory: " & objItem.WindowsDirectory & VbCr & _
""
Next
WSCript.Quit

This script would provide the following information on the Operating System where it is being executed:

output
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Microsoft (R) Windows Script Host Version 5.7
Copyright (C) Microsoft Corporation. All rights reserved.
NULL
Machine Name: DevL0001
===================================
Processor: Development Laptop
Manufacturer: Microsoft Corporation
Operating System: Microsoft Windows XP Professional
Version: 5.1.2600
Service Pack: Service Pack 3
CodeSet: 1252
CountryCode: 1
OSLanguage: 1033
CurrentTimeZone: -240
Locale: 0409
SerialNumber: 06417-651-0000000-23029
SystemDrive: C:
WindowsDirectory: C:\WINDOWS

NULL

(6 row(s) affected)

Storing the Information into a Table

To take this example one step further, we could store this information into a Temporary Table or a Table Variable or regular table. This could be combined with other types of information like what software is installed on a computer. Or what Ports are in use on a particular computer. To insert the information into a table you need to create a table and issue an Insert Into command as so:

CREATE Table #OS (info varchar(8000))

INSERT INTO #OS
execute xp_cmdshell 'cscript.exe "C:\temp\script\HELLOWORLD.vbs"'

SELECT * FROM #OS

DROP TABLE #OS

Get Information on Running OS Processes

If you needed to determine which processes are running on a computer, the following command and script would provide you with the following information (abbreviated for demonstration purposes):

output
----------------------------------------------------------------------------------------------------------------------------
Microsoft (R) Windows Script Host Version 5.7
Copyright (C) Microsoft Corporation. All rights reserved.
NULL
Open Process ID: 672
Apple Mobile Device
Windows Audio
Background Intelligent Transfer Service
Bonjour Service
Computer Browser
Symantec Event Manager
Symantec Settings Manager
Com4QLBEx
COM+ System Application
Crypkey License
Cryptographic Services
DCOM Server Process Launcher
DHCP Client
DNS Client
Event Log
COM+ Event System
Intelr PROSet/Wireless Event Log
Help and Support
HID Input Service
hpqwmiex
IIS Admin
iPod Service
........

This is the query:
execute xp_cmdshell 'cscript.exe "C:\temp\script\OpenProcesses.vbs"'

And this is the script contents in OpenProcesses.vbs

Dim oDic
Dim oWMI, ResultSet
Dim ProcIDs

set oDic= CreateObject("Scripting.Dictionary")

'This computer
strComputer = "."

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

'Get Query Result
Set ResultSet = oWMI.ExecQuery ("Select * from Win32_Service Where State <> 'Stopped'")

For Each objService in ResultSet
If oDic.Exists(objService.ProcessID) Then
Else
oDic.Add objService.ProcessID, objService.ProcessID
End If
Next

ProcIDs = oDic.Items

For i = 0 to oDic.Count - 1
Set colServices = oWMI.ExecQuery ("Select * from Win32_Service Where ProcessID = '" & _
ProcIDs(i) & "'")
Wscript.Echo "Open Process ID: " & ProcIDs(i)
For Each objService in ResultSet
Wscript.Echo VbTab & objService.DisplayName
Next

Summary

To conclude, the xp_cmdshell stored procedure is a powerful tool. Of course in a production environment security is tantamount and may impose restrictions on the extend you may use these powerful features. Keep in mind the script must be stored on the same machine where the SQL SERVER is installed and if the query is running in a SSIS or SQL Agent, the System or Proxy Account must have access to the location where the scripts are located.

More by this Author


Comments

No comments yet.

    Sign in or sign up and post using a HubPages Network account.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.


    Click to Rate This Article
    working