• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3309
  • Last Modified:

Omit DN information from csvde results

I'm trying to create a csv to import users into a database. I'm using the command below and everything works other than i cannot seem to omit the dn in the results file. Any clues?

csvde -f results.csv -d "ou=users,dc=domain,dc=com" -r (&(obbjectClass=user)(objectcategory=person))" -l "cn,sn,givenName,name,sAMAccountName,userPrincipalName,initials" -o "DN"
0
josgan
Asked:
josgan
  • 3
  • 3
1 Solution
 
josganAuthor Commented:
need this answer so i'm increasing the points.
0
 
Chris DentPowerShell DeveloperCommented:

DN can't be excluded, it's the unique identifier for the object. You can't delete it afterwards?

Chris
0
 
josganAuthor Commented:
I want to do a straight import into SQL, and the DN information is messing that whole process up. I'm creating a searchable database users can use to check to see if their account exists.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
Chris DentPowerShell DeveloperCommented:

Well all is not lost. It's quite easy to dump that kind of information out with VbScript. There's an example below... you might want to play with the order of the output a little perhaps...

objRootDSE.Get("defaultNamingContext") gets your domain name, so effectively replaces the dc=domain,dc=com if your CSVDE command above.


On Error Resume Next

Const ADS_SCOPE_SUBTREE = 2

Set objFileSystem = CreateObject("Scripting.FileSystemObject")
Set objFile = objFileSystem.OpenTextFile("results.csv", 2, True, 0)

Set objConnection = CreateObject("ADODB.Connection")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"

Set objCommand = CreateObject("ADODB.Command")
objCommand.ActiveConnection = objConnection

Set objRootDSE = GetObject("LDAP://RootDSE")
objCommand.CommandText = "SELECT aDSPath, cn, sn, givenName, name, sAMAccountName, userPrincipalName, initials " &_
      "FROM 'LDAP://OU=Users," & objRootDSE.Get("defaultNamingContext") & "'"
Set objRootDSE = Nothing

objCommand.Properties("Page Size") = 1000
objCommand.Properties("Timeout") = 600
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE
objCommand.Properties("Cache Results") = False

Set objRecordSet = objCommand.Execute
While Not objRecordSet.EOF
      Set objEntry = GetObject(objRecordSet.Fields("aDSPath"))
      strClass = "" : strClass = objEntry.Class

      If strClass = "user" Then
            strCN = "" : strSN = "" : strGivenName = "" : strName = "" : strSAMAccountName = ""
            strUPN = "" : strInitials = ""

            strCN = objRecordSet.Fields("cn")
            strSN = objRecordSet.Fields("sN")
            strGivenName = objRecordSet.Fields("givenName")
            strName = objRecordSet.Fields("name")
            strSAMAccountName = objRecordSet.Fields("sAMAccountName")
            strUPN = objRecordSet.Fields("userPrincipalName")
            strInitials = objRecordSet.Fields("initials")

            objFile.WriteLine strCN & "," & strSN & "," & strGivenName & "," & strName & "," &_
                  strSAMAccountName & "," & strUPN & "," & strInitials
      End If
      objRecordSet.MoveNext
Wend

objConnection.Close

Set objRecordSet = Nothing
Set objCommand = Nothing
Set objConnection = Nothing

Set objFile = Nothing
Set objFileSystem = Nothing
0
 
josganAuthor Commented:
Awesome, thanks this does exactly what i need.
0
 
Chris DentPowerShell DeveloperCommented:

Pleasure :)

Chris
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now