JSI Tip 7722. The DSQUERY Filter Extender.


In tips 7717 and 7714, we discussed DSQUERY attributed filtering.

It bothered me that there doesn't appear to be a way to filter an attribute to return a record only if the attribute value is nul.

Additionally, if is very difficult to script a report that requires testing attribute values. If you use the -L switch to make testing easier, you loose all the column width formatting that the query calculated.

I have scripted DSQFE.bat, a general purpose DSQuery Filter Extender, to allow you to script reports and easily test the data value of any of the reported attributes. In your script, you would apply any allowable filters you desired, select the attributes that you wish to include in the report, and then call DSQFE to allow you to test or manipulate any of the attributes returned by the query.

The syntax for using DSQFE.bat, by example, is:

set status=s
set query=dsquery * domainroot -filter "Your standard filtering" -attr attribute1 attribute2 attributeN -limit 0
for /f "Tokens=*" %%q in ('%query%') do set line=%%q&call :tstDSQFE
...
...
goto :EOF
:tstDSQFE
call DSQFE table status line
If /i "%status%" EQU "N" goto Error
....
....
Where:
table    is a call directed environment variable that DSQFE.bat uses to store attribute information.

status   is a call directed environment variable that you set to:
                s   - First call. DSQFE uses the information from the heading line to build the table  
                N   - DSQFE encountered and error.
                Y   - processing of this line was successful.
                M   - Multi-line attribute. See tip 7724.

line     is a call directed environment variable that you set to the contents of each returned query line.
                NOTE: The first attribute after the -attr switch must never be blank or nul, unless you use tip 7723.
                If you don't want sAMAccountName, distinguishedName, or another never nul attributes to be first,
                and you don't use the technique in tip 7723, use instanceType, 
                so that FOR command parsing doesn't shift the data, and DSQFE will strip if from the line.
In addition to these variables, DSQFE.bat will set a call directed environment variable, equal to the attribute names after the -attr switch, for each attribute you queried, and set them to the the quote encapsulated data value reported. A nul value is returned as "". The heading line will also return the quoted attribute names.

Sample Usage:

1. Report all Georgia residence who live in Alpharetta that do not have a postal code that starts with a 3 or do not have a manager. The report should look like:

distinguishedName                                 department    manager                                         postalCode
CN=John Doe,CN=Users,DC=JSIINC,DC=COM                                                                           30004     
CN=OU TEST,OU=OU_TEST,DC=JSIINC,DC=COM            Testing       CN=Jerold Schulman,CN=Users,DC=JSIINC,DC=COM    12345     
CN=Jane Doe,CN=Users,DC=JSIINC,DC=COM                                                                                     

set status=s
set query=dsquery * domainroot -filter "(&(objectCategory=Person)(objectClass=User)(sAMAccountName=*)(c=us)(st=GA)(l=Alpharetta))" -attr distinguishedName department manager postalCode -limit 0
for /f "Tokens=*" %%q in ('%query%') do set line=%%q&call :tstDSQFE
...
...
goto :EOF
:tstDSQFE
if /i "%status%" EQU "s" call DSQFE table status line&goto out
call DSQFE table status line
if %manager% EQU "" goto out
if "%postalCode:~1,1%" NEQ "3" goto out 
goto :EOF
:out
If /i "%status%" EQU "N" goto Error
@echo %line%
2. Report missing manager data. The report should look like:

department    sAMAccountNmame  manager                                         
              DoeJane
Accounting    SmithJ
Testing       JonesB


set status=s
set query=dsquery * domainroot -filter "(&(objectCategory=Person)(objectClass=User)(sAMAccountName=*))" -attr instanceType department sAMAccountName manager -limit 0
for /f "Tokens=*" %%q in ('%query%') do set line=%%q&call :tstDSQFE
...
...
goto :EOF
:tstDSQFE
if /i "%status%" EQU "s" call DSQFE table status line&goto out
call DSQFE table status line
if %manager% NEQ "" goto :EOF
:out
If /i "%status%" EQU "N" goto Error
@echo %line%
3. Generate a CSV containing sAMAccountName l st postalCode c:
set status=s
set query=dsquery * domainroot -filter "(&(objectCategory=Person)(objectClass=User)(sAMAccountName=*))" -attr sAMAccountName l st postalCode c -limit 0
for /f "Tokens=*" %%q in ('%query%') do set line=%%q&call :tstDSQFE
...
...
goto :EOF
:tstDSQFE
call DSQFE table status line
@echo %sAMAccountName%,%l%,%st%,%postalCode%,%c%
DSQFE.bat contains:
@echo off
setlocal
if \{%3\}

