VBS Script To List Server Drive Information To Excel

This VBS script will take a server or machine name form an input box and write the following disk drive information to an excel spreadsheet. System Name, Device ID, Description, File System, Volume Name, Disk Size (GB) and Free Space

VBS Script:

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True

objExcel.Workbooks.Add

intRow = 2

objExcel.Cells(1, 1).Value = "SystemName"

objExcel.Cells(1, 2).Value = "DeviceID"

objExcel.Cells(1, 3).Value = "Description"

objExcel.Cells(1, 4).Value = "FileSystem"

objExcel.Cells(1, 5).Value = "VolumeName"

objExcel.Cells(1, 6).Value = "Disk Size (GB)"

objExcel.Cells(1, 7).Value = "FreeSpace"

strComputer = InputBox("Enter Server Name")

Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\Cimv2")

Set colItems = objWMIService.ExecQuery("Select * from Win32_LogicalDisk Where DriveType = 3")

For Each objItem in colItems

objExcel.Cells(intRow, 1).Value = objItem.SystemName 

objExcel.Cells(intRow, 2).Value = objItem.DeviceID

objExcel.Cells(intRow, 3).Value = objItem.Description

objExcel.Cells(intRow, 4).Value = objItem.FileSystem

objExcel.Cells(intRow, 5).Value = objItem.VolumeName

objExcel.Cells(intRow, 6).Value = Int(objItem.Size / 1048576 / 1024)

intFreeSpace = objItem.FreeSpace

intTotalSpace = objItem.Size

pctFreeSpace = intFreeSpace / intTotalSpace

objExcel.Cells(intRow, 7).Value = FormatPercent(pctFreeSpace)

intRow = intRow + 1

Next

objExcel.Range("A1:G1").Select

objExcel.Selection.Interior.ColorIndex = 19

objExcel.Selection.Font.ColorIndex = 11

objExcel.Selection.Font.Bold = True

objExcel.Cells.EntireColumn.AutoFit

MsgBox "Done"

VBS Scripts To Create An Access Database

Here you will find a VBS script to create an empty or blank Microsoft Access database based on the name you specify from an input box. You will also find an additional VBS script that will perform the same function as described above but will add sample columns to the access database.

VBS Script To Create An Empty Access database

strFileName = InputBox("Enter Access File Name",, "MyAccessDatabase")

Set objCatalog = CreateObject("ADOX.Catalog")

objCatalog.Create "Provider = Microsoft.Jet.OLEDB.4.0;Data Source =" & strFileName & ".Mdb" & ";"

Set objCatalog = Nothing

MsgBox "Done"

Note: You do not need to add the file extension (.Mdb) for the database you want to create as the file extension is automatically added to the filename taken from the input box.

VBS Script To Create An Access Database And Add Table Columns

strFileName = InputBox("Enter Access File Name",, "MyAccessDatabase")

Set objCatalog = CreateObject("ADOX.Catalog")

objCatalog.Create "Provider = Microsoft.Jet.OLEDB.4.0;Data Source =" & strFileName & ".Mdb" & ";"

Set objConnection = CreateObject("ADODB.Connection")

objConnection.Open _

"Provider = Microsoft.Jet.OLEDB.4.0;Data Source =" & strFileName & ".Mdb" & ";"

objConnection.Execute "Create Table MyTable(" & _

"ID Counter," & _

"TextBox Text(50)," & _

"NumberBox Integer," & _

"MemoBox Memo," & _

"TimeAndDate DateTime)"

objConnection.Close

Set objCatalog = Nothing

Set objConnection  = Nothing

MsgBox "Done"

Note: Change the MyTable name to the name of the table you want to create.

The script above like the first script does not require you to specify the access database file extension. The samples above are designed to provide you with a few of available data types that are more common to access development such as the Counter, Text, Integer, Memo and Date-Time.

Find Last Database Backup Time Stamp

Use the SQL script below to find your last database backup time.

SQL Query:

Use Master

Select (SubString(Database_Name,1,32)) 'Database Name',

Backup_Finish_Date 'Last Backup Time Stamp'

From Msdb.Dbo.Backupset

SMS Client Machines With Less Than 300 MB Free Disk Space On Their Primary Partition

This SQL script will return all of the SMS client machine names and their last logged on user name where their primary C: partition has less than 300 MB of remaining free disk space.

