Don Hite at myITforum.com

Configuration Manager Report To Display Package Information For A Specified Package Server

This SQL query was developed to display package information for a specified package server.

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

Name: Display Package Information For A Specified Package Server

Category: Software Distribution - Packages

Comment: This Report Will Display Package Information For A Specified Package Server.

Prompt Properties:

Name: Variable

Prompt Text: Package Server Name

Check “Provide a SQL statement” and enter the following:

Select Distinct

PkgServer

From v_PackageStatus

Order By PkgServer

Else

Select Distinct

PkgServer

From v_PackageStatus

Where PkgServer like @__filterwildcard

Order By PkgServer

End

SQL Query:

Select

vPS.PkgServer Server,

PKG.PackageId ID,

PKG.Name Name,

PKG.Version,

PKG.Manufacturer,

PKG.Description,

PKG.PkgSourcePath Path

From v_Package PKG

Join v_PackageStatus vPS On PKG.PackageID = vPS.PackageID

Where vPS.PkgServer like @Variable

Configuration Manager Report To List Collections A Specified Machine Is A Member Of

This SQL query was developed to prompt you for a machine name and will display all of the collections that the specified machine is a member of. 

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

Name: List Collections A Specified Machine Is A Member Of

Category: Software Distribution - Collections

Comment: This Report Will Prompt You For A Machine Name And Will Display The Collections The Machine Is A Member Of.

Prompt Properties:

Name: Variable

Prompt Text: Machine Name

Check “Provide a SQL statement” and enter the following:

Begin If (@__FilterWildcard = '')

Select Distinct Sys.Name0

From v_R_System Sys

Order By Sys.Name0

Else

Select Distinct Sys.Name0

From v_R_System Sys

Where Sys.Name0 Like @__FilterWildcard

Order By Sys.Name0

End

SQL Query:

Select

vC.Name 'Collection Name',

vC.CollectionID 'Collection ID'

From v_FullCollectionMembership vF,

v_Collection vC

Where vC.CollectionID = vF.CollectionID

And vF.Name = @Variable

Configuration Manager Report To Prompt For Version And Find Machines With Adobe Flash Player Installed

This SQL query was developed to prompt you to select an Adobe Flash Player version number and will in turn enumerate or list any machines with thge specified Adobe Flash Player version installed.

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

Name: Find Machines With Adobe Flash Player - Prompts For Version

Category: Software - Files

Comment: This Report Will Prompt You To Select An Adobe Flash Player Version Number And Will Enumerate Machines With Adobe Flash Player installed.

Prompt Properties:

Name: Variable

Prompt Text: Select Adobe Flash Player Version Number

Check “Provide a SQL statement” and enter the following:

Select Distinct Version0

From v_Add_Remove_Programs

Where DisplayName0 Like 'Adobe Flash Player%'

SQL Query:

Select

vRS.Name0 'Machine Name',

vApps.DisplayName0,

vApps.Publisher0,

vApps.Version0

From v_R_System vRS

Join v_Add_Remove_Programs vApps On vRS.ResourceID = vApps.ResourceID

Where vApps.DisplayName0 Like 'Adobe Flash Player%'

And vApps.Version0 = @Variable

Configuration Manager Report To Display Failed Advertisements From The Last Thirty Days

This SQL query was developed to report and display all of the failed advertisements from the last thirty days.

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

Name: Display Failed Advertisements From The Last Thirty Days

Category: Software Distribution - Advertisements

Comment: This Report Will Display The Failed Advertisements From The Last Thirty Days.

SQL Query:

Select

Count(*) 'Advertisement Count',

vA.AdvertisementName 'Advertisement Name',

vCAS.AdvertisementID 'Advertisement ID',

vCAS.LastStatusTime Updated

From v_ClientAdvertisementStatus vCAS

Join v_Advertisement vA On vA.AdvertisementID = vCAS.AdvertisementID

Where vCAS.LastStateName = 'Failed'

And (vCAS.LastStatusTime > DateAdd(D, - 30, GetDate()))

Group By vA.AdvertisementName, vCAS.AdvertisementID, vCAS.LastStatusTime

Configuration Manager Report To Retrieve The Last Logon User Name And Full Name For A Specified Machine

This SQL query was developed as a Report to retrieve the last logon user name and full name for a specified machine.

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

Name: Last Logon User Information For A Specified Machine

