csvde export, excluding specific OU's.

[Michael-Dallas]

I'm banging away at a csvde to export all user accounts but I need to exclude
certain OU's. I'm having a hard time with it and wanted to pass it out here
for someone that can correct me. I'm working with -r (filter) and I'm
currently working it as:

"(&(objectCategory=user)(!cn=*,ou=serviceaccounts,dc=company,dc=com))"
or
"(&(objectCategory=user)(!ou=serviceaccounts,dc=company,dc=com))"
and other variants.

The ! is to exclude or NOT from what I've read and seen in the examples as
used for attributes of accounts to filter on. I'm going to get over and try
joeware but wanted to check on the csvde syntax because I have a feeling that
I'm going to be asked for other things such as multiple OU's to exclude and
certain attributes and then use them for import into our lab for testing
either migrations or policies.

Thanks in advance.

[Joe Richards [MVP]]

You can't exclude OUs that way, you would need to set your search scope
and use as many filters as necessary to avoid those OUs. That is an LDAP
thing.

Depending on what you are doing you may want to look at adfind, as it
can output CSV format and allows you to specify DNs to exclude (i.e. you
can filter out objects in certain OUs). It does it by returning all of
the objects and then filtering the output. Note however that not all
attributes will be output in a way that CSVDE would like on an import,
so you will have to watch out there.

Also, user is not an objectcategory. It is only an objectclass, the
objectcategory of user is person. So when you specify a filter of
objectcategory=user what actually gets used for the filter is
objectcategory=person. That won't make a difference unless you have
contacts at which point they will also start showing up in your output
as well.

joe

--
Joe Richards Microsoft MVP Windows Server Directory Services
Author of O'Reilly Active Directory Third Edition
www.joeware.net


---O'Reilly Active Directory Third Edition now available---

http://www.joeware.net/win/ad3e.htm

Post by Michael-Dallas
I'm banging away at a csvde to export all user accounts but I need to exclude
certain OU's. I'm having a hard time with it and wanted to pass it out here
for someone that can correct me. I'm working with -r (filter) and I'm
"(&(objectCategory=user)(!cn=*,ou=serviceaccounts,dc=company,dc=com))"
or
"(&(objectCategory=user)(!ou=serviceaccounts,dc=company,dc=com))"
and other variants.
The ! is to exclude or NOT from what I've read and seen in the examples as
used for attributes of accounts to filter on. I'm going to get over and try
joeware but wanted to check on the csvde syntax because I have a feeling that
I'm going to be asked for other things such as multiple OU's to exclude and
certain attributes and then use them for import into our lab for testing
either migrations or policies.
Thanks in advance.

[d***@yahoo.co.in]

You can use "ADManager Plus" Tool to export all the users except
certain OUS.

See the demo link:
http://demo.admanagerplus.com/Report.do?selectedTab=reports&methodToCall=report&categoryId=2&reportId=15#

Download link :
http://manageengine.adventnet.com/products/ad-manager/download.html?free

[Michael-Dallas]

Thanks Joe, I guess I know why I was banging my head on the filter and trying
to use it. We're going through a re-design and were working on the current
bloated OU structure so I was hoping for a straight-up filter. With AdFind
by using the -excldn option it can't be used with -csv so I'll try to look at
using multiple csvde's. I'm in need not only for reporting (.csv) but also
importing into lab for testing. I'll look more into the usage of
objectCategory and objectClass, thanks for the info there. I had someone say
that it was an indexed field that would return better performance (which did)
and I have gotten stuck with it. Then again maybe I'm not using the best
object type wth that query...

Many thanks for your time and efforts.

