locked
how to deal with arrays when one of them is a csv? RRS feed

  • Question

  • I have been able to execute this script successfully when dealing with two arrays in static form.

    # Define searchbase
    $OUs = "OU=Employees,OU=User Accounts,DC=School,DC=edu",
           "OU=Generic Accounts,OU=User Accounts,DC=School,DC=edu"
    
    # To find staff
    $filter_staff = {
        (Title -notlike "*Instructor*") -and 
        (Title -notlike "*Professor*")
    }
    $staff = $OUs | ForEach-Object -Process { Get-ADUser -Filter $filter_staff -Property Title -SearchBase $_ }
    $staff | Sort-Object -Property SAMAccountName | Select-Object -Property SAMAccountName,Title,Enabled | Export-CSV -Path c:\userlists\staff.csv

    However, with the ever expanding $Ous and $filter_staff, I'd like to turn one or both of them into csv files. Thus, would make it more scalable.

    I first started experimenting with $filter_staff first by creating csv with the "filter_staff" header with the following script, but obviously it's going nowhere. 

    Any insight?

    # Define searchbase
    $OUs = "OU=Employees,OU=User Accounts,DC=School,DC=edu",
           "OU=Generic Accounts,OU=User Accounts,DC=School,DC=edu"
    
    # To find staff
    $filter_staff = Import-Csv -Path C:\userlists\filters_staff.csv | ForEach-Object -Process {$_.filter_staff}
    $staff = $OUs | ForEach-Object -Process { Get-ADUser -Filter $filter_staff  -Property Title -SearchBase $_ }
    $staff | Sort-Object -Property SAMAccountName | Select-Object -Property SAMAccountName,Title,Enabled | Export-CSV -Path c:\userlists\staff.csv

    Friday, August 2, 2019 2:34 PM

