This should work for SCCM 2007 and newer.
How often do you get a request to push software or setting change to specific list of users and computers and groan at the thought of having to create the collection for it?
I cannot tell you how many times I have searched for this solution. Someone made some great PowerShell scripts, but I couldn't always get them to work and/or it was fairly slow. (REF: http://blogs.technet.com/b/mniehaus/archive/2009/04/10/adding-members-to-a-configmgr-collection-using-powershell-v2-ctp3.aspx)
I found another way to import using a .CSV, but that, too, was a little cumbersome. That method involved using a report that pulled the SMBIOS and MAC Address information, then using Access to query against the list to create the .CSV file to import. But that only worked for Computers, not users. (REF: http://www.windows-noob.com/forums/index.php?/topic/4505-how-can-i-import-computers-into-sccm-2012-using-a-file/)
A few days ago, I found a method in a forum that simplifies this tremendously (REF: http://www.myitforum.com/forums/Can-you-import-users-into-a-collection-via-CSV-m211472.aspx):
The query to import computers is simply:
select * from SMS_R_System where name in ("computer1" , "computer2" , "computer3" , "computer4"
)
For users, you would change this to:
select * from SMS_R_User where name in ("user1" , "user2" , "user3" , "user4"
)
The trick here is how to get your list of computers or users formatted with the quotes and commas. I did this in Excel.
Copy your list of users or computers to Column A. In cell B1, enter the following:
="""" & A1 & ""","
That's equals four quotes (space) ampersand (space) A1 (space) ampersand (space) three quotes, comma, quote. Copy the formula down from B1.
I use Notepad from here, so I can copy and paste the query into SCCM later. In Notepad, write your query like this to start:
select * from SMS_R_User where name in (
Copy and paste Column B from your Excel spreadsheet and add a closing parentheses:
select * from SMS_R_User where name in ("USER1",
"USER2","USER3",
"USER4",)
Now fire up your SCCM Console. The above query is a User collection, so go there to create a new user collection.
- Give the collection a name, click Next, then choose Query Rule from the drop down list.
- Give the query rule a name, then click Edit Query Statement...
- Click Show Query Language
- Copy and paste your query from Notepad into this window.
- Click OK
- Click Next a couple more times to finish creating the collection.
- Update Membership, then Refresh after a couple minutes.
Hope that helps!
Is there a way this could be modified to work across multiple domains? There are 4 domains in SCCM, so the search fails at that point.
ReplyDeleteIf you have collections based on each domain, you can use those as your "Limiting Collection". Any collection can be used as a Limiting Collection, so if you don't already have them, I would recommend creating collections for each domain and using these in your user limited collections.
ReplyDeleteOtherwise, you can try this:
select * from SMS_R_User where WindowsNTDomain = "DomainName" and name in ("DomainName\\USER1",
"DomainName\\USER2",
"DomainName\\USER3",
"DomainName\\USER4",)
Thank you Jen; this was very helpful.
DeleteIn my case, I used the Pre-W2K names. Instead of John Doe or Doe, John it was jdoe. So instead of using WindowsNTDomain or User, I used UserName as the matching column. So, 'Anonymous', look at the various columns available by examining the built-in 'All Users' query in SCCM to find what will match your name syntax. There are a few choices, including UniqueUserName which follows the syntax DOMAIN\Pre-W2K name (eg. MYDOM\jdoe)
ReplyDeleteMany thanks for the info, I have lost the query and I kept looking for it. Glad I found your post =)
ReplyDeleteThanks a lot. This article helped me get the job done. I ended up writing this so thought I'd share if it helps someone. This is for machine/server names. Copy and paste the result in to the device collection as you say above. No need for Excel as PS does that bit for you. Starting with a list of servers in a text file the code reopens the same file with the wmi code amended. Or you can point it to another text file or the screen - just suit yourself.
ReplyDelete$servers = Get-Content C:\temp\nopatch.txt
[array]$serversNew = $servers | Foreach-Object{ '"' + $_ + '",' }
[string]$string = "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 name in ("
$code = invoke-command -ScriptBlock {Write-output $string
$($serversnew | fl)
")"
}
$code2 = $code | out-string
$code2 | Add-Content C:\temp\nopatch.txt
Invoke-Item C:\temp\nopatch.txt
Thanks this was super helpful!
DeleteThank you for this! Excellent tip that saved me a massive amount of time.
ReplyDeleteThis was very helpful. I ran this on SCCM 2012 r2 SP1.
ReplyDeleteThank you for sharing this. Saved me from lots of manually entries!
ReplyDeleteWorked great, thanks for sharing!
ReplyDeleteI've found creating an AD group to add computers to and using a collection query for the name of the AD group eliminates the need for any of the manual work.
ReplyDeleteI did it using Notepad ++. ALT+mouse select allows to select a vertical stripe before and after the list and add the required " and , . Then replace \r\l (=crlf) by nothing using extended find & replace et voilà.
ReplyDeleteREALLY useful! Thanks ever so much for this.
ReplyDeleteVery useful. Thanks for sharing!
ReplyDeleteGreat tool ..excellent ..really simplifies a complex task
ReplyDeleteThank you Rowdy, still works in 2021
ReplyDelete