Solved

SID to user from CSV

Posted on 2014-01-22
32
665 Views
Last Modified: 2014-02-08
Is there a PowerShell script listed that will pull in a csv file with SID's and give whether SID or user has been deleted or the user name exported to csv file. Window domain 2008 r2. Thanks
0
Comment
Question by:hlaten
  • 15
  • 6
  • 2
  • +3
32 Comments
 
LVL 35

Expert Comment

by:Joseph Daly
ID: 39800557
The quest cmdlets will let you do this.

get-qaduser -identity {SID}

This will search for and return the user if the sid exists.
0
 

Author Comment

by:hlaten
ID: 39800562
Sounds great with quest but we are not allow to use quest within our different forests and domains within the forest, International politics do not allow this.
0
 
LVL 53

Expert Comment

by:Will Szymkowski
ID: 39800601
This can be accomplished very easily using native powershell syntax. Use the below syntax to accomplish this...

Copy your SID's into notepad like below. save it as sid.txt put it on the c:\ drive. and run the script.
Notepad File
S-1-5-21-4051580463-4157580989-1737113629-XXXX
S-1-5-21-4051580463-4157580989-1737113629-XXXY
S-1-5-21-4051580463-4157580989-1737113629-XXXU
S-1-5-21-4051580463-4157580989-1737113629-XXXP
etc

import-module activedirectory
Get-Content c:\sid.txt | foreach {get-aduser -identity $_} | select name, samaccountname, Enabled | Export-csv -NoTypeInformation c:\users.csv

Open in new window


This script will then export your results to c:\users.csv

Will.
0
 

Author Comment

by:hlaten
ID: 39800665
Thank you for the script. The script does throw on screen "Get-ADUser : Cannot find an object with identity:" Is there anyway to get an output to the csv for SID's that can not be found in the domain, such as  ""SID identifyer" not found under DC=make,DC=day,DC=com", I am cleaning up GPO's and unresolving SID's for the GPO's in 3 forests and 25 domains, it would be nice to have csv for all results whether good or bad and show during our GPO meetings. This is my first problem I have ask at this site, am I asking too much from the experts? Thanks and all is really looking great for a place to solve problems.
0
 
LVL 53

Expert Comment

by:Will Szymkowski
ID: 39800697
The Get-Aduser error is happening because you need to have the Active Directory module added to the Powershell session. You need to run this on the domain controller where ADDS is installed, or if you want to run this locally on a remote machine you need to have RSAT (remote server admin tools) installed adding the ADDS portion to the workstation.

to add the SID to the out-put just add ,SID after enabled (example below...)
select name, samaccountname, Enabled, SID | export-csv....

Open in new window

Once that is done the script will run.

Will.
0
 

Author Comment

by:hlaten
ID: 39800698
under
-identity $_} | select name, samaccountname, Enabled ;

does the enabled only retreive account if it is enabled?
0
 
LVL 53

Expert Comment

by:Will Szymkowski
ID: 39800725
No this will retrieve all accounts in the notepad file. Enabled is the heading and the value will be either True or False.


Will.
0
 

Author Comment

by:hlaten
ID: 39800764
Thank you Will, all is working as expected except no export if accounts are not in AD, I aded a known good SID in txt file and output is as such:

"name","samaccountname","Enabled","SID"
"aakern","aakern","True","SID Number"

it still prints on the screen for deleted users:
Get-ADUser : Cannot find an object with identity: 'SID Number Removed for security' under: 'DC=blank,D
C=notreal,DC=com'.
At H:\scripts\csvSIDtoUser.ps1:2 char:56
0
 

Author Comment

by:hlaten
ID: 39801125
Will,
Have you finished with this post or still working it? I dont mean to be pushy  but I am watching this post to keep you updated in case you post something. Thanks
0
 
LVL 53

Expert Comment

by:Will Szymkowski
ID: 39801144
In order to write the out-put to the file you will need to use the write-host cmdlet.

Will.
0
 

Author Comment

by:hlaten
ID: 39801183
Will isnt the write host cmdlet used for writing the output to the screen? Thanks for what you have given me. It has helped treamendously.
0
 
LVL 53

Expert Comment

by:Will Szymkowski
ID: 39801207
Yes my mistake. Been a long day.

Will.
0
 

Author Comment

