How to Query IP adress and Hostname of SCCM client from SCCM database

 

As described in the following article you can retrieve IP / Hostname of SCCM client using following query

 

SELECT LEFT(IPAddress0, LEN(‘%.%.%.%.%.%.%’) – patIndex(‘,’, IPAddress0)) AS [IP Address], DNSHostName0 AS [Host Name]
FROM v_GS_NETWORK_ADAPTER_CONFIGUR
WHERE (IPAddress0 IS NOT NULL)
ORDER BY [Host Name]

https://social.technet.microsoft.com/Forums/en-US/b5f720f0-48ae-4793-81b5-114d28831857/sccm-sql-query-ip-address?forum=configmgrgeneral

How to get list of installed HotFixes from SCCM database.

Use the following SQL query to easily get list of installed Hotfixes.

Query:

select
v_r_s.Resourceid,
v_r_s.Name0,v_r_s.User_Name0,
q_f_e.HotFixID00,q_f_e.InstallDate00,
q_f_e.InstalledOn00,q_f_e.Caption00,
q_f_e.InstalledBy00
from QUICK_FIX_ENGINEERING_DATA q_f_e
join v_R_System v_r_s on v_r_s.ResourceID =q_f_e.MachineID
join v_FullCollectionMembership f_c_m on f_c_m.ResourceID=v_r_s.ResourceID
where HotFixID00 in (‘KB4056888′,’KB4056893′,’KB4056898′,’KB4056895′,’KB4056897′,’KB4056568′,’KB4056894’)
order by v_r_s.Name0 desc

 

Result:

Capture

List Software Inventory Items of Computers Discovered by SCCM

Hi,

Using the following sql query, you can access the installed software of computers that are discovered by SCCM.

DECLARE @CollID AS VARCHAR(8);

SET @CollID = ‘SMS00001’
SELECT  DisplayName0 As ‘Product Name’, Version0 As ‘Product Version’, FCM.Name As ‘Computer Name’

FROM  v_Add_Remove_Programs As ARP 

JOIN v_FullCollectionMembership As FCM on ARP.ResourceID=FCM.ResourceID  

WHERE  FCM.CollectionID = @CollID 

GROUP BY DisplayName0, Version0, FCM.Name

ORDER BY DisplayName0 Asc, Version0 Asc

 

Untitled.png