Populating the address book

[SoCi0pAtH]

We have Konica all in one copiers in the office. We heavily use the scan feature and copy the file to the users home drive. With 60+ copiers and 700+ users in 40 offices maintaining the address books on each printer is a daily chore..

My goal is to automate this task as much as possible.

Goal : Query AD for user information to build csv to import. Each office has its own OU in AD, so I should be able to create a seperate csv for each office based upon the OU.

Challenges
1. I'm not able to use the homedir attribute because its populated with a DFS link that the copiers cant support.
2. Each site has its own DFS home dir server/host. Although there is a naming convention there are old servers that dont follow it.
3. Home directories root are different for each site.
4. Each site has its own AD service account to grant access to the users home drive.

This is a NOT working work in progress

The logic goes
1. Query AD for list of OUs (sites)
2. Create a file with the OU name
3. Write column headers
4. Populate user info
5. Repeat until all AD OUs have been processed.


clsTaskkill /IM Excel.exe /F
setlocal enabledelayedexpansion


for /f "delims=" %%i in ('adfind ^-nodn ^-list ^-s one ^-soao ^-f objectclass^=OrganizationalUnit ^-b ^"OU^=Users^-Corporate^,OU^=UserAccounts^,DC^=mycompany^,DC^=in t^" distinguishedname') do (
for /f "delims=" %%v in ('adfind ^-f "%%i" ^-nodn ^-list name') do (
echo Processing OU %%v
)
echo Abbreviated name,Destination type,Search key,E-Mail: E-Mail Address,SMB: Host Address,SMB: File Path,SMB: User ID > %%v.csv
for /f "delims=" %%j in ('adfind ^-soao ^-f objectclass^=person ^-b ^"%%i^" ^-nodn ^-list sAMAccountName') do (
set abbreviated=
set first=
set last=
set destination=SMB
set mail=
set smbhost=CRP^-SRV^-FILE1
set smbpath=user\
set smbid=CRP-PRT
echo processing AD %%i
for /f "delims=" %%k in ('adfind ^-nodn ^-nocsvheader ^-csv ^-f ^"sAMAccountName^=%%j^" displayName') do set abbreviated=%%k
for /f "delims=" %%m in ('adfind ^-nodn ^-nocsvheader ^-list ^-f ^"sAMAccountName^=%%j^" givenname') do set first=%%m
for /f "delims=" %%q in ('adfind ^-nodn ^-nocsvheader ^-list ^-f ^"sAMAccountName^=%%j^" sn') do set last=%%q
for /f "delims=" %%r in ('adfind ^-nodn ^-nocsvheader ^-list ^-f ^"sAMAccountName^=%%j^" mail') do set mail=%%r
echo !abbreviated!,!destination!,search key,!mail!,!smbhost!,!smbpath!!first!.!last!,!smbi d!
echo !abbreviated!,!destination!,,!mail!,!smbhost!,!smb path!!first!.!last!,!smbid! >> %%v.csv
)
start c:\scripts\%%v.csv
)


Once I get it working I'm going to write it in Powershell.

Any suggestions for a command line utility to upload the csv's to the printers would be great.

[SoCi0pAtH]

Example output

Abbreviated name,Destination type,Search key,E-Mail: E-Mail Address,SMB: Host Address,SMB
"Sean Smith",SMB,search key,sean.brennan@mycompany.net,CRP-SRV-FILE1,user\Sean.smith,CRP-PRT

[copier addict]

Try Pagescope Data Administrator. With it you are able to manage multiple MFPs at the same time.
It is a free download from KonMin. Just google it.

[SoCi0pAtH]

Try Pagescope Data Administrator. With it you are able to manage multiple MFPs at the same time.
It is a free download from KonMin. Just google it.

I have it, but with 60+ copiers I'm hoping to make this a completely automated task.

[copyman]

Depends on the model of the machines.

[SoCi0pAtH]

Depends on the model of the machines.


Which ones, and using what utility?

https://i.imgur.com/YBbtwbx.png

[SoCi0pAtH]

The latest.....Still a lot of junk code and few test lines.. but its %95 working....



