Don Hite

VBS Script To Retrieve SMS Executive Service Information From A List Of Machines

This VBS script will read a text file called MachineList.Txt and will write the SMS Executive services information for the site servers to an excel spreadsheet. If the service state is stopped the results will be written in red.

VBS Script:

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True

objExcel.Workbooks.Add

intRow = 2

Set Fso = CreateObject("Scripting.FileSystemObject")

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

Do While Not (InputFile.atEndOfStream)

strComputer = InputFile.ReadLine

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

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

objExcel.Cells(1, 3).Value = "Current State"

servicename = "SMS_Executive"

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

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

For Each objItem in colItems

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

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

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

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

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

End If

intRow = intRow + 1

Next

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 Browse For Folder And Send Sub Folders Name And Size To Excel

This VBS Script will allow to browse for a directory folder and will write the subfolders name and size to an excel spreadsheet.

VBS Script:

strComputer = "."

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

Set objShell = CreateObject("Shell.Application")

Set objFolder = objShell.BrowseForFolder (0, "Select Folder:", (0))

If objFolder Is Nothing Then

Wscript.Quit

Else

Set objFolderItem = objFolder.Self

objPath = objFolderItem.Path

End If

Set objFolderItem = objWMIService.ExecQuery _

("Associators of {Win32_Directory.Name='" & strFolderName & "'} " _

& "Where AssocClass = Win32_Subdirectory " _

& "ResultRole = PartComponent")

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True

objExcel.Workbooks.Add

intRow = 2

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

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

Set objShell = CreateObject ("Shell.Application")

For Each strFileName in objFolder.Items

objExcel.Cells(intRow, 1).Value = objFolder.GetDetailsOf(strFileName, 0)

objExcel.Cells(intRow, 2).Value = objFolder.GetDetailsOf(strFileName, 8)

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

MsgBox "Done"

VBS Script Find Clients Not Reporting A Serial Number To Excel

This VBS script will take a site server name and site code via input dialog boxes and will send the machine names to Excel that do not have a serial number present in the SMS database.

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 = "Serial Number"

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 SD.Name0, SN.SerialNumber00" & _

" From v_R_System SD" & _

" Join System_Enclosure_Data SN  On SN.MachineID = SD.ResourceID" & _

" Where SD.Client0 = 1" & _

" And SN.SerialNumber00 = 'Not Available' Or SN.SerialNumber00 Is Null", 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("SerialNumber00").Value

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

objExcel.Cells(intRow, 2).Value = "NULL"

End If

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 Query To List Add And Remove Programs For A Specified Machine Excluding Updates And Hot Fixes

This SQL Query will allow you to list the installed applications registered in the Add and Remove Applications applet for a specified machine excluding those applications such as Security Updates and Hot fixes and other system updates.

SQL Query:

Select

AP.DisplayName0 'Display Name',

IsNull(AP.Version0, '') Version

From v_R_System SD

Join v_Add_Remove_Programs AP on SD.ResourceID = AP.ResourceID

Where SD.Name0 = 'MachineName'

And AP.DisplayName0 Not Like '%Security Update%'

And AP.DisplayName0 Not Like '%Update For%'

And AP.DisplayName0 Not Like '%Hotfix%'

Order By AP.DisplayName0

A Maverick

Samuel Augustus Maverick (1803– 1870) was a Texas cattleman from whom the term Maverick originated. In the west it was a custom and still is to some extent to brand your cattle so that you know they are yours. Because he thought that branding was cruel he refused to brand his cattle and he would scour Texas for unbranded cattle and pen them up and claim they were his even if they we not.

Unbranded cattle were traditionally considered the property of the first person who found and branded them.  The term was originally meant to apply to an unbranded cow which was known as “A Maverick“ but today is usually applies to someone who plays by their own rules.

Bits And Bytes Conversion Tables

In computer systems a byte is a binary unit of measurement used to refer to disk storage space in a hard disk drive or Random Access Memory (RAM) memory on computer systems.

It takes 8 Bits to create one Byte where a Bit is also a binary digit consisting of a value of 0 or 1. For example 10101010, 00000000, 11111111 are all 8 bits long and form 1 Byte. Put simply a Byte is a collection of Bits.

The chart directly below will provide you with the conversions and the second chart shows you to abbreviations for each.

Abbreviations

myITforum Shared User Group Material

The myITforum shared user group materials page gives you the tools and access needed to “Upload and download shared presentations (PPTs), videos, meeting notes, etc. — basically anything that is required to host and hold a User Group meeting.  Share your User Group materials and others will follow, giving you ideas and information for your own next meeting.”

If you are a user group presenter I urge you to upload your content here for the benefit of others. If you are a user group member looking for content stop by here and see what topics others are giving presentations on.

myITforum Shared User Group Material

http://myitforum.com/cs2/files/folders/ugfodder/default.aspx

Fabio Cozzolino’s SQL Script Generator 0.1.3123

This is a freeware download of the SQL Script Generator by Fabio Cozzolino.

SQL Script Generator is a tool designed to create or generate Insert and Update statements into SQL scripts. With SQL Script Generator you can create scripts to copy data from one database to another.

Screen Print:

http://www.softpedia.com/screenshots/SQL-Script-Generator_1.png

SQL Script Generator 0.1.3123:

http://www.codeplex.com/sqlgen/Release/ProjectReleases.aspx?ReleaseId=15458

Using ConfigMgr 2007 To Capture Microsoft Windows Media Player Media Files

Software Inventory in ConfigMgr 2007 as well as in the previous versions - SMS 2.0 and SMS 2003 - can be harnessed to capture application executable (Exe) file extensions by default. However you can always add file extensions such as Outlook Personal Folder Files (PST) files, Initialization and Configuration Files (INI) files, WinRAR Compressed Archive (RAR) files and Compressed Archive (ZIP) files just to name a few to see what is installed on client resources in your infrastructure environment. 

Any time you have clients with Microsoft Windows Media Player installed and they have access to the internet or removable media chances are that they will begin using the resources at their disposal even if it is frowned upon. Listening to music on your computer is not distractive or a big time waster and it is no different than listening to a radio in your office and neither one are uncommon today. Watching movies like Juno or watching the antics of Harold and Kumar the other hand can be disruptive and a time waster when employees are on the clock.

From an infrastructure standpoint both music and movies can affect the environment especially where shared resources such as users home shares and departmental shares are concerned. The problem is not so much that the employees are wasting time and are not being as productive as they should be. The real problem is that users seem to have the need to keep and store their digital media on their local machines or on shared resources and therein lays the problem. This can even cost the company money when they have to expand the disk space or buy additional disks when the resource run low or out of physical disk space.

To find machines and users that are storing Windows Media Player media files on shares or their local machines use the chart below and add the file type file extensions to your Software Inventory Client Agent Inventory Collection.     

Microsoft Windows Media Player File Extensions