Don Hite at myITforum.com

VBS Script To Send Windows NT or Active Directory User Names To Excel

This VBS Script will send all of the NT or Active Directory (AD) domain user names to excel.

VBS Script:

strDomain = InputBox ("Enter Domain Name Or Machine Name")

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True

objExcel.Workbooks.Add

intRow = 2

objExcel.Cells(1, 1).Value = "Domain Name"

objExcel.Cells(1, 2).Value = "User Name"

Set colAccounts = GetObject("WinNT://" & strDomain & "")

colAccounts.Filter = Array("user")

For Each objUser In colAccounts

objExcel.Cells(intRow, 1).Value = UCase(strDomain)

objExcel.Cells(intRow, 2).Value = objUser.Name

intRow = intRow + 1

Next

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

objExcel.Selection.Interior.ColorIndex = 19

objExcel.Selection.Font.ColorIndex = 11

objExcel.Selection.Font.Bold = True

objExcel.Cells.EntireColumn.AutoFit

Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)

Set objRange = objExcel.Range("B1")

objRange.Sort objRange,1,,,,,,1

MsgBox "Done"

VBS Script To Send Windows NT or Active Directory Group Names To Excel

This VBS Script will send all of the NT or Active Directory (AD) domain group names to excel.

VBS Script:

strDomain = InputBox ("Enter Domain Name Or Machine Name")

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True

objExcel.Workbooks.Add

intRow = 2

objExcel.Cells(1, 1).Value = "Domain Name"

objExcel.Cells(1, 2).Value = "Group Name"

Set colGroups = GetObject("WinNT://" & strDomain & "")

colGroups.Filter = Array("group")

For Each objGroup In colGroups

objExcel.Cells(intRow, 1).Value = UCase(strDomain)

objExcel.Cells(intRow, 2).Value = objGroup.Name

intRow = intRow + 1

Next

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

objExcel.Selection.Interior.ColorIndex = 19

objExcel.Selection.Font.ColorIndex = 11

objExcel.Selection.Font.Bold = True

objExcel.Cells.EntireColumn.AutoFit

Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)

Set objRange = objExcel.Range("B1")

objRange.Sort objRange,1,,,,,,1

MsgBox "Done"

VBS Script To Add Multiple MMC 2.0 Snapins And Save It

This Script Requires MMC 2.0 and Above.

VBS Script:

Set objMMC = CreateObject("MMC20.Application")

objMMC.Show

objMMC.Document.snapins.add("Active Directory Users and Computers")

objMMC.Document.snapins.add("Active Directory Sites and Services")

objMMC.Document.snapins.add("Active Directory Domains and Trusts")

VBS Script To Add An MMC Snapin From An Input Box

This Script Requires MMC 2.0 And Above.

VBS Script:

strSnapin = InputBox("Enter Snapin Name")

Set objMMC = CreateObject("MMC20.Application")

objMMC.Show

objMMC.Document.snapins.add(strSnapin)

VBS Script To Monitor SQL Servers SQL Service

This VBS Script will allow you to monitor the SQL Server service state for machines in a text file called MachineList.Txt. If the service is stopped the color will be red.

VBS Script:

strServiceName = "MSSQLSERVER"

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True

objExcel.Workbooks.Add

intRow = 2

objExcel.Cells(1, 1).Value = "Machine Name"

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

Set Fso = CreateObject("Scripting.FileSystemObject")

Set InputFile = fso.OpenTextFile("MachineList.Txt")

Do While Not (InputFile.atEndOfStream)

strComputer = InputFile.ReadLine

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

Set colItems = objWMIService.ExecQuery("Select * from Win32_Service Where Name = '" & strServiceName & "'")

For Each objItem in colItems

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

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

If objExcel.Cells(intRow, 2).Value = "Stopped" Then

objExcel.Cells(intRow, 2).Font.ColorIndex = 3

End If

Next

intRow = intRow + 1

loop

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

objExcel.Selection.Interior.ColorIndex = 19

objExcel.Selection.Font.ColorIndex = 11

objExcel.Selection.Font.Bold = True

objExcel.Cells.EntireColumn.AutoFit

MsgBox "Done"

VBS Script To Send Clients Management Point Information To Excel

This VBS Script will take a list of machines in a text file called MachineList.Txt and will send their Management Point (MP) information to Excel

VBS Script:

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True

objExcel.Workbooks.Add

intRow = 2

objExcel.Cells(1, 1).Value = "Machine Name"

objExcel.Cells(1, 2).Value = "Current Management Point"

objExcel.Cells(1, 3).Value = "Site Code"

Set Fso = CreateObject("Scripting.FileSystemObject")

Set InputFile = fso.OpenTextFile("MachineList.Txt")

Do While Not (InputFile.atEndOfStream)