by:hlaten
ID: 39803129
Will,
I have done everything that you have ask and I keep getting these errors when I run the script. If the SID is in AD i still get the CSV and all info I need for existing users. But as ask before can I get a value in the exported csv stating the SID and the SID does not exist in domain?

erros when run:

Get-ADUser : The search filter cannot be recognized
At H:\scripts\csvSIDtoUser.ps1:1 char:56
+ Get-Content h:\scripts\getsid.txt | foreach {get-aduser <<<<  -identity $_} | select samaccountname,Enabled,S
ort-csv -NoTypeInformation h:\scripts\sidtousers.csv
    + CategoryInfo          : NotSpecified: ( :ADUser) [Get-ADUser], ADException
    + FullyQualifiedErrorId : The search filter cannot be recognized,Microsoft.ActiveDirectory.Management.Comma
   ADUser

Open in new window


Get-ADUser : Cannot bind parameter 'Identity' to the target. Exception setting "Identity": "Cannot validate arg
 parameter: 'Identity'. The argument is null or empty. Supply an argument that is not null or empty and then tr
mmand again."
At H:\scripts\csvSIDtoUser.ps1:1 char:66
+ Get-Content h:\scripts\getsid.txt | foreach {get-aduser -identity <<<<  $_} | select samaccountname,Enabled,S
ort-csv -NoTypeInformation h:\scripts\sidtousers.csv
    + CategoryInfo          : WriteError: (:) [Get-ADUser], ParameterBindingException
    + FullyQualifiedErrorId : ParameterBindingFailed,Microsoft.ActiveDirectory.Management.Commands.GetADUser

Open in new window


Get-ADUser : Cannot bind parameter 'Identity' to the target. Exception setting "Identity": "Cannot validate arg
 parameter: 'Identity'. The argument is null or empty. Supply an argument that is not null or empty and then tr
mmand again."
At H:\scripts\csvSIDtoUser.ps1:1 char:66
+ Get-Content h:\scripts\getsid.txt | foreach {get-aduser -identity <<<<  $_} | select samaccountname,Enabled,S
ort-csv -NoTypeInformation h:\scripts\sidtousers.csv
    + CategoryInfo          : WriteError: (:) [Get-ADUser], ParameterBindingException
    + FullyQualifiedErrorId : ParameterBindingFailed,Microsoft.ActiveDirectory.Management.Commands.GetADUser

Open in new window

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 53

Assisted Solution

by:Will Szymkowski
Will Szymkowski earned 250 total points
ID: 39803170
In order to get the SID for users that exists in AD, you need to refer to my above post. You need to add the SID at the select section of the script see the below modified script for details.

import-module activedirectory
Get-Content c:\sid.txt | foreach {get-aduser -identity $_} | select name, samaccountname, Enabled, SID | Export-csv -NoTypeInformation c:\users.csv

Open in new window


This will add the SID for the users that exists in Active Directory to the CSV file export.

Will.
0
 

Author Comment

by:hlaten
ID: 39803209
Will I understand about adding the SID and it works fine:

"samaccountname","Enabled","SID"
"aakern","True","S-1-5-21-********-**********-1373009395-2023"

Open in new window


What I am asking about is the errors when I have a good SID that I am trying to translate:

PS H:\scripts> ./csvsidtouser.ps1
Get-ADUser : The search filter cannot be recognized
At H:\scripts\csvSIDtoUser.ps1:1 char:56
+ Get-Content h:\scripts\getsid.txt | foreach {get-aduser <<<<  -identity $_} | select samaccountname,Enabled,SID | Exp
ort-csv -NoTypeInformation h:\scripts\sidtousers.csv
    + CategoryInfo          : NotSpecified: ( :ADUser) [Get-ADUser], ADException
    + FullyQualifiedErrorId : The search filter cannot be recognized,Microsoft.ActiveDirectory.Management.Commands.Get
   ADUser

Open in new window


Get-ADUser : Cannot bind parameter 'Identity' to the target. Exception setting "Identity": "Cannot validate argument on
 parameter: 'Identity'. The argument is null or empty. Supply an argument that is not null or empty and then try the co
mmand again."
At H:\scripts\csvSIDtoUser.ps1:1 char:66
+ Get-Content h:\scripts\getsid.txt | foreach {get-aduser -identity <<<<  $_} | select samaccountname,Enabled,SID | Exp
ort-csv -NoTypeInformation h:\scripts\sidtousers.csv
    + CategoryInfo          : WriteError: (:) [Get-ADUser], ParameterBindingException
    + FullyQualifiedErrorId : ParameterBindingFailed,Microsoft.ActiveDirectory.Management.Commands.GetADUser