SQL Script:

Select

SD.Name0 'Machine Name',

SD.User_Name0 'User Name',

LD.FreeSpace0 'Free Space'

From v_R_System SD

Join v_Gs_Logical_Disk LD on SD.ResourceId = LD.ResourceId

Where LD.DeviceId0 = 'C:'

And LD.FreeSpace0 < 300

And SD.Client0 = 1

SMS 2003 Error 2186

If you attempt to manually start the SMS_Executive service on an SMS 2003 site server that is not running the following error message may appear:

Error 2186 "Service is not responding to the internal control function"

Note: This error may also appear if you attempt to manually start the Sms_Site_Component_Manager service as well.

Unfortunately A site reset is in order to correct this issue. To correct this issue follow the steps below from the site server locally not from a remote machine or the SMS admin console on your workstation.

Note: Make sure that all remote SMS Admin consoles are not connected to the site server before proceeding.

1. From the Systems Management Server program group select “SMS Setup”

2. Then click “Next” at the Systems Management Server Setup Wizard task. Then select “Next” again to continue.

3. Select “Modify or reset the current installation" and then click “Next”.

4. From here follow the steps provided by the Systems Management Server Setup Wizard to reset your site.

Tip: If you are currently running in Standard security mode you can elect to switch to Advanced security mode however you cannot change your site to Standard form Advanced sectary mode.

Creating Windows 2003 Server Transparent Desktop Icons

If you want to make your Windows 2003 Server desktop icons blend in with the desktop color scheme or wallpapers like Windows XP and Vista workstations do this tip will show you how.

This tip does not require you to download utilities or hack the registry to do so however both are viable options to allow you to have transparent desktop icons. This tip uses the 2003 Server Network Operating System (NOS) to apply transparent desktop icons.

Follow the steps below to set transparent desktop icons:

1. From the Control Panel select open the System icon.

2. Next click the Advanced tab.

3. At the Performance leaf select Settings.

4. At the Performance Options page select “Adjust for best performance”.

5. To save you changes select “OK”.

6. Select “OK” once again to close the System properties and exit the control panel.

SCE 2007 Management Console Workstation Installation Prerequisites

Here you will find a listing of the prerequisites that are checked when you attempt to install the SCE 2007 User Interface Management Console on a workstation or another Server.

To install the User Interface which is the Management Console the following prerequisites must be met and are check prior to installing the User Interface:

2048 MB Physical Memory

Windows Server 2003 Service Pack 1 or Windows XP Professional Service Pack 2

.NET Framework v2.0

.NET Framework 3.0 Components

User is an administrator

MSI version - Windows Installer 3.1 or later must be installed.

Windows drive is NTFS

DotNet Framework v2.0

Setup not running under WOW64

Microsoft Windows XP or later must be installed.

WSUS Version

WSUS valid date – Checks to ensure you have the Current WSUS version.

PowerShell Script To Return The Local Machines Installed Memory In Megabytes

This simple PowerShell script will return the local machines Installed memory in Megabytes.

PS1 Script:

$strComputer = $Host

Clear

$RAM = WmiObject Win32_ComputerSystem

$MB = 1048576

"Installed Memory: " + [int]($RAM.TotalPhysicalMemory /$MB) + " MB"

Now You’re Cooking With Gas

Before the invention of the Gas stove (Patented in 1826 by James Sharp) and later the Electric stove (Patented in 1896 by William Hadaway) wood burning stoves were the only kind available. After James Sharp patented the Gas stove the push was on to try and produce and sell as many as they could however the pricing was so that the common family could not afford the purchase. It was not until the 1950’s that advertising could be forced upon radio listeners and television watchers that the real push was on to sell as many gas stoves as possible.

There were Radio ads and television ads stating that Gas was faster, cleaner and therefore more efficient that wood burning stoves which is true and from these advertisements we get the phrase that they touted: Now you're cooking with gas!.

OpsMgr 2007 Inventory Utility

The Operations Manager inventory utility from Microsoft is a command line utility that also is a graphical user interface or GUI that collects current OpsMgr 2007 inventory information for the Management server it is executed on. It then saves the results into a Cab file name that you specify.

Note: The Cab file can be opened with WinZip or WinRar.

