Garth Jones at myITforum.com
What is on his mind, aka Does he have a mind?
Browse by Tags
All Tags
»
SQL
(
RSS
)
70-431
Canada
Code
ConfigMGr
Fun Links
Hyper-v
MVP
OpsMgr
SCVMM
self-study
SMS
Training
User Groups
Virtual Server
VS 2008
Windows 2008
SQL and virtual technologies
Friday, November 14, 2008 3:48 PM
Last night I was at the Ottawa SQL Group event helping out and I found out an interesting tidbit. This tidbit is only for production environments only, in Dev or in test labs, anything goes. SQL 2000 are NOT supported under at virtualization product! Now some people are going to asked about SQL 2005 & 2008, yes it is supported HOWEVER there is only one supported...
Read More...
DevTeach...
Friday, November 14, 2008 9:28 AM
DevTeach Montreal is less than a month away but it's not too late to register. This is a great conference with sessions covering .NET FX, Future, SQL Server, VSTS/Team System, Silverlight, Agile Development, Software Architecture, ASP.NET. Aside from the great content, build up your professional network by rubbing shoulders with the speakers in an intimate...
Read More...
One reason to use network discovery
Thursday, November 13, 2008 8:30 AM
Are you wanting to know how to determine which PCs within your environment have vPro? Well you will need to turn on Network Discovery to add this information to your db. http://technet.microsoft.com/en-ca/library/cc431387.aspx Here is the SQL query to tell you. Select * from v_R_System where AMTStatus0=3
Read More...
OU Count of PCs not reporting within 1 day
Saturday, November 01, 2008 11:35 AM
SELECT OU.System_OU_Name0, count(*) as 'Count' FROM dbo.v_GS_WORKSTATION_STATUS as WS, dbo.v_R_System as SYS, dbo.v_RA_System_SystemOUName as OU Where SYS.ResourceID = OU.ResourceID and SYS.ResourceID = WS.ResourceID and Datediff(dd,WS.LastHWScan, getdate()) > 1 group by OU.System_OU_Name0 order by OU.System_OU_Name0
Read More...
List of collection and their parent collection
Friday, October 31, 2008 10:19 AM
SELECT COL.CollectionID, COL.Name, COL.Comment, CTSC.parentCollectionID FROM dbo.v_Collection COL, dbo.v_CollectToSubCollect CTSC WHERE CTSC.subCollectionID = COL.CollectionID
Read More...
Creating Custom Reports by Using Configuration Manager 2007 SQL Views
Thursday, October 30, 2008 8:21 AM
The Creating Custom Reports by Using Configuration Manager 2007 SQL Views documentation is out. This documentation provides the information to help you create custom reports, determine which Configuration Manager 2007 SQL views contain the information you need for your reports, and identify the path between the necessary SQL views and determine what columns can...
Read More...
Upgrading and Migrating to SQL Server 2008
Wednesday, October 29, 2008 9:28 AM
Free Evening Event @ Microsoft Ottawa Thursday, November 13 th 5:15 - 8:00 PM Feature Presentation: Upgrading and Migrating to SQL Server 2008 Best Practices and Lessons from the Field, Damir Bersinic Damir will be in town for the Ottawa stop of the Align IT tour on November 13 th . He will be taking this opportunity to share some of his SQL Server 2008 experience...
Read More...
Find everyone with two or more workstations
Tuesday, October 28, 2008 1:56 PM
SELECT CS.UserName0 Into #tmp FROM dbo.v_GS_Computer_system CS group by CS.UserName0 Having Count(CS.UserName0) > 1 SELECT CS.Name0, CS.Manufacturer0, CS.Model0, CS.UserName0 FROM dbo.v_GS_Computer_system CS, #tmp Where #tmp.UserName0 = CS.UserName0 or CS.UserName0 != Null Order by CS.UserName0, CS.Name0, CS.Manufacturer0, CS.Model0 Drop table #tmp
Read More...
What is in your Quick Launch bar?
Friday, October 24, 2008 8:47 AM
Listed in order: Show Desktop Internet Explorer Outlook Live Writer Microsoft Query Microsoft SQL Server Management Studio SnagIt 8 ConfigMgr help file SCVMM Console VMCRPlus Hyper-V Manager ConfigMgr SDK help file ConfigMgr Console OpsMgr Console
Read More...
Exclude this software ...
Sunday, October 19, 2008 2:59 PM
select distinct R.Netbios_Name0, R.User_Name0, R.AD_Site_Name0, R.Active0, R.Client0, R.Obsolete0, ARP.DisplayName0, ARP.Version0, R.Operating_System_Name_and0 from v_R_System R inner join v_GS_ADD_REMOVE_PROGRAMS ARP on ARP.ResourceID = R.ResourceId where ARP.DisplayName0 like 'AutoCAD%' or ARP.DisplayName0 like 'Autodesk%' or ARP.DisplayName0...
Read More...
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...
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...
Maintenance Windows
Tuesday, September 16, 2008 1:43 PM
SELECT COL.CollectionID, COL.Name, COL.Comment, MW.Name, MW.Description, MW.StartTime, MW.Duration, MW.UseGMTTimes FROM dbo.v_Collection COL, dbo.vMWS_ServiceWindow MW WHERE COL.CollID = MW.CollectionID
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...
Count of Unidentified & Uncategorized software
Thursday, September 04, 2008 2:56 PM
SELECT ISC.NormalizedPublisher, ISC.NormalizedName, ISC.NormalizedVersion, ISC.CategoryName, count(*) as 'count' FROM dbo.v_GS_Installed_Software_Categorized ISC WHERE ISC.FamilyName In ('Unidentified','Uncategorized') group by ISC.NormalizedPublisher, ISC.NormalizedName, ISC.NormalizedVersion, ISC.CategoryName order by ISC.NormalizedPublisher...
Read More...
DevTeach & SQLTeach
Wednesday, September 03, 2008 1:49 PM
Get a $50 discount to SQL Teach! Our user group has been give a discount code that will give you $50 off of the conference, use the code ON00NEPEAN to get your discount to SQLTeach / DevTeach!
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...
Add Date filter to Advert status
Thursday, August 14, 2008 10:21 AM
SELECT CS.Name0, adv.AdvertisementName, stat.LastStateName, adv.Comment AS C072, pkg.Name AS C062, adv.ProgramName AS C071, adv.SourceSite, adv.AdvertisementID FROM v_Advertisement adv JOIN v_Package pkg ON adv.PackageID = pkg.PackageID JOIN v_ClientAdvertisementStatus stat ON stat.AdvertisementID = adv.AdvertisementID JOIN v_GS_Computer_System CS ON stat.ResourceID...
Read More...
Managers and web reports
Thursday, August 14, 2008 9:56 AM
During a discussion on the myITforum ConfigMgr /SMS mailing this statement was made “ It’s never bad admin mojo to provide a report based on what management seeks. You can either do it or you can’t. “ Without going into all of the details of what this person was looking, I will attempt to explain why what this person was looking for is a bad idea. This is the...
Read More...
More Posts
Next page »
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-635
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
MDT
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
November 2008 (10)
October 2008 (31)
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