Post by Joe Richards [MVP]
You can't exclude OUs that way, you would need to set your search scope
and use as many filters as necessary to avoid those OUs. That is an LDAP
thing.
Depending on what you are doing you may want to look at adfind, as it
can output CSV format and allows you to specify DNs to exclude (i.e. you
can filter out objects in certain OUs). It does it by returning all of
the objects and then filtering the output. Note however that not all
attributes will be output in a way that CSVDE would like on an import,
so you will have to watch out there.
Also, user is not an objectcategory. It is only an objectclass, the
objectcategory of user is person. So when you specify a filter of
objectcategory=user what actually gets used for the filter is
objectcategory=person. That won't make a difference unless you have
contacts at which point they will also start showing up in your output
as well.
joe
--
Joe Richards Microsoft MVP Windows Server Directory Services
Author of O'Reilly Active Directory Third Edition
www.joeware.net
---O'Reilly Active Directory Third Edition now available---
http://www.joeware.net/win/ad3e.htm

Post by Michael-Dallas
I'm banging away at a csvde to export all user accounts but I need to exclude
certain OU's. I'm having a hard time with it and wanted to pass it out here
for someone that can correct me. I'm working with -r (filter) and I'm
"(&(objectCategory=user)(!cn=*,ou=serviceaccounts,dc=company,dc=com))"
or
"(&(objectCategory=user)(!ou=serviceaccounts,dc=company,dc=com))"
and other variants.
The ! is to exclude or NOT from what I've read and seen in the examples as
used for attributes of accounts to filter on. I'm going to get over and try
joeware but wanted to check on the csvde syntax because I have a feeling that
I'm going to be asked for other things such as multiple OU's to exclude and
certain attributes and then use them for import into our lab for testing
either migrations or policies.
Thanks in advance.

[Joe Richards [MVP]]

I wrote adfind, you can use -csv with -excldn. If you can't send me an
email (address in the header of adfind when you run it) showing what you
are trying to do and what is failing.

objectcategory is indexed, however user is a special case in that the
default objectcategory for user is not also user, it is person. So you
want to use the objectcategory combined with objectclass or use another
indexed attribute such as samaccounttype.

joe

--
Joe Richards Microsoft MVP Windows Server Directory Services
Author of O'Reilly Active Directory Third Edition
www.joeware.net


---O'Reilly Active Directory Third Edition now available---

http://www.joeware.net/win/ad3e.htm

Post by Michael-Dallas
Thanks Joe, I guess I know why I was banging my head on the filter and trying
to use it. We're going through a re-design and were working on the current
bloated OU structure so I was hoping for a straight-up filter. With AdFind
by using the -excldn option it can't be used with -csv so I'll try to look at
using multiple csvde's. I'm in need not only for reporting (.csv) but also
importing into lab for testing. I'll look more into the usage of
objectCategory and objectClass, thanks for the info there. I had someone say
that it was an indexed field that would return better performance (which did)
and I have gotten stuck with it. Then again maybe I'm not using the best
object type wth that query...
Many thanks for your time and efforts.

Post by Joe Richards [MVP]
You can't exclude OUs that way, you would need to set your search scope
and use as many filters as necessary to avoid those OUs. That is an LDAP
thing.
Depending on what you are doing you may want to look at adfind, as it
can output CSV format and allows you to specify DNs to exclude (i.e. you
can filter out objects in certain OUs). It does it by returning all of
the objects and then filtering the output. Note however that not all
attributes will be output in a way that CSVDE would like on an import,
so you will have to watch out there.
Also, user is not an objectcategory. It is only an objectclass, the
objectcategory of user is person. So when you specify a filter of
objectcategory=user what actually gets used for the filter is
objectcategory=person. That won't make a difference unless you have
contacts at which point they will also start showing up in your output
as well.
joe
--
Joe Richards Microsoft MVP Windows Server Directory Services
Author of O'Reilly Active Directory Third Edition
www.joeware.net
---O'Reilly Active Directory Third Edition now available---
http://www.joeware.net/win/ad3e.htm

Post by Michael-Dallas
I'm banging away at a csvde to export all user accounts but I need to exclude
certain OU's. I'm having a hard time with it and wanted to pass it out here
for someone that can correct me. I'm working with -r (filter) and I'm
"(&(objectCategory=user)(!cn=*,ou=serviceaccounts,dc=company,dc=com))"
or
"(&(objectCategory=user)(!ou=serviceaccounts,dc=company,dc=com))"
and other variants.
The ! is to exclude or NOT from what I've read and seen in the examples as
used for attributes of accounts to filter on. I'm going to get over and try
joeware but wanted to check on the csvde syntax because I have a feeling that
I'm going to be asked for other things such as multiple OU's to exclude and
certain attributes and then use them for import into our lab for testing
either migrations or policies.
Thanks in advance.

