Computer Make & Model Report

First Report I'd like to share is my Computers Make & Model report. It's a simple report that lists all computers in a collection with it's make and model plus some other information. The two main features of this report is that it displays the correct model name for Levovo's and the little colored square that visualizes changes from green ,to yellow to red based on hos long the computer have been offline.

For Lenovos you get the Model ,default they show up as for instance "23444TG" instead of "Thinkpad T430"... 

Download Client - Computer Details.RDL

To get this report up and running in your environment you just need to download the .RDL file and import it into your environment. Then you need to edit it in Report Builder and change the "Datasource1" to point to your Configmgr SQL.

The report should now work(sort of) ,there are still some stuff you might want to change:

• The Bar Chart have a filter to only show models that there's 3 or more of.(Category Group Filter)
• The list of avaible collections is controlled by the parameter collectionID witch in turn gets its values from the "Dataset2" query. Change the "Dataset2" query to something like:

SELECT Name, CollectionIDFROM v_Collection where Name Like '%yourKeyword%'Order by Name asc

• The Dataset contains some more information that you can add to the tablix if you want ,for instance "Chassis Type" 
• The values for the colured square are controlled by the two parameters colourfadeyellow and colourfadered that ,you guessed it...controlles how many days the computers need to be offline before the colour starts fading to yellow and red.

If you are interested in just the query ,here it is:

SELECT DISTINCT dbo.v_r_system.resourceid, 

                dbo.v_r_system.name0 
                AS ComputerName, 
                dbo.v_r_system.resource_domain_or_workgr0 
                AS Domain, 
                dbo.v_r_system.user_name0 
                AS LastloggedonUser, 
                Max(dbo.v_ra_system_systemouname.system_ou_name0) 
                AS OU, 
                dbo.v_gs_computer_system.manufacturer0 
                AS Manufacturer, 
                CASE 
                  WHEN dbo.v_gs_computer_system.manufacturer0 LIKE 'Lenovo' THEN 
                  v_gs_computer_system_product.version0 
                  ELSE dbo.v_gs_computer_system.model0 
                END 
                AS Model, 
                dbo.v_gs_pc_bios.serialnumber0 
                AS Serialnumber, 
                CASE 
                  WHEN dbo.v_gs_computer_system.manufacturer0 LIKE 'Lenovo' THEN 
                  v_gs_computer_system.model0 
                  ELSE dbo.v_gs_computer_system_product.version0 
                END 
                AS Details, 
                dbo.v_gs_operating_system.caption0 
                AS OS, 
                dbo.v_gs_operating_system.csdversion0 
                AS ServicePack, 
                Replace(dbo.v_gs_processor.name0, 'Intel(R) Core(TM)', 
                'Intel Core') AS CPU, 
                dbo.v_gs_processor.maxclockspeed0 
                AS CPUinGhz, 
                Round(Round(CONVERT(FLOAT, 
                            dbo.v_gs_x86_pc_memory.totalphysicalmemory0) / 
                            1048576, 2) 
                      * 1024, 0) 
                AS MemoryInMB, 
                Round(CONVERT(FLOAT, dbo.v_gs_logical_disk.size0) / 1024, 2) 
                AS DisksizeinGB, 
                CASE 
                  WHEN v_gs_system_enclosure.chassistypes0 LIKE '1' THEN 
                  'Virtual' 
                  WHEN v_gs_system_enclosure.chassistypes0 LIKE '2' THEN 
                  'Blade Server' 
                  WHEN v_gs_system_enclosure.chassistypes0 LIKE '3' THEN 
                  'Desktop' 
                  WHEN v_gs_system_enclosure.chassistypes0 LIKE '4' THEN 
                  'Low-Profile Desktop' 
                  WHEN v_gs_system_enclosure.chassistypes0 LIKE '5' THEN 
                  'Pizza-Box' 
                  WHEN v_gs_system_enclosure.chassistypes0 LIKE '6' THEN 
                  'Mini Tower' 
                  WHEN v_gs_system_enclosure.chassistypes0 LIKE '7' THEN 'Tower' 
                  WHEN v_gs_system_enclosure.chassistypes0 LIKE '8' THEN 
                  'Portable' 
                  WHEN v_gs_system_enclosure.chassistypes0 LIKE '9' THEN 
                  'Laptop' 
                  WHEN v_gs_system_enclosure.chassistypes0 LIKE '10' THEN 
                  'Notebook' 
                  WHEN v_gs_system_enclosure.chassistypes0 LIKE '11' THEN 
                  'Hand-Held' 
                  WHEN v_gs_system_enclosure.chassistypes0 LIKE '12' THEN 
                  'Mobile Device in Docking Station' 
                  WHEN v_gs_system_enclosure.chassistypes0 LIKE '13' THEN 
                  'All-in-One' 
                  WHEN v_gs_system_enclosure.chassistypes0 LIKE '14' THEN 
                  'Sub-Notebook' 
                  WHEN v_gs_system_enclosure.chassistypes0 LIKE '15' THEN 
                  'Space Saving Chassis' 
                  WHEN v_gs_system_enclosure.chassistypes0 LIKE '16' THEN 
                  'Ultra Small Form Factor' 
                  WHEN v_gs_system_enclosure.chassistypes0 LIKE '17' THEN 
                  'Server Tower Chassis' 
                  WHEN v_gs_system_enclosure.chassistypes0 LIKE '18' THEN 
                  'Mobile Device in Docking Station' 
                  WHEN v_gs_system_enclosure.chassistypes0 LIKE '19' THEN 
                  'Sub-Chassis' 
                  WHEN v_gs_system_enclosure.chassistypes0 LIKE '20' THEN 
                  'Bus-Expansion chassis' 
                  WHEN v_gs_system_enclosure.chassistypes0 LIKE '21' THEN 
                  'Peripheral Chassis' 
                  WHEN v_gs_system_enclosure.chassistypes0 LIKE '22' THEN 
                  'Storage Chassis' 
                  WHEN v_gs_system_enclosure.chassistypes0 LIKE '23' THEN 
                  'Rack-Mounted Chassis' 
                  WHEN v_gs_system_enclosure.chassistypes0 LIKE '24' THEN 
                  'Sealed-Case PC' 
                  ELSE 'Unknown' 
                END 
                AS [Chassis Type], 
                health.lastactivetime 
                AS Active 
