CSV to CSV

This topic contains 9 replies, has 2 voices, and was last updated by Profile photo of Dave Wyatt Dave Wyatt 2 years, 1 month ago.

  • Author
    Posts
  • #19857
    Profile photo of Paul Goffar
    Paul Goffar
    Participant

    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.....:(

    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

  • #19867
    Profile photo of Dave Wyatt
    Dave Wyatt
    Moderator

    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.

  • #19868
    Profile photo of Paul Goffar
    Paul Goffar
    Participant

    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.

  • #19869
    Profile photo of Dave Wyatt
    Dave Wyatt
    Moderator

    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"

  • #19870
    Profile photo of Paul Goffar
    Paul Goffar
    Participant

    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.

  • #19871
    Profile photo of Dave Wyatt
    Dave Wyatt
    Moderator

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

  • #19872
    Profile photo of Paul Goffar
    Paul Goffar
    Participant

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

    Thanks again

  • #19873
    Profile photo of Dave Wyatt
    Dave Wyatt
    Moderator

    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.

  • #19874
    Profile photo of Paul Goffar
    Paul Goffar
    Participant

    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

  • #19875
    Profile photo of Dave Wyatt
    Dave Wyatt
    Moderator

    Cool, glad it worked! 🙂

You must be logged in to reply to this topic.