sccmug

Select distinct R.Netbios_Name0 AS “Computer Name”, ISC.NormalizedName AS “Product Name”, “Publisher” = CASE when (CAST(ISC.NormalizedPublisher as varchar) is NULL or CAST(ISC.NormalizedPublisher as varchar) = ‘-1’) then ‘Unknown’ Else CAST(ISC.NormalizedPublisher as varchar) End, “Version” = CASE when (CAST(ISC.NormalizedVersion as varchar) is NULL or CAST(ISC.NormalizedVersion as varchar) = ‘-1’) then ‘Unknown’ Else CAST(ISC.NormalizedVersion as varchar)End, “Language” = CASE when (CAST(ISC.Language0 as varchar) […]

SELECT DISTINCT dbo.v_RA_System_IPSubnets.IP_Subnets0, dbo.v_RA_System_SystemOUName.System_OU_Name0, dbo.v_R_System.Netbios_Name0, dbo.v_R_System.User_Name0, dbo.v_GS_ADD_REMOVE_PROGRAMS.DisplayName0FROM dbo.v_GS_ADD_REMOVE_PROGRAMS LEFT OUTER JOIN dbo.v_CM_RES_COLL_SMS00004 ON dbo.v_GS_ADD_REMOVE_PROGRAMS.ResourceID = dbo.v_CM_RES_COLL_SMS00004.ResourceID  INNER JOIN dbo.v_R_System ON dbo.v_GS_ADD_REMOVE_PROGRAMS.ResourceID = dbo.v_R_System.ResourceID INNER JOIN dbo.v_RA_System_IPSubnets ON dbo.v_GS_ADD_REMOVE_PROGRAMS.ResourceID = dbo.v_RA_System_IPSubnets.ResourceID  INNER JOIN dbo.v_GS_SoftwareFile ON dbo.v_GS_ADD_REMOVE_PROGRAMS.ResourceID = dbo.v_GS_SoftwareFile.ResourceID left JOIN dbo.v_RA_System_SystemOUName ON dbo.v_GS_ADD_REMOVE_PROGRAMS.ResourceID = dbo.v_RA_System_SystemOUName.ResourceID WHERE dbo.v_RA_System_IPSubnets.IP_Subnets0 NOT LIKE ‘%.0.0’ AND ((dbo.v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 LIKE ‘%microsoft%office%pro%’  OR dbo.v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 LIKE ‘%microsoft%office%stan%’)  AND NOT (dbo.v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 LIKE ‘%visio%’  OR dbo.v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 LIKE […]

This query will list most of the place where user name can be determined. Also sometimes I have seen people confuse RegisteredUser0 as the last logon user, this is not the case. This is the Registration name of used for Windows OS. SELECT  v_GS_COMPUTER_SYSTEM.Name0,  v_GS_COMPUTER_SYSTEM.UserName0,  v_GS_SYSTEM_CONSOLE_USER.SystemConsoleUser0,  v_GS_SYSTEM_CONSOLE_USAGE.TopConsoleUser0,  v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0,  v_R_System.User_Name0,  v_R_System_Valid.User_Name0,  v_GS_OPERATING_SYSTEM.RegisteredUser0FROM  dbo.v_GS_COMPUTER_SYSTEM v_GS_COMPUTER_SYSTEM,  dbo.v_GS_OPERATING_SYSTEM v_GS_OPERATING_SYSTEM, […]

SELECT DISTINCT  CS.Name0,  NAC.IPAddress0FROM  dbo.v_FullCollectionMembership FCM,  dbo.v_GS_COMPUTER_SYSTEM CS,  dbo.v_GS_NETWORK_ADAPTER_CONFIGUR  NACWHERE  CS.ResourceID = FCM.ResourceID  AND CS.ResourceID = NAC.ResourceID  AND NAC.IPAddress0 Is Not Null  AND FCM.CollectionID=’SMS00004′ORDER BY  CS.Name0

SELECT  distinct  CS.name0 as ‘Computer Name’,  CS.domain0 as ‘Domain’,  CS.UserName0 as ‘User’,  BIOS.SerialNumber0 as ‘Bios serial’,  SE.SerialNumber0 as ‘System Enclosure serial’, CS.Manufacturer0 as ‘Manufacturer’,  CS.Model0 as ‘model’,  OS.Caption0 as ‘OS’,  RAA.SMS_Assigned_Sites0 as ‘Site’,  RAM.TotalPhysicalMemory0 as ‘Total Memory’,  sum(isnull(LDisk.Size0,’0′)) as ‘Hardrive Size’,  sum(isnull(LDisk.FreeSpace0,’0′)) AS ‘Free Space’,  CPU.CurrentClockSpeed0 as ‘CPU Speed’ from    v_GS_COMPUTER_SYSTEM CS right join v_GS_PC_BIOS […]