To use the utility after you download it from the directory in which the utility is extracted to run the following from a command prompt:

MomInventory /Silent /CabFile:C:\fileName.Cab

This will begin the GUI interface that creates an EventLog and Profile directory as well as creates an Inventory.Xml file which includes information about your Management server. The Inventory.Xml file include the following information among other items:

Windows Installer Information.

MOM File Information.

MOM Registry Information.

MOM Server Configuration.

Current running process.

The utility then copies the following MOM log files to the Profile directory and then creates a PrereqReport.Xl file:

MOM0.log

MOMPerfCtrsInstall.log

The following Event logs are also copied to the EventLog directory:

Application.evt

Operations Manager.evt

Security.evt

System.evt

Windows PowerShell.evt

The utility can also be executed by running the fowling VBS Script:

VBS Script:

strCabName = InputBox ("Enter File Name")

Set oShell = CreateObject("WScript.Shell") 

strCommand = oShell.Run("MomInventory /Silent /CabFile:" & strCabName & ".Cab", 0, True) 

Note: You do not have to enter the .Cab file extension however you must enter the File Name as in the example here: C:\MyFile

Operations Manager Inventory Download

http://www.microsoft.com/downloads/details.aspx?familyid=10e79763-6906-4f44-86c6-fbabb0bf1b9f&displaylang=en

Welcome Gwen Zierdt To The myITforum Blog Community

Please stop by and visit Gwen Zierdt’s Blog where she will be posting and blogging about System Center products such as Essentials 2007 as well as a host of other topics such as System Center Operations Manager 2007.  

About Gwen Zierdt

Gwen is the eldest of four children and was born in the U.S. Army hospital in Waco, TX in March 1962. Educated at Boston University as an archaeologist, Gwen began her career as an import/export trade specialist. She quickly transitioned into the growing software industry in the late 80's.She took a brief break from full-time employment to move to Chicago and earn a Master's in Fine Arts. During that time she worked her first telecommuting stint for a software company in Fremont, CA.  After finishing her program, she returned to the Seattle area and resumed her technical career. In the spring of 2007, Gwen began working as a freelance writer.

The Real World Is Messy:

Making Sense with Essentials at myITforum.com

http://myitforum.com/cs2/blogs/gzierdt/default.aspx

Daylight Saving Time (DST) Patch Tester

This page uses JavaScript to detect your Daylight Saving Time settings to determine if you still need an update. University of Minnesota

Free SqlCmd 2005 Utility Tutorials From Microsoft

Microsoft has provided the following SqlCmd 2005 Utility Tutorials that will allow you to run ad hock SQL queries against a database from the command line in an interactive mode.

Lesson 1: Starting sqlcmd

http://technet.microsoft.com/en-us/library/ms166559.aspx

Lesson 2: Running Transact-SQL Script Files by Using sqlcmd

http://technet.microsoft.com/en-us/library/ms170572.aspx

System Center Configuration Manager TechNet Forums Are Now Available

The System Center Configuration Manager (CfgMgr) 2007 TechNet web Forums are now avalible.

Configuration Manager – General

http://forums.microsoft.com/TechNet/ShowForum.aspx?ForumID=1818&SiteID=17

General Discussion on Systems Management Server 2003 and System Center Configuration Manager on topic or feature not already covered by one of the other forums.

Announcements

http://forums.microsoft.com/TechNet/ShowForum.aspx?ForumID=1814&SiteID=17

General Anouncements on Systems Management Server 2003 and System Center Configuration Manager Forums

Admin Console

http://forums.microsoft.com/TechNet/ShowForum.aspx?ForumID=1815&SiteID=17

Discussion on the Administrator Console in Systems Management Server 2003 or System Center Configuration Manager

Asset Intelligence

http://forums.microsoft.com/TechNet/ShowForum.aspx?ForumID=1816&SiteID=17

Discussion on the Asset Intellegence Features in Systems Management Server 2003 or System Center Configuration Manager

Backup and Recovery

http://forums.microsoft.com/TechNet/ShowForum.aspx?ForumID=1895&SiteID=17

Discussions on backing up and restoring System Center Configuration Manager sites

Desired Configuration Management

http://forums.microsoft.com/TechNet/ShowForum.aspx?ForumID=1817&SiteID=17

Discussion on the Desired Configuration Management Features in System Center Configuration Manager

