Don Hite at myITforum.com

VBS Script To Find Resources With Duplicate GUIDs And Send To Excel

This VBS script will allow you to enter a Site server name and Site code into input dialog boxes and will then locate all machines that have a shared or duplicate Globally Unique Identifier (GUID) and send the old and new machine names to excel.

For additional information see the link at the end of this post.

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 = "Old Name"

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

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 Distinct SH.Name0 Old, SD.Name0 New" & _

" From System_Data SD" & _

" Join System_Hist SH on SH.MachineId = SD.MachineId" & _

" And SD.Name0 Not Like SH.Name0" , objConnection, adOpenStatic, adLockOptimistic

Do Until objRecordSet.EOF

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

objExcel.Cells(intRow, 2).Value = objRecordSet.Fields("New").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"

Managing Duplicate Globally Unique Identifiers in Systems Management Server 2003

http://www.microsoft.com/downloads/details.aspx?FamilyID=AAF6F10D-BD84-405E-9AF3-B48CED1D7F2D&displaylang=en

VBS Script To Export SMS WQL System Resource Queries To Microsoft Word

This Vbs script will take an SMS site server name and site code from an input box and then enumerate all of the System queries on that server and read their corresponding WQL Queries. It will then write them to a Microsoft Word document that you can save for future reference.

VBS Script:

strComputer = InputBox ("Enter Site Server Name")

strSiteCode = InputBox("Enter Site Code")

Set objWord = CreateObject("Word.Application")

objWord.Visible = True

Const wdAlignParagraphCenter = 1

Const wdAlignParagraphLeft = 0 

Const wdColorGreen = 32768

Const wdColorBlue = 16711680

Const wdColorBlack = 0

Const wdColorGray = 15132390

Set objDoc = objWord.Documents.Add()

Set objSelection = objWord.Selection

objSelection.Font.Bold = True

objSelection.Font.Color = wdColorGreen

objSelection.ParagraphFormat.Alignment = wdAlignParagraphCenter

objSelection.TypeText "SMS WQL System Resource Queries For " & UCase(strComputer)

objSelection.Font.Bold = False

objSelection.TypeParagraph()

objSelection.TypeText "Report Created: " & Date

objSelection.TypeParagraph()

objSelection.TypeParagraph()

objSelection.ParagraphFormat.Alignment = wdAlignParagraphLeft

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

Set colItems = objWMIService.ExecQuery("Select * from SMS_Query Where TargetClassName = 'SMS_R_System'")

For Each objItem in colItems

objSelection.ParagraphFormat.Alignment = wdAlignParagraphCenter

objSelection.Font.Color = wdColorBlue

objSelection.Font.Bold = True

objSelection.ParagraphFormat.Shading.BackgroundPatternColor = wdColorGray

objSelection.TypeText objItem.Name

objSelection.TypeParagraph()

objSelection.TypeParagraph()

objSelection.ParagraphFormat.Alignment = wdAlignParagraphLeft

objSelection.Font.Color = wdColorBlack

objSelection.Font.Bold = False

objSelection.TypeText objItem.Expression

objSelection.TypeParagraph()

objSelection.TypeParagraph()

Next

objSelection.ParagraphFormat.Alignment = wdAlignParagraphCenter

objSelection.Font.Color = wdColorGreen

objSelection.TypeText "Created by Don Hite For myITforum.Com"

objSelection.TypeParagraph()

VBS Script To Export SMS Queries To Microsoft Word

http://myitforum.com/cs2/blogs/dhite/archive/2008/02/24/vbs-script-to-export-sms-queries-to-microsoft-word.aspx

VBS Script to Browse For A Text File And Convert It From Comma To Tab Delimited

This VBS script will allow you browse for a text file and then convert it from a comma delimited text file to a tab delimited text file. You can also change the Replace string character from the comma to another value such as = as needed.

You can also take the script one step further by changing the comma to a string value and the vbTab to yet another sting value by using the syntax here: 

strNewFile = Replace(strTextCharacter, "Old_String", "New_String",)

VBS Script:

Set objDialog = CreateObject("UserAccounts.CommonDialog")

objDialog.Filter = "Text Files|*.Txt"

objDialog.InitialDir = "C:\"

intResult = objDialog.ShowOpen

Set WshShell = WScript.CreateObject("WScript.Shell")

Const ForReading = 1

Const ForWriting = 2

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objFile = objFSO.OpenTextFile(objDialog.FileName, ForReading)

strTextCharacter = objFile.ReadAll

objFile.Close

strNewFile = Replace(strTextCharacter, ",", vbTab)

Set objFile = objFSO.OpenTextFile(objDialog.FileName, ForWriting)

objFile.WriteLine strNewFile

objFile.Close

MsgBox "Done"

SQL Query To Find And Count Software File And Product Information For A Specified Executable

This SQL query will allow you to find and count software file and product information for a specified executable.

SQL Query:

Select

SF.FileId 'File ID',

SF.ProductId 'Product ID',