Open in new window


Get-ADUser : Cannot bind parameter 'Identity' to the target. Exception setting "Identity": "Cannot validate argument on
 parameter: 'Identity'. The argument is null or empty. Supply an argument that is not null or empty and then try the co
mmand again."
At H:\scripts\csvSIDtoUser.ps1:1 char:66
+ Get-Content h:\scripts\getsid.txt | foreach {get-aduser -identity <<<<  $_} | select samaccountname,Enabled,SID | Exp
ort-csv -NoTypeInformation h:\scripts\sidtousers.csv
    + CategoryInfo          : WriteError: (:) [Get-ADUser], ParameterBindingException
    + FullyQualifiedErrorId : ParameterBindingFailed,Microsoft.ActiveDirectory.Management.Commands.GetADUser

Open in new window

0
 

Author Comment

by:hlaten
ID: 39803220
and also when I run script with unknown SID I get this:

PS H:\scripts> ./csvsidtouser.ps1
Get-ADUser : Cannot find an object with identity: 'S-1-5-21-165822833-1632583300-1373009395-290748' under: 'DC=ccanet,D
C=rockwellcollins,DC=com'.
At H:\scripts\csvSIDtoUser.ps1:1 char:56
+ Get-Content h:\scripts\getsid.txt | foreach {get-aduser <<<<  -identity $_} | select samaccountname,Enabled,SID | Exp
ort-csv -NoTypeInformation h:\scripts\sidtousers.csv
    + CategoryInfo          : ObjectNotFound: (S-1-5-21-*******...********-290748:ADUser) [Get-ADUser], ADIdentityNotFo
   undException
    + FullyQualifiedErrorId : Cannot find an object with identity: 'S-1-5-21-165822833-1632583300-1373009395-290748' u
   nder: 'DC=ccanet,DC=rockwellcollins,DC=com'.,Microsoft.ActiveDirectory.Management.Commands.GetADUser

Open in new window


Get-ADUser : The search filter cannot be recognized
At H:\scripts\csvSIDtoUser.ps1:1 char:56
+ Get-Content h:\scripts\getsid.txt | foreach {get-aduser <<<<  -identity $_} | select samaccountname,Enabled,SID | Exp
ort-csv -NoTypeInformation h:\scripts\sidtousers.csv
    + CategoryInfo          : NotSpecified: ( :ADUser) [Get-ADUser], ADException
    + FullyQualifiedErrorId : The search filter cannot be recognized,Microsoft.ActiveDirectory.Management.Commands.Get
   ADUser

Open in new window

Get-ADUser : Cannot bind parameter 'Identity' to the target. Exception setting "Identity": "Cannot validate argument on
 parameter: 'Identity'. The argument is null or empty. Supply an argument that is not null or empty and then try the co
mmand again."
At H:\scripts\csvSIDtoUser.ps1:1 char:66
+ Get-Content h:\scripts\getsid.txt | foreach {get-aduser -identity <<<<  $_} | select samaccountname,Enabled,SID | Exp
ort-csv -NoTypeInformation h:\scripts\sidtousers.csv
    + CategoryInfo          : WriteError: (:) [Get-ADUser], ParameterBindingException
    + FullyQualifiedErrorId : ParameterBindingFailed,Microsoft.ActiveDirectory.Management.Commands.GetADUser

Open in new window


Get-ADUser : Cannot bind parameter 'Identity' to the target. Exception setting "Identity": "Cannot validate argument on
 parameter: 'Identity'. The argument is null or empty. Supply an argument that is not null or empty and then try the co
mmand again."
At H:\scripts\csvSIDtoUser.ps1:1 char:66
+ Get-Content h:\scripts\getsid.txt | foreach {get-aduser -identity <<<<  $_} | select samaccountname,Enabled,SID | Exp
ort-csv -NoTypeInformation h:\scripts\sidtousers.csv
    + CategoryInfo          : WriteError: (:) [Get-ADUser], ParameterBindingException
    + FullyQualifiedErrorId : ParameterBindingFailed,Microsoft.ActiveDirectory.Management.Commands.GetADUser

Open in new window

0
 

Author Comment