Documentation

http://forums.microsoft.com/TechNet/ShowForum.aspx?ForumID=1829&SiteID=17

Discussion on the Help and Documentation for Systems Management Server 2003 or System Center Configuration Manager

Internet Clients and Native Mode

http://forums.microsoft.com/TechNet/ShowForum.aspx?ForumID=1888&SiteID=17

Discussion on internet based clients and running sites in native mode, certificate and SSL issues for System Center Configuration Manager

Inventory

http://forums.microsoft.com/TechNet/ShowForum.aspx?ForumID=1819&SiteID=17

Discussion on the Inventory Features in Systems Management Server 2003 or System Center Configuration Manager

Operating System Deployment

http://forums.microsoft.com/TechNet/ShowForum.aspx?ForumID=1824&SiteID=17

Discussion on the Operating Systems Deployment Features in Systems Management Server 2003 or System Center Configuration Manager

SDK

http://forums.microsoft.com/TechNet/ShowForum.aspx?ForumID=1823&SiteID=17

Discussion on the Software Development Kits for Systems Management Server 2003 or System Center Configuration Manager

Setup/Deployment

http://forums.microsoft.com/TechNet/ShowForum.aspx?ForumID=1821&SiteID=17

Discussion on the Setup and Client and Server Deployment of Systems Management Server 2003 or System Center Configuration Manager

Software Distribution

http://forums.microsoft.com/TechNet/ShowForum.aspx?ForumID=1822&SiteID=17

Discussion on the Software Distribution Features in Systems Management Server 2003 or System Center Configuration Manager

Software Updates Management

http://forums.microsoft.com/TechNet/ShowForum.aspx?ForumID=1820&SiteID=17

Discussion on the Software Updates Management Features in Systems Management Server 2003 or System Center Configuration Manager

Creating A SMS Client Health Monitoring Tool Web Report

Here you will find information on how to create an SMS Web Report for the Client Health Monitoring Tool database.

Note: This assumes that you have the SMS client health monitoring tool installed in your SMS_XXX database where XXX is your sites three letter site code. If the SMS Client Health Monitoring Tool is installed in another database you must specify the fully qualified naming convention to the database as a linked server.

First you need to create a Cleint Health View and apply the appropriate permissions using the SQL Query below:

Note: You can change the View name as needed by replacing all the instances of v_R_ClientHealth with the view name of your choice in the script below. Also change the Use SMS_XXX to your database name where XXX is your sites three letter site code.

SQL Query:

Use SMS_XXX

If Exists (Select * From dbo.SysObjects Where ID = Object_ID(N'[v_R_ClientHealth]')

and ObjectProperty(ID, N'IsView') = 1)

Drop View v_R_ClientHealth

Go

Create View v_R_ClientHealth As Select

ResourceID,

Name 'Machine Name',

Guid,

Sitecode 'Site Code',

LastUser 'Last User Name',

Type 'Client Type',

Version 'Client Version',

LastDDR 'Last DDR Recieved',

LastHW 'Last Hardware Scan Date',

LastSW 'Last Software Scan Date',

DDRState 'Last DDR State',

HWState 'Last Hardware State',

SWState 'Last Software State',

TOTState 'Last TOT State',

Active 'Active Client',

Obsolete 'Obsolete Client',

ChangeActive 'Active Status Change',

ChangeObsolete 'Obsolete Status Change',

LastPing 'Last Ping Time Stamp',

LastPolicy 'Last Policy Time Stamp',

WasOnline 'On Line',

LastPingResult 'Last Ping Status',

Classification 'Last Client State'

From ClientHealthResults

Go

Grant Select On v_R_ClientHealth To smsschm_users, webreport_approle

Go

Select * From v_R_ClientHealth

To create the Web Report follow the steps below:

Note: To use the SQL script as a web report copy and paste the Name, Category, Description and of course the SQL statement shown below to the appropriate new web report boxes or modify to your liking as needed.

Name: Client Health Results

Category: Client Health Reports

Description: Displays the current Client Health information for the site..

SQL Statement:

Select *

From v_R_ClientHealth

By Request Script To Create A SkpSwi.Dat File

This By Request script was created in response to a users request for a simple way to create a SkpSwi.Dat file as mentioned in my previous post found below using a script.

VBS Script:

