Month – November 2012

Asset Information with User Full name

For full details please see:     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 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   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: 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   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