Windows 7 Upgrade Assessments

[peled]

Hello all

Soon, we will start moving to Windows7.

 

I have built in report for Windows7 Assessments (Code attached).

 

I need to add to the report, few more datiales about computers:

OU of the computer account in the AD.

Last logon Username

Last hardware scan date

 

Can enyobe assist with this?

 

Thanks

Peled

 

Threport Code:

SELECT DISTINCT SYS.Netbios_Name0, fcm.SiteCode,

OPSYS.Caption0 AS 'Operating System', OPSYS.Version0 AS 'Version',

OPSYS.CSDVersion0 AS 'Service Pack Level',

CASE

WHEN (OPSYS.Caption0 LIKE '%Server%')

OR (OPSYS.Version0 < '6.0.6001')

OR (OPSYS.Version0 = '6.0.6001' AND OPSYS.CSDVersion0 < 'Service Pack 1') THEN 'FAIL'

WHEN OPSYS.Caption0 IS NULL THEN 'UNKNOWN'

ELSE 'OK' END AS Status0,

ROUND (CONVERT (FLOAT, PROC1.MaxClockSpeed0), -2)/1000 AS 'Processor (GHz)',

CASE

WHEN PROC1.MaxClockSpeed0 IS NULL THEN 'UNKNOWN'

WHEN ROUND (CONVERT (FLOAT, PROC1.MaxClockSpeed0), -2)/1000 < 2.6 THEN 'FAIL'

ELSE 'OK' END AS CpuStatus0,

ROUND (ROUND(CONVERT (FLOAT ,MEM.TotalPhysicalMemory0) / 1048576, 2) * 1024, 0)

AS C083,

CASE

WHEN MEM.TotalPhysicalMemory0 IS NULL THEN 'UNKNOWN'

WHEN ROUND (ROUND(CONVERT (FLOAT ,MEM.TotalPhysicalMemory0) / 1048576, 2) * 1024, 0) < 2048 THEN 'FAIL'

ELSE 'OK' END AS Status0,

ROUND (CONVERT (FLOAT, LDISK.FreeSpace0) / 1024, 2) AS FreeSpace0,

CASE

WHEN LDISK.FreeSpace0 IS NULL THEN 'UNKNOWN'

WHEN ROUND (CONVERT (FLOAT, LDISK.FreeSpace0) / 1024, 2) < 16 THEN 'FAIL'

ELSE 'OK' END AS Status0,

MAX (DA.AdapterRAM0) / 1024 AS AdapterRAM

 

FROM v_FullCollectionMembership fcm

JOIN v_R_System SYS on fcm.ResourceID=SYS.ResourceID

LEFT OUTER JOIN v_GS_PROCESSOR PROC1 on SYS.ResourceID = PROC1.ResourceID

LEFT OUTER JOIN v_GS_X86_PC_MEMORY MEM on SYS.ResourceID = MEM.ResourceID

LEFT OUTER JOIN v_GS_OPERATING_SYSTEM OPSYS on SYS.ResourceID = OPSYS.ResourceID

LEFT OUTER JOIN v_GS_LOGICAL_DISK LDISK on SYS.ResourceID = LDISK.ResourceID and LDISK.DeviceID0=SUBSTRING(OPSYS.WindowsDirectory0,1,2)

LEFT OUTER JOIN v_GS_VIDEO_CONTROLLER DA on SYS.ResourceID = DA.ResourceID

WHERE fcm.CollectionID = @CollID

GROUP BY SYS.Netbios_Name0, fcm.SiteCode, OPSYS.Caption0, OPSYS.Version0,

OPSYS.CSDVersion0, PROC1.MaxClockSpeed0, MEM.TotalPhysicalMemory0,

LDISK.FreeSpace0

ORDER BY 6, 8, 10, 12, 13, 1

[Eswar Koneti]

I would suggest you to refer this document avilable here on http://www.microsoft...&displaylang=en for list of Views and avilable columns.

 

Here you go with this. I have added additional content in Bold.

 

 

SELECT DISTINCT SYS.Netbios_Name0, fcm.SiteCode,

OPSYS.Caption0 AS 'Operating System', OPSYS.Version0 AS 'Version',

OPSYS.CSDVersion0 AS 'Service Pack Level',

CASE

WHEN (OPSYS.Caption0 LIKE '%Server%')