strFileName = "SkpSwi.Dat"

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objFile = objFSO.CreateTextFile(strFileName, True)

MsgBox "Done"

Excluding Servers From Running Software Inventory

http://myitforum.com/cs2/blogs/dhite/archive/2007/01/28/excluding-servers-from-running-software-inventory.aspx

Bugs Bunny Quotes

• Aah, ya brother blows bubble gum.
• Ain't I a stinker.
• Captain! Captain! Just what's the meaning of this?
• Eh, hey buddy, where'd you go?
• Gold! Gold! I'm rich, I'm fabulously wealthy.
• Hey girls, the life of the party is here.
• Hey look fella's. I'm a hare plane.
• Hmm, innocent as a new born baby…Baby rat, that is.
• I don't ask questions, I just have fun!
• I knew I shoulda takin' that left turn at Albuquerque.
• I know this defies the law of gravity, but I never studied law!
• I wonder what the poor bunnies are doing this season?
• I wonder why they put the South so far south?
• If it's the Captain's Mess, let him clean it up.
• I'm gonna punch you right square in the nose.
• I'm willing to let bygones be bygones if ya promise not to do it again.
• I've got a notion to take you apart with a screwdriver.
• Last one in's a rotten egg.
• Neh, I couldn't do that to the little nimrod.
• Of course you realize, this means war.
• Run for the hills, folks, or you'll be up to your armpits in Martians.
• So long screwy, see ya in St. Louie.
• Step aside son, ya bother me.
• There's gold in them thar hills.
• This is an outrage! I demand an explanation.
• Time out whilst I think up some more deviltry.
• Weeeeell, goodbye... And don't think it hasn't been a little slice of heaven... 'cause it hasn't.
• Well shut my mouth and call me Cornpone, if it ain't the little ol' South.
• Well, if ya can't lick 'em ya might as well join 'em.
• Well, it's 5 o'clock somewhere.
• Well, ya talked me into it.
• What a bird brain.
• What a maroon, what an ignoranimous.
• What's the hassle, Schmassle?
• What's up doc?
• Why you couldn't even catch a cold.
• Yeah, I know, I know. But anything can happen in Texas.
• You'll be sorry!

Okay Okay I'm shuttin' up. Why should I continue to keep yappin' when I'm told to shut up. I'm not the kind that don't know when to stop.

And my personal favorite for those that know me!

Three Things To Think About:

1. COWS

2. THE CONSTITUTION

3. THE TEN COMMANDMENTS

ON COWS

Is it just me, or does anyone else find it amazing that our government can track a cow born in Canada almost three years ago, right to the stall where she sleeps in the state of Washington? And, they tracked her calves to their stalls. But they are unable to locate 11 million illegal aliens wandering around our country. Maybe we should give them all a cow.

ON THE CONSTITUTION

They keep talking about drafting a Constitution for Iraq, why don't we just give them ours? It was written by a lot of really smart guys, it's worked for over 200 years and we're not using it anymore.

ON THE TEN COMMANDMENTS

The real reason that we can't have the Ten Commandments in a courthouse........

You cannot post "Thou Shalt Not Steal," "Thou Shalt Not Commit Adultery",

and "Thou Shall Not Lie" in a building full of lawyers, judges and politicians --

it creates a hostile work environment.

Thanks J. Michaels

VBS Script To Read XML File Information

This VBS script will provide you with an example of how to retrieve XML file information from a specified XML file. Also included is a sample XML file called Servers.Xml file to use with the script to test its functionality.

VBS Script:

Set xmlDoc = CreateObject("Microsoft.XMLDOM")

xmlDoc.Async = "False"

xmlDoc.Load("Servers.xml")

strQuery = "/ApplicationServers/Servers/ServerTask"

Set colItem = xmlDoc.selectNodes(strQuery)

For Each objItem in colItem

MsgBox objItem.nodeName & ": " & objItem.text

Next

Servers XML File:

<?xml version="1.0" encoding="ISO-8859-1"?>

<ApplicationServers>

<Servers>

<ServerTask>SQL Server</ServerTask>

<ServerName>SQL_Server_001</ServerName>

<ServerName>SQL_Server_002</ServerName>

<ServerName>SQL_Server_003</ServerName>

</Servers>

<Servers>

<ServerTask>SMS Server