by:hlaten
ID: 39803226
If the SID is in AD i still get the CSV and all info I need for existing users. But as ask before can I get a value in the exported csv stating the SID and the SID does not exist in domain?

And why are we getting errors when the script is run whether the SID exists or not?
0
 

Author Comment

by:hlaten
ID: 39804724
Will,
are you finsihed with this post?
0
 
LVL 37

Expert Comment

by:Jamie McKillop
ID: 39807159
Hello,

Try this:

import-module activedirectory
$report = @()

Get-Content c:\sid.txt | foreach {
	$User = get-aduser -identity $_
	If ($User -ne $null) {
		$myobject = New-Object PSObject
		$myobject | Add-Member NoteProperty Name $user.name
		$myobject | Add-Member NoteProperty samaccountname $user.samaccountname
		$myobject | Add-Member NoteProperty Enabled $user.enabled
		$myobject | Add-Member NoteProperty SID $user.sid
		$report += $myobject		
	} Else {
		$myobject = New-Object PSObject
		$myobject | Add-Member NoteProperty Name "Not found"
		$myobject | Add-Member NoteProperty samaccountname "Not found"
		$myobject | Add-Member NoteProperty Enabled "Not found"
		$myobject | Add-Member NoteProperty SID $_		
		$report += $myobject
	}
}	
$report | Export-csv -NoTypeInformation c:\users.csv

Open in new window


-JJ
0
 
LVL 39

Accepted Solution

by:
footech earned 250 total points
ID: 39807219
I think you will face some issues with the script provided by jjmck, just because when Get-ADUser is supplied an identity that doesn't exist it generates a terminating error and the variable doesn't get updated.  The script should would if you used the -filter parameter instead, but I think the code is a bit simpler if you use a try/catch statement.
Import-Module ActiveDirectory
Get-Content c:\sid.txt | ForEach `
{
    $sid = $_
    try { Get-ADUser -Identity $_ | Select name, samaccountname, Enabled, SID }
    catch { "" | Select @{n="name";e={"Not found"}}, @{n="samaccountname";e={""}}, @{n="Enabled";e={""}}, @{n="SID";e={$sid}} }
} | Export-Csv -NoTypeInformation c:\users.csv

Open in new window

0
 
LVL 37

Expert Comment

by:Jamie McKillop
ID: 39807233
Thanks for catching that footech. I'm used to using Get-QADUser, which won't throw a terminating error.

-JJ
0
 
LVL 35

Expert Comment

by:Mahesh
ID: 39807524
Hlaten,

If you could please shade some light what you are looking for exactly, some background please ?

Mahesh
0
 

Author Comment

by:hlaten
ID: 39812453
Experts,
Thanks for your advice and scripting, is there a way to list also the SID account that is not found in the csv export? Then I will be able to send the output to whomever is asking for the SID translation. Thank for your help and no erros on the screen.
0
 
LVL 39

Expert Comment

by:footech
ID: 39813319
I'm not sure what you mean.
With what I provided, the SID will always be shown, along with either the associated account or "not found".  If there is no match for the SID found, then how can you expect to display what it used to be associated with?  If you're asking for something different, please explain.
0
 

Author Comment

by:hlaten
ID: 39818850
I am sorry that I did not see that your team has provided everything that has been ask, The solution you provided me was excellent. Thank you.
0
 

Assisted Solution

by:hlaten
hlaten earned 0 total points
ID: 39819417
I've requested that this question be closed as follows:

Accepted answer: 0 points for hlaten's comment #a39818850

for the following reason:

Everything that I have asked of the experts has been provided to me in an excellent and professional manner.
0
 

Author Closing Comment

by:hlaten
ID: 39843886
Sorry for not grading this appropriately the first time. Thanks for the scripting and guidance.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In this article, we will see the basic design consideration while designing a Multi-tenant web application in a simple manner. Though, many frameworks are available in the market to develop a multi - tenant application, but do they provide data, cod…
Possible fixes for Windows 7 and Windows Server 2008 updating problem. Solutions mentioned are from Microsoft themselves. I started a case with them from our Microsoft Silver Partner option to open a case and get direct support from Microsoft. If s…
This tutorial will walk an individual through configuring a drive on a Windows Server 2008 to perform shadow copies in order to quickly recover deleted files and folders. Click on Start and then select Computer to view the available drives on the se…
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles from a Windows Server 2008 domain controller to a Windows Server 2012 domain controlle…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now