SF.FileName 'File Name',

SF.FileDescription 'File Description',

SF.FileSize 'File Size',

SF.FileVersion 'File Version',

SP.ProductName 'Product Name',

SP.ProductVersion 'Product Version',

SP.CompanyName 'Company Name',

Count(SF.FileName) 'Installed Count'

From System_DISC SD

Join vSMS_G_System_SoftwareFile SF on SF.ClientId = SD.ItemKey 

Join vSMS_G_System_SoftwareProduct SP on SP.ClientId = SD.ItemKey  

Where SF.ProductId = SP.ProductId

And SF.FileName = 'FileName.Exe'

Group by SF.FileId,SF.ProductId,SF.FileName,SF.FileDescription,

SF.FileSize,SF.FileVersion,SP.ProductName,SP.ProductVersion,SP.CompanyName

Order By SF.FileId ASC

List Total Number Of Records In The Software File Table

This query will return the number of software file records in the current database. Just for fun execute it to see just how many unique file executables you have in your database and be prepared for a shock

SQL Query:

Select Count(*) as 'Total Number Of Records'

From SoftwareFile

Going To See A Man About A Horse

When someone states that they are going to go and see a man about a horse (Or Dog) it is meant to imply that they are going to leave your presence and want to conceal the true reason for their departure.

For example if someone is going out for a drink\smoke or going to go to the bathroom they often say that they are going to see a man about a horse.

Microsoft Deployment Toolkit 2008 Now Available

Microsoft has released its successor to the Business Desktop Deployment (BDD) 2007 solution accelerator called the Microsoft Deployment Toolkit (MDT) 2008. This update now includes support for Windows Vista Service Pack (SP1) and Windows Server 2008.

Microsoft Deployment Toolkit 2008

http://go.microsoft.com/fwlink/?LinkId=112437

PssDiag Diagnostics Manager Utility For SQL Server 7.0 And SQL 2000

The PssDiag Data collection utility is a Microsoft Product Support Services (PSS) diagnostic data collector tool for Microsoft SQL Server used by PSS engineers to collect diagnostic data from end user installations and can also be used by end-users to troubleshoot and monitor their own SQL Server installations.

To run simply unzip the files to a directory folder of your choice and then run the DiagConfig.Exe. At the Target Machine dialog box enter the name of the SQL server you want to diagnose into the “Machine” text box and select “OK” to begin.

This will bring up the Diag Manager graphical user interface. Then click on the “Start” button in the upper right hand side of the dialog and this will bring up the Startup Parameters interface where you can select the appropriate radial buttons as required. When you have competed your selections click OK to begin the diagnostics.

PssDiag Diagnostics Download

http://www.microsoft.com/downloads/details.aspx?familyid=5564386A-28C2-4483-8293-76FFF67B9EB3&displaylang=en

Machines That Have Been Removed From ConfigMgr 2007 Reappear

In ConfigMgr 2007 you may find that when you manually or programmatically remove a machine that has been either decommissioned or reimaged from the “All Systems” collection that resource name reappears in the collection(s) during the subsequent Active Directory Systems Discovery process.

This functionality is by default because ConfigMgr has determined that the NetBIOS name is still an active member of the domain(s) you have configured as a ConfigMgr domain(s) even though to your knowledge the machine is a nonexistent resource.

Active Directory Systems Discovery determines active computer resources by looking for machine or computer accounts in the Active Directory Domain Services. When it finds a new potential client resource it attempts to ping the computer and if a ping request is confirmed as having a successful IP Address Name Resolution a Data Discovery Record (DDR) is created for the computer resource and it is “Discovered” and this is when the machine reappears in your ConfigMgr database.

To combat this issue you must remove the computer name(s) from the Active Directory Users And Computers application. Then you can remove the resource once again from your site server. After this has been accomplished the resource should not reappear during the next Active Directory Systems Discovery process interval

Note: You should also enable and configure DNS Scavenging on your DNS servers as well to ensure that the issue does not reemerge. For additional information see the post below:

DNS Aging And Scavenging

http://myitforum.com/cs2/blogs/dhite/archive/2007/09/09/dns-aging-and-scavenging.aspx

Client Health Solutions Blog Post

Client health monitoring is not new to SMS or ConFigMgr however Microsoft has provided us with tools to ease this responsibility. There is the SMS 2003 Client Health Monitoring Tool as well as other means in which you can ensure that your clients are responding and functioning as expected.

Paul Thomsen has written a Blog post on client health solutions that are worth looking at found at the link below. He discusses some of the solutions that are available and if you have anything to add of use for client health monitoring please post your comments or solutions to his post or here.

Client Health Solutions

http://myitforum.com/cs2/blogs/pthomsen/archive/2008/03/23/114142.aspx

VBS Script To Open A Remote Clients CcmSetup Folder

This by request VBS script will allow you to enter a remote client machine name and will open the clients  CcmSetup directory folder.

VBS Script:

strcomputer = InputBox ("Enter Client Machine Name")

