Linking values from imported CSV

PowerShell Help
1

Hi all,

I’ll start by saying that I’m still learning my way through PS. I’m trying to figure out a way to link data that I import from a csv to an existing variable that contains a field with matching data. For instances:

$value1 = get-content C:\List\List1.txt
List1 contains a specific list of computers I want to gather information on:
PC1,PC2,PC3

$value2 = import-csv C:\List\List2.csv
List2 contains a list of ALL computers in the company and last user logged in
PCName: PC1,PC2,PC3,PC4,PC5,…,pc4500
User: SmithJ,DoeJ,RogersF

How can I connect the contents of $value2 that match $value1 and output the users for those particular computers? I’ve provided the working script which is also gathering some AD and Vsphere data as well. Somehow I need to work in last user as well.

Thanks in advance!
Mike

$getcsv = Import-Csv “C:\Users\carnesm\Google Drive\Powershell\Lists\computer_status_export.csv”
$list= Get-Content “C:\Users\carnesm\Google Drive\Powershell\Lists\vmlist.txt”

$GetVMObject = foreach ($line in $list)
{
$vmo = get-vm $line
if ($vmo -eq $null)
{
$qad = Get-QADComputer $line -IncludeAllProperties -service OMA1DC01.am.tsacorp.com
if ($qad -eq $null)
{
New-object PSObject -Property @{
‘Name’ = $line;
‘vmhost’ = “Not in VSphere”;
‘PowerState’ = “Not in VSphere”
‘LastLogon’ = “Not in AD”
‘OS’ = " "
}
}

            else
            {
                New-object PSObject -Property @{
                'Name' = $line;
                'vmhost'  = "Not in VSphere";
                'LastLogon' = $qad.LastLogonTimeStamp
                'PowerState' = "Not in VSphere"
	    'OS' = $qad.OSName
				}
            }
            }
    
        else 
            {
            $qad = Get-QADComputer $line -IncludeAllProperties -service OMA1DC01.am.tsacorp.com
            if ($qad -eq $null)
                {
                    New-object PSObject -Property @{
                    'Name' = $vmo.name;
                    'vmhost'  = $vmo.vmhost;
                    'PowerState' = $vmo.PowerState;
                    'LastLogon' = "Not in AD"
		'OS' = " " 
					}  

                 }
            else
                {
                    New-object PSObject -Property @{
                    'Name' = $vmo.name;
                    'vmhost'  = $vmo.vmhost;
                    'PowerState' = $vmo.PowerState;
                    'LastLogon' = $qad.LastLogonTimeStamp
		'OS' = $qad.OSName
                    }
                        
                }

        }

}

$GetVMObject | select name,vmhost,LastLogon,PowerState,OS | Export-Csv getvmobject.csv

2

You could try to let Sort-Object -Unique do the work for you. However without a sample of the CSV it’s hard for me to see what you’re needing. I understand if you can not post the actual user IDs that’s good IT security but if you could sample the CSV and post a cleansed version that would help me to be able to find a solution.

-VERN

3

Thanks Vern. Here’s a few sample linesof the CSV with for computers with my userID’s…it’s basically an export from a Symantec report that shows latest user logged in.

Computer Name,Computer Domain Name,Current login domain,Current User,Operating System
COV7P01-019,am.tsacorp.com,AM.TSACORP.COM,carnesm,Windows 7 Ultimate Edition
OMA7P03-009,am.tsacorp.com,AM.TSACORP.COM,carnesm,Windows 7 Ultimate Edition
NRC7P06-009,am.tsacorp.com,AM.TSACORP.COM,carnesma,Windows XP Professional
NRC7P05-001,am.tsacorp.com,AM.TSACORP.COM,carnesm,Windows 7 Ultimate Edition
COV7P07-TEST2,am.tsacorp.com,AM.TSACORP.COM,carnesma,Windows 7 Ultimate Edition

The Text file that I’m using for get-content would just have the names of computers that I’m looking for the information on (vsphere info, last login, and username)

Happy to provide any add’l information that is needed.
Mike

4

The PowerShell team made a blog post on a similar topic. It was regarding joining CSV files, rather than one CSV and one TXT file, but you can accomplish the same thing by changing this line:

$value1 = get-content C:\List\List1.txt

# to this:

$value1 = Import-Csv -Path C:\List\List1.txt -Header 'ComputerName'

The blog post is here: http://blogs.msdn.com/b/powershell/archive/2012/07/13/join-object.aspx

5

Oh, that’s weird, because I just wrote this in the last two days and posted the article this morning: Merge CSV files or PSObjects in PowerShell - Svendsen Tech

I swear I’m psychic sometimes.

I hadn’t seen this before doing that. It works with custom PowerShell objects (like the ones created from Import-Csv), so I might as well have named it Merge-Object, or, indeed, Join-Object, I guess. I didn’t read the article on MSDN thoroughly yet, but it looks like similar functionality. I bet mine sucks in comparison? :frowning:

6

No worries Joakim, I’m guilty of reinventing the wheel almost every time I sit down to write a script. I get a fair amount of enjoyment and satisfaction from producing my own code, anyway. Also, sometimes I feel like it takes so much time to search, read, understand, and potentially debug and fix the code that I find online, I’d have had it done faster if I just wrote it from scratch in the first place.

7

I have to agree. My threshold for using someone else’s code is way too high, I think, though; I probably need to work on changing that. For people like you and me, who are probably capable of writing most of the code we need ourselves, it’s often more “comfortable” writing it yourself. I’m not entirely sure, but I tend to read 100-line scripts carefully before considering using them sometimes, so… I know it must be similar for a lot of people.

For those who aren’t capable, finding helpful, pre-written functions/cmdlets is probably very nice. Writing this Merge-Csv cmdlet was painful enough for me not to want to do it over anytime soon, so I hope someone finds some use in it. I’m likely to use it myself later.

I skimmed through parts of the code of the PowerShell team’s Join-Object cmdlet and they have some filtering in place in the cmdlet ([ValidateSet(“AllInLeft”, “OnlyIfInBoth”, “AllInBoth”, “AllInRight”)]). This seems to indicate they only support two files, and I just looked to see a “-Left” and “-Right” argument which further indicate this. I considered imposing that limitation, but aimed a bit higher. :slight_smile: I made the number of CSV files arbitrary (you can have three or more) and made such filtering optional afterwards by just using Select-Object and property titles, as demonstrated briefly in the article. A bit less dynamic in that respect. Shrug. Design decisions are sometimes the hardest part.