none
Report for machines that do not meet win 7 requirements, with ou and model.....need advice on query please.

    Question

  • The stock built in report "Computers that do not meet the recommended system requirements for Windows 7" is missing two items I need: the OU and the model. Can someone help me out? Here is the query......

    SELECT DISTINCT SYS.Netbios_Name0, fcm.SiteCode, 
    ROUND(CONVERT (FLOAT ,LDISK.FreeSpace0) / 1024, 2) AS FreeSpace0,
      ROUND (CONVERT (FLOAT, PROC1.MaxClockSpeed0), -2)/ 1000 AS 'Processor (GHz)', 
    ROUND (ROUND(CONVERT (FLOAT ,MEM.TotalPhysicalMemory0) / 1048576, 2) * 1024, 0) AS C083, 
    OPSYS.Caption0 AS 'Operating System',
    CONVERT(FLOAT, LEFT(OPSYS.Version0, 3)) Version
    FROM v_FullCollectionMembership fcm
    JOIN v_R_System SYS ON fcm.ResourceID=SYS.ResourceID
    JOIN v_GS_PROCESSOR PROC1 ON SYS.ResourceID = PROC1.ResourceID
    JOIN v_GS_X86_PC_MEMORY MEM ON SYS.ResourceID = MEM.ResourceID
    JOIN v_GS_OPERATING_SYSTEM OPSYS ON SYS.ResourceID = OPSYS.ResourceID
    JOIN v_GS_LOGICAL_DISK LDISK ON SYS.ResourceID = LDISK.ResourceID and LDISK.DeviceID0=SUBSTRING(OPSYS.WindowsDirectory0,1,2)
    WHERE fcm.CollectionID=@CollID
    AND (ROUND (ROUND(CONVERT (FLOAT ,TotalPhysicalMemory0) / 1048576, 2) * 1024, 0) < 3000
    OR ROUND (CONVERT (FLOAT, PROC1.MaxClockSpeed0), -2)/ 1000 < 1.9 
    OR ROUND(CONVERT (FLOAT ,LDISK.FreeSpace0) / 1024, 2) < 40)

    Tuesday, April 02, 2013 6:03 PM