strComputer = InputFile.ReadLine

Set objWMIService = GetObject("winmgmts://" & strComputer & "/root/ccm")

Set colItems = objWMIService.ExecQuery("Select * from SMS_Authority")

For Each objItem in colItems

strSiteCode = Replace(objItem.Name, "SMS:", "")

objExcel.Cells(intRow, 1).Value = UCase(strComputer)

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

objExcel.Cells(intRow, 3).Value = strSiteCode

Next

intRow = intRow + 1

loop

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

objExcel.Selection.Interior.ColorIndex = 19

objExcel.Selection.Font.ColorIndex = 11

objExcel.Selection.Font.Bold = True

objExcel.Cells.EntireColumn.AutoFit

Msgbox "Done"

VBS Script To Find A Configuration Manager Collection ID For Specified Collection Name

This VBS Script will allow you to find the collection ID for a specified collection name.

VBS Script:

strComputer = InputBox ("Enter Site Server Name")

strSiteCode = InputBox ("Enter Site Code")

strName = InputBox ("Enter Collection Name")

Set objWMIService = GetObject("winmgmts://" & strComputer & "\root\sms\site_" & strSiteCode)

Set colItems = objWMIService.ExecQuery("Select * From SMS_Collection Where Name = '" & strName & "'")

For Each objItem in colItems

MsgBox "Collection ID: " & objItem.CollectionID _

& Chr(13) & "Collection Name: " & objItem.Name

Next

VBS Script To Send Last Hardware Scan Date Information To Excel

This VBS Script will send last hardware scan information to Excel for your sites resources.

VBS Script:

strServer = InputBox ("Enter Site Server Name")

strDatabase = InputBox ("Enter Three Letter Site Code")

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True

objExcel.Workbooks.Add

intRow = 2

objExcel.Cells(1, 1).Value = "Machine Name"

objExcel.Cells(1, 2).Value = "Resource ID"

objExcel.Cells(1, 3).Value = "Time Stamp"

objExcel.Cells(1, 4).Value = "Scan Date"

Const adOpenStatic = 3

Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")

objConnection.Open "Provider=SQLOLEDB;Data Source =" & strServer & ";" & _

"Trusted_Connection=Yes;Initial Catalog =SMS_" & strDatabase

Set objRecordSet = CreateObject("ADODB.Recordset")

objRecordSet.Open _

" Select SD.Name0, SD.ResourceID, WD.LastHWScan," & _ 

" Convert(VarChar(11), WD.LastHwScan, 109) CD " & _ 

" From v_R_System SD" & _

" Join v_Gs_Workstation_Status WD On SD.ResourceID = WD.ResourceID" _

, objConnection, adOpenStatic, adLockOptimistic

objRecordSet.MoveFirst

Do Until objRecordSet.EOF

objExcel.Cells(intRow, 1).Value = objRecordSet.Fields("Name0").Value

objExcel.Cells(intRow, 2).Value = objRecordSet.Fields("ResourceID").Value

objExcel.Cells(intRow, 3).Value = objRecordSet.Fields("LastHWScan").Value

objExcel.Cells(intRow, 4).Value = objRecordSet.Fields("CD").Value

objRecordSet.MoveNext

intRow = intRow + 1

Loop

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

objExcel.Selection.Font.ColorIndex = 11

objExcel.Selection.Font.Bold = True

objExcel.Cells.EntireColumn.AutoFit

Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)

Set objRange = objExcel.Range("A1")

objRange.Sort objRange,1,,,,,,1

MsgBox "Done"

VBS Script To Send A Remote Machines Local Administrators List To Excel

VBS Script:

strComputer = InputBox ("Enter Machine Name")

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True

objExcel.Workbooks.Add

intRow = 2

objExcel.Cells(1, 1).Value = "Machine Name"

objExcel.Cells(1, 2).Value = "User Name"

Set colGroups = GetObject("WinNT://" & strComputer & "/Administrators")

For Each objUser In colGroups.Members

objExcel.Cells(intRow, 1).Value = UCase(strComputer)

objExcel.Cells(intRow, 2).Value = objUser.Name

intRow = intRow + 1

Next

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

objExcel.Selection.Interior.ColorIndex = 19

objExcel.Selection.Font.ColorIndex = 11

objExcel.Selection.Font.Bold = True

objExcel.Cells.EntireColumn.AutoFit

Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)

Set objRange = objExcel.Range("B1")

objRange.Sort objRange,1,,,,,,1

MsgBox "Done"

VBS Script To Send All Configuration Manager IP Subnet Boundaries To Excel

VBS Script:

strServer = InputBox ("Enter Primary Parent Site Server Name")

strDatabase = InputBox ("Enter Three Letter Site Code")

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True

objExcel.Workbooks.Add

intRow = 2

