Garth Jones at myITforum.com
What is on his mind, aka Does he have a mind?
Browse by Tags
All Tags
»
SQL
»
ConfigMGr
(
RSS
)
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
70-671
70-672
70-673
A+
Canada
Code
exam
Flowcharts
Fun Links
Hyper-v
Joke
MCSE
MDT
MOM
MS Support
MVP
OpsMgr
Ottawa
Podcast
Q&A
SCE
SCMDM
SCSM
SCVMM
self-study
SMS
SoftGrid
Study group
System Center
Training
User Groups
Virtual Server
Vista
VS 2008
Windows 2003
Windows 2008
WQL
myITForum Donation
Thursday, January 08, 2009 9:23 AM
With the start of a new year, it is that time again to support the System Management Community, with that in mind I have made my yearly donation to MyITForum (MIF). Why do I do it? Well as a User Group leader for www.OWSUG.ca , I know how much it cost to host a small user group community. OWSUG is extremely small compared to MIF! Our network traffic is nothing...
Read More...
Finding all chained apps
Thursday, December 18, 2008 5:44 PM
SELECT PRO.ProgramName, PRO.PackageID, PRO.CommandLine, PRO.Comment, PRO.Description, PRO.DependentProgram FROM dbo.v_Program PRO WHERE PRO.DependentProgram !=''
Read More...
1st Anniversary
Thursday, December 11, 2008 10:30 AM
Well today is 1 st anniversary of me taking steps to upgrade my certifications to Windows 2003 / Windows 2008. As many of you know I have blog about my experiences with all the exams. So this blog post is designed to talk about a few things. Which exam I write and pass? Which certifications did I achieve? What exam tips can I give you? Which exams did I write...
Read More...
ARP for Collection SMS000ES
Wednesday, December 10, 2008 8:32 AM
SELECT CS.Name0, CSUM.TopConsoleUser0, ARP.DisplayName0, ARP.Publisher0, ARP.Version0 FROM dbo.v_GS_ADD_REMOVE_PROGRAMS ARP, dbo.v_GS_COMPUTER_SYSTEM CS, dbo.v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP CSUM, dbo._RES_COLL_SMS000ES ES WHERE CS.ResourceID = ARP.ResourceID AND CS.ResourceID = CSUM.ResourceID AND CS.ResourceID = ES.MachineID
Read More...
NIC PNP Devices
Tuesday, December 09, 2008 8:44 AM
SELECT DISTINCT PNP.DeviceID0, PNP.Name0, PNP.PNPDeviceID0 FROM dbo.v_GS_NETWORK_ADAPTER NA, dbo.v_GS_PNP_DEVICE_DRIVER PNP WHERE NA.Name0 = PNP.Name0 AND NA.MACAddress0 Is Not Null
Read More...
PC, OS, SP1 and IP Address
Friday, December 05, 2008 6:39 PM
SELECT CS.Name0, OS.Caption0, OS.CSDVersion0, NAC.IPAddress0 FROM dbo.v_GS_Computer_System CS JOIN dbo.v_GS_NETWORK_ADAPTER_CONFIGUR NAC on CS.ResourceID = NAC.ResourceID JOIN dbo.v_GS_OPERATING_SYSTEM OS on CS.ResourceID = OS.ResourceID WHERE (NOT (NAC.IPAddress0 = 'NULL')) And NAC.IPAddress0 != '0.0.0.0' ORDER BY CS.Name0
Read More...
How to add AD data to ConfigMgr reporting
Wednesday, December 03, 2008 3:25 PM
First off, this will not be supported by Microsoft or me. :-) AD it just another database, just like SQL server is. With that in mind there is nothing stopping you from using SQL to link to AD to give you data about your AD environment! 1) Create Linked Server using SSMS exec master.dbo.sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces'...
Read More...
PCs by Model
Friday, November 28, 2008 8:32 AM
SELECT CS.Name0, CS.Model0 FROM dbo.v_GS_COMPUTER_SYSTEM CS WHERE CS.Model0='PowerEdge 2850'
Read More...
List PC with site code and site server name
Sunday, November 23, 2008 9:49 PM
SELECT CS.Name0, RA_SIS.SMS_Installed_Sites0, S.ServerName FROM dbo.v_GS_COMPUTER_SYSTEM CS, dbo.v_RA_System_SMSInstalledSites RA_SIS, dbo.v_Site S WHERE CS.ResourceID = RA_SIS.ResourceID AND RA_SIS.SMS_Installed_Sites0 = S.SiteCode
Read More...
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...
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...
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...
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...
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
70-671
70-672
70-673
A+
Canada
Code
ConfigMGr
CTE Solutions
EnergizeIT
exam
Flowcharts
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
January 2009 (4)
December 2008 (13)
November 2008 (15)
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