Garth Jones at myITforum.com
What is on his mind, aka Does he have a mind?
Browse by Tags
All Tags
»
Code
(
RSS
)
70-293
70-294
70-299
70-400
70-401
70-402
70-403
70-536
70-620
70-622
70-640
70-642
70-643
70-652
ConfigMGr
exam
self-study
SMS
SQL
Study group
Training
User Groups
Virtual Server
VS 2008
Windows 2008
WQL
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
Go
This Blog
Home
Contact
Links
About
Tags
2830
70-293
70-294
70-298
70-299
70-400
70-401
70-402
70-403
70-431
70-536
70-620
70-622
70-624
70-640
70-642
70-643
70-646
70-647
70-649
70-652
A+
Canada
Code
ConfigMGr
CTE Solutions
EnergizeIT
exam
Fun Links
Hyper-v
Joke
MCSE
MOM
MS Support
MVP
OpsMgr
Ottawa
Ottawa Windows Server User Group
Podcast
Q&A
Q&A
SCE
SCMDM
SCSM
SCVMM
Sector
self-study
SMS
SoftGrid
SQL
Study group
System Center
TechDays Canada
Training
UK
User group
User Groups
Virtual Server
Vista
VS 2008
Windows 2003
Windows 2008
WQL
Navigation
Site Home
Home
Bloggers List
Blogs
Photos
Downloads
Archives
October 2008 (14)
September 2008 (59)
August 2008 (72)
July 2008 (57)
June 2008 (46)
May 2008 (39)
April 2008 (4)
January 2007 (1)
November 2006 (5)
October 2006 (5)
May 2006 (5)
April 2006 (3)
March 2006 (7)
February 2006 (4)
January 2006 (8)
December 2005 (2)
November 2005 (1)
October 2005 (5)
September 2005 (6)
August 2005 (9)
April 2005 (1)
March 2005 (1)
February 2005 (2)
January 2005 (10)
December 2004 (26)
Syndication
RSS
Atom
Comments RSS