objExcel.Cells(1, 1).Value = "Site Code"

objExcel.Cells(1, 2).Value = "IP Subnet"

Const adOpenStatic = 3

Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")

objConnection.Open "Provider=SQLOLEDB;Data Source =" & strServer & ";" & _

"Trusted_Connection=Yes;Initial Catalog =SMS_" & strDatabase

Set objRecordSet = CreateObject("ADODB.Recordset")

objRecordSet.Open " Select SiteCode, IpSubnet" & _ 

" From v_SiteBoundary_IPSubnet", objConnection, adOpenStatic, adLockOptimistic

objRecordSet.MoveFirst

Do Until objRecordSet.EOF

objExcel.Cells(intRow, 1).Value = objRecordSet.Fields("SiteCode").Value

objExcel.Cells(intRow, 2).Value = objRecordSet.Fields("IpSubnet").Value

objRecordSet.MoveNext

intRow = intRow + 1

Loop

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

objExcel.Selection.Font.ColorIndex = 11

objExcel.Selection.Font.Bold = True

objExcel.Cells.EntireColumn.AutoFit

Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)

Set objRange = objExcel.Range("A1")

objRange.Sort objRange,1,,,,,,1

MsgBox "Done"

SQL Server Versioning Information

Here you will find SQL Server 6.5 to SQL Server 2008 Version information to January 2009.

SQL Server 6.5

6.50.201 - SQL Server 6.5 Release To Manufacturing (RTM)

6.50.213 - SQL Server 6.5 Service Pack (SP) 1

6.50.240 - SQL Server 6.5 Service Pack (SP) 2

6.50.258 - SQL Server 6.5 Service Pack (SP) 3

6.50.281 - SQL Server 6.5 Service Pack (SP) 4

6.50.415 - SQL Server 6.5 Service Pack (SP) 5

6.50.416 - SQL Server 6.5 Service Pack (SP) 5A

SQL Server 7.0

7.00.623 - SQL Server 7.0 Release To Manufacturing (RTM)

7.00.699 - SQL Server 7.0 Service Pack (SP) 1

7.00.842 - SQL Server 7.0 Service Pack (SP) 2

7.00.961 - SQL Server 7.0 Service Pack (SP) 3

7.00.1063 - SQL Server 7.0 Service Pack (SP) 4

SQL Server 2000

8.00.194 - SQL Server 2000 Release To Manufacturing (RTM)

8.00.384 - SQL Server 2000 Service Pack (SP) 1

8.00.534 - SQL Server 2000 Service Pack (SP) 2

8.00.760 - SQL Server 2000 Service Pack (SP) 3

8.00.2039 - SQL Server 2000 Service Pack (SP) 4

SQL Server 2005

9.00.1399 - SQL Server 2005 Release To Manufacturing (RTM)

9.00.2047 - SQL Server 2005 Service Pack (SP) 1

9.00.3042 - SQL Server 2005 Service Pack (SP) 2

9.00.3042.01 - SQL Server 2005 Service Pack (SP) 2A

9.00.3054 - SQL Server 2005 KB934458 Fix

9.00.3152 - SQL Server 2005 SP2 Cumulative Update 1 

9.00.3175 - SQL Server 2005 SP2 Cumulative Update 2

9.00.3186 - SQL Server 2005 SP2 Cumulative Update 3

9.00.3200 - SQL Server 2005 SP2 Cumulative Update 4

9.00.3215 - SQL Server 2005 SP2 Cumulative Update 5

9.00.3228 - SQL Server 2005 SP2 Cumulative Update 6

9.00.3233 - SQL Server 2005 QFE Security Update

9.00.3239 - SQL Server 2005 SP2 Cumulative Update 7

9.00.3257 - SQL Server 2005 SP2 Cumulative Update 8

9.00.3282 - SQL Server 2005 SP2 Cumulative Update 9

9.00.3294 - SQL Server 2005 SP2 Cumulative Update 10

9.00.3301 - SQL Server 2005 SP2 Cumulative Update 11

9.00.4035 - SQL Server 2005 SP3

9.00.4207 - SQL Server 2005 SP3 Cumulative Update 1

SQL Server 2008

10.00.1600 - SQL Server 2008 Release To Manufacturing (RTM)

10.00.1763 - SQL Server 2008 RTM Cumulative Update 1

10.00.1779 - SQL Server 2008 RTM Cumulative Update 2

10.00.1787 - SQL Server 2008 RTM Cumulative Update 3

By Request VBS Script To Delete All Files From A Specified Folder

VBS Script:

strDirectory = "C:\DirectoryFolderName"

Set FSO = CreateObject("Scripting.FileSystemObject")

Set objFolder = fso.GetFolder(strDirectory)

For Each objFile in objFolder.Files

objFile.Delete True

Next

MsgBox "Done"