Announcement

Collapse
No announcement yet.

Exclude results from DSQUERY

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Exclude results from DSQUERY

    I am doing a basic query to find users whose passwords are going to expire in 10 days or less. When the results come in, there are about 10 accounts who are either disabled or accounts set to "Password does not expire". Is there a way to remove these from my results?

    Here is the command I placed in my .BAT file.

    dsquery user OU=Users,DC=Test,DC=local -o rdn -stalepwd 80

    my results would be as follows:

    "Conference Room B"
    "STS User"
    "Helpdesk"
    "Investor Relations"



    Can I exclude these accounts from showing up everytime I run this command?

  • #2
    Re: Exclude results from DSQUERY

    Imho, it would be better to use vbscript instead of a batch.


    This is how it can be done,
    For the check boxes in the Account options dialog box of the properties for a user account, numerical values are assigned to the UserAccountControl attribute. The value that is assigned to the attribute tells Windows which options have been enabled.
    The userAccountControl attribute on user and computer objects is used to describe a whole series of properties, including account status (i.e., enabled or disabled), account lockout, password not required, smartcard authentication required, etc.

    To search against these types of attributes, you need to use bitwise search filters.
    For your script you can use this to filter out "disabled" and accounts set to "Password does not expire".

    In a batch you can use Dsquery.exe * -Filter with a generic LDAP query to use bitwise search filters.
    However, for your script "to find users whose passwords are going to expire in 10 days or less", you cannot use the "stalepwd"-option any more when using the LDAP query filter. Instead you can use the pwdLastSet attibute to compare with a date you entered. BUT it then will be nessesary to calculate a date to compare with, and have it translated to an Integer8 format. I never have tried to do that with a batch, I don't think that would be so easy if possible at all. That is the reason that you probably better off with a vbscript.

    The batch below will work, but you''l have to update the datestring (LssEqDate variable) manually for every new run.
    Code:
    @echo off
    
    :: You'll have to calculate "a date" to compare with the 'pwdLastSet' attribute.
    :: ( LssEqDate = (currentDate - Maximum password age) + 10 days )
    :: note! the date must be converted to an Integer8 date format,
    :: you can find an Integer8 generator here: www.petri.com/ldap_search_samples_for_windows_2003_and_exchange.htm
    :: Enter the generated date below to define the 'LssEqDate' variable,
    Set "LssEqDate=xxxxxxxxxx"   as Integer8 !
    
    :: Define Property flag and its Value in decimal
    :: (http://support.microsoft.com/kb/305144)
    Set/a DONT_EXPIRE_PASSWORD=65536
    Set/a ACCOUNTDISABLE=2
    
    :: counting bits for the (/each) bitwise rule
    Set/a "Bitwise_Value1=%DONT_EXPIRE_PASSWORD% + %ACCOUNTDISABLE%
    
    :: bitwise rules:
    :: (http://codeidol.com/active-directory/active-directory/Searching-and-Manipulating-Objects/Searching-with-a-Bitwise-Filter)
    :: A logical "OR" filter will return success if any bit specified by value is stored in attributename.
    :: ,,the logical OR matching rule OID is 1.2.840.113556.1.4.804
    :: A logical "AND" filter will return success if all bits specified by value match the value of attributename.
    :: ,,the logical AND matching rule OID is 1.2.840.113556.1.4.803
    
    dsquery.exe * -limit 0 -Filter "(&(objectCategory=person)(objectClass=user)(pwdLastSet<=%LssEqDate%)(!(userAccountControl:1.2.840.113556.1.4.804:=%Bitwise_Value1%)(pwdLastSet={0})))" -attr name
    
    pause
    When using a vbscript instead, you just hardcode once the value of 'Maximum password age' and the value '10' = 'days before Expiring Date' to do the check for password expiration. The script will caculate the date in the right format for you.


    \Rems
    Last edited by Rems; 28th January 2009, 15:07. Reason: typo in LssEqDate description

    This posting is provided "AS IS" with no warranties, and confers no rights.

    __________________

    ** Remember to give credit where credit's due **
    and leave Reputation Points for meaningful posts

    Comment


    • #3
      Re: Exclude results from DSQUERY

      There is one other solution (although not as pretty as the previous solution) that you can use for a batch;

      The batch below uses dsquery.exe with the option -stalepwd 80 and pipe the results to a dsget.exe command.
      Dsget output the Displayname of the user ( <- you may choose another name attribute for this),
      and also output the 'account disabled' and the 'password never expires' status <- this is to use for the filter.
      Next, a Findstr statement is used with the /v switch to filter out all lines that are containing exact the word "yes" (not if it is a part of a word).

      Code:
      @echo off & Setlocal EnableDelayedExpansion
      title Users that have not changed their password for at least 80 days.
      
      :: note, Useraccounts that are Disabled and 
      :: Useraccounts where the Passw is set to Never Expire
      :: are filtered out and these users do not appear on the list. 
      
      Set "strOU=OU=Users,DC=Test,DC=local"
      
      echo/QUERYING (incl. sub-OUs):
      echo/%strOU%
      echo/
      echo/Results,
      echo/	display name	/	pwdneverexpires	/	accountdisabled
      echo/+------------------------------------------------------------------------------+
      Set /a iCnt=0
      For /f "skip=1 delims=" %%a in (
         'dsquery.exe user "%strOU%" -stalepwd 80 ^| dsget user -disabled -pwdneverexpires -display ^|Findstr /iv "\<yes\>" ^|Findstr /iv "\<dsget\>"'
         ) DO (
         Set /a iCnt=!iCnt!+1
         echo/!iCnt!	:%%a
      )
      
      echo/&echo/&echo/finished.&pause>nul
      \Rems
      Last edited by Rems; 2nd February 2009, 11:35. Reason: correction in the code!!

      This posting is provided "AS IS" with no warranties, and confers no rights.

      __________________

      ** Remember to give credit where credit's due **
      and leave Reputation Points for meaningful posts

      Comment


      • #4
        Re: Exclude results from DSQUERY

        thanks Rems, I will try the vbscript. I'm just nervous about vb cause Im not very familiar at all and because I am going off what other people tell me to put in, I am afraid I will run the script and my DC is going to restart or something.

        At least with batch, even if I dont recognize the exact command, I can pretty much figure out what it will or wont do.

        Comment


        • #5
          Re: Exclude results from DSQUERY

          I can help writing the vbscript, - and will try to explain what the code is doing.

          This posting is provided "AS IS" with no warranties, and confers no rights.

          __________________

          ** Remember to give credit where credit's due **
          and leave Reputation Points for meaningful posts

          Comment


          • #6
            Re: Exclude results from DSQUERY

            So the script you gave me in your previous post wont work?

            Comment


            • #7
              Re: Exclude results from DSQUERY

              Both will work, but they are not vbscripts.
              The samples in both of my replies are both batch code. (*.bat or *.cmd files)

              In my first reply, you have to manually insert a reference_date in Integer8 notation every time you want to run the batch, since a batch cannot count dates and cannot generate Integer8 dates (a vbscript could).

              The second batch is a simple work-around for the date problem. If the second batch runs fine in your environment, use that solution.

              -EDIT- Note,
              I have made a nesserary correction in the 2nd batch, I changed "\^<yes\^>" into "\<yes\>". Because there should be no escape sign in the Findstr string!!
              (Also made some enhancements to the existing code just to make the results will appear nicer on screen)



              \Rems
              Last edited by Rems; 30th January 2009, 16:44.

              This posting is provided "AS IS" with no warranties, and confers no rights.

              __________________

              ** Remember to give credit where credit's due **
              and leave Reputation Points for meaningful posts

              Comment


              • #8
                Re: Exclude results from DSQUERY

                Rems,

                The second one did work without a hitch. I would like to begin to learn and use vbscript. Could you help me with creating a script?

                Also, is there a VBscript book you would recommend I pickup?

                EDIT:

                How about this one? http://www.amazon.com/Microsoft-VBSc...3597541&sr=1-3
                Last edited by nappyjim; 2nd February 2009, 20:23.

                Comment

                Working...
                X