Querying SCCM Server to get ADD/Remove Programs list of a specific computer

querysccmvbscript

First of all, i'm sorry for my english.
I'm new in vbs scripting and wmi queries and I need your help.

My goal is to get the Add/Remove Programs list of a specific computer.
I saw many scripts which query the Uninstall registry key on the target computer but I want to do it even if the computer is offline. That's why i'm querying the SCCM Server.

My query seems to be good because when i put it in Report Builder, it returns a good result.

Here is the query :

Select sys.Name0,adv.DisplayName0 FROM v_R_System sys , v_Add_Remove_Programs adv where sys.ResourceID=adv.ResourceID and sys.Name0 like '%XXXXXXXX%'

My problem is to get the result of the query using vbscript. I'm getting an error on the line of the "For Each"

Here is my script :

'Central SCCM Site Server name
strComputer = "SCCM_SERVER"

'Central SCCM Site Code
strSiteCode = "DC1"

'Set up the connection String
Set objWMIService = GetObject("winmgmts://" & strComputer & "\root\sms\site_" & strSiteCode)




'Get the info with a query
Set colGroups = objWMIService.ExecQuery("Select sys.Name0,adv.DisplayName0 FROM v_R_System sys , v_Add_Remove_Programs adv where sys.ResourceID=adv.ResourceID and sys.Name0 like 'XXXXXXXXXXX'")


'output the info
For Each objGroup in colGroups
    Wscript.echo objGroup.DisplayName0 & " - "
Next

Can you please provide me some help ?

Update : it seems that i'm not using the right language for the query. I'm using SQL instead of WQL.

I don't know how to translate my query into WQL.
Can you help please ?

Best Answer

You are trying to use a SQL query to get results from CM07/CM12. You need to use WQL to query CM07/CM12. Translate your query to WQL and it should work or rewrite your VBS to query SQL directly.

Related Solutions

Please help me add to this script to get server hardware info

Have a go at this. New info is added at the bottom of the script. One caveat, NIC (ndis) link speed is given by a different provider than the other NIC properties. As such there are 2 new sections for NIC info: NIC Instance Information and NIC Link Information. Each enumerates all the NICs in your machine and gives the relevant info.

'*****************************************************
'       Script Witten by Larry Heintz
'       Jan 2005 www.larryheintz.com
'       Modified Sep 2009 by Shawn Quillman for use on ServerFault.com
' This script will connect to the computer name you
' enter and query and return the following information
'
' CPU:
'   - CPU Speed MHZ
'   - CPU Manufacture
'   - CPU Type
'   - CPU Current Load
'
' Memory
'   - Memory Bank
'   - Memory Stick Size MB
'   - Memory Speed MHZ
'   - Memory Type
'
' Hard Drive
'   - HD Model
'   - HD Size GB
'   - HD # of Partions
'   - HD Type
'   - Drive Letter
'   - Used Space
'   - Free Space
'   - Size of Partion
'   - Free Space %
'
' Script Usage:
' cscript hardware.vbs /computer:[Computer Name]
'*****************************************************
On Error Resume Next
' Dims stuff
Dim objStdOut, args
Dim servername
' Set Stuff
Set objStdOut = WScript.stdOut
Set args = WScript.Arguments.Named
servername = Trim(args.Item("computer"))

