Browse by Tags

Count of how often clients report HW inventory
Wednesday, December 05, 2012 9:03 AM
For full detail see. http://social.technet.microsoft.com/Forums/en-US/configmgrreporting/thread/32c4db2f-ae84-4fed-93c1-c3f35f8a6fff     I liked this request, I might ever try to make an NICE SSRS report out of this one. But not for a few weeks, so here you go.   select CS.Name0 as 'PC' , Count(HLD.ResourceID)+1 as 'HW inv' from... Read More...
Asset Information with User Full name
Wednesday, November 21, 2012 8:43 AM
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'... Read More...
PC with MAC Address
Saturday, November 17, 2012 6:42 PM
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... Read More...
by Garth Jones
Filed under: , , , , , ,
SCCM 2012 Table has multiple records for Server IP
Tuesday, October 23, 2012 6:03 PM
For full details see http://social.technet.microsoft.com/Forums/en-US/configmanagergeneral/thread/43686ca9-3064-4533-8f94-f28c889b4511/   HS view are for History data, not current data. RA views are discovery data views. vSMS are “extra” views.. I don’t generally use them.   It is generally better to limit the use of NOT LIKE statements Here is the... Read More...
by Garth Jones
Filed under: , , , ,
PC by Gateway
Thursday, October 18, 2012 2:39 PM
  select CS.Name0, CS.UserName0, NA.Description0, NAC.IPAddress0, NAC.MACAddress0, NAC.DefaultIPGateway0 From dbo.v_GS_COMPUTER_SYSTEM CS join dbo.v_GS_NETWORK_ADAPTER_CONFIGUR NAC on NAC.ResourceID = CS.ResourceID join dbo.v_GS_NETWORK_ADAPTER NA on NA.ResourceID = CS.ResourceID and NAC.ServiceName0 = NA.ServiceName0 Where NAC.DefaultIPGateway0 = '192... Read More...
by Garth Jones
Filed under: , , ,
Software by OU
Thursday, October 18, 2012 10:24 AM
SELECT ARP.DisplayName0, Count (*) AS 'Count' , ARP.Publisher0, ARP.Version0 FROM dbo.v_Add_Remove_Programs ARP join dbo.v_RA_System_SystemOUName OU on OU.ResourceID = ARP.ResourceID WHERE OU.System_OU_Name0 = @OU GROUP BY ARP.DisplayName0, ARP.Publisher0, ARP.Version0 ORDER BY ARP.Publisher0, ARP.Version0 Read More...
IE7 Collection
Monday, October 08, 2012 2:17 PM
For full detail please see forum post   select * from SMS_R_System left join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId left join SMS_G_System_ADD_REMOVE_PROGRAMS_64 on SMS_G_System_ADD_REMOVE_PROGRAMS_64.ResourceId = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.ProdID = "ie7"... Read More...
Count of Manufacturers
Sunday, July 08, 2012 4:50 PM
For full details see: http://social.technet.microsoft.com/Forums/en-US/configmgrreporting/thread/4e4019b0-a28e-4637-ae8c-4ff523d11c1a select top 3 Manufacturer0, Count (*) from dbo.v_GS_COMPUTER_SYSTEM GROUP BY Manufacturer0 Order by 2 desc Read More...
by Garth Jones
Filed under: , , , , ,
How to find an application installed in the last 7 days
Monday, July 02, 2012 9:27 PM
For full details see http://social.technet.microsoft.com/Forums/en-US/configmgrreporting/thread/38f96191-6e3e-4150-8f3a-18b1c4db8d7d SELECT R.Netbios_name0, R.AD_Site_Name0, ARP.DisplayName0, ARP.InstallDate0, DATEDIFF( Day , CONVERT ( Datetime ,ARP.InstallDate0), GETDATE()) AS [days since installed] FROM dbo.v_R_System R join dbo.v_FullCollectionMembership FCM... Read More...
by Garth Jones
Filed under: , , , , , , ,
Finding Adobe X for both x68 and x64
Monday, July 02, 2012 9:45 AM
For full details see http://social.technet.microsoft.com/Forums/en-US/configmgrgeneral/thread/66c80e66-9760-49b3-b429-08bf2ca8f572 http://social.technet.microsoft.com/Forums/en-US/configmanagerapps/thread/b68ce42c-98f5-4500-bced-0e8e901cb211/ select distinct SMS_R_SYSTEM.ResourceID, SMS_R_SYSTEM.ResourceType, SMS_R_SYSTEM.Name, SMS_R_SYSTEM.SMSUniqueIdentifier... Read More...
by Garth Jones
Filed under: , , , , , ,
Query on user history
Sunday, July 01, 2012 9:37 AM
For full details please see http://www.myitforum.com/Forums/tm.aspx?m=230539&high = SELECT DISTINCT R.Netbios_Name0, tu.SystemConsoleUser0, tu.logontime FROM dbo.v_R_System R join ( SELECT husr.ResourceID, Husr.SystemConsoleUser0, max (Husr.LastConsoleUse0) as logontime FROM dbo.v_HS_SYSTEM_CONSOLE_USER Husr WHERE Husr.SystemConsoleUser0 LIKE ' Gartek... Read More...
by Garth Jones
Filed under: , , , , , ,
Query for computers with single partition
Saturday, June 09, 2012 5:53 PM
For full details see: http://social.technet.microsoft.com/Forums/en-US/configmgrinventory/thread/4d84e5f9-fd48-4fcb-b490-d0a64052aa08 select SMS_R_System.Name from SMS_R_System where SMS_R_System.ResourceId not in ( select SMS_G_System_PARTITION.ResourceId from SMS_G_System_PARTITION where SMS_G_System_PARTITION.DeviceID = " Disk #0, Partition #1 "... Read More...
List of PCs with Project, Visio or Visual
Friday, June 01, 2012 7:53 AM
For full details, please see this forum post. http://social.technet.microsoft.com/Forums/en-US/configmgrreporting/thread/50b2b524-3c11-41c5-a2d5-b8ae979ee3c2/ Select sys.Netbios_Name0, sys.User_Domain0, sys.User_Name0, sys.Operating_System_Name_and0, arp.DisplayName0, ARP.Version0 FROM v_R_System sys JOIN v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID... Read More...
by Garth Jones
Filed under: , , , , ,
Find Programs Removed from a PC (updated)
Monday, May 21, 2012 10:15 AM
For full details see: http://social.technet.microsoft.com/Forums/en-US/configmgrgeneral/thread/db96da44-a9e0-4996-bca2-286b6e2c473a SELECT DISTINCT CS.Name0, HARP.Publisher0 AS ' Publisher ', HARP.ProdID0 AS ' Product ', HARP.DisplayName0 AS ' Name ' FROM dbo.v_GS_COMPUTER_SYSTEM CS join dbo.v_GS_ADD_REMOVE_PROGRAMS ARP on ARP.ResourceID... Read More...
How to exclude a collection from sccm collection query
Saturday, May 19, 2012 10:47 AM
For full details, see this forums post. http://social.technet.microsoft.com/Forums/en-US/configmgrinventory/thread/75288b97-1fe6-4634-a20a-54091bba8d91   select SMS_R_SYSTEM.ResourceID, SMS_R_SYSTEM.ResourceType, SMS_R_SYSTEM.Name, SMS_R_SYSTEM.SMSUniqueIdentifier, SMS_R_SYSTEM.ResourceDomainORWorkgroup, SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_SoftwareFile... Read More...
More Posts « Previous page - Next page »

This Blog

Archives

Syndication