OR (OPSYS.Version0 < '6.0.6001')

OR (OPSYS.Version0 = '6.0.6001' AND OPSYS.CSDVersion0 < 'Service Pack 1') THEN 'FAIL'

WHEN OPSYS.Caption0 IS NULL THEN 'UNKNOWN'

ELSE 'OK' END AS Status0,

ROUND (CONVERT (FLOAT, PROC1.MaxClockSpeed0), -2)/1000 AS 'Processor (GHz)',

CASE

WHEN PROC1.MaxClockSpeed0 IS NULL THEN 'UNKNOWN'

WHEN ROUND (CONVERT (FLOAT, PROC1.MaxClockSpeed0), -2)/1000 < 1 THEN 'FAIL'

ELSE 'OK' END AS CpuStatus0,

ROUND (ROUND(CONVERT (FLOAT ,MEM.TotalPhysicalMemory0) / 1048576, 2) * 1024, 0)

AS C083,

CASE

WHEN MEM.TotalPhysicalMemory0 IS NULL THEN 'UNKNOWN'

WHEN ROUND (ROUND(CONVERT (FLOAT ,MEM.TotalPhysicalMemory0) / 1048576, 2) * 1024, 0) < 1024 THEN 'FAIL'

ELSE 'OK' END AS Status0,

ROUND (CONVERT (FLOAT, LDISK.FreeSpace0) / 1024, 2) AS FreeSpace0,

CASE

WHEN LDISK.FreeSpace0 IS NULL THEN 'UNKNOWN'

WHEN ROUND (CONVERT (FLOAT, LDISK.FreeSpace0) / 1024, 2) < 16 THEN 'FAIL'

ELSE 'OK' END AS Status0,

MAX (DA.AdapterRAM0) / 1024 AS AdapterRAM ,a.System_OU_Name0,b.UserName0,c.LastHWScan

 

FROM v_FullCollectionMembership fcm

JOIN v_R_System SYS on fcm.ResourceID=SYS.ResourceID

LEFT OUTER JOIN v_GS_PROCESSOR PROC1 on SYS.ResourceID = PROC1.ResourceID

LEFT OUTER JOIN v_GS_X86_PC_MEMORY MEM on SYS.ResourceID = MEM.ResourceID

LEFT OUTER JOIN v_GS_OPERATING_SYSTEM OPSYS on SYS.ResourceID = OPSYS.ResourceID

LEFT OUTER JOIN v_GS_LOGICAL_DISK LDISK on SYS.ResourceID = LDISK.ResourceID and LDISK.DeviceID0=SUBSTRING(OPSYS.WindowsDirectory0,1,2)

LEFT OUTER JOIN v_GS_VIDEO_CONTROLLER DA on SYS.ResourceID = DA.ResourceID

LEFT OUTER JOIN v_RA_System_SystemOUName a ON a.ResourceID=sys.ResourceID

LEFT OUTER JOIN v_GS_COMPUTER_SYSTEM b ON b.ResourceID=a.ResourceID

LEFT OUTER JOIN v_GS_WORKSTATION_STATUS c ON c.ResourceID=b.ResourceID

 

WHERE fcm.CollectionID = @CollID

GROUP BY SYS.Netbios_Name0, fcm.SiteCode, OPSYS.Caption0, OPSYS.Version0,

OPSYS.CSDVersion0, PROC1.MaxClockSpeed0, MEM.TotalPhysicalMemory0,

LDISK.FreeSpace0,a.System_OU_Name0,b.UserName0 ,c.LastHWScan

ORDER BY 6, 8, 10, 12, 13, 1

[peled]

I would suggest you to refer this document avilable here on http://www.microsoft...&displaylang=en for list of Views and avilable columns.

 

 

Thanks Eswar.

Graet information about reports.

 

The OU data is causing dupleaction for each computer.

Is there a way to get it in other way?

If not i will have to work it out in Excel

 

Thanks

[wmmayms]

Thanks Eswar.

Graet information about reports.

 

The OU data is causing dupleaction for each computer.

Is there a way to get it in other way?

If not i will have to work it out in Excel

 

Thanks

 

Here you go:

 

 

Code:

 

