Creating an optimized lookup table

Hi All. I have created a script that scan old files in our servers, and from the file ownership, queries our AD for the owner full name and email address. As this could mean thousands of files to go thru, I wanted to have some kind of lookup table during the script run, so that if it has already queried the AD for a particular user, I will save that user’s full name and email in a lookup table so that during the next iteration of my for loop, rather than going to the AD immediately to check the user details, it will check first if the user record already exists on the lookup table. If it does, it will use the data already available, and if not, it will then query the AD, then add the user data on the lookup table.

With my still limited knowledge on Powershell, I tried to utilize objects to do this, and came up with the script below, but I think this can still be further improved (although currently, this is already working for my requirement).

I would just like to ask if someone else can help and advise how this script can be further improved/shortened, e.g. use dictionary vs objects, make this as a function, etc. I am using the function Get-UserADInfo instead of the PS AD module as we don’t have this installed on our servers:

# Create an object containing user full name and emails based on the scanned files
# This will be used similar to a lookup table, instead of doing an AD query every time

Write-Output "Identifying file owner full name and email and writing to the Excel file."
foreach ($fileobj in $OutputFiles) {

    $username = ($fileobj.FileOwner).ToUpper()

	#Write-Output "Processing $username"
    # Check if this is the first time processing a user
    If (-not $ADObject) {
		try{
			#Write-Output "Adding first user on the lookup object"
			$userobj = Get-UserADInfo($username)
			$FullName = $userobj.DisplayName
			$Email = $userobj.Email
			$ADObject = [PSCustomObject]@{
				UserName = $username
				FullName = $FullName
				Email = $Email
			}
		} 
		catch {
			# User is not existing in the Active Directory, use username as FullName and Email
			$FullName = "Non-existent user"
			$Email = "Non-existent user"
			$ADObject = [PSCustomObject]@{
				UserName = $username
				FullName = $FullName
				Email = $Email
			}
		}
        # Add the user data to our user lookup object
        $AddUserObj += [PSCustomObject]@{UserName = $username; FullName = $FullName; Email = $Email}
    } else {
        # Lookup object already contains user data
        # Check if the user email data has already been added to the lookup object
        $searchuser = $AddUserObj | Where-Object {($_.UserName).ToUpper() -like $username} 
        if ($searchuser) { 
            # User email is already there, just use the existing user data in the lookup object
            $ADObject = [PSCustomObject]@{
                UserName = $username
                FullName = $searchuser.FullName
                Email = $searchuser.Email
            }
        } else {
			try{
				# User data is still not there, add it to the lookup object
				$userobj = Get-UserADInfo($username)
				if ( $null -ne $userobj -and
					$userobj -ne 0 -and
					$userobj -ne '' -and
					$usreobj -ne $false ) {
						$FullName = $userobj.DisplayName
						$Email = $userobj.Email
						$ADObject = [PSCustomObject]@{
							UserName = $username
							FullName = $FullName
							Email = $Email
						}
					}
			}
			catch {
				# User is non-existent in the Active Directory
				$FullName = "Non-existent user"
				$Email = "Non-existent user"
				$ADObject = [PSCustomObject]@{
					UserName = $username
					FullName = $FullName
					Email = $Email
				}
			}
            $AddUserObj += [PSCustomObject]@{UserName = $username; FullName = $FullName; Email = $Email}
        }
    }

Without digging into your script right now … why not getting ALL users from your AD at the beginning of your script and using this list instead of querying single user accounts inside your loop? Probably that’s way more efficient and fast. :wink:

1 Like

Thanks for the recommendation Olaf. Did thought about this before, but with hundred thousand of user AD entries in our company, and my script only being applied on a small subset of users in our region, I discarded this idea initially.

You could have mentioned that. There are not a lot of companies with such an amount of users. But especially in a company like this there are usually ways to divide structures like this in managable chunks. If you can limit the amount of potentially needed users to a single subdomain or OU or group that would make your current task somewaht easier.

The other option you have would be to split the big task in two smaller ones. First you collect the owners of all files. Then you remove all duplicates from this list. Then you query the AD for this users.

1 Like