Set WshShell = WScript.CreateObject("WScript.Shell")

WshShell.Run "Explorer \\" & strComputer & "\Admin$\System32\CcmSetup"

WScript.Quit

Employee Evaluations

• A gross ignoramus -- 144 times worse than an ordinary ignoramus.
• Any dumber and he would have to be watered twice a week.
• Donated his brain to science before he was quite finished using it.
• Fell out of his family tree.
• Got into the gene pool while the lifeguard wasn't looking.
• I would not allow this man to breed.
• If you stand close enough to him, you can hear the ocean.
• Since my last report, this employee has reached rock bottom and shows signs of starting to dig.
• This associate is really not so much of a has-been, but more of a definitely won't be.
• This employee is depriving a village somewhere of an idiot.
• This employee should go far -- the sooner he starts, the better.
• When he opens his mouth, it seems that this is only to change whichever foot was previously in there.
• Works well when under constant supervision and cornered like a rat in a trap.

Arthur C. Clarke Quotes

Sir Arthur Charles Clarke  (1917 – 2008) British science fiction author.

• Any teacher that can be replaced by a machine should be!
• How inappropriate to call this planet Earth when it is quite clearly Ocean.
• I don't believe in astrology; I'm a Sagittarius and we're skeptical.
• I don't pretend we have all the answers. But the questions are certainly worth thinking about.
• I now spend a good part of my day dreaming of times past, present and future.
• If we have learned one thing from the history of invention and discovery, it is that, in the long run — and often in the short one — the most daring prophecies seem laughably conservative.
• I'm sure the universe is full of intelligent life. It's just been too intelligent to come here.
• It has yet to be proven that intelligence has any survival value.
• Perhaps it is better to be un-sane and happy, than sane and un-happy. But it is the best of all to be sane and happy.
• Reading computer manuals without the hardware is as frustrating as reading manuals without the software.
• Sometimes I think we're alone in the universe, and sometimes I think we're not. In either case the idea is quite staggering.
• The Information Age offers much to mankind, and I would like to think that we will rise to the challenges it presents
• The limits of the possible can only be defined by going beyond them into the impossible.
• We should always be prepared for future technologies, because otherwise they will come along and clobber us.
• We should be less concerned about adding years to life, and more about adding life to years.

VBS Script To Retrieve A Specified Directories Files And Last Accessed Date And Send To Excel

This VBS script will allow you to enter a Directory Folder name and will list all of the files in the directory folder along with their last accessed date timestamp.

VBS Script:

strDirectoryFolder = InputBox ("Enter Directory Folder Name")

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True

objExcel.Workbooks.Add

intRow = 2

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

objExcel.Cells(1, 2).Value = "Last Accessed"

Set Fso = CreateObject("Scripting.FileSystemObject")

Set objDirectory = Fso.GetFolder(strDirectoryFolder)

For Each objFile in objDirectory.Files

objExcel.Cells(intRow, 1).Value = objFile.Name

objExcel.Cells(intRow, 2).Value = objFile.DateLastAccessed

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("A2")

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

MsgBox "Done"

VBS Script To Locate Files On A Drive With A Specified File Extension And Write To Excel

This VBS script will allow you to enter a machine name, drive letter and file extension to search for and write the results to an excel spreadsheet. It will capture the File name, File size, File path, Creation date and last accessed date.

The script can be used to find Mp3 files on a remote machine or find all of the Outlook Pst files on a user file share.

VBS Script:

strComputer = InputBox ("Enter Machine Name")

strDrive = InputBox ("Enter Drive Letter")

strExtension = InputBox ("Enter File Extension")

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True

objExcel.Workbooks.Add

intRow = 2

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

objExcel.Cells(1, 2).Value = "File Size"

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

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

objExcel.Cells(1, 5).Value = "Last Accessed"

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

Set colFiles = objWMIService.ExecQuery _

("Select * from CIM_DataFile Where Drive = '" & strDrive & "' And Extension = '" & strExtension & "'")

For Each objItem in colFiles

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

objExcel.Cells(intRow, 2).Value = FormatNumber(objItem.FileSize/1024/1024,1) & " MB"

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

objExcel.Cells(intRow, 4).Value = ConvWbemTime(objItem.CreationDate)

objExcel.Cells(intRow, 5).Value = ConvWbemTime(objItem.LastAccessed)

intRow = intRow + 1

Next

Function ConvWbemTime(IntervalFormat)

sMonth = mid(IntervalFormat,5,2)

sDay = mid(IntervalFormat,7,2)

sYear = mid(IntervalFormat,1,4)

sHour = mid(IntervalFormat,9,2)

sMinutes = mid(IntervalFormat,11,2)

sSeconds = mid(IntervalFormat,13,2)

ConvWbemTime =  sMonth & "-" & sDay & "-" & sYear & " " & sHour & ":" & sMinutes & ":" & sSeconds

End Function

objExcel.Range("A1:E1").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("A2")

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

MsgBox "Done"