[ekrengel]

I am also trying to do the same thing, exclude certain OU's or only use
the OU's that I have specified. This is what I have:

CSVDE -d "OU=Users,OU=Information
Technology,OU=Departments,DC=domain,DC=domain,dc=com" -f Export.csv -r
objectCategory=person -l "cn, l, st, title, department, company,
userPrincipalName"

That only does 1 OU...

[Joe Richards [MVP]]

With pure LDAP, your only option is to query each level individually,
there is no way to filter out specific subou's. Adfind has filtering
built into it via the -excldn switch, download adfind and look at adfind /??

--
Joe Richards Microsoft MVP Windows Server Directory Services
Author of O'Reilly Active Directory Third Edition
www.joeware.net


---O'Reilly Active Directory Third Edition now available---

http://www.joeware.net/win/ad3e.htm

Post by ekrengel
I am also trying to do the same thing, exclude certain OU's or only use
CSVDE -d "OU=Users,OU=Information
Technology,OU=Departments,DC=domain,DC=domain,dc=com" -f Export.csv -r
objectCategory=person -l "cn, l, st, title, department, company,
userPrincipalName"
That only does 1 OU...

[ekrengel]

If I queried each OU individually, how would I get it to append to th
same file

--
ekrenge
-----------------------------------------------------------------------
ekrengel's Profile: http://forums.techarena.in/member.php?userid=2219
View this thread: http://forums.techarena.in/showthread.php?t=51907

http://forums.techarena.i

[Joe Richards [MVP]]

YOu can't with csvde, you would need to create separate files and then
join them.

--
Joe Richards Microsoft MVP Windows Server Directory Services
Author of O'Reilly Active Directory Third Edition
www.joeware.net


---O'Reilly Active Directory Third Edition now available---

http://www.joeware.net/win/ad3e.htm

If I queried each OU individually, how would I get it to append to the
same file?

[ekrengel]

I know...I have created the scripts to make the seperate files, I just
need to know how to join the files through some type of script...and
not an external program. I know there are programs out there that will
join them, but I'm trying to make this all into one script. Right now
I'm using vbs.

[Joe Richards [MVP]]

o You open an output file
o Get list of files to read
o open file from to read list
o read file
o strip header line and if the first one right it to file, else validate
that it is the same as first header file (i.e. nothing changed)
o write in file to out file
o loop

The touchy part is making sure that the attributes returned are the same
as the headers can vary based on what is returned.

--
Joe Richards Microsoft MVP Windows Server Directory Services
Author of O'Reilly Active Directory Third Edition
www.joeware.net


---O'Reilly Active Directory Third Edition now available---

http://www.joeware.net/win/ad3e.htm

Post by ekrengel
I know...I have created the scripts to make the seperate files, I just
need to know how to join the files through some type of script...and
not an external program. I know there are programs out there that will
join them, but I'm trying to make this all into one script. Right now
I'm using vbs.

[ekrengel]

To export users and contacts, would I just use:

"(objectCategory=person)"

??

[J_Fry]

Yes, using that filter with -r will work and give you users and contacts, but
also computer accounts. Using
"(&(objectCategory=person)(!objectCategory=computer))" will filter the
results down to only users and contacts. Hope that helps!

Jason

Post by ekrengel
"(objectCategory=person)"
??
--
ekrengel
------------------------------------------------------------------------
ekrengel's Profile: http://forums.techarena.in/member.php?userid=22196
View this thread: http://forums.techarena.in/showthread.php?t=519079
http://forums.techarena.in

[ekrengel]

Ok thanks. I have one more question...how would I get the fields to be
exported in a certain order by columns in the csv file? I am having a
horrible time trying to figure it out, it seems like its random in the
way that it puts it in the csv. It is definitely not in the order that
you list the fields...