Don Hite at myITforum.com

VBS Script To Query The SMS Database For Machines With A Specified Product Installed

This VBS script will take a ConfigMgr 2007 or SMS 2003 site server name and site code from input dialog boxes and will write all of the machines that have a specified product installed to an excel spreadsheet sorted alphabetically by machine name.

Note: Change the strProductName = "Microsoft .NET Framework 3.0" to reflect the Product name you wish to query for.

VBS Script:

strServer = InputBox ("Enter Site Server Name")

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

strProductName = "Microsoft .NET Framework 3.0"

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"

objExcel.Cells(1, 3).Value = "Product Name"

objExcel.Cells(1, 4).Value = "Product Version"

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.User_Name0, SP.ProductName, SP.ProductVersion" & _ 

" From vSMS_G_System_SoftwareProduct SP" & _

" Join System_Disc SD on SD.ItemKey = SP.ClientId" & _

" Where SP.ProductName = '" & strProductName & "' " _

, 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("User_Name0").Value

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

objExcel.Cells(intRow, 4).Value = objRecordSet.Fields("ProductVersion").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 Enumerate MP3 Files In A Specified Media Folder And Send To Excel

This is a VBS script that I wrote to allow me enumerate the MP3 files in my Windows Media Player C:\Documents and Settings\Don\My Documents\My Music folder.

The script will send the following information to an excel spreadsheet: Track, Title, Artist, Album, Size and Length.

VBS Script:

Set objShell = CreateObject("Shell.Application")

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

If objFolder Is Nothing Then

Wscript.Quit

Else

Set objFolderItem = objFolder.Self

objPath = objFolderItem.Path

End If

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True

objExcel.Workbooks.Add

intRow = 2

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

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

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

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

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

objExcel.Cells(1, 6).Value = "Length"

Set objShell = CreateObject ("Shell.Application")

For Each strFileName in objFolder.Items

objExcel.Cells(intRow, 1).Value = objFolder.GetDetailsOf(strFileName, 20) 'Track

objExcel.Cells(intRow, 2).Value = objFolder.GetDetailsOf(strFileName, 0) 'Title

objExcel.Cells(intRow, 3).Value = objFolder.GetDetailsOf(strFileName, 10) 'Artist

objExcel.Cells(intRow, 4).Value = objFolder.GetDetailsOf(strFileName, 18) 'Ablum

objExcel.Cells(intRow, 5).Value = objFolder.GetDetailsOf(strFileName, 8) 'Size

objExcel.Cells(intRow, 6).Value = objFolder.GetDetailsOf(strFileName, 22) 'Length

intRow = intRow + 1

Next

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

objExcel.Selection.Interior.ColorIndex = 19

objExcel.Selection.Font.ColorIndex = 11

objExcel.Selection.Font.Bold = True

objExcel.Cells.EntireColumn.AutoFit

MsgBox "Done"

SQL Query To See When Collections Were Last Updated

This SQL Query will list all of the collections on the site server from which the query is executed on and will write the collection ID, Collection Name and the time stamp for when the collection was last updated.

SQL Query:

Select

CC.CollectionID,

CN.CollectionName,

Convert(VarChar(10), CC.TimeUpdated, 101) 'Last Updated'

From Collection_MemberChg_Notif CC

Join Collections CN on CC.CollectionID = CN.SiteID

Order By CollectionName

SQL Query To Get Members Of A Specified Collection

This SQL Query will retrieve the machine names from a specified collection.

SQL Query:

Select

Members.Name

'Collection Members:'

From CollectionMembers Members

Join Collections Coll on Members.SiteID = Coll.SiteID

Where Coll.CollectionName = 'All Systems'

To see a list of all the available Collections use this query and then change the 'All Systems' collection name as needed:

Select CollectionName From Collections

Configuring Windows Server 2008 Remote Desktop Administration For Windows XP

When you install Windows server 2008 and attempt to connect to it via Remote Desktop from a Windows XP machine the connection may fail yet you can successfully connect to it from a Windows Vista machine.

