Month – February 2008

Newsgroup reply…

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) […]

70-293 Self – Study group

I’m starting up a virtual self study group for 70-293 with two others. This is roughly how we proceeding. We are using the MS Press book “Windows Server 2003” Network Infrastructure” ISBN # 0-7356-1893-3 Each member writes a summary of a assigned section All summaries will be combine into one big study guide Follow weekly […]

Adding OU to a report for MS Office installations

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 […]

Location of last logon user

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, […]

Basic All in one.

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 […]