none
Using Values from imported .CSV file in a custom field RRS feed

  • Question

  • I am trying to create a powershell script which will export a list of computers and their custom properties I've chosen when I run it. It's working ok except for the fact that one of the properties I need is an asset tag pulled from a .csv file, given the current computer's serial number matches a serial number from the .csv table. Here's what I have so far which is obviously incorrect:

    $Computers = "Host1", "Host2", "Host3", "Host4"
    $Assets = Import-Csv C:\assettags.csv
    foreach ($Computer in $Computers) {
     #test for a connection to host, output computer name if ping fails
     if ((test-connection -cn $Computer -quiet) -eq $False)
      {Write-Host $Computer}
     #Collect and format the information I need from each host
     else {Get-WmiObject -Class win32_ComputerSystem -cn $Computer -EA silentlyContinue | Select-Object -prop Name, Model,
      @{name="OS"; expression={(Get-WmiObject -class Win32_OperatingSystem -cn $Computer ).Caption}},
      @{name="Memory"; expression={"{0:N0}" -f ($_.TotalPhysicalMemory / 1gb) + 'GB'}},
      @{name="Serial #"; expression={Get-Wmiobject -cn $Computer -class Win32_BIOS | Select-Object -ExpandProperty SerialNumber}},
      #This is the custom property I'm having trouble with, I've attempted to loop through the objects to see if my $Computer serial matches and output the corresponding asset tag
      @{name="Asset tag"; expression={foreach($Asset in $Assets){if ($Asset."Serial Number" = $_."Serial #"){select -expandproperty $Asset."Asset tag"}}}}|export-csv C:\test.csv}
    }

    "C:\assettags.csv" format example

    Serial Number,Asset Tag
    1ABCABCABC,D1112223
    1ABCABCABD,D1112224
    1ABCABCABE,D1112225

    "C:\test.csv" output example

    #TYPE Selected.System.Management.ManagementObject
    "Name","Model","OS","Memory","Serial #","Asset tag"
    "Host3","HP ProDesk 600 G1 SFF","Microsoft Windows 7 Professional ","8GB","1ABCABCABD",""

    Thank you for your time and assistance!

    Friday, December 30, 2016 5:17 PM

Answers

  • I would create a hashtable containing the serial number/asset tag from your asset tag file for quick lookup. For example:


    $assetTags = @{}
    Import-Csv "AssetTags.csv" | ForEach-Object {
      $assetTags[$_."Serial Number"] = $_."Asset Tag"
    }
    

    Do this at the top of your script before the foreach loop because you only need to do it once.

    Inside your loop, you can get the computer's asset tag from the hashtable by writing something like this:


    $serialNumber = Get-WmiObject Win32_BIOS -ComputerName $Computer | Select-Object -ExpandProperty SerialNumber
    $assetTag = $assetTags[$serialNumber]
    


    -- Bill Stewart [Bill_Stewart]

    Friday, December 30, 2016 5:42 PM
    Moderator

All replies

  • I would create a hashtable containing the serial number/asset tag from your asset tag file for quick lookup. For example:


    $assetTags = @{}
    Import-Csv "AssetTags.csv" | ForEach-Object {
      $assetTags[$_."Serial Number"] = $_."Asset Tag"
    }
    

    Do this at the top of your script before the foreach loop because you only need to do it once.

    Inside your loop, you can get the computer's asset tag from the hashtable by writing something like this:


    $serialNumber = Get-WmiObject Win32_BIOS -ComputerName $Computer | Select-Object -ExpandProperty SerialNumber
    $assetTag = $assetTags[$serialNumber]
    


    -- Bill Stewart [Bill_Stewart]

    Friday, December 30, 2016 5:42 PM
    Moderator
  • Thank you very much, Bill. I was able to get the hash table working, however the shell doesn't like that format for the index. $Assettags looks good when I pass it, All of the serials are under the "Name" Heading and all of the asset tags are under the "Value" heading. However, if I try $assetTags[$Serialnumber] I get "Index operation failed; the array index avaluated to null." and if try $assettags[*anyvalidserial*] I get "array index expression is missing or not valid".
    Friday, December 30, 2016 8:35 PM
  • This code:

    $assetTags[$Serialnumber]

    won't work (i.e., will fail with the error you mentioned) if the $Serialnumber variable is empty.


    -- Bill Stewart [Bill_Stewart]

    Friday, December 30, 2016 8:39 PM
    Moderator