Category: Users

Comment: This Report Will Retrieve The Last Logon User Name And Full Name For A Specified Machine.

Prompt Properties:

Name: Variable

Prompt Text: Select Machine Name

Check “Provide a SQL statement” and enter the following:

Select Netbios_Name0

From v_R_System

SQL Query:

Select

vRS.Name0 'Machine Name',

vRS.User_Name0 'Login Name',

vRU.Full_User_Name0 'Full Name'

From v_R_System vRS

Join v_R_User vRU On vRS.User_Name0 = vRU.User_Name0

Where vRS.Name0 like @Variable

Configuration Manager Report To Review Successfully Completed Advertisements From The Last Thirty Days

This SQL query was developed to display the successfully completed advertisements from the last thirty days.

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

Name: Successfully Completed Advertisements From The Last Thirty Days

Category: Software Distribution - Advertisements

Comment: This Report Will Display The Successfully Completed Advertisements From The Last Thirty Days.

SQL Query:

Select

Count(*) 'Advertisement Count',

vA.AdvertisementName 'Advertisement Name',

vCAS.AdvertisementID 'Advertisement ID',

vCAS.LastStatusTime Updated

From v_ClientAdvertisementStatus vCAS

Join v_Advertisement vA On vA.AdvertisementID = vCAS.AdvertisementID

Where vCAS.LastStateName = 'Succeeded'

And vCAS.LastStatusMessageIdName = 'Program Completed With Success'

And (vCAS.LastStatusTime > DateAdd(D, - 30, GetDate()))

Group By vA.AdvertisementName, vCAS.AdvertisementID, vCAS.LastStatusTime

Configuration Manager Report To Count And Display All Network Operating Systems Installed

This SQL query was developed without any prompts to report, count and display all of the Network Operating Systems (NOS) installed including any Service Pack (SP) that may be installed.

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

Name: Count Network Operating Systems (NOS)

Category: Operating System

Comment: This Report Without Prompts Will Count And Display All The Network Operating Systems (NOS) Installed Including Any Service Packs.

SQL Query:

Select

Count(Caption0) 'Count',

Caption0 as 'Network Operating System (NOS)',

'Service Pack' = Case

When CSDVersion0 Is Null Then ''

Else CSDVersion0

End

From v_GS_Operating_System

Group By Caption0, CSDVersion0

Order By 'Count'

Configuration Manager Report To Display Software Distribution Collections With No Members

This SQL query was developed to be used as a Report to provide administrators with a list of collections that have no resources or members in them.

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

Name: Software Distribution Collections With No Members

Category: Software Distribution – Collections

Comment: This Report Will Display A List of Collections That Do Not Have Any System Resources Or Members In Them And Is Useful For Software Distribution Trueups.

SQL Query:

Select

CollectionID 'Collection ID',

Name 'Collection Name',

Comment,

LastRefreshTime 'Last Refresh Update'

From v_Collection

Where (CollectionID

Not In

(Select CollectionID

From v_FullCollectionMembership))

Microsoft SQL Server 2008 Service Pack 2 Released

On September 29th 2010 Microsoft Released Service Pack (SP) 2 for Microsoft SQL Server 2008 in support for the following versions:

Microsoft SQL Server 2008 Analysis Services

Microsoft SQL Server 2008 Developer

Microsoft SQL Server 2008 Enterprise

Microsoft SQL Server 2008 Express

Microsoft SQL Server 2008 Express with Advanced Services

Microsoft SQL Server 2008 Reporting Services

Microsoft SQL Server 2008 Standard

Microsoft SQL Server 2008 Standard Edition for Small Business

Microsoft SQL Server 2008 Web

Microsoft SQL Server 2008 Workgroup

Among other things Microsoft touts the following new improvements listed on the download page:

15K partitioning Improvement

Reporting Services in SharePoint Integrated Mode

SQL Server 2008 Instance Management

Data-tier Application (DAC) Support

For a complete list of fixes see the KB article:

Microsoft KB2285068 Knowledge Base (KB) Article:

http://support.microsoft.com/?kbid=2285068

The available downloads can be found below:

Microsoft SQL Server 2008 SP2 Readme

SQLServer2008SP2-KB2285068-IA64-ENU (343.4 MB)

SQLServer2008SP2-KB2285068-x64-ENU (349.8 MB)

