Wednesday, May 12, 2010

Custom SCCM 2007 Queries for FULL Name

SCCM2007 has some interesting querying capabilities if you know SQL. The Database contains numerous polling information about Users, Hardware and Software. You can generate reports based on multiple criteria, and some of them can be pretty extensive. This Report/SQL query finds the user, IP Address and MAC Address based custom parameters for the Username.


SELECT DISTINCT CON.IPAddress0, SYS.Netbios_Name0 [System Name],
CON.MACAddress0, USR.Full_User_Name0, SYS.User_Name0, SYS.User_Domain0,
Operating_System_Name_and0, SYS.Resource_Domain_OR_Workgr0
FROM v_R_System SYS
INNER JOIN v_R_User USR
ON SYS.User_Name0 = USR.User_Name0
INNER JOIN v_GS_NETWORK_ADAPTER NET
ON SYS.ResourceID = NET.ResourceID
INNER JOIN v_GS_NETWORK_ADAPTER_CONFIGUR CON
ON SYS.ResourceID = CON.ResourceID
WHERE USR.Full_User_Name0 LIKE @txtSearch AND
CON.IPAddress0 LIKE '%%'
ORDER BY USR.Full_User_Name0, SYS.Netbios_Name0

No comments:

Post a Comment