only Desktops in collection
select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_SYSTEM_ENCLOSURE on SMS_G_System_SYSTEM_ENCLOSURE.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SYSTEM_ENCLOSURE.ChassisTypes != "10"
select SMS_R_System.ResourceID,SMS_R_System.ResourceType,SMS_R_System.Name,SMS_R_System.SMSUniqueIdentifier,SMS_R_System.ResourceDomainORWorkgroup,SMS_R_System.Client from SMS_R_System where ResourceId in (select ResourceID from SMS_R_System inner join SMS_G_System_LastSoftwareScan on SMS_G_System_LastSoftwareScan.ResourceID = SMS_R_System.ResourceId where DATEDIFF(dd,SMS_G_System_LastSoftwareScan.LastScanDate,GetDate()) > 21 )
select SMS_R_System.ResourceID,SMS_R_System.ResourceType,SMS_R_System.Name,SMS_R_System.SMSUniqueIdentifier,SMS_R_System.ResourceDomainORWorkgroup,SMS_R_System.Client from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Microsoft Systems Management Server 2003 Administrator Console"
ignore other collection
select SMS_R_System.ResourceID,SMS_R_System.ResourceType,
SMS_R_System.Name,SMS_R_System.SMSUniqueIdentifier,
SMS_R_System.ResourceDomainORWorkgroup,SMS_R_System.Client
from SMS_R_System where Client = 1 and ClientType = 1 and
ResourceId not in (select ResourceID from SMS_CM_RES_COLL_XXXxxxxx)
Some times old version could cause of some issues… below is a quick query to find the old version of systems
select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where ((DATEDIFF(hh, SMS_R_SYSTEM.AgentTime, getdate()) < 23) and AgentName = "SMS_AD_SYSTEM_DISCOVERY_AGENT") and ( SMS_R_System.ClientVersion is null)
All computers that are in a state of pending restart:
select SMS_R_SYSTEM.ResourceID, SMS_R_SYSTEM.ResourceType, SMS_R_SYSTEM.Name, SMS_R_SYSTEM.SMSUniqueIdentifier, SMS_R_SYSTEM.ResourceDomainORWorkgroup, SMS_R_SYSTEM.Client from sms_r_system AS sms_r_system inner join SMS_UpdateComplianceStatus as c on c.machineid=sms_r_system.resourceid where c.LastEnforcementMessageID = 9
All computers that failed to install an update:
select SMS_R_SYSTEM.ResourceID, SMS_R_SYSTEM.ResourceType, SMS_R_SYSTEM.Name, SMS_R_SYSTEM.SMSUniqueIdentifier, SMS_R_SYSTEM.ResourceDomainORWorkgroup, SMS_R_SYSTEM.Client from sms_r_system inner join SMS_UpdateComplianceStatus on SMS_UpdateComplianceStatus.machineid=sms_r_system.resourceid where SMS_UpdateComplianceStatus.LastEnforcementMessageID = 11
All computers that are waiting for another installation to complete:
select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from sms_r_system AS sms_r_system inner join SMS_UpdateComplianceStatus as c on c.machineid=sms_r_system.resourceid where c.LastEnforcementMessageID = 3
To create a collection based on failed advertisement systems.
SELECT sys.ResourceID,sys.ResourceType,sys.Name,sys.SMSUniqueIdentifier,sys.ResourceDomainORWorkgroup,sys.Client FROM sms_r_system as sys inner join SMS_ClientAdvertisementStatus as offer on sys.ResourceID=offer.ResourceID
WHERE AdvertisementID = ‘CEN12345' and LastStateName = "Failed"
http://blog.coretech.dk/confmgr07/collection-for-computers-that-failed-to-run-an-advertisement/
http://blog.coretech.dk/confmgr07/config-mgr-inventory-and-reporting/audit-software-installations/
http://www.myitforum.com/myITToolbar/frame-click.asp?
on error resume next
dim oUIResManager
dim oCache
dim oCacheElement
dim oCacheElements
set oUIResManager = createobject(“UIResource.UIResourceMgr”)
if oUIResManager is nothing then
wscript.echo “Couldn’t create Resource Manager – quitting”
wscript.quit
end if
set oCache=oUIResManager.GetCacheInfo()
if oCache is nothing then
set oUIResManager=nothing
wscript.echo “Couldn’t get cache info – quitting”
wscript.quit
end if
set oCacheElements=oCache.GetCacheElements
for each oCacheElement in oCacheElements
oCache.DeleteCacheElement(oCacheElement.CacheElementID)
next
set oCacheElements=nothing
set oUIResManager=nothing
set oCache=nothing
----------
use this script below …
1. Create a folder and place the following batch files and txt file on it: Collect.bat, TestAll.bat, TestPC.bat, and Computers.txt. The codes for the batch files are given below. Place the name or ip address of the machines in computers.txt (one computer name/ip address per line)
2. Run TestAll.bat
3. A folder named “Result” will be created inside the folder where the batch files are located. This folder will contain the log files of the machines that you entered in computers.txt
A. Collect.bat
MD .\Result\%1
MD .\Result\%1\CCMLog
MD .\Result\%1\CCMSETUPLog
xcopy \\%1\admin$\system32\ccm\logs\*.* .\Result\%1\CCMLog /E /Y
xcopy \\%1\admin$\system32\ccmsetup\*.log .\Result\%1\CCMSETUPLog /E /Y
B. TestAll.bat
rd /Q /S .\Result_OLD
Move /Y .\Result .\Result_OLD
rd /Q /S .\Result
MD .\Result
del FailPing.txt
del FailConnect.txt
del Succeed.txt
for /f %%i in (computers.txt) do call TestPC %%i
C. TestPC.bat
@echo off
REM usage: TestPC PCName
if not %1. == . goto INSTALL
Echo Usage: TestPC PCName
goto END
:INSTALL
REM @echo on
Echo Now test %1
Echo Now try to ping %1
ping %1 -n 1 | find /i “ttl=” && Goto ONLINE
Goto OFFLINE
:ONLINE
Echo can ping %1
Echo Now try to connect to \\%1\Admin$
Dir \\%1\Admin$ >nul
If ERRORLEVEL=1 goto ConnectionFailed
Echo Connection test succeeded for %1. Can connect to \\%1\admin$.
Echo %1 >>.\result\Succeed.txt
Echo Now collecting data
call Collect.bat %1
goto END
:OFFLINE
echo cannot ping %1
Echo %1 >>.\result\FailPing.txt
goto Failed
:ConnectionFailed
Echo cannot connect to \\%1\admin$.
Echo %1 >>.\result\FailConnect.txt
goto Failed
:Failed
Echo Connection test failed for %1
:END
Echo.
D. Computers.txt
I like SCCM (System Center Configuration Manager) and in my opinion it is a great management tool, but it's developers, apparently, forgot the small things that makes it better. One of the missing feathers is the copy & paste menu option ("Elementary my dear Watson"). When I first installed SCCM 2007, I was very excited to find the copy option, but till today I couldn't find his little sister, "paste", I suspect that they decided to release her in the next version.
Please help "Copy" find his little sister "Paste" in the following picture: 
There is a saying "If the mountain won't come to Muhammad, Muhammad must go to the mountain.", so I tried to write something of my own. Microsoft delivers a good SDK pack for SCCM, which helped me reach my goal.
My tool adds Copy and Paste options to the context menu of Collections (Queries), Packages, Programs and Advertisements.
Collections (Collection Queries)
Select the source collection (from the right or left pane) and choose "Copy Collection Query"
Select target collection (from the right or left pane) and choose "Paste Collection Query"
Choose a name for the new query
If you press cancel the specific query will not be copied (It will proceed to the next query)
The result:
At the end of the process you will be asked whether or not you want to delete the cashed settings
Packages
Select the source package and choose "Copy Package"
To paste the new package select any package on the right pane, and choose "Paste Package" from the Popup menu
Choose a name for the new package
Choose whether or not you want to delete the cached settings
Programs
Select the source package and choose "Copy Program"
To paste the new program to the same package, right click the source program (on the right pane), and choose "Paste Program" from the menu
To paste the new program to another package, right click the target "Programs" (on the left pane), and choose "Paste Program" from the menu
Choose a name for the new program
Choose whether or not you want to delete the cached settings
Advertisements
Select the source package and choose "Copy Advertisement"
To paste the new advertisement select any advertisement on the right pane, and choose "Paste Advertisement" from the menu
Choose a name for the new Advertisement
Choose whether or not you want to delete the cached settings
The purpose of this behavior is to avoid automatic deployment after pasting the new advertisement.
The target mandatory assignments will have two assignments:
SETUP
During the setup you can choose which extension to install:
You can download the setup from HERE
SELECT DISTINCT
TOP (100) PERCENT SYS.Name0 AS [Machine Name], UCS.Status AS [Patch Status Code],
CASE WHEN UCS.Status = '2' THEN 'Applicable' WHEN UCS.Status = '3' THEN 'Installed' ELSE '' END AS 'Patch Status', UI.BulletinID, UI.ArticleID, UI.Title,
dbo.v_FullCollectionMembership.ResourceID, dbo.v_Collection.CollectionID
FROM dbo.v_FullCollectionMembership INNER JOIN
dbo.v_Collection ON dbo.v_FullCollectionMembership.CollectionID = dbo.v_Collection.CollectionID INNER JOIN
dbo.v_R_System AS SYS LEFT OUTER JOIN
dbo.v_Update_ComplianceStatusAll AS UCS ON SYS.ResourceID = UCS.ResourceID INNER JOIN
dbo.v_UpdateInfo AS UI ON UCS.CI_ID = UI.CI_ID ON dbo.v_FullCollectionMembership.ResourceID = UCS.ResourceID
WHERE (UCS.Status IN ('3', '2')) AND (dbo.v_Collection.CollectionID = 'CollectionID')
ORDER BY UI.ArticleID
Computer that failed to run an advertisement:
select sys.ResourceID,sys.ResourceType,sys.Name,sys.SMSUniqueIdentifier,sys.ResourceDomainORWorkgroup,sys.Client from sms_r_system as sys inner join SMS_ClientAdvertisementStatus as offer on sys.ResourceID=offer.ResourceID WHERE AdvertisementID = ‘A0120005′ and LastStateName = ‘Failed’
Computer that ran an advertisement successfully:
select sys.ResourceID,sys.ResourceType,sys.Name,sys.SMSUniqueIdentifier,sys.ResourceDomainORWorkgroup,sys.Client from sms_r_system as sys inner join SMS_ClientAdvertisementStatus as offer on sys.ResourceID=offer.ResourceID WHERE AdvertisementID = ‘A0120005′ and LastStateName = ‘Succeeded’
Notice for both collections you need to find the unique advertisement ID
Workstation computers that have not restarted the last 7 days:
select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System INNER JOIN SMS_G_System_OPERATING_SYSTEM ON SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId WHERE (SMS_G_System_OPERATING_SYSTEM.Caption like "%xp%" or SMS_G_System_OPERATING_SYSTEM.Caption like "%vista%" or SMS_G_System_OPERATING_SYSTEM.Caption like "%windows 7%") and (DateDiff(day, SMS_G_System_OPERATING_SYSTEM.LastBootUpTime, GetDate()) >7)
All Obsolete Clients:
select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.Obsolete = 1
All Inactive Clients:
select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.Active = 0
All Clients without a Config Mgr. Agent installed:
select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.Client is null
All Unapproved clients
select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_CM_RES_COLL_SMS00001 on SMS_CM_RES_COLL_SMS00001.ResourceId = SMS_R_System.ResourceId where SMS_CM_RES_COLL_SMS00001.IsApproved<>1
SELECT Caption0 as 'Operating System',CSDVersion0 as 'Service Pack', COUNT(*) AS 'Count'
FROM v_GS_OPERATING_SYSTEM,v_R_System
WHERE v_GS_OPERATING_SYSTEM.Resourceid=v_R_System.Resourceid
GROUP BY Caption0,CSDVersion0
ORDER BY Caption0,CSDVersion0
SQL Query to find the collections Hierarchy
WITH folderHierarchy (CollectionID,[Name],ParentCollectionID,[Path])
AS
(
SELECT
CollectionID,
[Name],
ParentCollectionID,
CAST('/'+[Name]+'/' AS VARCHAR(MAX)) AS [Path]
FROM
(SELECT
CollectionID,
[Name],
ParentCollectionID
FROM v_Collection
INNER JOIN v_CollectToSubCollect
ON v_Collection.CollectionID = v_CollectToSubCollect.SubCollectionID) AS V_1
WHERE
ParentCollectionID = 'COLLROOT'
UNION ALL
SELECT
child.CollectionID,
child.Name,
child.ParentCollectionID,
parent.[Path]+child.[Name]+'/' AS [Path]
FROM
(SELECT
CollectionID,
[Name],
ParentCollectionID
FROM v_Collection
INNER JOIN v_CollectToSubCollect
ON v_Collection.CollectionID = v_CollectToSubCollect.SubCollectionID) AS child
INNER JOIN folderHierarchy AS parent
ON parent.CollectionID = child.ParentCollectionID
)
SELECT
fldr.[Path],
fldr.[Name],
fldr.CollectionID
FROM
folderHierarchy AS fldr
Credit to Dave Fuller for which this information is based upon.
Creating a collection query that is based upon SQL can be done. It’s a little complicated, but not all that bad once you’ve done it a few times. There are many times where you have a fantastic SQL query you would love to use, but cannot figure how to get that same query in WQL. When you create a collection query in the administrative console, it’s created in WQL and translated to SQL. It is possible to create a SQL query and simple update a collection with that new SQL statement to process the collection membership.
Things to understand:
What you see as the “CollectionID” in the admin console is actually the “SiteID” in the SQL database.
The CollectionID in the SQL Database, is actually the number of the collection as it was created, so if the last collection you created was your 965th collection, it’s CollectionID in SQL would actually be 965, even though the CollectionID you see in the admin console is “ABC00012”
If you have it setup so that you can see the node information, you will see the “Store Name” ID for that collection that you can use as well.
This process involves a few steps:
1) Create a target collection with a blank query, aka don’t define anything in the query statement
2) Get the collection SiteID (as it’s referred to in SQL) otherwise known as the Collection ID in reports, or the Store Name in the node information tab in the admin console
3) Run the following query in SQL query analyzer to find the CollectionID as it’s listed in SQL
Select CollectionID, SiteID from Collections where SiteID= ‘CollectionID/StoreName as seen in the console’
This will return the CollectionID you need to update the SQL statement with
4) Run the following update command to insert your SQL statement in the SQL statement query portion of the Collection.
Update Collection_Rules_SQL
set SQL = ‘YOUR SQL STATEMENT’
where CollectionID = ‘X’
5) You can run the following query to identify that your SQL statement is in fact in the correct place now
select SQL, WQL
from Collection_Rules_SQL
where CollectionID = ‘X’
This will also show you the WQL statement for any collection you want to see.
6) Update your collection to see if you have gotten the intended results
7) I would highly recommend you make sure you SQL statement works before inserting it into the collection SQL statement
IMPORTANT NOTE: If you modify the query in the admin console, you will overwrite the SQL statement you created. You will have to re-update the SQL statement through query analyzer if you accidentally modify the query through the console
SELECT dbo.v_R_System.Name0 AS [Computer Name], A.System_OU_Name0
FROM dbo.v_RA_System_SystemOUName AS A INNER JOIN
(SELECT ResourceID, MAX(LEN(System_OU_Name0)) AS len
FROM dbo.v_RA_System_SystemOUName
GROUP BY ResourceID) AS B ON A.ResourceID = B.ResourceID AND LEN(A.System_OU_Name0) = B.len INNER JOIN
dbo.v_R_System ON B.ResourceID = dbo.v_R_System.ResourceID
If you want just for one computer then…
SELECT dbo.v_R_System.Name0 AS [Computer Name], A.System_OU_Name0
FROM dbo.v_RA_System_SystemOUName AS A INNER JOIN
(SELECT ResourceID, MAX(LEN(System_OU_Name0)) AS len
FROM dbo.v_RA_System_SystemOUName
GROUP BY ResourceID) AS B ON A.ResourceID = B.ResourceID AND LEN(A.System_OU_Name0) = B.len INNER JOIN
dbo.v_R_System ON B.ResourceID = dbo.v_R_System.ResourceID
WHERE (dbo.v_R_System.Name0 = 'Computer Name')
SELECT TOP (100) PERCENT dbo.v_Collection.Name, dbo.v_Collection.CollectionID, dbo.v_CollectionRuleQuery.RuleName, dbo.v_CollectionRuleQuery.QueryID,
dbo.v_CollectionRuleQuery.LimitToCollectionID, dbo.v_CollectionRuleQuery.QueryExpression
FROM dbo.v_Collection INNER JOIN
dbo.v_CollectionRuleQuery ON dbo.v_Collection.CollectionID = dbo.v_CollectionRuleQuery.CollectionID
ORDER BY dbo.v_Collection.Name, dbo.v_Collection.CollectionID
---------------------------------------------
select SMS_R_System.ResourceID,SMS_R_System.ResourceType,SMS_R_System.Name,SMS_R_System.SMSUniqueIdentifier,SMS_R_System.ResourceDomainORWorkgroup,SMS_R_System.Client from SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_COMPUTER_SYSTEM.Name not in (select SMS_R_System.Name from SMS_R_System inner join SMS_G_System_WORKSTATION_STATUS on SMS_G_System_WORKSTATION_STATUS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_WORKSTATION_STATUS.LastHardwareScan >= DateAdd(dd,-14,GetDate()) )
-----------------------
select SMS_R_System.ResourceID,SMS_R_System.ResourceType,SMS_R_System.Name,SMS_R_System.SMSUniqueIdentifier,SMS_R_System.ResourceDomainORWorkgroup,SMS_R_System.Client from SMS_R_System where obsolete = 1
1.Client all system collection
2.Non client systems
3.Inactive systems
4.Obsolete system
5.Duplicate client Yes or No
6. Last hardware inventory 14 days
7.Last software inventory 14 days
8.Last Data discovery cycle.
9. XYZ package, XYZ Advertisement success systems’ collection
10. XYZ package, XYZ Advertisement Failed systems’ collection
11.XYZ subnet collection system
12.Add XYZ system’s to a collection of Existing
13. All SMS server system collection
14. All windows server, workstation,DP,BDP collection
15. All system’s with AD site based
16. Collection limiting to sub collection, linking
17.System’s are in “A” collection But not in “B” Collection & Vice versa
18. In collection “XYZ” Software installed system
19. In collection “XYZ” File inventory(s\w inventory based) installed system
20. In collection “XYZ” file specific method(H\w inventory based) system
21.XYZ patch Installed & Not Installed system
22. All windows update Agent version 7.6 below
23.XYZ user/group collection
--Systems Part of What Collections
SELECT v_R_System.Name0, v_Collection.Name FROM v_FullCollectionMembership INNER JOIN v_R_System ON v_FullCollectionMembership.ResourceID = v_R_System.ResourceID INNER JOIN v_Collection ON v_FullCollectionMembership.CollectionID = v_Collection.CollectionID WHERE (v_R_System.Name0 = 'Systemname')
