locked
Script that will open a dedicated Excel file and populate the next cell with PC serial number RRS feed

  • Question

  • I am trying to automate a process. We keep an Excel spreadsheet of all new machines that are being set up. What I'd like the script to do is: use this command (or similar) WMIC BIOS GET SERIALNUMBER. Take that serial number and populate the next open cell in a column in that dedicated sheet or workbook.  Any help would be appreciated, thank you.
    Wednesday, February 1, 2017 2:59 PM

Answers

  • Start by reading the first post from the top of this forum:

    This forum is for scripting questions rather than script requests

    That said, we would definitely recommend PowerShell rather than WMIC. Here's an example PowerShell command:


    Get-ADComputer -Filter * -SearchBase "OU=Sales,DC=fabrikam,DC=com" |
      Select-Object -ExpandProperty Name |
      ForEach-Object { Get-WmiObject Win32_BIOS -ComputerName $_ } |
      Select-Object @{Name="ComputerName"; Expression={$_.__SERVER}},SerialNumber
    

    This command will get all the computers in an OU and retrieve the WMI SerialNumber property from each computer. You can pipe this command to Export-Csv to save the results in a CSV file.

    Because it's so easy to retrieve a computer's serial number in this way:


    Get-WmiObject Win32_BIOS -ComputerName whatever

    You may not need to keep a spreadsheet file containing this list (you can just ask the computer what its serial number is whenever you need it).


    -- Bill Stewart [Bill_Stewart]



    Wednesday, February 1, 2017 3:22 PM
  • Modifying an actual xlsx spreadsheet is going to involve some complicated .NET work. However, if you're just doing a single column spreadsheet, you can easily use a CSV file, which PowerShell natively talks to. 

    If you're running this action from the individual computer:

    $outputFilePath = "\\fileServer\Share\SerialNumbers.csv"
    Get-WmiObject win32_bios | select SerialNumber | Export-Csv -NoTypeInformation -Append -Path $outputFilePath


    If you're running this action from another computer and are querying a specific hostname:

    $hostname = "SomeComputerName"
    $outputFilePath = "C:\Temp\SerialNumbers.csv"
    Get-WmiObject -ComputerName $hostname -class win32_bios | select SerialNumber | Export-Csv -NoTypeInformation -Append -Path $outputFilePath

    If you're running this action from another computer and want to query every computer:

    import-module ActiveDirectory
    $outputFilePath = "C:\Temp\SerialNumbers.csv"
    get-adcomputer -filter * | foreach {Get-WmiObject -ComputerName $_.SamACcountName -class win32_bios | select SerialNumber | Export-Csv -NoTypeInformation -Append -Path $outputFilePath}

    • Proposed as answer by SYN_ACK_87 Thursday, February 2, 2017 5:48 PM
    • Marked as answer by Richard MuellerMVP Thursday, February 9, 2017 3:32 PM
    Thursday, February 2, 2017 5:46 PM

All replies

  • Start by reading the first post from the top of this forum:

    This forum is for scripting questions rather than script requests

    That said, we would definitely recommend PowerShell rather than WMIC. Here's an example PowerShell command:


    Get-ADComputer -Filter * -SearchBase "OU=Sales,DC=fabrikam,DC=com" |
      Select-Object -ExpandProperty Name |
      ForEach-Object { Get-WmiObject Win32_BIOS -ComputerName $_ } |
      Select-Object @{Name="ComputerName"; Expression={$_.__SERVER}},SerialNumber
    

    This command will get all the computers in an OU and retrieve the WMI SerialNumber property from each computer. You can pipe this command to Export-Csv to save the results in a CSV file.

    Because it's so easy to retrieve a computer's serial number in this way:


    Get-WmiObject Win32_BIOS -ComputerName whatever

    You may not need to keep a spreadsheet file containing this list (you can just ask the computer what its serial number is whenever you need it).


    -- Bill Stewart [Bill_Stewart]



    Wednesday, February 1, 2017 3:22 PM
  • Modifying an actual xlsx spreadsheet is going to involve some complicated .NET work. However, if you're just doing a single column spreadsheet, you can easily use a CSV file, which PowerShell natively talks to. 

    If you're running this action from the individual computer:

    $outputFilePath = "\\fileServer\Share\SerialNumbers.csv"
    Get-WmiObject win32_bios | select SerialNumber | Export-Csv -NoTypeInformation -Append -Path $outputFilePath


    If you're running this action from another computer and are querying a specific hostname:

    $hostname = "SomeComputerName"
    $outputFilePath = "C:\Temp\SerialNumbers.csv"
    Get-WmiObject -ComputerName $hostname -class win32_bios | select SerialNumber | Export-Csv -NoTypeInformation -Append -Path $outputFilePath

    If you're running this action from another computer and want to query every computer:

    import-module ActiveDirectory
    $outputFilePath = "C:\Temp\SerialNumbers.csv"
    get-adcomputer -filter * | foreach {Get-WmiObject -ComputerName $_.SamACcountName -class win32_bios | select SerialNumber | Export-Csv -NoTypeInformation -Append -Path $outputFilePath}

    • Proposed as answer by SYN_ACK_87 Thursday, February 2, 2017 5:48 PM
    • Marked as answer by Richard MuellerMVP Thursday, February 9, 2017 3:32 PM
    Thursday, February 2, 2017 5:46 PM