All replies

  • A few comments/questions...

    Is the CSV file you have simply your previous filter as a single line of text?  Maybe show your CSV file?

    I don't see you doing any sort of loop on the $filter_staff variable.  The CSV *could* have more than one filter in it.

    Given the rules of CSV files and the potential complexity of a filter, would a simple text file not be easier?  Get-Content will return an array of lines from the file.

    Is your CSV storing the {curly braces} in the file for the filter?  If so, that's not going to work since the braces denote the begin/end of a script block, not text.  You might need to store the filter minus the braces.


    -Eriq VanBibber, CTO, Priasoft Inc.

    Friday, August 2, 2019 2:58 PM
  • You can very easily do it this way

    $OUs = 'OU=Employees,OU=User Accounts,DC=School,DC=edu',
           'OU=Generic Accounts,OU=User Accounts,DC=School,DC=edu'
    
    $filter_staff = {$_.Title -notmatch 'Instructor|Professor'}
    
    $OUs | 
        ForEach-Object{
            Get-ADUser -Filter $filter_staff -Properties Title -SearchBase $_
        } |
        Where-Object $filter_staff |
        Sort-Object -Property SAMAccountName | 
        Select-Object -Property SAMAccountName, Title, Enabled | 
        Export-CSV -Path c:\userlists\staff
    

    To obtain the filter from a file place the filter matches, one per line, in a file.

    $filter_staff = [Scriptblock]::Create( '$_.Filter -notmatch ' +  ((Get-Content filters.txt) -join '|'))

    "-notmatch" with the "OR" operator "|" is the same as "NOTLIKE" with wildcards fore and aft.

    We need to turn the string into a script block for the "Where" CmdLet.


    \_(ツ)_/


    Friday, August 2, 2019 8:29 PM
  • A few comments/questions...

    Is the CSV file you have simply your previous filter as a single line of text?  Maybe show your CSV file?

    I don't see you doing any sort of loop on the $filter_staff variable.  The CSV *could* have more than one filter in it.

    Given the rules of CSV files and the potential complexity of a filter, would a simple text file not be easier?  Get-Content will return an array of lines from the file.

    Is your CSV storing the {curly braces} in the file for the filter?  If so, that's not going to work since the braces denote the begin/end of a script block, not text.  You might need to store the filter minus the braces.


    -Eriq VanBibber, CTO, Priasoft Inc.

    Here is the content of a sample CSV:

    filter_staff
    *Instructor*
    *Professor*
    *Affiliate Faculty*
    *Dean*
    *Department Chair*

    Get-Content sounds like a good idea, but I am just unable to envision how to put it to use.

    Friday, August 2, 2019 10:40 PM
  • You can very easily do it this way

    $OUs = 'OU=Employees,OU=User Accounts,DC=School,DC=edu',
           'OU=Generic Accounts,OU=User Accounts,DC=School,DC=edu'
    
    $filter_staff = {$_.Title -notmatch 'Instructor|Professor'}
    
    $OUs | 
        ForEach-Object{
            Get-ADUser -Filter $filter_staff -Properties Title -SearchBase $_
        } |
        Where-Object $filter_staff |
        Sort-Object -Property SAMAccountName | 
        Select-Object -Property SAMAccountName, Title, Enabled | 
        Export-CSV -Path c:\userlists\staff

    To obtain the filter from a file place the filter matches, one per line, in a file.

    $filter_staff = [Scriptblock]::Create( '$_.Filter -notmatch ' +  ((Get-Content filters.txt) -join '|'))

    "-notmatch" with the "OR" operator "|" is the same as "NOTLIKE" with wildcards fore and aft.

    We need to turn the string into a script block for the "Where" CmdLet.


    \_(ツ)_/


    With this line, would I be back to square one as I try to avoid static entries?

    $filter_staff = {$_.Title -notmatch 'Instructor|Professor'}

    Also, the line below is way over my head as I only have two weeks of PS under my belt. I need some handholding ;-)

    $filter_staff = [Scriptblock]::Create( '$_.Filter -notmatch ' +  ((Get-Content filters.txt) -join '|'))

    Friday, August 2, 2019 10:45 PM
  • I cannot teach you PS. The last line creates a line in the format of the line you complained about. It is what you asked for.


    \_(ツ)_/

    Friday, August 2, 2019 10:57 PM
  • You can very easily do it this way

    $OUs = 'OU=Employees,OU=User Accounts,DC=School,DC=edu',
           'OU=Generic Accounts,OU=User Accounts,DC=School,DC=edu'
    
    $filter_staff = {$_.Title -notmatch 'Instructor|Professor'}
    
    $OUs | 
        ForEach-Object{
            Get-ADUser -Filter $filter_staff -Properties Title -SearchBase $_
        } |
        Where-Object $filter_staff |
        Sort-Object -Property SAMAccountName | 
        Select-Object -Property SAMAccountName, Title, Enabled | 
        Export-CSV -Path c:\userlists\staff

    To obtain the filter from a file place the filter matches, one per line, in a file.

    $filter_staff = [Scriptblock]::Create( '$_.Filter -notmatch ' +  ((Get-Content filters.txt) -join '|'))

    "-notmatch" with the "OR" operator "|" is the same as "NOTLIKE" with wildcards fore and aft.

    We need to turn the string into a script block for the "Where" CmdLet.


    \_(ツ)_/


    With this line, would I be back to square one as I try to avoid static entries?

    $filter_staff = {$_.Title -notmatch 'Instructor|Professor'}

    Also, the line below is way over my head as I only have two weeks of PS under my belt. I need some handholding ;-)

    $filter_staff = [Scriptblock]::Create( '$_.Filter -notmatch ' +  ((Get-Content filters.txt) -join '|'))

    depending upon how many filters you might end up with, it may be better to have each 'filter' as one line of text in a file, as you were already sort of doing.

    now that you showed the CSV, you could continue to use that.  Get-Content would simply return each line of text in the file as an array of strings.  not too much different than CSV since you only have one column of info.  However, since a regex pattern can have some crazy characters in it, i'd be worried about the CSV getting tripped up on that when you start adding quotes and such.

    as far as "static entries", you could simply create a simple text file with one long text line for the filter, with each filter 'option' separated by a pipe (denotes 'or' in regex).

    So, you could simply place 'instructor|professor' in your text file (minus the quotes).  If you wanted to an another filter option for 'assistant', you'd update the text file with 'instructor|professor|assistant'.

    however, like i said, if this will someday get very long with filtered elements, it may be easier to manipulate as lines in a text file.

    you could even take each line in the file as a sort of mini-regex that you concatenate in script later.

    for example:

    text file (exclusions.txt):

    instructor
    professor
    assistant
    director

    $lines = Get-Content .\exclusions.txt
    $filter = [string]::Join('|', $lines)
    
    ## [String]::Join provides access to the .NET String class which makes it easy to join an array of strings as a single string.



    -Eriq VanBibber, CTO, Priasoft Inc.

    Friday, August 2, 2019 10:58 PM
  • You can very easily do it this way

    $OUs = 'OU=Employees,OU=User Accounts,DC=School,DC=edu',
           'OU=Generic Accounts,OU=User Accounts,DC=School,DC=edu'
    
    $filter_staff = {$_.Title -notmatch 'Instructor|Professor'}
    
    $OUs | 
        ForEach-Object{
            Get-ADUser -Filter $filter_staff -Properties Title -SearchBase $_
        } |
        Where-Object $filter_staff |
        Sort-Object -Property SAMAccountName | 
        Select-Object -Property SAMAccountName, Title, Enabled | 
        Export-CSV -Path c:\userlists\staff

    To obtain the filter from a file place the filter matches, one per line, in a file.

    $filter_staff = [Scriptblock]::Create( '$_.Filter -notmatch ' +  ((Get-Content filters.txt) -join '|'))

    "-notmatch" with the "OR" operator "|" is the same as "NOTLIKE" with wildcards fore and aft.

    We need to turn the string into a script block for the "Where" CmdLet.


    \_(ツ)_/


    With this line, would I be back to square one as I try to avoid static entries?

    $filter_staff = {$_.Title -notmatch 'Instructor|Professor'}

    Also, the line below is way over my head as I only have two weeks of PS under my belt. I need some handholding ;-)

    $filter_staff = [Scriptblock]::Create( '$_.Filter -notmatch ' +  ((Get-Content filters.txt) -join '|'))

    depending upon how many filters you might end up with, it may be better to have each 'filter' as one line of text in a file, as you were already sort of doing.

    now that you showed the CSV, you could continue to use that.  Get-Content would simply return each line of text in the file as an array of strings.  not too much different than CSV since you only have one column of info.  However, since a regex pattern can have some crazy characters in it, i'd be worried about the CSV getting tripped up on that when you start adding quotes and such.

    as far as "static entries", you could simply create a simple text file with one long text line for the filter, with each filter 'option' separated by a pipe (denotes 'or' in regex).

    So, you could simply place 'instructor|professor' in your text file (minus the quotes).  If you wanted to an another filter option for 'assistant', you'd update the text file with 'instructor|professor|assistant'.

    however, like i said, if this will someday get very long with filtered elements, it may be easier to manipulate as lines in a text file.

    you could even take each line in the file as a sort of mini-regex that you concatenate in script later.

    for example:

    text file (exclusions.txt):

    instructor
    professor
    assistant
    director

    $lines = Get-Content .\exclusions.txt
    $filter = [string]::Join('|', $lines)
    
    ## [String]::Join provides access to the .NET String class which makes it easy to join an array of strings as a single string.



    -Eriq VanBibber, CTO, Priasoft Inc.

    There is no RegEx in the file or csv.  Just list the values and the code I posted creates the filter.

    Look at what I posted. If you cannot understand the code then please use your search engine to look up the elements until you understand it.  It is just very basic kindergarten PowerShell and does not require an extensive explanation.  If yo u runt he code a line at a time it will explain itself.


    \_(ツ)_/

    Friday, August 2, 2019 11:02 PM

  • There is no RegEx in the file or csv.  Just list the values and the code I posted creates the filter.

    Look at what I posted. If you cannot understand the code then please use your search engine to look up the elements until you understand it.  It is just very basic kindergarten PowerShell and does not require an extensive explanation.  If yo u runt he code a line at a time it will explain itself.


    \_(ツ)_/

    Hmm.  Technically it is a regex pattern.  The -notmatch implies such.  Sure it may not have complex regex symbols yet, but that doesn't mean its not a regex pattern.  

    If there becomes a desire later to add a more complex filter, it may be nice to know that it IS a regex pattern and being used as one.

    Also, its a bit of an assumption that I don't know the script or code.


    -Eriq VanBibber, CTO, Priasoft Inc.

    Friday, August 2, 2019 11:11 PM
  • I need sometime to digest all of your input and will update the status, hopefully, soon.

    Thanks all again!

    Friday, August 2, 2019 11:16 PM

  • There is no RegEx in the file or csv.  Just list the values and the code I posted creates the filter.

    Look at what I posted. If you cannot understand the code then please use your search engine to look up the elements until you understand it.  It is just very basic kindergarten PowerShell and does not require an extensive explanation.  If yo u runt he code a line at a time it will explain itself.


    \_(ツ)_/

    Hmm.  Technically it is a regex pattern.  The -notmatch implies such.  Sure it may not have complex regex symbols yet, but that doesn't mean its not a regex pattern.  

    If there becomes a desire later to add a more complex filter, it may be nice to know that it IS a regex pattern and being used as one.

    Also, its a bit of an assumption that I don't know the script or code.


    -Eriq VanBibber, CTO, Priasoft Inc.

    There is no RegEx stored in any file.  There are the strings that get concatenated to create a RegEx pattern by using "-join '|'"  which is in the code.  The rest is obvious but don't take my word.  Look up the components in the help or with your search engine.  This si a very fundamental design pattern or technique in PowerShell and all other languages.

    There is no need for a CSV with one column. If you want a CSV then this would be the solution:

    $filter = (Import-Csv file.Csv|select -expand <your header>) -join '|'
    $filter_staff = [scriptblock]::Create($filter)


    \_(ツ)_/



    Friday, August 2, 2019 11:28 PM
  • Hi,

    Was your issue resolved?

    If you resolved it using our solution, please "mark it as answer" to help other community members find the helpful reply quickly.

    If you resolve it using your own solution, please share your experience and solution here. It will be very beneficial for other community members who have similar questions.

    If no, please reply and tell us the current situation in order to provide further help.

    Best Regards,

    Lee


    Just do it.

    Friday, September 6, 2019 12:55 PM