FROM   dbo.v_r_system 
       LEFT OUTER JOIN dbo.v_gs_computer_system 
                    ON dbo.v_r_system.resourceid = 
                       dbo.v_gs_computer_system.resourceid 
       LEFT OUTER JOIN dbo.v_ra_system_systemouname 
                    ON dbo.v_r_system.resourceid = 
                       dbo.v_ra_system_systemouname.resourceid 
       LEFT OUTER JOIN dbo.v_gs_pc_bios 
                    ON dbo.v_r_system.resourceid = dbo.v_gs_pc_bios.resourceid 
       LEFT OUTER JOIN dbo.v_gs_operating_system 
                    ON dbo.v_r_system.resourceid = 
                       dbo.v_gs_operating_system.resourceid 
       LEFT OUTER JOIN dbo.v_gs_x86_pc_memory 
                    ON dbo.v_r_system.resourceid = 
                       dbo.v_gs_x86_pc_memory.resourceid 
       LEFT OUTER JOIN dbo.v_gs_processor 
                    ON dbo.v_r_system.resourceid = dbo.v_gs_processor.resourceid 
       LEFT OUTER JOIN dbo.v_gs_logical_disk 
                    ON dbo.v_r_system.resourceid = 
                       dbo.v_gs_logical_disk.resourceid 
                       AND dbo.v_gs_logical_disk.deviceid0 = Substring( 
       dbo.v_gs_operating_system.windowsdirectory0, 1, 2) 
       LEFT OUTER JOIN dbo.v_gs_system_enclosure 
                    ON dbo.v_r_system.resourceid = 
                       dbo.v_gs_system_enclosure.resourceid 
       LEFT OUTER JOIN dbo.v_gs_computer_system_product 
                    ON dbo.v_r_system.resourceid = 
                       dbo.v_gs_computer_system_product.resourceid 
       RIGHT OUTER JOIN v_ch_clientsummary health 
                     ON health.resourceid = dbo.v_r_system.resourceid 
WHERE  ( dbo.v_gs_pc_bios.serialnumber0 IS NOT NULL ) 
       AND ( dbo.v_r_system.client0 = 1 ) 
AND dbo.v_r_system.resourceid IN (SELECT resourceid  
                                  FROM   dbo.v_fullcollectionmembership  
                                  WHERE  
    dbo.v_fullcollectionmembership.collectionid  
    = @CollectionID)  
GROUP  BY dbo.v_r_system.resourceid, 
          dbo.v_r_system.name0, 
          dbo.v_r_system.resource_domain_or_workgr0, 
          dbo.v_r_system.user_name0, 
          dbo.v_gs_computer_system.manufacturer0, 
          dbo.v_gs_computer_system.model0, 
          dbo.v_gs_pc_bios.serialnumber0, 
          dbo.v_gs_computer_system_product.version0, 
          dbo.v_gs_operating_system.caption0, 
          dbo.v_gs_operating_system.csdversion0, 
          dbo.v_gs_processor.name0, 
          dbo.v_gs_processor.maxclockspeed0, 
          dbo.v_gs_x86_pc_memory.totalphysicalmemory0, 
          dbo.v_gs_logical_disk.size0, 
          dbo.v_r_system.creation_date0, 
          dbo.v_gs_system_enclosure.chassistypes0, 
          health.lastactivetime