If WScript.arguments.count = 1 Then
    WScript.echo "CPU,Memory,Hard Drive Information for " & UCase(servername)
    WScript.echo ""
    If Not (errorChecking(servername)) Then
        '// CPU Info
        WScript.echo "CPU Information:"
        WScript.echo "================"
        For Each objCPU In GetObject("winmgmts:{impersonationLevel=impersonate}\\" & servername & "\root\cimv2").InstancesOf("Win32_Processor")
                Select Case objCPU.Family
                        Case 2
                                cputype = "Unknown"
                        Case 11
                                cputype = "Pentium brand"
                        Case 12
                                cputype = "Pentium Pro"
                        Case 13
                                cputype = "Pentium II"
                        Case 14
                                cputype = "Pentium processor with MMX technology"
                        Case 15
                                cputype = "Celeron "
                        Case 16
                                cputype = "Pentium II Xeon"
                        Case 17
                                cputype = "Pentium III"
                        Case 28
                                cputype = "AMD Athlon Processor Family"
                        Case 29
                                cputype = "AMD Duron Processor"
                        Case 30
                                cputype = "AMD2900 Family"
                        Case 31
                                cputype = "K6-2+"
                        Case 130
                                cputype = "Itanium Processor"
                        Case 176
                                cputype = "Pentium III Xeon"
                        Case 177
                                cputype = "Pentium III Processor with Intel SpeedStep Technology"
                        Case 178
                                cputype = "Pentium 4"
                        Case 179
                                cputype = "Intel Xeon"
                        Case 181
                                cputype = "Intel Xeon processor MP"
                        Case 182
                                cputype = "AMD AthlonXP Family"
                        Case 183
                                cputype = "AMD AthlonMP Family"
                        Case 184
                                cputype = "Intel Itanium 2"
                        Case 185
                                cputype = "AMD Opteron? Family"
                End Select
                WScript.echo " CPU MHZ: " & objCPU.CurrentClockSpeed
                WScript.echo " CPU Manufacture: " & objCPU.Manufacturer
                WScript.echo " CPU Type: " & cputype
                WScript.echo " CPU Current Load: " & objCPU.LoadPercentage & "%"
        Next
        WScript.echo ""
        Set objCPU = Nothing

        '// Memory Info
        WScript.echo "Memory Information:"
        WScript.echo "==================="
        For Each objMem In GetObject("winmgmts:{impersonationLevel=impersonate}\\" & servername & "\root\cimv2").InstancesOf("Win32_PhysicalMemory")
                Select Case objMem.MemoryType
                        Case 0
                                rtype = "Unknown"
                        Case 1
                                rtype = "Other"
                        Case 2
                                rtype = "DRAM"
                        Case 3
                                rtype = "Synchronous DRAM"
                        Case 4
                                rtype = "Cache DRAM"
                        Case 5
                                rtype = "EDO"
                        Case 6
                                rtype = "EDRAM"
                        Case 7
                                rtype = "VRAM"
                        Case 8
                                rtype = "SRAM"
                        Case 9
                                rtype = "RAM"
                        Case 10
                                rtype = "ROM"
                        Case 11
                                rtype = "Flash"
                        Case 12
                                rtype = "EEPROM"
                        Case 13
                                rtype = "FEPROM"
                        Case 14
                                rtype = "EPROM"
                        Case 15
                                rtype = "CDRAM"
                        Case 16
                                rtype = "3DRAM"
                        Case 17
                                rtype = "SDRAM"
                        Case 18
                                rtype = "SGRAM"
                        Case 19
                                rtype = "RDRAM"
                        Case 20
                                rtype = "DDR"
                End Select
                WScript.echo " Memory Bank: " & objMem.BankLabel
                WScript.echo " Memory Size: " & objMem.Capacity / 1024 / 1024 & " MB"
                WScript.echo " Memory Speed: " & objMem.Speed & " MHZ"
                WScript.echo " Memory Type: " & rtype
                WScript.echo ""
        Next
        WScript.echo ""
        Set objMem = Nothing

        '// Hard Drive Info
        WScript.echo "Hard Drive Information:"
        WScript.echo "======================="
        For Each objHDInfo In GetObject("winmgmts:{impersonationLevel=impersonate}\\" & servername & "\root\cimv2").InstancesOf("Win32_DiskDrive")
                WScript.echo " Hard Drive Model: " & objHDInfo.Model
                WScript.echo " Hard Drive Size: " & CLng(objHDInfo.Size / 1024 / 1024 / 1024) & " GB"
                WScript.echo " # of Partitions: " & objHDInfo.Partitions
                WScript.echo " Hard Drive Type: " & objHDInfo.InterfaceType
                WScript.echo ""
        Next
        WScript.echo ""
        Set objHDinfo = Nothing

        For Each objDisk In GetObject("winmgmts:{impersonationLevel=impersonate}\\" & servername & "\root\cimv2").InstancesOf("Win32_LogicalDisk Where DriveType = '3'")
                freespace = Left(objDisk.freespace / 1024 / 1024 / 1024, 4)
                totalspace = Left(objDisk.size / 1024 / 1024 / 1024, 4)
                usedspace = Left(totalspace / freespace, 4)
                freeperct = Left(100 / usedspace, 2)
                WScript.echo " Drive: " & objDisk.deviceid
                WScript.echo " Used Space: " & usedspace & " GB"
                WScript.echo " Free Space: " & freespace & " GB"
                WScript.echo " Total Space: " & totalspace & " GB"
                WScript.echo " Free: " & freeperct & "%"
                WScript.echo ""
        Next
        Set objDisk = Nothing

        '// Operating System and Service Pack
        WScript.echo "Operating System Information:"
        WScript.echo "======================="
        For Each objOSInfo In GetObject("winmgmts:{impersonationLevel=impersonate}\\" & servername & "\root\cimv2").InstancesOf("Win32_OperatingSystem")
                WScript.echo " Operating System: " & left(objOSInfo.Name,instr(objOSInfo.Name,"|")-1)
                WScript.echo " Service Pack Major Version: " & objOSInfo.ServicePackMajorVersion
                WScript.echo " Service Pack Minor Version: " & objOSInfo.ServicePackMinorVersion
                WScript.echo ""
        Next
        WScript.echo ""
        Set objOSinfo = Nothing

        '// NIC Info
        WScript.echo "NIC Instance Information:"
        WScript.echo "======================="
        For Each objNICInfo In GetObject("winmgmts:{impersonationLevel=impersonate}\\" & servername & "\root\cimv2").InstancesOf("Win32_NetworkAdapterConfiguration")
                WScript.echo " Caption (Name of NIC): " & objNICInfo.Caption
                WScript.echo "     MAC Address: " & objNICInfo.MACAddress
                WScript.echo "     IP Address(es): " & Join(objNICInfo.IPAddress,", ")
                set objWMI = GetObject("winmgmts:\\" & servername & "\root\WMI")
                set colNICProps = objWMI.ExecQuery("SELECT * FROM MSNdis_LinkSpeed")
                for each objProp in colNICProps
                        WScript.echo "     Active:" & objProp.Active
                        WScript.echo "     Link Speed:" & objProp.NdisLinkSpeed
                        WScript.echo "     Instance Name:" & objProp.InstanceName & " - " & objNICInfo.Caption
                next
                WScript.echo ""
        Next
        WScript.echo ""
        Set objNICinfo = Nothing

        '// NIC Info
        WScript.echo "NIC Link Information:"
        WScript.echo "======================="
        set objWMI = GetObject("winmgmts:\\" & servername & "\root\WMI")
        set colNICProps = objWMI.ExecQuery("SELECT * FROM MSNdis_LinkSpeed")
        for each objProp in colNICProps
                WScript.echo " Instance Name:" & objProp.InstanceName 
                WScript.echo "     Active:" & objProp.Active
                WScript.echo "     Link Speed:" & objProp.NdisLinkSpeed & " bps"
        next
        WScript.echo ""
        WScript.echo ""
        Set objNICinfo = Nothing

        '// Powersupply Info
        WScript.echo "Power Supply Information:"
        WScript.echo "======================="
        intPSCount = 0
        For Each objPSInfo In GetObject("winmgmts:{impersonationLevel=impersonate}\\" & servername & "\root\cimv2").InstancesOf("Win32_PowerSupply")
                intPSCount = intPSCount + 1
        Next
        WScript.echo "     Number of Power Supplies: " & intPSCount
        WScript.echo ""
        WScript.echo ""
        Set objPSinfo = Nothing
    End If
    WScript.echo "------------------------------------------------"
