CODEWITHSUNDEEP
powershellcsvimportexport-to-csv
Nick
Nick

Reputation: 1208

Powershell Include data from import-csv in export-csv

I am writing a script to import a list of computers from a CSV file, then reach out to each of these computers, pull all of their certificate information, and export it to a new CSV. The script that I have works perfectly, but I cannot get it to include the name of the computer in the final product. I have tried everything that I can possibly think of including creating a new PSObject, trying to pull the env:Computername, and trying to export the names from the original CSV but nothing is working!This will be run locally on a single PC.

Here is what the input looks like:

Server

Compname

Compname2

Compname3

Here is the export: This is dummy data ![1]: https://i.sstatic.net/lSHXK.png

Here is what I need the export to be: This is dummy data, the highlight is for example purposes only ![2]: https://i.sstatic.net/IPuSt.png

I will copy my code down below, but any assistance would be appreciated! I am relatively new at powershell and have been teaching myself exclusively, so please excuse any errors or redundancies in the code. The code already works perfectly except for not including the computername in it's export.

    #Import File
    $Serverlist = Import-Csv "C:\Servers.csv"

    #Find Cert info
    Foreach ($Server in $ServerList) 
    {
    Write-Host "$Server"
    $Servername = $Server
    $Certlist = Get-ChildItem -Recurse Cert: | Select-Object Subject, Issuer, Thumbprint, FriendlyName, NotBefore, NotAfter, Extensions
    }

    #Export CSV
    $Certlist | Export-CSV C:\ServerResults.csv

Upvotes: 0

Views: 2132

Answers (2)

Kiran Reddy
Kiran Reddy

Reputation: 2904

Use add-member to add a custom property to every object that comes out of the pipeline. The where-object filters out the those objects that don't have a subject property

$ServerList | 
 ForEach-Object {
   Get-ChildItem -Recurse Cert: | Where-Object Subject -NE $null |
   Add-Member Noteproperty -Name Server -Value $_ -PassThru

  } |  Select-Object Server,Subject, Issuer, Thumbprint, FriendlyName, NotBefore, NotAfter, Extensions |
       Export-csv -Path C:\temp\mycsv.csv -NoTypeInformation

Upvotes: 1

Avshalom
Avshalom

Reputation: 8889

Nicky, you can't get the computer name because you run it only on your local computer, your Get-ChildItem cmdlet work on cert: which is your local CERT Drive, to see the other computers you first need to get the Remote Computer CERT folder,

You Can Use This Function, and then see the changes/remarks i made in your script to understand what you've missed.

    ## Helper Function to Get the Remote CERT PSDrive

    function Get-RemoteCert( $ComputerName=$env:computername ){

    $ro=[System.Security.Cryptography.X509Certificates.OpenFlags]"ReadOnly"

    $lm=[System.Security.Cryptography.X509Certificates.StoreLocation]"LocalMachine"

    $store=new-object System.Security.Cryptography.X509Certificates.X509Store("\\$computername\root",$lm)

    $store.Open($ro)

$store.Certificates

}

    #Import File
    $Serverlist = Import-Csv "C:\Servers.csv"
    $Output = @()

    #Find Cert info
    Foreach ($Server in $ServerList) 
    {
        $Certlist = Get-RemoteCert -ComputerName $Server
        #$Certlist = Get-ChildItem -Recurse Cert: | Select-Object Subject, Issuer, Thumbprint, FriendlyName, NotBefore, NotAfter, Extensions

        Foreach ($cert in $Certlist)
        {
            $Result = "" | Select Computer,Subject, Issuer, Thumbprint, FriendlyName, NotBefore, NotAfter, Extensions
            $Result.Computer = $Server
            $Result.Subject =  $cert.Subject
            $Result.Issuer = $cert.Issuer
            $Result.Thumbprint = $cert.Thumbprint
            $Result.FriendlyName = $cert.FriendlyName 
            $Result.NotBefore = $cert.NotBefore
            $Result.NotAfter = $cert.NotAfter
            $Result.Extensions = $cert.Extensions

            $Output += $Result

        }
    }


    #Export CSV
    $Output | Export-CSV C:\ServerResults.csv

Upvotes: 0

Related Questions