echo
cls
Taskkill /IM Excel.exe /F
setlocal enabledelayedexpansion


for /f "delims=" %%i in ('adfind ^-nodn ^-list ^-s one ^-soao ^-f objectclass^=OrganizationalUnit ^-b ^"OU^=Users^-Corporate^,OU^=UserAccounts^,DC^=mycompany^,DC^=in t^" OU') do (
echo =============================Processing OU %%i========================================
echo Abbreviated name,E-Mail: E-Mail Address,SMB: Host Address,SMB: File Path,SMB: User ID,SMB: Password > "%%i.csv"
for /f "delims=" %%j in ('adfind ^-soao -b "OU=%%i,OU=Users-Corporate,OU=UserAccounts,DC=mycompany,DC=int" ^-f objectclass^=person ^-list sAMAccountName') do (
set abbreviated=
set first=
set last=
set destination=SMB
set mail=
set password="7EV!9%"
set smbhost=CRP
set smbpath=users\
set smbid=CRP-PRT
echo Adding %%j to site %%i
for /f "delims=" %%k in ('adfind -b ^"OU^=Users^-Corporate^,OU^=UserAccounts^,DC^=mycompany^,DC^=in t^" ^-nodn ^-nocsvheader ^-list ^-f ^"sAMAccountName^=%%j^" displayName') do set abbreviated=%%k
for /f "delims=" %%m in ('adfind -b ^"OU^=Users^-Corporate^,OU^=UserAccounts^,DC^=mycompany^,DC^=in t^" ^-nodn ^-nocsvheader ^-list ^-f ^"sAMAccountName^=%%j^" givenname') do set first=%%m
for /f "delims=" %%q in ('adfind -b ^"OU^=Users^-Corporate^,OU^=UserAccounts^,DC^=mycompany^,DC^=in t^" ^-nodn ^-nocsvheader ^-list ^-f ^"sAMAccountName^=%%j^" sn') do set last=%%q
for /f "delims=" %%r in ('adfind -b ^"OU^=Users^-Corporate^,OU^=UserAccounts^,DC^=mycompany^,DC^=in t^" ^-nodn ^-nocsvheader ^-list ^-f ^"sAMAccountName^=%%j^" mail') do set mail=%%r
echo !abbreviated!,!mail!,!smbhost!,!smbpath!!first!.!l ast!\scans,!smbid!,!password!
Echo
echo !abbreviated!,!mail!,!smbhost!,!smbpath!!first!.!l ast!\scans,!smbid!,!password!>> "%%i.csv"
)
rem "C:\Program Files\Microsoft Office 15\root\Office15\excel.exe" "c:\scripts\%%i.csv"
)

[rrrohan]

Why not enable LDAP search and just print instructions on how to search and register your email address.

[NeoMatrix]

The latest.....Still a lot of junk code and few test lines.. but its %95 working....



echo
cls
Taskkill /IM Excel.exe /F
setlocal enabledelayedexpansion