SQLServer2008SP2-KB2285068-x86-ENU (284.8 MB)

Downloads And Readme:

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=8fbfc1de-d25e-4790-88b5-7dda1f1d4e17&displaylang=en

Release Notes

http://go.microsoft.com/?linkid=9742746

HTA Script To Retrieve A Specified WMI Class Property Values

This HTA script will retrieve WMI class property values for a class that you specify and write the class property name and data to the active window to see the results retrieved for the class property.

HTA Script:

<Html>

<Head>

<Title>Retrieve WMI Class Property Values</Title>

<Style>

Body {Background-Color: CornSilk}

</Style>

<HTA:Application

Caption = Yes

Border = Thick

ShowInTaskBar = No

MaximizeButton = Yes

MinimizeButton = Yes>

<script Language = VBScript>

Sub WindowsLoad

strHTML = "<table border='1' style='border-collapse: collapse' bordercolor='SaddleBrown' id='Table1' >"

strHTML = strHTML & "<tr>"

strHTML = strHTML & "<td width='10%' bgcolor = 'SeaShell'><font color = 'Blue'><b>Property Name</td>"

strHTML = strHTML & "<td width='10%' bgcolor = 'SeaShell'><font color = 'Blue'><b>Property Value</td>"

strHTML = strHTML & "</tr>"

strComputer = MachineName.value

strClassName = className.value

On Error Resume Next

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

Set objDisks = objWMI.InstancesOf(strClassName)

For Each objDisk In objDisks

For Each objProperty In objDisk.Properties_

If objProperty.IsArray = True Then

For I = LBound(objProperty.Value) To UBound(objProperty.Value)

Next

Else

strHTML = strHTML & "<tr>"

Window.Document.Title = "Properties For The " & strClassName & " Class"

strHTML = strHTML & "<td width='1%'>" & objProperty.Name & "</td>"

strHTML = strHTML & "<td width='1%'>" & objProperty.Value & "</td>"

End If

Next

Next

strHTML = strHTML & "</tr>"

strHTML = strHTML & "</table>"

DataArea.InnerHTML = strHTML

End Sub

</Script><Body>

Enter Machine Name: <Input Type = "Text" Value = "LocalHost" Name = "MachineName">

Enter Class Name: <Input Type = "Text" Value = "Win32_ComputerSystem" Name = "ClassName">

<Input Type = "Button" Value = "Run Script" Name = "Run_Button" onClick = "WindowsLoad"><P>

<Span Id = "DataArea"></Span></Body><Div Align = "Center">

<P><A Href="http://myitforum.com/cs2/blogs/dhite">Created For myITforum By Don Hite</A>

HTA Script To Determine Who Is Logged Onto A Remote PC

This HTA script will allow you to determine who is logged on to a remote machine that you specify and will write the results to the active window.

HTA Script:

<Html>

<Head>

<Title>HTA Script</Title>

<Style>

Body {Background-Color: CornSilk}

</Style>

<HTA:Application

Caption = Yes

Border = Thick

ShowInTaskBar = No

MaximizeButton = Yes

MinimizeButton = Yes>

<script Language = VBScript>

Sub WindowsLoad

strHTML = "<table border='1' style='border-collapse: collapse' bordercolor='SaddleBrown' id='Table1' >"

strHTML = strHTML & "<tr>"

strHTML = strHTML & "<td width='10%' bgcolor = 'SeaShell'><font color = 'Blue'><b>Machine Name</td>"

strHTML = strHTML & "<td width='10%' bgcolor = 'SeaShell'><font color = 'Blue'><b>User Name</td>"

strHTML = strHTML & "</tr>"

strComputer = MachineName.Value

Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")

Set colComputer = objWMIService.ExecQuery ("Select * from Win32_ComputerSystem")

For Each objComputer in colComputer

strHTML = strHTML & "<tr>"

Window.Document.Title = "Remote Machine Logon Information"

strHTML = strHTML & "<td width='1%'>" &  objComputer.Name & "</td>"

strHTML = strHTML & "<td width='1%'>" & objComputer.UserName & "</td>"

Next

strHTML = strHTML & "</tr>"

strHTML = strHTML & "</table>"

DataArea.InnerHTML = strHTML

End Sub

</script><Body>

<p><h3 align = center><font color='Orange'>Please Visit myITforum.Com</font></h3>

