ForEach-Object Where

This topic contains 8 replies, has 3 voices, and was last updated by Profile photo of Max Kozlov Max Kozlov 1 month ago.

  • Author
    Posts
  • #66997
    Profile photo of Von
    Von
    Participant

    I'm working on a script that will:
    1. Query a SQL table that shows what people have access to, and who they report to (total result 160,000 rows).
    2. Query a SQL table that shows all the distinct manager names (total result 400 rows).
    3. For each manager, create a separate XLSX file.
    4. Add the header row to each XLSX file.
    5. Place all results of query #1 into the XLSX file, but only where the manager name in Query 1 matches the first manager name.
    6. Iterate through the list of managers and repeat step #5 for manager #2, manager #3, etc.
    7. Save each file as "Access Review – [manager name].xlsx"

    The following script does everything, except it places the total result of 160,000 rows into each manager's XLSX file, rather than the subset of just that manager's data. The script seems so close, but I need step #5 to limit the result of each iteration based on a match with the manager's name. I hope this explanation makes sense. Any guidance is much appreciated.

    #server and db variables 
    $serverName = "ServerName"; 
    $databaseName = "Database"; 
    #the save location for the new Excel file
    $filepath = "C:\users\desktop\reviews\Access Review - " 
           
    #create two Datasets to store the DataTables for Access and Manager  
    $accessDataSet = new-object "System.Data.DataSet" "Access"
    $managerDataSet = new-object "System.Data.DataSet" "Manager"
        
    #create a Connection to the SQL Server database
    $cn = new-object System.Data.SqlClient.SqlConnection "server=$serverName;database=$databaseName;Integrated Security=sspi"
    $queryAccess = "SELECT * FROM AccessReviewTable ORDER BY Manager, Associate, LdapID, [Order], [Application], Permission_Code;"
    $queryManager = "SELECT [Manager Name] FROM ManagerTable ORDER BY [Manager Name];"
    #Create a SQL Data Adapter to place the access data into the first DataSet
    $dataAdapter1 = new-object "System.Data.SqlClient.SqlDataAdapter" ($queryAccess, $cn) 
        $dataAdapter1.Fill($accessDataSet) | Out-Null
    #Create a SQL Data Adapter to place the manager data into the second DataSet
    $dataAdapter2 = new-object "System.Data.SqlClient.SqlDataAdapter" ($queryManager, $cn) 
        $dataAdapter2.Fill($managerDataSet) | Out-Null
    #close the connection 
    $cn.Close()
          
    $dataTable1 = new-object "System.Data.DataTable" "Access"
    $dataTable1 = $accessDataSet.Tables[0]
    $dataTable2 = new-object "System.Data.DataTable" "Manager"
    $dataTable2 = $managerDataSet.Tables[0]
        
        $dataTable2 | ForEach-Object{
            #create excel object
            $excel = New-Object -ComObject Excel.Application
            $excel.visible = $True
            $workbook = $excel.Workbooks.add()
            #create and name worksheet 
            $sheet1 = $workbook.worksheets.Item(1)
            $sheet1.name = "Access Review"
            #Add the Row Header
            $sheet1.Cells.Item(1,1) = 'LdapID'
            $sheet1.Cells.Item(1,2) = 'Associate'
            $sheet1.Cells.Item(1,3) = 'Platform'
            $sheet1.Cells.Item(1,4) = 'Application'
            $sheet1.Cells.Item(1,5) = 'Permission_Code'
            $sheet1.Cells.Item(1,6) = 'Permission_Desc'
            $sheet1.Cells.Item(1,7) = 'Read'
            $sheet1.Cells.Item(1,8) = 'Add'
            $sheet1.Cells.Item(1,9) = 'Update'
            $sheet1.Cells.Item(1,10) = 'Delete'
            $sheet1.Cells.Item(1,11) = 'Manager'
            $sheet1.Cells.Item(1,12) = 'Authorized Y/N'
                
                $x = 2
                $dataTable1 | ForEach-Object (Where {$_."Manager Name" -eq $_.Manager}){
                    $sheet1.cells.item($x, 1) =  $_.LdapID
                    $sheet1.cells.item($x, 2) =  $_.Associate
                    $sheet1.cells.item($x, 3) =  $_.Platform
                    $sheet1.cells.item($x, 4) =  $_.Application
                    $sheet1.cells.item($x, 5) =  $_.Permission_Code
                    $sheet1.cells.item($x, 6) =  $_.Permission_Desc
                    $sheet1.cells.item($x, 7) =  $_.Read
                    $sheet1.cells.item($x, 8) =  $_.Add
                    $sheet1.cells.item($x, 9) =  $_.Update
                    $sheet1.cells.item($x, 10) =  $_.Delete
                    $sheet1.cells.item($x, 11) =  $_.Manager
                    $sheet1.cells.item($x, 12) =  $_.Authorized
                    $range1 = $sheet1.UsedRange
                    $range1.EntireColumn.AutoFit()
                    $x++
                }
            $excel.ActiveWorkbook.SaveAs("$filepath" + $_."Manager Name" + ".xlsx")
            $excel.quit()
        }
    
  • #67000
    Profile photo of Sam Boutros
    Sam Boutros
    Participant

    uhm.. line 52 should use -eq operator not = sign..

  • #67006
    Profile photo of Von
    Von
    Participant

    Thanks for catching that, Sam. Yes, I updated = to the -eq operator, but I'm getting the same results. Looks like the Where filter isn't doing anything. I even tried modifying it to (Where {$_.Manager -eq 'Doe, Jane'}) to try and filter the first query results to manager name "Doe, Jane" but the script is still adding all 160k rows of data into each XLSX.

  • #67021
    Profile photo of Max Kozlov
    Max Kozlov
    Participant

    seens you try to compare $_."Manager Name" from one datatable with $_.Manager from another datatable. use temp variable for first $_

    and why you do not do correlation by sql query ?

    something like


    "select .... join .... sort by manager, date"
    ...
    foreach table.line
    if (currentmanager != previousmanager) createnewmanagerxls()
    savelinetoxls()

    • #67078
      Profile photo of Von
      Von
      Participant

      I'll try to explain differently. I have a large set of data from SQL, that looks something like this:

      UserID Access Manager
      ajones Admin Joe Smith
      bjones Admin Joe Smith
      cjones Admin Joe Smith
      djones Admin Joe Smith
      asdfg Standard Jane Doe
      asasf Standard Jane Doe
      wdxd Standard Jane Doe
      zcsqw Standard Jane Doe

      I need to create separate XLSX reports for every manager. In the above example, Joe Smith would have his own report showing only the first 4 rows. Jane Doe would have her own report, showing only the last 4 rows.

      What I do understand, is that line 69 works to iterate through the list of managers, creating a separate XLSX file with each manager's name after the filepath, like "Access Review – Joe Smith.xlsx", "Access Review – Jane Doe.xlsx"

      What I don't understand is how to use that same manager's name to filter the results on line 52, to only write the data for manager Joe Smith on that XLSX file, next time through only write the data for manager Jane Doe, etc.

      Max, I'm not sure why I would use a temp variable, since $_."Manager Name" is currently working to create separate filenames for each manager (line 69). I don't need SQL to correlate the data, I can join the data in SQL all day long. I'm trying to get PowerShell to separate the 160k total rows of data into separate blocks of data for each manager.

  • #67093
    Profile photo of Von
    Von
    Participant

    Solved!

    I figured out that I had to set the position of $datatable2."Manager Name"[$y] using a number I could increment through 0,1,2,3 etc. I also moved the Where method in front of the ForEach method, and it worked.

    Here's the corrected code if anyone is interested:

    #server and db variables 
    $serverName = "ServerName"; 
    $databaseName = "Database"; 
    #the save location for the new Excel file
    $filepath = "C:\users\desktop\reviews\Access Review - " 
           
    #create two Datasets to store the DataTables for Access and Manager  
    $accessDataSet = new-object "System.Data.DataSet" "Access"
    $managerDataSet = new-object "System.Data.DataSet" "Manager"
        
    #create a Connection to the SQL Server database
    $cn = new-object System.Data.SqlClient.SqlConnection "server=$serverName;database=$databaseName;Integrated Security=sspi"
    $queryAccess = "SELECT * FROM AccessReviewTable ORDER BY Manager, Associate, LdapID, [Order], [Application], Permission_Code;"
    $queryManager = "SELECT [Manager Name] FROM ManagerTable ORDER BY [Manager Name];"
    #Create a SQL Data Adapter to place the access data into the first DataSet
    $dataAdapter1 = new-object "System.Data.SqlClient.SqlDataAdapter" ($queryAccess, $cn) 
        $dataAdapter1.Fill($accessDataSet) | Out-Null
    #Create a SQL Data Adapter to place the manager data into the second DataSet
    $dataAdapter2 = new-object "System.Data.SqlClient.SqlDataAdapter" ($queryManager, $cn) 
        $dataAdapter2.Fill($managerDataSet) | Out-Null
    #close the connection 
    $cn.Close()
          
    $dataTable1 = new-object "System.Data.DataTable" "Access"
    $dataTable1 = $accessDataSet.Tables[0]
    $dataTable2 = new-object "System.Data.DataTable" "Manager"
    $dataTable2 = $managerDataSet.Tables[0]
    $y=0    
        $dataTable2 | ForEach-Object{
            #create excel object
            $excel = New-Object -ComObject Excel.Application
            $excel.visible = $True
            $workbook = $excel.Workbooks.add()
            #create and name worksheet 
            $sheet1 = $workbook.worksheets.Item(1)
            $sheet1.name = "Access Review"
            #Add the Row Header
            $sheet1.Cells.Item(1,1) = 'LdapID'
            $sheet1.Cells.Item(1,2) = 'Associate'
            $sheet1.Cells.Item(1,3) = 'Platform'
            $sheet1.Cells.Item(1,4) = 'Application'
            $sheet1.Cells.Item(1,5) = 'Permission_Code'
            $sheet1.Cells.Item(1,6) = 'Permission_Desc'
            $sheet1.Cells.Item(1,7) = 'Read'
            $sheet1.Cells.Item(1,8) = 'Add'
            $sheet1.Cells.Item(1,9) = 'Update'
            $sheet1.Cells.Item(1,10) = 'Delete'
            $sheet1.Cells.Item(1,11) = 'Manager'
            $sheet1.Cells.Item(1,12) = 'Authorized Y/N'
                
                $x = 2
                $dataTable1.Where{$_.Manager -eq $dataTable2."Manager Name"[$y]} | ForEach-Object {
                    $sheet1.cells.item($x, 1) =  $_.LdapID
                    $sheet1.cells.item($x, 2) =  $_.Associate
                    $sheet1.cells.item($x, 3) =  $_.Platform
                    $sheet1.cells.item($x, 4) =  $_.Application
                    $sheet1.cells.item($x, 5) =  $_.Permission_Code
                    $sheet1.cells.item($x, 6) =  $_.Permission_Desc
                    $sheet1.cells.item($x, 7) =  $_.Read
                    $sheet1.cells.item($x, 8) =  $_.Add
                    $sheet1.cells.item($x, 9) =  $_.Update
                    $sheet1.cells.item($x, 10) =  $_.Delete
                    $sheet1.cells.item($x, 11) =  $_.Manager
                    $sheet1.cells.item($x, 12) =  $_.Authorized
                    $range1 = $sheet1.UsedRange
                    $range1.EntireColumn.AutoFit()
                    $x++
                }
                $y++
            $excel.ActiveWorkbook.SaveAs("$filepath" + $_."Manager Name" + ".xlsx")
            $excel.quit()
        }
    

    Thank you, Max and Sam for thinking through this with me.

  • #67102
    Profile photo of Max Kozlov
    Max Kozlov
    Participant

    so you did almost as I say: you use temp variable for "other $_" but in different form
    but
    instead of $dataTable1.Where{$_.Manager -eq $dataTable2."Manager Name"[$y]} | ForEach-Object {
    you can write

    $dataTable2 | ForEach-Object{
    $currentmanager = $_ # save current iterator from outer cycle to temp variable
    #create excel object
    # ...
    # ...
    # ...
    $dataTable1.Where{$_.Manager -eq $currentmanager."Manager Name"} | ForEach-Object {
    # ...
    

    and achieve the same results with lesser overhead and faster speed
    for 10 managers you cannot see the delay, but for 1000...

    what you do now: on every $datatable2's item you create temporary array with "Manager name" in it and get $y'th value
    which already equal current item in outer cycle. but you can't access it because you have inner cycle (foreach in first code version and .where in second)

  • #67201
    Profile photo of Von
    Von
    Participant

    Thanks for the suggestion, Max! I agree that only 10 managers was no big deal, but switching to all 400 managers had some serious performance problems. It would have taken 20 hours to run 400 reports. I decided to make some changes and begin with creating the Manager's datatable, then within the first ForEach, I go back to SQL and run the query for each manager name instead of using PowerShell's Where method. The results of each query goes into the second ForEach loop. This took the 20 hour script down to 2 hours. Removing the $excel.visible = $True took it down to 1 hour & 20 minutes. Then I added your suggestion by creating that temp variable and reduced the total script run time down to 1 hour for all 400 reports. The rest of the performance problem is likely in SQL where each query can take 8-10 seconds. Once I fix the SQL query down to 1 second or less, I should be able to run all reports in 5-10 minutes, which would be just fine for my purpose.

  • #67315
    Profile photo of Max Kozlov
    Max Kozlov
    Participant

    I think if you switch from launching excel to export-csv it can be even better (if you can accept report in csv form)

You must be logged in to reply to this topic.