for /f "delims=" %%i in ('adfind ^-nodn ^-list ^-s one ^-soao ^-f objectclass^=OrganizationalUnit ^-b ^"OU^=Users^-Corporate^,OU^=UserAccounts^,DC^=mycompany^,DC^=in t^" OU') do (
echo =============================Processing OU %%i========================================
echo Abbreviated name,E-Mail: E-Mail Address,SMB: Host Address,SMB: File Path,SMB: User ID,SMB: Password > "%%i.csv"
for /f "delims=" %%j in ('adfind ^-soao -b "OU=%%i,OU=Users-Corporate,OU=UserAccounts,DC=mycompany,DC=int" ^-f objectclass^=person ^-list sAMAccountName') do (
set abbreviated=
set first=
set last=
set destination=SMB
set mail=
set password="7EV!9%"
set smbhost=CRP
set smbpath=users\
set smbid=CRP-PRT
echo Adding %%j to site %%i
for /f "delims=" %%k in ('adfind -b ^"OU^=Users^-Corporate^,OU^=UserAccounts^,DC^=mycompany^,DC^=in t^" ^-nodn ^-nocsvheader ^-list ^-f ^"sAMAccountName^=%%j^" displayName') do set abbreviated=%%k
for /f "delims=" %%m in ('adfind -b ^"OU^=Users^-Corporate^,OU^=UserAccounts^,DC^=mycompany^,DC^=in t^" ^-nodn ^-nocsvheader ^-list ^-f ^"sAMAccountName^=%%j^" givenname') do set first=%%m
for /f "delims=" %%q in ('adfind -b ^"OU^=Users^-Corporate^,OU^=UserAccounts^,DC^=mycompany^,DC^=in t^" ^-nodn ^-nocsvheader ^-list ^-f ^"sAMAccountName^=%%j^" sn') do set last=%%q
for /f "delims=" %%r in ('adfind -b ^"OU^=Users^-Corporate^,OU^=UserAccounts^,DC^=mycompany^,DC^=in t^" ^-nodn ^-nocsvheader ^-list ^-f ^"sAMAccountName^=%%j^" mail') do set mail=%%r
echo !abbreviated!,!mail!,!smbhost!,!smbpath!!first!.!l ast!\scans,!smbid!,!password!
Echo
echo !abbreviated!,!mail!,!smbhost!,!smbpath!!first!.!l ast!\scans,!smbid!,!password!>> "%%i.csv"
)
rem "C:\Program Files\Microsoft Office 15\root\Office15\excel.exe" "c:\scripts\%%i.csv"
)




What is the "adfind" trying to execute ?

Is "adfind" a konmin or macintosh command for searching a string ?
You appear to be "escaping ^" a lot of strings in the "For /F" function.
You may be able to reduce the "for function" escapes to a simplified variable set directly.

Example only . Have you tried using:
Any string line of text found with adfind "-" will be broken into single tokens at each "-"

for /f "tokens=1-9, delims=-" %%i do ( 'adfind /sw "any string"' ) do (

set abbreviated=%%i
set first= %%j
set last= %%k
set destination=%%l
set mail=%%m
set password=%%n
set smbhost=%%o
set smbpath=%%p
set smbid=%%q

)

Token variables are case sensitive eg. "for /f %%i" is not the same as "for /f %%I"
Every "for" loop loses all token %%? values once outside the bracket delimits of the "for /f" loop.
The for %%? are only local vars while inside the "for /f" loop. You cannot access these values, thought it will store the last only value until the batch file terminates with "EndLocal" command.
Endlocal is automatically implied at the end of the batch file.


Post a sample/portion of a typical input *.txt or *.csv file to the forum.
You've already shown the format of the *.csv output file.
I may get a spare moment to throw some batch code together.

[SoCi0pAtH]

Why not enable LDAP search and just print instructions on how to search and register your email address.

Using this method to populate the address book means the users can scan to their home directory with about 5 buttons to press. Searching would be atleast 30. Our users complain a lot LOL

[SoCi0pAtH]

Sample of the output

https://i.imgur.com/QHFxGht.png

[SoCi0pAtH]

What is the "adfind" trying to execute ?

Is "adfind" a konmin or macintosh command for searching a string ?