<div></div>

Machine Name: <Input Type = "Text" Name = "MachineName">

<Input Type = "Button" Value = "Run Script" Name = "Run_Button" onClick = "WindowsLoad"><P>

<Span Id = "DataArea"></Span></Body><Div Align = "Center">

<P><A Href="http://myitforum.com/cs2/blogs/dhite">Created For myITforum By Don Hite</A>

VBS Script To Retrieve Logon Information

The VBS script here can be modified to track logon information and store the results for your information.

VBS Script:

strComputer = "."

Set objComputer = CreateObject("WScript.Network")

const HKEY_CURRENT_USER = &H80000001

const HKEY_LOCAL_MACHINE = &H80000002

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

Set colItems = objWMIService.ExecQuery("Select * from Win32_NetworkAdapterConfiguration Where IPEnabled = True")

For Each objItem in colItems

If Not IsNull(objItem.IPAddress) Then

For Each objIPAddress in objItem.IPAddress

MsgBox "IP Address: " & objIPAddress

Next

End If

Next

strComputerName = objComputer.ComputerName

MsgBox "Machine Name: " & strComputerName

strDomain = objComputer.UserDomain

MsgBox "Account Domain Name: " & strDomain

Set objReg=GetObject("winmgmts:{impersonationLevel=impersonate}!\\" &_

strComputer & "\root\default:StdRegProv")

strKeyPath = "SOFTWARE\Microsoft\Windows NT\CurrentVersion\Winlogon"

strValue = "DefaultUserName"

objReg.GetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValue,strDefaultUserName

MsgBox "User Name : "  & strDefaultUserName

strKeyPath = "SOFTWARE\Microsoft\Windows NT\CurrentVersion"

strValue = "ProductName"

objReg.GetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValue,strProductName

MsgBox "Operating System : " & strProductName

strValue = "CSDVersion"

objReg.GetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValue,strCSDVersion

MsgBox "Service Pack : " & strCSDVersion

VBS Script To Send Microsoft Windows Updates To Excel

This VBS script will send the following local machine Windows Updates information to an excel spreadsheet: Windows update name, the date it was installed, the description and the updates Support URL address.

VBS Script:

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True

objExcel.Workbooks.Add

intRow = 2

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

objExcel.Cells(1, 2).Value = "Date Installed"

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

objExcel.Cells(1, 4).Value = "Support URL"

Set objSession = CreateObject("Microsoft.Update.Session")

Set objSearcher = objSession.CreateUpdateSearcher

intHistoryCount = objSearcher.GetTotalHistoryCount

Set colHistory = objSearcher.QueryHistory(1, intHistoryCount)

For Each objEntry in colHistory

objExcel.Cells(intRow, 1).Value = objEntry.Title

objExcel.Cells(intRow, 2).Value = objEntry.Date

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

objExcel.Cells(intRow, 4).Value = objEntry.SupportURL

intRow = intRow + 1

Next

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

objExcel.Cells.HorizontalAlignment = 2

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"

VBS Script To Determine If A SQL Database Has Auto Shrink On Or Off

This VBS Script will allow you to enter a SQL server name via an input dialog box and it will write the servers database names and if Auto shrink is on or off to an excel spreadsheet.

VBS Script:

strServer = InputBox ("Enter SQL Server Name")

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True

objExcel.Workbooks.Add

intRow = 2

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

objExcel.Cells(1, 2).Value = "Auto Shrink On"

Const adOpenStatic = 3

Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")

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

"Trusted_Connection=Yes;Initial Catalog =Master"

Set objRecordSet = CreateObject("ADODB.Recordset")

objRecordSet.Open " Select Name, Is_Auto_Shrink_On " & _

" From Sys.Databases", objConnection, adOpenStatic, adLockOptimistic

objRecordSet.MoveFirst

Do Until objRecordSet.EOF

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

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

VBS Script To Retrieve CD Drive Information For A Remote Machine

This VBS script will allow you to retrieve CD-ROM drive information for a remote machine.

VBS Script:

strComputer = InputBox ("Enter Machine Name")

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

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

For Each objItem in colItems

MsgBox "Machine Name: " & objItem.SystemName

MsgBox "Drive Letter: " & objItem.Drive

MsgBox "Description: " & objItem.Description

MsgBox "Caption: " & objItem.Caption

Next