Linking values from imported CSV

This topic contains 6 replies, has 4 voices, and was last updated by Profile photo of Joakim Svendsen Joakim Svendsen 3 years, 3 months ago.

  • Author
    Posts
  • #12609
    Profile photo of Mike Carnes
    Mike Carnes
    Participant

    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

  • #12621
    Profile photo of Vern Anderson
    Vern Anderson
    Participant

    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

  • #12622
    Profile photo of Mike Carnes
    Mike Carnes
    Participant

    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

  • #12633
    Profile photo of Dave Wyatt
    Dave Wyatt
    Moderator

    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

  • #12756
    Profile photo of Joakim Svendsen
    Joakim Svendsen
    Participant

    Oh, that's weird, because I just wrote this in the last two days and posted the article this morning: http://www.powershelladmin.com/wiki/An_Advanced_Cmdlet_to_Merge_Csv_Files_in_PowerShell

    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? 🙁

  • #12757
    Profile photo of Dave Wyatt
    Dave Wyatt
    Moderator

    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.

    • #12759
      Profile photo of Joakim Svendsen
      Joakim Svendsen
      Participant

      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. 🙂 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.

You must be logged in to reply to this topic.