Browse by Tags

List ARP by PCs with name like
Thursday, October 09, 2008 8:55 AM
SELECT ARP.ProdID0, ARP.DisplayName0, ARP.Version0, CS.Name0 FROM dbo.v_Add_Remove_Programs ARP, dbo.v_GS_COMPUTER_SYSTEM CS WHERE ARP.ResourceID = CS.ResourceID AND CS.Name0 Like 'AB%' Read More...
by Garth Jones
Filed under: , , ,
Count of Max Run Time for Software Updates
Tuesday, October 07, 2008 9:00 AM
SELECT UI.MaxExecutionTime, Count(UI.MaxExecutionTime) FROM dbo.v_UpdateInfo UI GROUP BY UI.MaxExecutionTime ORDER BY UI.MaxExecutionTime Read More...
by Garth Jones
Filed under: , ,
List PSTs
Monday, October 06, 2008 10:38 AM
SELECT CS.Name0, CS.UserName0, SF.FileName, SF.FileSize, SF.FileModifiedDate, SF.FilePath FROM dbo.v_GS_COMPUTER_SYSTEM CS, dbo.v_GS_SoftwareFile SF, dbo.v_GS_SYSTEM SYS WHERE SYS.ResourceID = CS.ResourceID AND SF.ResourceID = CS.ResourceID AND SYS.SystemRole0='Workstation' AND SF.FileName Like '%.pst' Order By CS.Name0, SF.FileName Read More...
by Garth Jones
Filed under: , , ,
Find all PCs with out any v_gs_Computer_System data
Tuesday, September 30, 2008 4:36 PM
Use this query to find all PCs that don't have any data within the v_GS_* views. Right John... :-) select R.Netbios_Name0, R.* from v_R_System r where R.ResourceID not in (select cs.resourceId from v_gs_computer_system cs) Order by R.Netbios_Name0 Read More...
by Garth Jones
Filed under: , , ,
Find computers by BIOS serial number
Sunday, September 28, 2008 12:22 PM
SELECT CS.Name0, CS.UserName0, BIOS.Manufacturer0, BIOS.SerialNumber0 FROM dbo.v_GS_COMPUTER_SYSTEM CS, dbo.v_GS_PC_BIOS BIOS WHERE CS.ResourceID = BIOS.ResourceID AND BIOS.SerialNumber0=<Enter Serial Number here> Read More...
by Garth Jones
Filed under: , , ,
Simple ARP report
Thursday, September 25, 2008 12:06 PM
SELECT ARP.DisplayName0, ARP.ProdID0, ARP.Publisher0, ARP.Version0, Count(ARP.ProdID0) AS 'Count' FROM dbo.v_Add_Remove_Programs ARP GROUP BY ARP.DisplayName0, ARP.ProdID0, ARP.Publisher0, ARP.Version0 ORDER BY ARP.DisplayName0 Read More...
by Garth Jones
Filed under: , , ,
Resently Installed Programs
Thursday, September 25, 2008 7:53 AM
SELECT CS.Name0, CS.UserName0, ISW.ProductName0, ISW.VersionMajor0, ISW.VersionMinor0, ISW.Publisher0, ISW.RegisteredUser0, ISW.InstallDate0, ISW.InstallSource0 FROM dbo.v_GS_COMPUTER_SYSTEM CS, dbo.v_GS_INSTALLED_SOFTWARE ISW WHERE ISW.ResourceID = CS.ResourceID ORDER BY ISW.InstallDate0 DESC, CS.Name0, CS.UserName0, ISW.ProductName0 Read More...
by Garth Jones
Filed under: , , ,
WQL - PCs with more that 2 GB free on C:
Wednesday, September 24, 2008 4:56 PM
select distinct SMS_R_System.Name, SMS_G_System_LOGICAL_DISK.* from SMS_R_System inner join SMS_G_System_LOGICAL_DISK on SMS_G_System_LOGICAL_DISK.ResourceID = SMS_R_System.ResourceId where SMS_G_System_LOGICAL_DISK.FreeSpace > 2048 and SMS_G_System_LOGICAL_DISK.DeviceID = "C:" order by SMS_R_System.Name Read More...
by Garth Jones
Filed under: , , ,
IP History
Wednesday, September 24, 2008 4:04 PM
SELECT CS.Name0, HNAC.TimeStamp, HNAC.DefaultIPGateway0, HNAC.DHCPServer0, HNAC.DNSDomain0, HNAC.DNSHostName0, HNAC.IPAddress0, HNAC.IPSubnet0, HNAC.MACAddress0, HNAC.ServiceName0 FROM dbo.v_GS_COMPUTER_SYSTEM CS, dbo.v_HS_NETWORK_ADAPTER_CONFIGUR HNAC WHERE HNAC.ResourceID = CS.ResourceID and HNAC.DHCPEnabled0 = 1 and HNAC.IPAddress0 != 'NULL' and HNAC... Read More...
by Garth Jones
Filed under: , , ,
List all non-DHCP NICs
Tuesday, September 09, 2008 9:08 AM
SELECT CS.Name0, NA.Name0, NAC.IPAddress0, NAC.DefaultIPGateway0, NAC.IPSubnet0, NAC.DNSHostName0 FROM dbo.v_GS_COMPUTER_SYSTEM CS, dbo.v_GS_NETWORK_ADAPTER NA, dbo.v_GS_NETWORK_ADAPTER_CONFIGUR NAC WHERE NAC.ResourceID = CS.ResourceID AND NA.ResourceID = CS.ResourceID AND NA.ServiceName0 = NAC.ServiceName0 AND NAC.DHCPEnabled0<>1 AND NAC.IPAddress0<>'NULL'... Read More...
by Garth Jones
Filed under: , , ,
List routers that ConfigMgr / SMS knows about
Saturday, September 06, 2008 1:08 PM
Select RASIP.IP_addresses0, RASR.System_roles0 from dbo.v_RA_System_IPAddresses RASIP, dbo.v_RA_System_SystemRoles RASR where RASR.ResourceID = RASIP.ResourceID and RASR.System_Roles0 = 'Router' Read More...
by Garth Jones
Filed under: , , ,
PCs with the no logon information for 30 days
Wednesday, August 27, 2008 9:26 AM
SELECT distinct CS.Name0 AS 'Computer', isnull(CS.UserName0,HCS.UserName0) AS 'User Name' , CS.TimeStamp FROM v_GS_COMPUTER_SYSTEM CS Left outer join v_HS_COMPUTER_SYSTEM HCS on CS.ResourceID = HCS.ResourceID WHERE HCS.UserName0 is not NULL and datediff(dd,CS.TimeStamp,getdate()) > 30 Group by CS.Name0, CS.UserName0, CS.TimeStamp, HCS.UserName0... Read More...
by Garth Jones
Filed under: , , ,
Forum Reply - T-SQL Best Practice
Friday, August 22, 2008 9:40 AM
SELECT DISTINCT ARP.DisplayName0, ARP.Publisher0, ARP.Version0, CS.Name0, CS.UserName0, WS.LastHWScan, BIOS.SerialNumber0, R.AD_Site_Name0 FROM dbo.v_GS_ADD_REMOVE_PROGRAMS ARP INNER JOIN dbo.v_GS_Computer_System CS ON ARP.ResourceID = CS.ResourceID INNER JOIN dbo.v_GS_PC_BIOS BIOS ON CS.ResourceID = BIOS.ResourceID INNER JOIN dbo.v_R_System R ON CS.ResourceID... Read More...
by Garth Jones
Filed under: , , ,
Query Help
Thursday, August 21, 2008 8:21 AM
declare @olddate datetime set @olddate=dateadd(day,-8, getdate()) select CS.Name0 as 'Computer Name', arp.installdate0 as 'Installed Date', arp.displayname0 as 'Application Name', from dbo.v_GS_ADD_REMOVE_PROGRAMS arp, dbo.v_GS_Computer_System CS where CS.resourceid = arp.resourceid and datediff(DD,arp.installdate0, @olddate) < 8 and... Read More...
by Garth Jones
Filed under: , , ,
List all discovered devices which are not a client
Wednesday, August 20, 2008 11:24 AM
This query will display all devices (PCs, routers, etc.) that ConfigMgr / SMS knows about which are not clients. select Name0, Resource_Domain_Or_Workgr0, Operating_system_name_and0, AD_Site_Name0, Community_name0 from v_R_System R where R.ResourceID Not in (Select CS.ResourceID From v_GS_Computer_System CS) Order by Name0 Read More...
by Garth Jones
Filed under: , , ,
Find PCs by Manufacturer and Model
Friday, August 15, 2008 8:51 AM
SELECT CS.Name0, CS.UserName0 FROM dbo.v_GS_COMPUTER_SYSTEM CS WHERE CS.Manufacturer0='Microsoft Corporation' AND CS.Model0='Virtual Machine' Read More...
by Garth Jones
Filed under: , , ,
Active workstations in the last 30 days
Friday, August 08, 2008 11:28 AM
Select     CS.Name0,     WS.LastHWScan From     dbo.v_GS_COMPUTER_SYSTEM CS,     dbo.v_GS_WORKSTATION_STATUS WS Where     CS.Resourceid = WS.ResourceID     and datediff(dd,WS.LastHWScan,getdate())< 30 Read More...
by Garth Jones
Filed under: , , ,
History install date for ARP (example)
Friday, August 08, 2008 10:57 AM
Select     ARP.DisplayName0,     HARP.InstallDate0 from     dbo.v_GS_ADD_REMOVE_PROGRAMS ARP,     dbo.v_HS_ADD_REMOVE_PROGRAMS HARP Where     ARP.ResourceID = HARP.ResourceID     and ARP.ProdID0 = HARP.ProdID0 Group by        ARP.DisplayName0... Read More...
by Garth Jones
Filed under: , , ,
ARP Dates
Friday, August 08, 2008 9:49 AM
select CS.Name0 as 'Computer Name',   convert(datetime,isnull(ARP.installdate0,'1980-1-1')) as 'Installed Date' From dbo.v_GS_ADD_REMOVE_PROGRAMS ARP, dbo.v_GS_COMPUTER_SYSTEM CS Where CS.ResourceID = ARP.ResourceID Read More...
by Garth Jones
Filed under: , ,
Two NICs enabled at the same time
Friday, August 08, 2008 9:42 AM
Select Distinct     CS.Name0,     NIC.Description0,     NAC.IPAddress0,     NAC.DefaultIPGateway0,     NIC.*,     Nac.* from     dbo.v_GS_COMPUTER_SYSTEM CS,     dbo.v_GS_NETWORK_ADAPTER NIC,     dbo.v_GS_NETWORK_ADAPTER_CONFIGUR... Read More...
by Garth Jones
Filed under: , , ,
Count of Device by AD site
Wednesday, July 30, 2008 7:03 PM
Select B.ADSite as 'AD Site', Count(B.ADSite) as 'Count' from (SELECT Case When R.AD_Site_Name0 = '' Then '<No AD Site>' When R.AD_Site_Name0 = NULL Then '<No AD Site>' When isnull(R.AD_Site_Name0,'one') = 'one' Then '<No AD Site>' else R.AD_Site_Name0 End as 'ADSite'--... Read More...
by Garth Jones
Filed under: , , ,
Network Report
Tuesday, July 29, 2008 8:36 AM
Select Distinct CS.Name0, NIC.Description0, NAC.IPAddress0, NAC.DefaultIPGateway0 from dbo.v_GS_COMPUTER_SYSTEM CS, dbo.v_GS_NETWORK_ADAPTER NIC, dbo.v_GS_NETWORK_ADAPTER_CONFIGUR NAC Where CS.ResourceID = NIC.ResourceID and CS.ResourceID = NAC.ResourceID and NAC.ServiceName0 = NIC.ServiceName0 and NAC.IPAddress0 != '' and NAC.IPAddress0 != '0.0.0... Read More...
by Garth Jones
Filed under: , , ,
List PCs by PC Description
Monday, July 21, 2008 9:09 AM
select CS.Name0, OS.Description0 from dbo.v_GS_OPERATING_SYSTEM OS, dbo.v_GS_COMPUTER_SYSTEM CS Where CS.ResourceID = OS.ResourceID Order by CS.Name0 Read More...
by Garth Jones
Filed under: , , ,
What Exam should I choose?
Sunday, June 08, 2008 11:01 AM
I get this question a lot, so here is the basic answer I gave to someone else. (emails has been edited) From: Ottawa Windows Server User Group Sent: June-05-08 10:11 AM Subject: RE: 70-299, 640, 642, and 643 self-study group Information and Schedules There is no right answer to this. However I will say, this is what I use to for determining which exam to do next... Read More...