locked
Export to CSV question RRS feed

  • Question

  • I have what might be a somewhat unique export question but hopefully has a simple answer.

    I found a custom function that will query a server's certificate store and return all the certificates, get-pkicertificates.  When I run this function against multiple servers and export it to CSV the location of each certificate is not included in the export since it's not a property of the individual certificate itself.  So what I'm looking for is a way to modify the lines below that will somehow notate in the CSV which server the certificates were exported from.

    $Server = Get-ADComputer -Filter {OperatingSystem -Like "Windows Server*"}

    $Server | foreach{Get-PKICertificates -computer $_.name -StoreLocation LocalMachine -StoreName My -OpenFlag ReadOnly} | export-csv certs.csv

    Thanks, any help greatly appreciated!

    Friday, April 18, 2014 6:31 PM

Answers

  • Trying to use the one you defined,

    $computers=Get-ADComputer -Filter {OperatingSystem -Like "Windows Server*"} 

    Get-PKICertificates -Computer $computers | select subject,system

    If I replace $computers with a specific system name it works fine.

    If I try to specify the name property, ie

    Get-PKICertificates -Computer $computers.name | select subject,system

    I get

    : Cannot validate argument on parameter 'ComputerName'. The argument is null or empty. Supply an argument that is not nu
    ll or empty and then try the command again.

    I said I have a copy that can do that.  The copy you have cannot because it has not been modified.

    You can use an array like this:

    $computers=Get-ADComputer -Filter {OperatingSystem -Like "Windows Server*"}  |%{$_.Name}

    Now it should work because the original was designed with $computer defined as [string[]]


    ¯\_(ツ)_/¯

    • Marked as answer by TimHau Friday, April 18, 2014 7:47 PM
    Friday, April 18, 2014 7:41 PM