Else
    objStdOut.Write "Usage: cscript hardware.vbs /computer:[Computer Name]"
    objStdOut.close
    WScript.quit
End If

' Error Checking Function
Function errorChecking(ComputerName) 
    errorChecking = False 
    If err.number <> 0 Then 
        If err.number = "462" Then
                WScript.echo ComputerName & ",Error,Unable to connect"
                err.Clear() 
                errorChecking = True
        ElseIf err.number = "70" Then   'General access denied error
                WScript.echo ComputerName & ",Error,Permission Denied while trying to connect."
                err.Clear() 
                errorChecking = True
        ElseIf err.number = "451" Then 'HEX Error is: 1C3
                err.Clear() 
                errorChecking = True
        ElseIf err.number = "-2147023174" Then   'The RPC server is unavailable.
                WScript.echo ComputerName & ",Error,Unable to connect error number -2147023174."
                err.Clear() 
                errorChecking = True
        ElseIf err.number = "-2147023836" Then
                WScript.echo ComputerName & ",Error,Unable to connect error number --2147023836."
                err.Clear() 
                errorChecking = True
        ElseIf err.number = "-2147022986" Then
                WScript.echo ComputerName & ",Error,Unable to connect error number -2147022986."
                err.Clear() 
                errorChecking = True
        Else
                WScript.echo "Error is: " & err.number
                WScript.echo "HEX Error is: " & Hex(err.number)
                WScript.echo "Desc. is: " & err.Description
                WScript.echo "Source is: " & err.Source
                err.Clear()
        End If 
    End If 
End Function

Sql-server – How to remove a specific bad plan from the SQL Server query cache

I figured out a few things

select * from sys.dm_exec_query_stats

will show all the cached query plans. Unfortunately, no SQL text is shown there.

However, you can join the SQL text to the plans like so:

select plan_handle, creation_time, last_execution_time, execution_count, qt.text
FROM 
   sys.dm_exec_query_stats qs
   CROSS APPLY sys.dm_exec_sql_text (qs.[sql_handle]) AS qt

From here it's pretty trivial to add a WHERE clause to find the SQL I know is in the query, and then I can execute:

DBCC FREEPROCCACHE (plan_handle_id_goes_here)

to remove each query plan from the query plan cache. Not exactly easy or convenient, but it appears to work..

edit: dumping the entire query cache will also work, and is less dangerous than it sounds, at least in my experience:

DBCC FREESYSTEMCACHE ('ALL') WITH MARK_IN_USE_FOR_REMOVAL;

Best Answer

Related Solutions

Please help me add to this script to get server hardware info

Sql-server – How to remove a specific bad plan from the SQL Server query cache

Related Topic