CSV to CSV

PowerShell Help
1

Hello all.

So, here is my mission.

  1. Create a script for all Windows 7/Windows 8 workstations to audit connected network drives, output to CSV and save out to a network share…Done
  2. Deploy this script to a few thousand workstations…Done
  3. Take all csv files and combine into one master sheet…Done
  4. Add FirstName,LastName,Email to master csv based on their network ID that was found during the audit…:frowning:

This is where my problem comes in. I have a working solution to add the data but it will take days of hitting AD for each user (and 90% of these users are shown multiple times if they have multiple drives mapped.)

EXAMPLE: MASTER.CSV

Computer UserID Date DeviceID ProviderName DriveType
Computer1234 djones 10/8/2014 12:00 H: \someserver\path\junk Network Drive
Computer1234 djones 10/8/2014 12:00 F: \someserver\path\otherjunk Network Drive
Computer1235 jsnover 10/8/2014 12:00 G: \server\unc Network Drive
Computer1288 bgates 10/8/2014 12:00 H: \server12\unc Network Drive

EXAMPLE: Desired result
Computer UserID FirstName LastName Email CostCenter Date DeviceID ProviderName DriveType
Computer1234 djones Don Jones djones@abc.com 123456 10/8/2014 12:00 H: \someserver\path\junk Network Drive
Computer1234 djones Don Jones djones@abc.com 123456 10/8/2014 12:00 F: \someserver\path\otherjunk Network Drive
Computer1235 jsnover Jeff Snover jsnover@abc.com 234567 10/8/2014 12:00 G: \server\unc Network Drive
Computer1288 bgates Bill Gates bgates@abc.com 918456 10/8/2014 12:00 H: \server12\unc Network Drive

If I use something like

$csv= "c:\somefolder\master.csv"

Import-CSV $csv | select 'Computer','UserID',`
@{ n = 'FirstName'; e = { ((Get-Qaduser $_.UserID).FirstName) } },`
@{ n = 'LastName'; e = { ((Get-Qaduser $_.UserID).LastName) } },`
@{ n = 'Email'; e = { ((Get-Qaduser $_.UserID).Email) } },`
@{ n = 'Cost Center'; e = { ((Get-Qaduser $_.UserID).physicalDeliveryOfficeName) } },`
'Date',`
'DeviceID',`
'ProviderName',`
'DriveType' | Export-CSV -Path "C:\somefolder\master_final.csv" -NoTypeInformation

I get results correctly but my kids will have graduated college by the time it’s finished. I’ve thought of a couple ways to do this with something like -unique and pulling that into an array but there has to be a better way.

Thanks everyone

PG

2

You can make a single call to Get-Qaduser which stores the information you need in memory, then use that information to perform the CSV transformations. Depending on how many users are in your domain, though, you may run into memory problems here. (Memory usage and execution time are often competing with each other in these situations.)

You can try something like this:

$csv= 'c:\somefolder\master.csv'

$userTable = @{}

filter UpdateUserTable
{
    $userTable[$_.SamAccountName] = [pscustomobject] @{
        FirstName  = $_.FirstName
        LastName   = $_.LastName
        Email      = $_.Email
        CostCenter = $_.PhysicalDeliveryOfficeName
    }
}

Get-QADUser | UpdateUserTable

filter UpdateObject
{
    $csvEntry = $_
    $userEntry = $userTable[$csvEntry.UserID]

    [pscustomobject] @{
        Computer      = $csvEntry.Computer
        UserID        = $csvEntry.UserID
        FirstName     = $userEntry.FirstName
        LastName      = $userEntry.LastName
        Email         = $userEntry.Email
        'Cost Center' = $userEntry.CostCenter
        Date          = $csvEntry.Date
        DeviceID      = $csvEntry.DeviceID
        ProviderName  = $csvEntry.ProviderName
        DriveType     = $csvEntry.DriveType
    }
}

Import-Csv $csv |
UpdateObject |
Export-Csv -Path 'C:\somefolder\master_final.csv' -NoTypeInformation

The filters will perform faster than ForEach-Object, which will hopefully squeeze a bit more performance out of your script (while still keeping memory usage to some reasonable level).

One thing that could speed this up even more, potentially, is to grab the unique usernames out of your CSV file first, and then filter the call to Get-QADUser so that only those usernames are retrieved. As written, this code would store the FirstName, LastName, Email and CostCenter data for every user in the domain, even if they’re not found in your CSV file.

Note: The code I wrote requires PowerShell 3.0 or later, for the [pscustomobject]@{} syntax. It can be tweaked to work with PowerShell 2.0 by using the New-Object cmdlet, but won’t be as fast.

On a side note, if your data set is getting large, you’re probably well into territory where you’d be better off using some real database software instead of fiddling with CSVs directly. You could keep these two tables separate (mappedDrives and users, or something like that) and perform SQL joins on them whenever needed.

3

Dave,

Thank you for the reply. As I’m standing right now, this is a one off with about 13000 lines. I see what you mean about the bulk data import. I’m only going to return 1000 rows anyway.

4

I’m not sure how that’s possible. I’ve run the code myself and can see the results.