This is because the configured option is set to use “Network Level Authentication” and since it is more secure you will not be able to connect to the server from a Windows XP machine.

Follow the steps below to initially set up your servers Remote Desktop to allow Windows XP machines to connect and manage it.

1. Open the Server Manager.

2. At the Server Summary task pane select “Configure Remote Desktop”.

3. From the Systems Properties dialog box in the “Remote Desktop” leaf  select “Allow connections from computers running any version of one of Remote Desktop (Less secure)”

4. Select “OK” to set the changes and close the Server Manager.

Note: Windows XP uses Remote Desktop Connection 5.x and Vista uses 6.x with Network Level Authentication (NLA).

Side Tracked

In the early days of the railroad a single track was laid from one point to the other until it finally reached across the United States linking the North, South, East and West together. In case two trains happened to be heading in opposite directions on the same track the problem was alleviated by creating short pieces of railroad track called sidings. The sidings allowed one train to ‘pull over’ onto the siding or side track to wait for the other train to pass. So one train was side tracked to let the other pass.

Today we use the term Sidetracked to imply that we have deviated from a main issue or course or have been delayed.

Internet Information Services 7.0 Manager Download

Internet Information Services (IIS) 7.0 Manager is an administration User Interface (UI) that provides users and administrators with a way to remotely manage IIS 7.0 Windows 2008 Server servers.

The download allows you to manage multiple IIS 7.0 servers remotely as a standalone install meaning that you do not have to have and IIS server on your client system to use the application.

Supported Operating Systems:

Windows Server 2003 Service Pack 1

Windows Vista Service Pack 1

Windows XP Service Pack 2

Note: You must have the Microsoft .NET Framework version 2.0 or greater installed.

IinetMgr Download:

http://www.microsoft.com/DownLoads/details.aspx?familyid=32C54C37-7530-4FC0-BD20-177A3E5330B7&displaylang=en

Welcome Sourceforge’s Roger Zander To myITforum

Please stop by and welcome Roger Zander to myITforum. Roger is the creator of many great SMS and Configuration Manager utilities designed to ease the burdens of admins in the field including the following: 

Screen Lock

Secure Autologon

SMS 2003 Adv.Client local policy import

SMS 2003 Offline Hardware Inventory

SMS 2003 Software Request Web Form

SMS Client Center

SMS Client Center Web GUI

SMS CloneDP

SMS Collection Commander

SMS OSD Program Import

SMS Package Dependency Viewer

SMS Site Settings tweak

SMS/SCCM Peer2Peer AddOn

SMS2003 Object Backup

Roger Zander Sourceforge Tools And Utilities

http://myitforum.com/cs2/blogs/dhite/archive/2008/01/15/roger-zander-sourceforge-tools-and-utilities.aspx

Roger Zander At myITforum

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

Welcome Jason Sandys To The myITforum Blogs

Drop by Jason Sandys new myITforum Blog page where he will be migrating some of his previous posts from his http://ihaveablog.wordpress.com Blog page.

Jason has written some great post on the following: Active Directory, Administration, Configuration Manager, Exchange, Hyper-V, Office, Operations Manager, Security, Server 2008 and SQL Server. He is also actively helping folks in the myITforum Forums

I Have A Blog, And I Must Scream:

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

Configuration Manager Last Logon User Name To Full Name Unspecified WQL Scripting

Here you will find examples of how to create Configuration Manager <unspecified> Console WQL queries to return users full names as well as their Login ID name.

All Users And All Machines

Select

S.Name,

U.UserName,

U.FullUserName

From SMS_R_System S, SMS_R_User U

Where S.LastLogonUserName = U.UserName

Prompts For Full Name

Select

S.Name,

U.UserName,

U.FullUserName

From SMS_R_System S, SMS_R_User U

Where S.LastLogonUserName = U.UserName

And FullUserName = ##PRM:SMS_R_User.FullUserName##

Prompts For Logon Name

Select

S.Name,

U.UserName,

U.FullUserName

From SMS_R_System S, SMS_R_User U