AdFind (http://joeware.net/freetools/tools/adfind/index.htm)

[NeoMatrix]

AdFind (http://joeware.net/freetools/tools/adfind/index.htm)

I have download an run the command line Adfind app. I don't have an LDAP server that I can test with Adfind.

Being a command line app the standard output of Adfind can be redirected to a typed file. Eg. adfind [s/w params] > temp.txt

The redirected temp.txt can then be used inside any batch file commands directly.

Example only:
----------------------------------------
echo off
cls
type nul > addr.txt

setlocal ENABLEDELAYEDEXPANSION ENABLEEXTENSIONS

Adfind [s/w params] > temp.txt

for /f "tokens=1-9*,delims=," %%i in ('type temp.txt') do (

echo %%i,%%j,%%k,%%l,%%m,%%n,%%o,%%p,%%r >> addr.txt

)
endlocal


---------8<-----------------------------

Note the commas "," between the echo line variable tokens.
Leave out the variable tokens that are not required in the final "addr.txt" output file.

You're on the right track....


A quick look at the adfind help file shows there are a few output file options.
Some of the options allow for customised *.csv file output.

[SoCi0pAtH]

I have download an run the command line Adfind app. I don't have an LDAP server that I can test with Adfind.

Being a command line app the standard output of Adfind can be redirected to a typed file. Eg. adfind [s/w params] > temp.txt

The redirected temp.txt can then be used inside any batch file commands directly.

Example only:
----------------------------------------
echo off
cls
type nul > addr.txt

setlocal ENABLEDELAYEDEXPANSION ENABLEEXTENSIONS

Adfind [s/w params] > temp.txt

for /f "tokens=1-9*,delims=," %%i in ('type temp.txt') do (

echo %%i,%%j,%%k,%%l,%%m,%%n,%%o,%%p,%%r >> addr.txt

)
endlocal


---------8<-----------------------------

Note the commas "," between the echo line variable tokens.
Leave out the variable tokens that are not required in the final "addr.txt" output file.

You're on the right track....


A quick look at the adfind help file shows there are a few output file options.
Some of the options allow for customised *.csv file output.

If you look at my original post/code you'll see I'm already outputting to a CSV file. Its a little hard to see because i'm naming the file with a var that is populated by the name of the OU being processed.


1st for - ADFIND queries a base OU one level deep only for names of OU's.
I write the header/column names for my CSV (each one) and name the csv after the OU being processed
For Each OU name a second for loop is called that queries the OU for a list of User objects
For each User object several ADFINDS are called that populate the user data in Vars
When the user data is collected its written to my CSV file in CSV format.

This repeats until all OU's and their users have been processed. I end up with about 40 csv files, named after the OU/Office. I can then connect to the printer and upload the correct CSV with the correct users for the location.

The script is working now, I'm having an issue with OU's that have spaces in the name that I havent had time to workout. IF the OU has spaces in the name the second ADFIND fails

[SoCi0pAtH]

I have download an run the command line Adfind app. I don't have an LDAP server that I can test with Adfind.

Online LDAP Test Server - Forum Systems (https://www.forumsys.com/tutorials/integration-how-to/ldap/online-ldap-test-server/)

[SoCi0pAtH]

Softterra LDAP browser is free and a great quick way to GUI browse a LDAP server. Its a easy way to see what attributes you can query and what you can expect as an output. It also comes populated with some free test ldap servers.

Softerra LDAP Browser (https://www.ldapadministrator.com/softerra-ldap-browser.htm)

[SoCi0pAtH]

Working code


echocls
Taskkill /IM Excel.exe /F
setlocal enabledelayedexpansion


for /f "delims=" %%i in ('adfind ^-nodn ^-list ^-s one ^-soao ^-f objectclass^=OrganizationalUnit ^-b ^"OU^=Users^-Corporate^,OU^=UserAccounts^,DC^=mycompany^,DC^=in t^" OU') do (
echo =============================Processing OU %%i========================================
echo Abbreviated name,E-Mail: E-Mail Address,SMB: Host Address,SMB: File Path,SMB: User ID,SMB: Password,WellUse> "%%i.csv"
for /f "delims=" %%j in ('adfind ^-soao -b "OU=%%i,OU=Users-Corporate,OU=UserAccounts,DC=mycompany,DC=int" ^-f objectclass^=person ^-list sAMAccountName') do (
set abbreviated=
set first=
set last=
set destination=SMB
set mail=
set password="XXXXXXX"
set smbhost=myserver
set smbpath=users\
set smbid=CRP-PRT
echo Adding %%j to site %%i
for /f "delims=" %%k in ('adfind -b ^"OU^=Users^-Corporate^,OU^=UserAccounts^,DC^=mycompany^,DC^=in t^" ^-nodn ^-nocsvheader ^-list ^-f ^"sAMAccountName^=%%j^" displayName') do set abbreviated=%%k
for /f "delims=" %%m in ('adfind -b ^"OU^=Users^-Corporate^,OU^=UserAccounts^,DC^=mycompany^,DC^=in t^" ^-nodn ^-nocsvheader ^-list ^-f ^"sAMAccountName^=%%j^" givenname') do set first=%%m
for /f "delims=" %%q in ('adfind -b ^"OU^=Users^-Corporate^,OU^=UserAccounts^,DC^=mycompany^,DC^=in t^" ^-nodn ^-nocsvheader ^-list ^-f ^"sAMAccountName^=%%j^" sn') do set last=%%q
for /f "delims=" %%r in ('adfind -b ^"OU^=Users^-Corporate^,OU^=UserAccounts^,DC^=mycompany^,DC^=in t^" ^-nodn ^-nocsvheader ^-list ^-f ^"sAMAccountName^=%%j^" mail') do set mail=%%r
echo !abbreviated!,!mail!,!smbhost!,!smbpath!!first!.!l ast!\scans,!smbid!,!password!
Echo
echo !abbreviated!,!mail!,!smbhost!,!smbpath!!first!.!l ast!\scans,!smbid!,!password!>> "%%i.csv"
)
rem "C:\Program Files\Microsoft Office 15\root\Office15\excel.exe" "c:\scripts\%%i.csv"
)

[NeoMatrix]

[snip]

The script is working now, I'm having an issue with OU's that have spaces in the name that I havent had time to workout. IF the OU has spaces in the name the second ADFIND fails


Example of how to remove spaces.

--------------------------------
echo off
cls
setlocal ENABLEDELAYEDEXPANSION

set OU_Text= The Cat Sat On The Mat

rem remove spaces here with ": ="
set NoSpaces=!OU_Text: =!

echo !NoSpaces!
--------8<------------------------

c:\> TheCatSatOnTheMat

[SoCi0pAtH]

Example of how to remove spaces.

--------------------------------
echo off
cls
setlocal ENABLEDELAYEDEXPANSION

set OU_Text= The Cat Sat On The Mat

rem remove spaces here with ": ="
set NoSpaces=!OU_Text: =!

echo !NoSpaces!
--------8<------------------------

c:\> TheCatSatOnTheMat

The spaces need to be there for Distinguished name (cant be modified) or it wont work. I have to figure out how to get CMD to ignore the spaces in the command while its running.

[NeoMatrix]

Some sample batch code using the public debian LDAP server.

Cut and paste the below batch code.

Should people decide to run this code, it will load the debian public LDAP server OU=users, all 1.4MB of user data.
The final filtered output will only output the first random 50 lines, for the sake of keeping it simple.

The purpose of posting this code is give those interested a start point within the LDAP topic.
Apology for the broken CTN text formatting when posting. People will have to fix the text over wrapping format themselves.
----------------------------------------------------------------------------------

echo off
setlocal ENABLEDELAYEDEXPANSION ENABLEEXTENSIONS
cls
echo ************* LDAP Server Query. ********************
echo This batch file Querys a public internet LDAP server.
echo Output is limited to 50 lines of formatted text.
echo This is a totally random LDAP filter demonstration.
echo Author:Neomatrix, www.copytechnet.com (http://www.copytechnet.com)
echo ***********************************[Ver 1.1]*********
echo.
echo Running...... please wait....

rem -------------------- Main --------------
rem Debian host is anonymous login, no password required.
set LDAP_Host=db.debian.org:389
set datafile="_ldaphost.txt"
set csvfile="outfile.txt"
set "T= "
for /L %%i in (1,1,70) do set "Bar=!Bar!="
rem set maxlines high value to output all lines.
set /a maxlines=50
rem matching fields to output
set /a matching=4

call :Init !LDAP_Host! !datafile!
call :Header !csvfile!
call :Body !datafile! !csvfile!
call :Footer !csvfile!
call :CRT_Display !csvfile!
goto End


rem ----------------- Init -----------------
:Init LDAP_Host datafile
set host=-h %1 -simple -up
rem set sw=-list -s one -soao
set "sw="
set params=-b ou=users,dc=debian,dc=org

(adfind %host% %sw% %params%) > %2
echo Host query done...

exit /b

rem ---------------- Header ----------------
:Header outfile
echo !Bar! > %1
echo First!T!Last!T!!T!PGP Key!T!!T!URL >>%1
echo !Bar! >> %1
exit /b

rem ------------------ Filter -------------
:Body datafile csvfile
set /a max=0
set /a block=0
set /a sameblock=1
set /a seeklines=0

for /f "tokens=1* delims=:" %%i in (%~1) do (
set id=%%i:
set value=%%j

rem *** filter values from datafile ***
rem add more search and output fields here.
rem must increment the "matching" variable to suite.

if "!id!"=="dn:" set /a block=!block!+1
set /a seeklines=!seeklines!+1

if !block!==!sameblock! (
if "!id!"==">cn:" (set cname=!value!
set /a cnt=!cnt!+1)
if "!id!"==">sn:" (set sname=!value!
set /a cnt=!cnt!+1)
if "!id!"==">labeledURI:" (set aUrl=!value!
set /a cnt=!cnt!+1)
if "!id!"==">keyFingerPrint:" (set Pgp=!value!
set /a cnt=!cnt!+1)

) else (

rem *** Output valid user block. Limit to 50 users while testing***
rem *** Only output user values greater equal to above 4 matching fields
set /a sameblock=!block!

if !cnt! GEQ !matching! (
echo !cname!,!sname!,!Pgp:~0,16!,!aUrl!>> %2
set /a cnt=0
rem **limit max lines output**
set /a max=!max!+1
if !max!==!maxlines! exit /b

)))
exit /b

rem ---------------- Footer ----------------
:Footer outfile
echo !Bar! >> %1
echo Search lines: !seeklines! truncated at : !maxlines! >> %1
exit /b

rem ---------------- CRT Display ----------------
:CRT_Display outfile
type %1
exit /b

:End
endlocal
timeout /t 15

-----------8<--------------------------------------------------------------------

Sample output from the above batch file.

First----Last--------- PGP Key-------- WEB
=================================================
Klaus, Kettner, 556054EA47665DE, die-kettners.de (http://www.die-kettners.de)
Yasuhiro, Araki, BADAB1EEB153F41, profile of yasu/Ar (http://www.araki.net/)
Mark, Howard, 7DDF975E73435AC, http://www.tildemh.com
Zhao, Way, 7DDF975E73435AC, http://www.tildemh.com
Massimo, Dal Zotto, F9AB00C13E3A812, http://people.debian.org/~dz/
Thomas, Bushnell, A7676E0D4B855C1, http://www.mit.edu/~tb
Mark, Ng, A7676E0D4B855C1, http://www.mit.edu/~tb
Jörgen, Hägg, 2F033C55478C67F, http://people.debian.org/~rb/
Michael, Schiansky, F369DA7EB08B032, http://www.dummdoof.de
Chris, Davis, F369DA7EB08B032, http://www.lrtw.org

[NeoMatrix]

echo off
setlocal ENABLEDELAYEDEXPANSION ENABLEEXTENSIONS
cls
echo ************* LDAP Server Query. ********************
echo This batch file Querys a public internet LDAP server.
echo Output is limited to 50 lines of formatted text.
echo This is a totally random LDAP filter demonstration.
echo Author:Neomatrix, Copytechnet Home (http://www.copytechnet.com)
echo ***********************************[Ver 1.1]*********
echo.

echo Running...... please wait....

snip



The above "ad.bat" version 1.1 has an output bug....

The lastest: Ver1.3 has the bug fixed plus a couple of optimisation tweaks, it executes a lot faster...
I made the search field filter set easier for people to understand and modify to suite.

If anybody is interested I will post the updated version 1.3 to the forum.

Otherwise people can PM me an I will email the update code in private...

[NeoMatrix]

Working code


echocls
Taskkill /IM Excel.exe /F
setlocal enabledelayedexpansion

{random snip}

)
rem "C:\Program Files\Microsoft Office 15\root\Office15\excel.exe" "c:\scripts\%%i.csv"
)




How'd you go with your latest LDAP address book program code....?

I'm interested to see what you finished up with....