ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

How To Query Windows OS with vbscripts, SQL and xp_cmdshell

Updated on April 7, 2011

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.

Comments

    0 of 8192 characters used
    Post Comment

    No comments yet.

    working

    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, hubpages.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://hubpages.com/privacy-policy#gdpr

    Show Details
    Necessary
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
    Features
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Marketing
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Statistics
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)