Month – November 2012

Asset Information with User Full name

For full details please see: http://social.technet.microsoft.com/Forums/en-US/configmgrai/thread/107d690a-4bbd-49c4-87bf-389278525b63     select distinct R.ResourceID, R.User_Name0, R.Netbios_Name0 AS ‘Computer_Name’, R.Resource_Domain_OR_Workgr0 AS ‘Domain_workgroup’, S.SiteName as ‘SMSSiteName’, OS.Caption0 AS ‘OperatingSystem’, OS.CSDVersion0 AS ‘ServicePackLevel’, SEQ.SerialNumber0 AS ‘SerialNumber’, SEQ.SMBIOSAssetTag0 AS ‘AssetTag’, CS.Manufacturer0 AS ‘Manufacturer’, CS.Model0 AS ‘Model’, WS.LastHWScan AS ‘Last_Scan_Date’, RAM.TotalPhysicalMemory0 AS ‘Memory_KBytes’, Pro.NormSpeed0 AS ‘Processor_GHz’, (Select sum(LD.Size0) from v_GS_LOGICAL_DISK LD where LD.ResourceID […]

PC with MAC Address

For full details see http://social.technet.microsoft.com/Forums/en-US/configmgrreporting/thread/6c00b44f-1b92-4c1b-924e-fd79c191d5e9 Select R.Netbios_Name0, MAC.MAC_Addresses0 from dbo.v_R_System R join dbo.v_RA_System_MACAddresses MAC ON R.ResourceID = MAC.ResourceID Where MAC.MAC_Addresses0 in ( SELECT MAC.MAC_Addresses0 FROM dbo.v_RA_System_MACAddresses MAC GROUP BY MAC.MAC_Addresses0 having (Count(MAC.MAC_Addresses0)>1) )

Softwares installed in last 90 days on a collection of PC

For full details, please see http://social.technet.microsoft.com/Forums/en-US/configmgrreporting/thread/80c794d7-a55f-4d13-985b-9b84efd6f9e7   SELECT R.Name0 as ‘PC’, CS.Manufacturer0 as ‘Man’, CS.Model0 as ‘Model’, ARP.DisplayName0 as ‘DisplayName’, ARP.Version0 as ‘Version’, installdate0 as ‘IDate’, datediff(dd,convert(datetime,cast(installdate0 as datetime),101),getdate()) as ‘DateD’ into #temp FROM dbo.v_R_System R JOIN dbo.v_ADD_REMOVE_PROGRAMS ARP ON R.ResourceID = ARP.ResourceID and isdate(arp.installdate0) = 1 JOIN dbo.v_GS_COMPUTER_SYSTEM CS ON R.ResourceID = CS.ResourceID JOIN […]

Office, Visio, Project with OS name.

For full details please see: http://social.technet.microsoft.com/Forums/en-US/configmgrgeneral/thread/66dbc370-9795-4f98-9193-d8f0bd84830b/ SELECT Distinct R.Name0, ARP.DisplayName0, OS.Caption0 FROM dbo.v_R_System R JOIN dbo.v_ADD_REMOVE_PROGRAMS ARP ON R.ResourceID = ARP.ResourceID Join dbo.v_GS_SYSTEM S ON S.ResourceID = ARP.ResourceID join dbo.v_GS_OPERATING_SYSTEM OS ON OS.ResourceID = ARP.ResourceID WHERE (ARP.DisplayName0 LIKE ‘%MICROSOFT oFFICE project%’ or ARP.DisplayName0 LIKE ‘%Microsoft Office Visio%’ or ARP.DisplayName0 LIKE ‘%MICROSOFT oFFICE %’) and S.SystemRole0 […]

Count of Video Controller details.

For full details, please see http://social.technet.microsoft.com/Forums/en-US/configmgrreporting/thread/38f8a5c1-3b82-4ca4-b664-167b6c8226c5   select r.netbios_name0 as nm, Count(r.netbios_name0) from v_GS_VIDEO_CONTROLLER vc join v_R_System R on VC.ResourceID = R.ResourceID where vc.CurrentHorizontalResolution0 not like ” Group by r.netbios_name0 order by R.netbios_name0