I don’t have the Quest AD cmdlets on my system, but I used Pester to mock the Get-QADUser command and simulate the output:

Describe 'CSV to CSV' {
    # This function exists because I don't have the Quest AD cmdlets installed; commands must exist
    # before they can be mocked.
    function Get-QADUser { }

    Mock Get-QADUser {
        [pscustomobject] @{
            SamAccountName             = 'djones'
            FirstName                  = 'Don'
            LastName                   = 'Jones'
            Email                      = 'djones@contoso.com'
            PhysicalDeliveryOfficeName = '1234'
        }

        [pscustomobject] @{
            SamAccountName             = 'jsnover'
            FirstName                  = 'Jeffrey'
            LastName                   = 'Snover'
            Email                      = 'jsnover@contoso.com'
            PhysicalDeliveryOfficeName = '1235'
        }

        [pscustomobject] @{
            SamAccountName             = 'bgates'
            FirstName                  = 'Bill'
            LastName                   = 'Gates'
            Email                      = 'bgates@contoso.com'
            PhysicalDeliveryOfficeName = '1236'
        }
    }

    $csvContents =
@'
Computer,UserID,Date,DeviceID,ProviderName,DriveType
Computer1234,djones,10/8/2014 12:00,H:,\\someserver\path\junk,Network Drive
Computer1234,djones,10/8/2014 12:00,F:,\\someserver\path\otherjunk,Network Drive
Computer1235,jsnover,10/8/2014 12:00,G:,\\server\unc,Network Drive
Computer1288,bgates,10/8/2014 12:00,H:,\\server12\unc,Network Drive
'@

    Setup -File Master.csv -Content $csvContents

    $csv= 'TestDrive:\master.csv'
 
    $userTable = @{}
 
    filter UpdateUserTable
    {
        $userTable[$_.SamAccountName] = [pscustomobject] @{
            FirstName  = $_.FirstName
            LastName   = $_.LastName
            Email      = $_.Email
            CostCenter = $_.PhysicalDeliveryOfficeName
        }
    }
 
    Get-QADUser | UpdateUserTable
 
    filter UpdateObject
    {
        $csvEntry = $_
        $userEntry = $userTable[$csvEntry.UserID]
 
        [pscustomobject] @{
            Computer      = $csvEntry.Computer
            UserID        = $csvEntry.UserID
            FirstName     = $userEntry.FirstName
            LastName      = $userEntry.LastName
            Email         = $userEntry.Email
            'Cost Center' = $userEntry.CostCenter
            Date          = $csvEntry.Date
            DeviceID      = $csvEntry.DeviceID
            ProviderName  = $csvEntry.ProviderName
            DriveType     = $csvEntry.DriveType
        }
    }
 
    Import-Csv $csv |
    UpdateObject |
    Export-Csv -Path 'TestDrive:\master_final.csv' -NoTypeInformation

    Get-Content TestDrive:\master_final.csv | Write-Verbose -Verbose
}

I got this output, which looks correct to me:

VERBOSE: “Computer”,“UserID”,“FirstName”,“LastName”,“Email”,“Cost Center”,“Date”,“DeviceID”,“ProviderName”,“DriveType”
VERBOSE: “Computer1234",“djones”,“Don”,“Jones”,"djones@contoso.com”,“1234”,“10/8/2014 12:00”,“H:”,“\someserver\path\junk”,“Network Drive”
VERBOSE: “Computer1234",“djones”,“Don”,“Jones”,"djones@contoso.com”,“1234”,“10/8/2014 12:00”,“F:”,“\someserver\path\otherjunk”,“Network Drive”
VERBOSE: “Computer1235",“jsnover”,“Jeffrey”,“Snover”,"jsnover@contoso.com”,“1235”,“10/8/2014 12:00”,“G:”,“\server\unc”,“Network Drive”
VERBOSE: “Computer1288",“bgates”,“Bill”,“Gates”,"bgates@contoso.com”,“1236”,“10/8/2014 12:00”,“H:”,“\server12\unc”,“Network Drive”

5

Dave,

I’m not sure where I went wrong. If I run the updatetable command by itself than I actually start doing work (recv’d a warning about 1000 row limit.)…At the end of the day, I was still hitting every subdomain and I would return an incredible amount of useless data.

6

You’re only talking to one domain here, not child domains. Those would have to be queried separately.

7

I really appreciate the help. I’m going to see what I can do about pulling from sql like you suggested.

Thanks again

8

You can try this modification to only pull back information about the users that you need:

$neededUsers = @(Import-Csv $csv | Select-Object -ExpandProperty UserID -Unique)

Get-QADUser -SamAccountName $neededUsers -SizeLimit $neededUsers.Count | UpdateUserTable

This will involve two iterations over the CSV file instead of one, but that’s local and will probably execute much faster than the network query.

9

Dave,

I should’ve known lol…the unique csv is what I tried the first time but it was still taking a very long time. I added your mods to the script and returned all lines of data in less than 3 minutes! Awesome!

Thank you so much for the help!

Paul

10

Cool, glad it worked! :slight_smile: