ConfigMgr SQL query to list hardware and add/remove programs inventory for a client

It lists NetBIOS Name, Install Site Code, processor Name, Processor Manufacturer, Model, Total Physical Memory, and Serial Number for a selected client.
It also lists the add remove programs inventory for a client.

SQL Query:
SELECT V_R_SYSTEM.Name0,v_RA_System_SMSInstalledSites.SMS_Installed_Sites0,v_GS_PROCESSOR.Name0,v_GS_COMPUTER_SYSTEM.Manufacturer0,v_GS_COMPUTER_SYSTEM.Model0,v_GS_COMPUTER_SYSTEM.Name0,v_GS_X86_PC_MEMORY.TotalPhysicalMemory0,v_GS_PC_BIOS.SerialNumber0 from v_r_system inner join v_GS_PROCESSOR on v_R_system.ResourceID=v_GS_PROCESSOR.ResourceID inner join v_GS_COMPUTER_SYSTEM on v_GS_COMPUTER_SYSTEM.ResourceID=v_r_system.ResourceID inner join v_GS_X86_PC_MEMORY on v_GS_X86_PC_MEMORY.ResourceID=v_r_system.ResourceID inner join v_RA_System_SMSInstalledSites on v_RA_System_SMSInstalledSites.ResourceID=v_r_system.ResourceID inner join v_GS_PC_BIOS on v_GS_PC_BIOS.ResourceID=V_r_system.ResourceID where v_r_system.Name0 like @computername

Select v_Add_Remove_Programs.DisplayName0, v_Add_Remove_Programs.Publisher0, v_Add_Remove_Programs.Version0
FROM v_Add_Remove_Programs
JOIN  v_R_System ON v_Add_Remove_Programs.ResourceID = v_R_System.ResourceID
WHERE v_R_System.Netbios_Name0 = @computername

Prompt Query:

Create a prompt with a variable “computername” using the following query.

begin
 if (@__filterwildcard = '')
  SELECT DISTINCT SYS.Netbios_Name0 from v_R_System SYS WHERE SYS.Client0=1 ORDER By SYS.Netbios_Name0
 else
  SELECT DISTINCT SYS.Netbios_Name0 from v_R_System SYS WHERE SYS.Client0=1
  and SYS.Netbios_Name0 like @__filterwildcard
  ORDER By SYS.Netbios_Name0
end

0 comments:

Post a Comment