Where S.LastLogonUserName = U.UserName

And UserName = ##PRM:SMS_R_User.UserName##

Prompts For Machine Name

Select

S.Name,

U.UserName,

U.FullUserName

From SMS_R_System S, SMS_R_User U

Where S.LastLogonUserName = U.UserName

And Name = ##PRM:SMS_R_System.Name##

10 Things To Do During a Boring Meeting

1. Bring a water gun and squirt the speaker when his back is turned.
2. Get the others on your side of the table to do the wave.
3. When the speaker asks a question raise your hand and when called upon point to someone else and say: "He knows."  (Pick a different person each time).
4. Bring an old loud typewriter and use it to take notes.
5. Get up to go to the bathroom five or six times and change clothes every time.
6. Raise your hand and ask when the movie is going to start.
7. Bring a flash camera and take pictures every few minutes.
8. Bring a light bulb and hold it over your head whenever you have the answer to a question.
9. Every time the speaker pauses for any length of time ask “Is that all you have?”
10. Two words: American Gladiators.

VBS Script To Retrieve All Systems with Hardware Inventory Collected And Send To Excel

This VBS Script will take a ConfigMgr 2007 or SMS 2003 site server name and site code from input dialog boxes and write the All Systems with Hardware Inventory Collected information to an excel spreadsheet sorted alphabetically.

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 = "Domain"

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

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 Disc.Name0, Data.Domain0, Data.TimeKey" & _

" From System_Disc Disc" & _

" Join System_Data Data on Data.MachineID = Disc.ItemKey" _

, 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("Domain0").Value

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

objRecordSet.MoveNext

intRow = intRow + 1

Loop

objExcel.Range("A1:C1").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 Verify Is A User Is A Member Of A Specified Domain Group

This VBS Script will allow you to determine if a specified user is a member of a specified domain group.

VBS Script:

strDomain = InputBox ("Enter Domain Name")

strGroup = InputBox ("Enter Group Name")

strUser = InputBox ("Enter User Name")

Set objUser = GetObject("WinNT://" & strDomain & "/" & strUser & ",User")

Set objGroup = GetObject("WinNT://" & strDomain & "/" & strGroup & ",Group")

If objGroup.IsMember(objUser.AdsPath) Then

MsgBox UCase(strUser) & " Is A Member Of " & UCase(StrDomain) & "\" & UCase(strGroup) 

Else

MsgBox UCase(strUser) & " Is A Not Member Of " & UCase(StrDomain) & "\" & UCase(strGroup)

End If

VBS Script To List Members Of A Specified NT Or AD Domain Group To Excel

This VBS script will allow you to send the members of a hard coded NT or Active Directory (AD) domain to an excel spreadsheet.

VBS Script:

strDomain = InputBox ("Enter Domain Name")

strGroup = "GroupName"

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True

objExcel.Workbooks.Add

intRow = 2

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

Set objGroup = GetObject("WinNT://" & strDomain & "/" & strGroup)

Set strMembers = objGroup.Members

For Each strMember In strMembers

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

intRow = intRow + 1

Next

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

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

MsgBox "Done"

SQL Query Get User Information From A Specified OU

This SQL query will return the following information from a specified Organizational Unit (OU): Machine name, User Name, Full name, Users OU and its Subnet.

SQL Query:

Select Distinct

CS.Name0 'Machine Name',

CS.UserName0 'User Name',

RU.Full_User_Name0 'Full Name',

UOU.User_OU_Name0 'Users OU',

RA.IP_Subnets0  'Subnet'

From v_Gs_Computer_System CS 

Join v_RA_System_IPSubnets RA on RA.ResourceID = CS.ResourceID

Join v_R_User RU on RU.Unique_User_Name0 = CS.UserName0

Join v_RA_User_UserOUName UOU on UOU.ResourceID = RU.ResourceID

Where UOU.User_OU_Name0 = 'DomainName.COM/OuName'

Order by  CS.Name0, CS.Username0, RU.Full_User_Name0, RA.IP_Subnets0

Thanks To Janis K for the suggestion.