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=sset query=dsquery * domainroot -filter "Your standard filtering" -attr attribute1 attribute2 attributeN -limit 0for /f "Tokens=*" %%q in ('%query%') do set line=%%q&call :tstDSQFE......goto :EOF:tstDSQFEcall DSQFE table status lineIf /i "%status%" EQU "N" goto Error........

Where:

table    is a <a href="https://www.itprotoday.com/article/jsifaq/jsi-tip-5535-how-can-i-cause-a-called-batch-file-to-return-a-call-directed-environment-variable-.aspx">call directed environment variable</a> that DSQFE.bat uses to store attribute information.status   is a <a href="https://www.itprotoday.com/article/jsifaq/jsi-tip-5535-how-can-i-cause-a-called-batch-file-to-return-a-call-directed-environment-variable-.aspx">call directed environment variable</a> that you set to:                s   - First call. DSQFE uses the information from the heading line to build the tableN   - DSQFE encountered and error.                Y   - processing of this line was successful.                M   - Multi-line attribute. See tip 7724.line     is a <a href="https://www.itprotoday.com/article/jsifaq/jsi-tip-5535-how-can-i-cause-a-called-batch-file-to-return-a-call-directed-environment-variable-.aspx">call directed environment variable</a> 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                                         postalCodeCN=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=sset query=dsquery * domainroot -filter "(&(objectCategory=Person)(objectClass=User)(sAMAccountName=*)(c=us)(st=GA)(l=Alpharetta))" -attr distinguishedName department manager postalCode -limit 0for /f "Tokens=*" %%q in ('%query%') do set line=%%q&call :tstDSQFE......goto :EOF:tstDSQFEif /i "%status%" EQU "s" call DSQFE table status line&goto outcall DSQFE table status lineif %manager% EQU "" goto outif "%postalCode:~1,1%" NEQ "3" goto out goto :EOF:outIf /i "%status%" EQU "N" goto Error@echo %line%

2. Report missing manager data. The report should look like:

department    sAMAccountNmame  manager                                                       DoeJaneAccounting    SmithJTesting       JonesBset status=sset query=dsquery * domainroot -filter "(&(objectCategory=Person)(objectClass=User)(sAMAccountName=*))" -attr instanceType department sAMAccountName manager -limit 0for /f "Tokens=*" %%q in ('%query%') do set line=%%q&call :tstDSQFE......goto :EOF:tstDSQFEif /i "%status%" EQU "s" call DSQFE table status line&goto outcall DSQFE table status lineif %manager% NEQ "" goto :EOF:outIf /i "%status%" EQU "N" goto Error@echo %line%

3. Generate a CSV containing sAMAccountName l st postalCode c:

set status=sset query=dsquery * domainroot -filter "(&(objectCategory=Person)(objectClass=User)(sAMAccountName=*))" -attr sAMAccountName l st postalCode c -limit 0for /f "Tokens=*" %%q in ('%query%') do set line=%%q&call :tstDSQFE......goto :EOF:tstDSQFEcall DSQFE table status line@echo %sAMAccountName%,%l%,%st%,%postalCode%,%c%

DSQFE.bat contains:

@echo offsetlocalif {%3}

{} goto syntaxcall set status=%%%2%%if /i "%status%" EQU "s" goto startendlocal:DSQFEdcall set $DSQFEw=%%%1:~50^,4%%%if "%$DSQFEw%" NEQ "0002" goto DSQFedncall set $DSQFEw=%%%3:~0,2%%#if "%$DSQFEw%" EQU " #" goto DSQFednset %2=Mexit /b 0:DSQFednset /a $DSQFEtp=0set %2=Ycall set $DSQFEw=%%%1:~%$DSQFEtp%^,12%%%if /i "%$DSQFEw%" EQU "instanceType" call set %3=%%%3:~16%%:tloopcall set $DSQFEw=%%%1:~%$DSQFEtp%^,50%%%if "%$DSQFEw:~0,3%" EQU "###" exit /b 0set $DSQFEw=%$DSQFEw: =%set /a $DSQFEtp=%$DSQFEtp% + 50call set /a $DSQFEp=10000%%%1:~%$DSQFEtp%^,4%%%%%%10000set /a $DSQFEtp=%$DSQFEtp% + 4call set /a $DSQFEl=100%%%1:~%$DSQFEtp%^,2%%%%%%100set /a $DSQFEtp=%$DSQFEtp% + 2call set %$DSQFEw%=%%%3:~%$DSQFEp%,%$DSQFEl%%%###call set %$DSQFEw%=%%%$DSQFEw%: =%%%call set %$DSQFEw%=%%%$DSQFEw%: ###=###%%%call set %$DSQFEw%="%%%$DSQFEw%:###=%%%"goto tloop:startset table=set /a $DSQFEp=0set /a $DSQFEl=0set /a this=0call set line=%%%3%%#if "%line:~0,1%" EQU " " set /a $DSQFEp=2&set /a this=2set it=%line:~0,12%:hdloopset /a this=%this% + 1call set char=%%%3:~%this%,1%%if "%char%" EQU "#" goto startendif "%char%" EQU "" goto startendif "%char%" NEQ " " goto hdloop:hdbloopset /a this=%this% + 1call set char=%%%3:~%this%,1%%if "%char%" EQU "#" goto startendif "%char%" EQU "" goto startendif "%char%" EQU " " goto hdbloopset /a $DSQFEl=%this% - %$DSQFEp% - 4set /a chk=%$DSQFEp% + %$DSQFEl%call set char=%%%3:~%chk%,1%%if "%char%" NEQ " " set /a $DSQFEl=%$DSQFEl% + 1call set char=%%%3:~%$DSQFEp%,%$DSQFEl%%%set $DSQFEw=%char% #set char=%$DSQFEw:~0,50%set /a nwrk=%$DSQFEp% + 10000set $DSQFEtp=%nwrk:~1,4%set /a nwrk=%$DSQFEl% + 100set tl=%nwrk:~1,2%set table=%table%%char%%$DSQFEtp%%tl%set /a $DSQFEp=%this%if /i "%it%" NEQ "instanceType" goto hdloopset it=set line=%line:~16%set /a $DSQFEp=0set /a $DSQFEl=0set /a this=0goto hdloop:startendset /a $DSQFEl=%this% - %$DSQFEp% - 2call set char=%%%3:~%$DSQFEp%,%$DSQFEl%%%set $DSQFEw=%char% #set char=%$DSQFEw:~0,50%set /a nwrk=%$DSQFEp% + 10000set $DSQFEtp=%nwrk:~1,4%set /a nwrk=%$DSQFEl% + 100set 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 AttrValueif not {%2}

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