All replies

  • This will tag all records with the server name.

    Get-ADComputer -Filter {OperatingSystem -Like "Windows Server*"}  | 
         Foreach-Object{
              $computer=$_.Name
              $certs=Get-PKICertificates -computer $_.name -StoreLocation LocalMachine -StoreName My -OpenFlag ReadOnly
              $certs | Add-Member NoteProperty Server $computer -PassThru
         } | export-csv certs.csv



    ¯\_(ツ)_/¯


    • Proposed as answer by Prashanth Jayaram Friday, April 18, 2014 6:52 PM
    • Edited by jrv Friday, April 18, 2014 7:34 PM
    Friday, April 18, 2014 6:36 PM
  • Here replace the switch in the script:

                Switch ($Pscmdlet.ParameterSetName) { 
                    "PKI" { 
                        #List all certificates in the store 
                        Write-Verbose "Listing all certificates in store" 
                        $ce.certificates | 
                            Add-Member -MemberType 'NoteProperty'-Name System -Value $c -PassThru
                        } 
                    "Expired" { 
                        Write-Verbose "Listing all expired certificates" 
                        $ce.Certificates | 
                            ? {$_.NotAfter -le (Get-Date)} |
                            Add-Member -MemberType 'NoteProperty'-Name System -Value $c -PassThru
                        } 
                    "Expiring" { 
                        Write-Verbose "Listing certificates that expire in $ExpiresIn days" 
                        #Create a datetime object with the expiration threshold to compare against certificate expiration timestamp 
                        $deadline = (Get-Date).AddDays($ExpiresIn) 
                        $ce.Certificates | 
                            ? {$_.NotAfter -le ($deadline)} |
                            Add-Member -MemberType 'NoteProperty'-Name System -Value $c -PassThru
                        } 
                    }                 
        

    Now you can do this:

    $computers=Get-ADComputer -Filter {OperatingSystem -Like "Windows Server*"} 
    Get-PKICertificates -Computer $computers | select subject,system

    All computers will be enumerated.

    I have modified a copy to take direct pipeline input alos:

    Get-ADComputer -Filter {OperatingSystem -Like "Windows Server*"} |  
          Get-PKICertificates |
          Export-Csv certs.csv -NoTypeInfo


    ¯\_(ツ)_/¯



    • Edited by jrv Friday, April 18, 2014 6:56 PM
    Friday, April 18, 2014 6:55 PM
  • Wow, thanks for the speedy reply!

    I think we're close.  The first option you posted I get the following error.

    Add-Member : Cannot bind argument to parameter 'InputObject' because it is null.
    At C:\scripts\getcerts.ps1:4 char:30
    +           $certs | Add-Member <<<<  NoteProperty Server $_.name -PassThru
        + CategoryInfo          : InvalidData: (:) [Add-Member], ParameterBindingValidationException
        + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.AddMemberCo
       mmand

    I liked the second option better but it doesn't seem to quite work.  I can run it against a specified server and I get a list of certs and another column with the system name but when I use the variable I don't get any results.

    Friday, April 18, 2014 7:27 PM
  • Wow, thanks for the speedy reply!

    I think we're close.  The first option you posted I get the following error.

    Add-Member : Cannot bind argument to parameter 'InputObject' because it is null.
    At C:\scripts\getcerts.ps1:4 char:30
    +           $certs | Add-Member <<<<  NoteProperty Server $_.name -PassThru
        + CategoryInfo          : InvalidData: (:) [Add-Member], ParameterBindingValidationException
        + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.AddMemberCo
       mmand

    I liked the second option better but it doesn't seem to quite work.  I can run it against a specified server and I get a list of certs and another column with the system name but when I use the variable I don't get any results.

    what variable are you using?


    ¯\_(ツ)_/¯

    Friday, April 18, 2014 7:31 PM
  • Trying to use the one you defined,

    $computers=Get-ADComputer -Filter {OperatingSystem -Like "Windows Server*"} 

    Get-PKICertificates -Computer $computers | select subject,system

    If I replace $computers with a specific system name it works fine.

    If I try to specify the name property, ie

    Get-PKICertificates -Computer $computers.name | select subject,system

    I get

    : Cannot validate argument on parameter 'ComputerName'. The argument is null or empty. Supply an argument that is not nu
    ll or empty and then try the command again.

    • Edited by TimHau Friday, April 18, 2014 7:36 PM
    Friday, April 18, 2014 7:32 PM
  • This should have looked like this:
    Get-ADComputer -Filter {OperatingSystem -Like "Windows Server*"}  | 
         Foreach-Object{
              $computer=$_.Name
              $certs=Get-PKICertificates -computer $computer -StoreLocation LocalMachine -StoreName My -OpenFlag ReadOnly
              $certs | Add-Member NoteProperty Server $computer -PassThru
         } | export-csv certs.csv


    ¯\_(ツ)_/¯


    • Edited by jrv Friday, April 18, 2014 7:38 PM
    Friday, April 18, 2014 7:34 PM
  • Trying to use the one you defined,

    $computers=Get-ADComputer -Filter {OperatingSystem -Like "Windows Server*"} 

    Get-PKICertificates -Computer $computers | select subject,system

    If I replace $computers with a specific system name it works fine.

    If I try to specify the name property, ie

    Get-PKICertificates -Computer $computers.name | select subject,system

    I get

    : Cannot validate argument on parameter 'ComputerName'. The argument is null or empty. Supply an argument that is not nu
    ll or empty and then try the command again.

    I said I have a copy that can do that.  The copy you have cannot because it has not been modified.

    You can use an array like this:

    $computers=Get-ADComputer -Filter {OperatingSystem -Like "Windows Server*"}  |%{$_.Name}

    Now it should work because the original was designed with $computer defined as [string[]]


    ¯\_(ツ)_/¯

    • Marked as answer by TimHau Friday, April 18, 2014 7:47 PM
    Friday, April 18, 2014 7:41 PM
  • Aha!  Thank you!

    I modified the switch in my function then modified my variable as well and it now works great.

    Major kudos to you, appreciate the help!

    Friday, April 18, 2014 7:47 PM