Month – February 2013

MAC Address report

For full details please see: http://social.technet.microsoft.com/Forums/en-ca/configmgrreporting/thread/19e0771c-eddc-43f7-956c-a5c9f0110e59   SELECT     A.Name0,     B.SerialNumber0 ,     A.Manufacturer0,     A.Model0,     C.Name0 ,     D.TotalPhysicalMemory0 ,     sum(E.Size0) ,     F.MACAddress0 ,     F.IPAddress0 ,     G.AD_Site_Name0 ,     A.UserName0 ,     H.Caption0 ,     H.CSDVersion0,     G.Creation_Date0 ,     I.LastHWScan–, —    BL.DriveLetter0, —    BL.ProtectionStatus0, —    BL.PersistentVolumeID0, —    […]

Adding total HDD and RAM size Part 2

For full details, please see http://social.technet.microsoft.com/Forums/en-ca/configmgrai/thread/7951b781-8354-490b-9b59-4fe4e68d1975 SELECT DISTINCT CS.Name0, CASE WHEN ES.ChassisTypes0 in (‘3′,’4′,’6′,’7′,’15’) THEN ‘Desktop’ WHEN ES.ChassisTypes0 in (‘8′,’9′,’10’,’21’) THEN ‘Laptop’ Else ‘Unknown’ END as ‘Chassis’, BIOS.SerialNumber0, CS.Manufacturer0, CS.Model0, OS.Caption0, OS.CSDVersion0, SCUM.TopConsoleUser0, R.User_Name0 AS ‘Last Logged’, BIOS.ReleaseDate0, NAC.IPAddress0, NAC.DefaultIPGateway0, RAM.TotalPhysicalMemory0, Sum(LD.Size0) FROM dbo.v_R_System R INNER JOIN dbo.v_GS_PC_BIOS BIOS ON BIOS.ResourceID = R.ResourceID INNER JOIN […]

Adding Ram and HD

  SELECT DISTINCT CS.Name0, CASE WHEN ES.ChassisTypes0 in (‘3′,’4′,’6′,’7′,’15’) THEN ‘Desktop’ WHEN ES.ChassisTypes0 in (‘8′,’9′,’10’,’21’) THEN ‘Laptop’ Else ‘Unknown’ END as ‘Chassis’, BIOS.SerialNumber0, CS.Manufacturer0, CS.Model0, OS.Caption0, OS.CSDVersion0, SCUM.TopConsoleUser0, R.User_Name0 AS ‘Last Logged’, BIOS.ReleaseDate0, NAC.IPAddress0, NAC.DefaultIPGateway0, RAM.TotalPhysicalMemory0, LD.DeviceID0, LD.Size0 FROM dbo.v_R_System R INNER JOIN dbo.v_GS_PC_BIOS BIOS ON BIOS.ResourceID = R.ResourceID INNER JOIN dbo.v_GS_COMPUTER_SYSTEM CS ON R.ResourceID […]

Microsoft True Up Report

For full details please see http://www.myitforum.com/Forums/tm.aspx?m=238674&high=   SELECT distinct DisplayName0, Count(arp.ResourceID) AS ‘Count’, Publisher0, @CollID as CollectionID FROM dbo.v_Add_Remove_Programs arp JOIN dbo.v_FullCollectionMembership fcm on arp.ResourceID = fcm.ResourceID WHERE fcm.CollectionID = @CollID AND (Publisher0 LIKE ‘Microsoft%’) AND DisplayName0 NOT LIKE ‘%Hotfix%’ AND DisplayName0 NOT LIKE ‘%Security Update%’ AND DisplayName0 NOT LIKE ‘%Update for%’ AND DisplayName0 NOT […]