Browse by Tags

All Tags » SMS » ConfigMGr (RSS)
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...
by Garth Jones
Filed under: , , ,
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...
by Garth Jones
Filed under: , , ,
ConfigMgr Ranger Training
Tuesday, November 11, 2008 10:08 AM
So Brian posted a blog about ConfigMgr Range training , this goes alone the same lines as System Center Configuration Manager Enterprise Administrator and System Center Operation Manager Enterprise Administrator Configuration Manager needs an MCITP and so does OpsMgr! Microsoft last suggestion that the System Center MCITP ( 70-402 ) would included both 70-400... Read More...
by Garth Jones
Filed under: , , , , , , , ,
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...
by Garth Jones
Filed under: , , ,
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...
by Garth Jones
Filed under: , , ,
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...
by Garth Jones
Filed under: , , ,
Tip of the Day!!! How to make CHM files readable!
Monday, October 20, 2008 1:46 PM
How to fix the font issue with CHM files!!!! This has been bugging me for a while so much so that I begged one of the docs team member to help me fix the issue!!! Notice that the text above is unreadable!!!! Fix: 1. From the CHM Menu click Options 2. Click Internet Options 3. From Internet Options window click Accessibility button 4. Check box Ignore font size... Read More...
by Garth Jones
Filed under: , , , , , , , , , , , ,
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...
by Garth Jones
Filed under: , , ,
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: , , ,
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: , , ,
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...
by Garth Jones
Filed under: , ,
ConfigMgr Command line
Friday, August 29, 2008 3:44 PM
Did you know that you can add command line switches in he command line? 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: , , ,
How to restore the default collections within ConfigMgr!
Friday, August 22, 2008 8:35 AM
Are you like me have you accidentally deleted the “All systems” collection instead of a PC? Have you wonder how to get it back? Well after some digging and asking around, I finally determine the answer! (with being pointed in the right direction) <Drum Roll> Install / Reinstall the last service pack! BTW This might work in SMS 2003 too. If you try it and... 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: , , ,
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...
by Garth Jones
Filed under: , ,
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...
by Garth Jones
Filed under: , ,
Learning TSQL
Tuesday, August 12, 2008 8:37 AM
The question of learning T-SQL comes up all the time with ConfigMgr/SMS/MOM/OpsMgr Admins, here are some resources to help you out. Part of this are taken for a previous article I wrote on the subject and it still applies, today. http://www.myitforum.com/articles/18/view.asp?id=5694 Here are two free website to help you learn TSQL. http://sqlcourse.com/ http... 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: , , ,
More Posts Next page »