SELECT DISTINCT SYS.Netbios_Name0, fcm.SiteCode,
OPSYS.Caption0 AS 'Operating System', OPSYS.Version0 AS 'Version',
OPSYS.CSDVersion0 AS 'Service Pack Level',
CASE 
WHEN (OPSYS.Caption0 LIKE '%Server%')
OR (OPSYS.Version0 < '6.0.6001')
OR (OPSYS.Version0 = '6.0.6001' AND OPSYS.CSDVersion0 < 'Service Pack 1') THEN 'FAIL'
WHEN OPSYS.Caption0 IS NULL THEN 'UNKNOWN' 
ELSE 'OK' END AS Status0,
ROUND (CONVERT (FLOAT, PROC1.MaxClockSpeed0), -2)/1000 AS 'Processor (GHz)',
CASE 
WHEN PROC1.MaxClockSpeed0 IS NULL THEN 'UNKNOWN'
WHEN ROUND (CONVERT (FLOAT, PROC1.MaxClockSpeed0), -2)/1000 < 1 THEN 'FAIL' 
ELSE 'OK' END AS CpuStatus0,
ROUND (ROUND(CONVERT (FLOAT ,MEM.TotalPhysicalMemory0) / 1048576, 2) * 1024, 0)
AS C083,
CASE 
WHEN MEM.TotalPhysicalMemory0 IS NULL THEN 'UNKNOWN'
WHEN ROUND (ROUND(CONVERT (FLOAT ,MEM.TotalPhysicalMemory0) / 1048576, 2) * 1024, 0) < 1024 THEN 'FAIL' 
ELSE 'OK' END AS Status0,
ROUND (CONVERT (FLOAT, LDISK.FreeSpace0) / 1024, 2) AS FreeSpace0,
CASE 
WHEN LDISK.FreeSpace0 IS NULL THEN 'UNKNOWN'
WHEN ROUND (CONVERT (FLOAT, LDISK.FreeSpace0) / 1024, 2) < 16 THEN 'FAIL' 
ELSE 'OK' END AS Status0,
MAX (DA.AdapterRAM0) / 1024 AS AdapterRAM ,MAX (a.System_OU_Name0) AS 'System OU',sys.User_Name0,c.LastHWScan

FROM v_FullCollectionMembership fcm
JOIN v_R_System SYS on fcm.ResourceID=SYS.ResourceID
LEFT OUTER JOIN v_GS_PROCESSOR PROC1 on SYS.ResourceID = PROC1.ResourceID
LEFT OUTER JOIN v_GS_X86_PC_MEMORY MEM on SYS.ResourceID = MEM.ResourceID
LEFT OUTER JOIN v_GS_OPERATING_SYSTEM OPSYS on SYS.ResourceID = OPSYS.ResourceID
LEFT OUTER JOIN v_GS_LOGICAL_DISK LDISK on SYS.ResourceID = LDISK.ResourceID and LDISK.DeviceID0=SUBSTRING(OPSYS.WindowsDirectory0,1,2)
LEFT OUTER JOIN v_GS_VIDEO_CONTROLLER DA on SYS.ResourceID = DA.ResourceID
LEFT OUTER JOIN v_RA_System_SystemOUName a ON a.ResourceID=sys.ResourceID
LEFT OUTER JOIN v_GS_WORKSTATION_STATUS c ON c.ResourceID=sys.ResourceID

WHERE fcm.CollectionID = @CollID
GROUP BY SYS.Netbios_Name0, fcm.SiteCode, OPSYS.Caption0, OPSYS.Version0, 
OPSYS.CSDVersion0, PROC1.MaxClockSpeed0, MEM.TotalPhysicalMemory0,
LDISK.FreeSpace0,sys.User_Name0 ,c.LastHWScan

ORDER BY 6, 8, 10, 12, 13, 1

 

 

Enjoy

[peled]

Here you go:

 

 

Enjoy

 

That was realy very good.

Another Problem .... how can i add numbers of CPU Cores to this report?

I'm trying to lern from the document you sent me how to do it my self but untill then... it is realy arjent so i need your help again :-)

 

Thanks

[Eswar Koneti]

That was realy very good.

Another Problem .... how can i add numbers of CPU Cores to this report?

I'm trying to lern from the document you sent me how to do it my self but untill then... it is realy arjent so i need your help again :-)

 

Thanks

 

 

make use of this report to get the processor information http://eskonr.com/2009/12/sccm-report-for-computers-asset-information-including-sn-number-and-model-name/