All replies

  • This is giving me the correct fields but the data isnt populating..................

    select
                v_R_System.Netbios_Name0 AS [Computer Name], 
                v_R_System.Resource_Domain_OR_Workgr0 AS [Domain/Workgroup],     
                (select top 1 ou2.System_OU_Name0 from v_RA_System_SystemOUName ou2
                where ou.ResourceID = ou2.ResourceID and LEN(ou2.System_OU_Name0) = MAX(LEN(ou.System_OU_Name0))) OU,
                v_R_System.location0 as [AD Location Field], 
                v_R_System.Description0 as [AD Description Field],     
                v_Site.SiteName as [SMS Site Name], 
                [Top Console User] = CASE 
                      when (v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 is NULL or v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 = '-1') 
                      then 'Unknown' 
                      Else v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 
                End,
                v_GS_OPERATING_SYSTEM.Caption0 AS [Operating System], 
                v_GS_OPERATING_SYSTEM.CSDVersion0 AS [Service Pack Level],
    v_GS_OPERATING_SYSTEM.InstallDate0 AS [OS Install Date],
                v_GS_SYSTEM_ENCLOSURE_UNIQUE.SerialNumber0 AS [Serial Number], 
                v_GS_SYSTEM_ENCLOSURE_UNIQUE.SMBIOSAssetTag0 AS [Asset Tag], 
                v_GS_COMPUTER_SYSTEM.Manufacturer0 AS [Manufacturer], 
                v_GS_COMPUTER_SYSTEM.Model0 AS [Model], 
                v_GS_X86_PC_MEMORY.TotalPhysicalMemory0 / 1024 AS [Memory (MB)], 
    v_GS_PROCESSOR.Name0 AS [CPU],
                (Select    sum(Size0)  
                 from      v_GS_LOGICAL_DISK inner join v_FullCollectionMembership 
                 on        (v_FullCollectionMembership.ResourceID = v_GS_LOGICAL_DISK.ResourceID ) 
                 where     v_GS_LOGICAL_DISK.ResourceID =v_R_System.ResourceID 
                 and       v_FullCollectionMembership.CollectionID = @CollID) As [Disk Space (MB)], 
                (Select    sum(v_GS_LOGICAL_DISK.FreeSpace0) 
                 from      v_GS_LOGICAL_DISK inner join v_FullCollectionMembership 
                 on        (v_FullCollectionMembership.ResourceID = v_GS_LOGICAL_DISK.ResourceID ) 
                 where     v_GS_LOGICAL_DISK.ResourceID =v_R_System.ResourceID 
                 and       v_FullCollectionMembership.CollectionID = @CollID) As [Free Disk Space (MB)] ,
                 case 
                            when v_R_System.Client0 is null then  ''
                            when v_R_System.Client0 = 1 then 'yes'
                            when v_R_System.Client0 = 0 then 'no'
                      end as [Client]
                from v_R_System 
                left join v_RA_System_SystemOUName ou 
                on (ou.ResourceID = v_R_System.ResourceID)
                left outer join v_GS_OPERATING_SYSTEM 
                on (v_GS_OPERATING_SYSTEM.ResourceID = v_R_System.ResourceID) 
                left join v_GS_SYSTEM_ENCLOSURE_UNIQUE 
                on (v_GS_SYSTEM_ENCLOSURE_UNIQUE.ResourceID = v_R_System.ResourceID) 
                left outer join v_GS_COMPUTER_SYSTEM 
                on (v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System.ResourceID) 
                left outer join v_GS_X86_PC_MEMORY 
                on (v_GS_X86_PC_MEMORY.ResourceID = v_R_System.ResourceID) 
                left outer join v_GS_PROCESSOR 
                on (v_GS_PROCESSOR.ResourceID = v_R_System.ResourceID) 
                right outer join  v_FullCollectionMembership 
                on (v_FullCollectionMembership.ResourceID = v_R_System.ResourceID) 
                left  join v_Site 
                on (v_FullCollectionMembership.SiteCode = v_Site.SiteCode) 
                left outer join v_GS_LOGICAL_DISK 
                on (v_GS_LOGICAL_DISK.ResourceID = v_R_System.ResourceID) and v_GS_LOGICAL_DISK.DeviceID0=SUBSTRING(v_GS_OPERATING_SYSTEM.WindowsDirectory0,1,2) 
                left join v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP 
                on (v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.ResourceID = v_R_System.ResourceID) 
                Where v_FullCollectionMembership.CollectionID = @CollID
                group by v_R_System.ResourceID, v_R_System.Netbios_Name0, v_R_System.Resource_Domain_OR_Workgr0,
                            v_R_System.Location0,v_R_System.Description0,
                            ou.ResourceID,
                            v_Site.SiteName,
                            v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0,
                            v_GS_OPERATING_SYSTEM.Caption0, v_GS_OPERATING_SYSTEM.CSDVersion0, v_GS_OPERATING_SYSTEM.InstallDate0,            v_GS_SYSTEM_ENCLOSURE_UNIQUE.SerialNumber0,v_GS_SYSTEM_ENCLOSURE_UNIQUE.SMBIOSAssetTag0,
                            v_GS_COMPUTER_SYSTEM.Manufacturer0, v_GS_COMPUTER_SYSTEM.Model0,
                            v_GS_X86_PC_MEMORY.TotalPhysicalMemory0,
                            v_GS_PROCESSOR.Name0,
    v_r_system.Client0
                      Order by v_R_System.Netbios_Name0

    SELECT DISTINCT SYS.Netbios_Name0, fcm.SiteCode, 
    ROUND(CONVERT (FLOAT ,LDISK.FreeSpace0) / 1024, 2) AS FreeSpace0,
      ROUND (CONVERT (FLOAT, PROC1.MaxClockSpeed0), -2)/ 1000 AS 'Processor (GHz)', 
    ROUND (ROUND(CONVERT (FLOAT ,MEM.TotalPhysicalMemory0) / 1048576, 2) * 1024, 0) AS C083, 
    OPSYS.Caption0 AS 'Operating System',
    CONVERT(FLOAT, LEFT(OPSYS.Version0, 3)) Version
    FROM v_FullCollectionMembership fcm
    JOIN v_R_System SYS ON fcm.ResourceID=SYS.ResourceID
    JOIN v_GS_PROCESSOR PROC1 ON SYS.ResourceID = PROC1.ResourceID
    JOIN v_GS_X86_PC_MEMORY MEM ON SYS.ResourceID = MEM.ResourceID
    JOIN v_GS_OPERATING_SYSTEM OPSYS ON SYS.ResourceID = OPSYS.ResourceID
    JOIN v_GS_LOGICAL_DISK LDISK ON SYS.ResourceID = LDISK.ResourceID and LDISK.DeviceID0=SUBSTRING(OPSYS.WindowsDirectory0,1,2)
    WHERE fcm.CollectionID=@CollID
    AND (ROUND (ROUND(CONVERT (FLOAT ,TotalPhysicalMemory0) / 1048576, 2) * 1024, 0) < 1024
    OR ROUND (CONVERT (FLOAT, PROC1.MaxClockSpeed0), -2)/ 1000 < 1 
    OR ROUND(CONVERT (FLOAT ,LDISK.FreeSpace0) / 1024, 2) < 16)

    Tuesday, April 02, 2013 6:53 PM