\{\} goto syntax call set status=%%%2%% if /i "%status%" EQU "s" goto start endlocal :DSQFEd call set $DSQFEw=%%%1:~50^,4%%% if "%$DSQFEw%" NEQ "0002" goto DSQFedn call set $DSQFEw=%%%3:~0,2%%# if "%$DSQFEw%" EQU " #" goto DSQFedn set %2=M exit /b 0 :DSQFedn set /a $DSQFEtp=0 set %2=Y call set $DSQFEw=%%%1:~%$DSQFEtp%^,12%%% if /i "%$DSQFEw%" EQU "instanceType" call set %3=%%%3:~16%% :tloop call set $DSQFEw=%%%1:~%$DSQFEtp%^,50%%% if "%$DSQFEw:~0,3%" EQU "###" exit /b 0 set $DSQFEw=%$DSQFEw: =% set /a $DSQFEtp=%$DSQFEtp% + 50 call set /a $DSQFEp=10000%%%1:~%$DSQFEtp%^,4%%%%%%10000 set /a $DSQFEtp=%$DSQFEtp% + 4 call set /a $DSQFEl=100%%%1:~%$DSQFEtp%^,2%%%%%%100 set /a $DSQFEtp=%$DSQFEtp% + 2 call set %$DSQFEw%=%%%3:~%$DSQFEp%,%$DSQFEl%%%### call set %$DSQFEw%=%%%$DSQFEw%: =%%% call set %$DSQFEw%=%%%$DSQFEw%: ###=###%%% call set %$DSQFEw%="%%%$DSQFEw%:###=%%%" goto tloop :start set table= set /a $DSQFEp=0 set /a $DSQFEl=0 set /a this=0 call set line=%%%3%%# if "%line:~0,1%" EQU " " set /a $DSQFEp=2&set /a this=2 set it=%line:~0,12% :hdloop set /a this=%this% + 1 call set char=%%%3:~%this%,1%% if "%char%" EQU "#" goto startend if "%char%" EQU "" goto startend if "%char%" NEQ " " goto hdloop :hdbloop set /a this=%this% + 1 call set char=%%%3:~%this%,1%% if "%char%" EQU "#" goto startend if "%char%" EQU "" goto startend if "%char%" EQU " " goto hdbloop set /a $DSQFEl=%this% - %$DSQFEp% - 4 set /a chk=%$DSQFEp% + %$DSQFEl% call set char=%%%3:~%chk%,1%% if "%char%" NEQ " " set /a $DSQFEl=%$DSQFEl% + 1 call set char=%%%3:~%$DSQFEp%,%$DSQFEl%%% set $DSQFEw=%char% # set char=%$DSQFEw:~0,50% set /a nwrk=%$DSQFEp% + 10000 set $DSQFEtp=%nwrk:~1,4% set /a nwrk=%$DSQFEl% + 100 set tl=%nwrk:~1,2% set table=%table%%char%%$DSQFEtp%%tl% set /a $DSQFEp=%this% if /i "%it%" NEQ "instanceType" goto hdloop set it= set line=%line:~16% set /a $DSQFEp=0 set /a $DSQFEl=0 set /a this=0 goto hdloop :startend set /a $DSQFEl=%this% - %$DSQFEp% - 2 call set char=%%%3:~%$DSQFEp%,%$DSQFEl%%% set $DSQFEw=%char% # set char=%$DSQFEw:~0,50% set /a nwrk=%$DSQFEp% + 10000 set $DSQFEtp=%nwrk:~1,4% set /a nwrk=%$DSQFEl% + 100 set tl=%nwrk:~1,2% set table=%table%%char%%$DSQFEtp%%tl% endlocal&set %2=Y&set %1=%table%### goto DSQFEd :syntax @echo Syntax: call DSQFE Table Status Line AttrName AttrValue if not \{%2\}

\{\} endlocal&set %2=N&exit /b 1 endlocal exit /b 1



Hide comments

Comments

  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
Publish