question

RolandS-6043 avatar image
0 Votes"
RolandS-6043 asked RolandS-6043 answered

Export a List of all AD users Certificates from Each user

Hi,

I had to have export the User X509 Certificates from each User in a specific OU, with the Issuer and the Expiration Date.

the Export should be looks like in the AD User Object unter Published Certificates Tab.

thank you in advanced

windows-active-directory
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

DaisyZhou-MSFT avatar image
2 Votes"
DaisyZhou-MSFT answered MICHAELIBUKUN commented

Hello @RolandS-6043,

You can try the PS script below.

 $ou = "ou=laps1,dc=b,dc=local"
  $path ="C:\certs"
  get-aduser -SearchBase $ou -Filter * -Properties displayname,usercertificate |ForEach-Object{
     $displayname = $_.displayname
     $_|select -ExpandProperty usercertificate | ForEach-Object{
         $cert = [System.Security.Cryptography.X509Certificates.X509Certificate2]$_
         [pscustomobject]@{
             DisplayName = $displayname
             IssuedTo = $cert.Subject
             IssuedBy = $cert.Issuer
             IntendedPurpose = $cert.EnhancedKeyUsageList
             ExpiredData = $cert.NotAfter
             SerialNumber = $cert.SerialNumber
        }
     }
  } | Export-Csv -NoTypeInformation $path\certs.csv


Here is the result:
87553-display1.png


--please don't forget to Accept as answer if the reply is helpful--


Best Regards,
Daisy Zhou


============================================
If the Answer is helpful, please click "Accept Answer" and upvote it.



display1.png (235.5 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

DaisyZhou-MSFT avatar image
2 Votes"
DaisyZhou-MSFT answered DaisyZhou-MSFT edited

Hello @RolandS-6043,

Thank you for your posting here.

After lots of test in my lab, I can get the result as below.

1.Here is my OU named LAPS1, there are three users in it.
86047-ps2.png

2.Open Powershell ISE(run as administrator) and type the command below.

 $ou = "ou=laps1,dc=b,dc=local"
 $path ="C:\certs" 
 get-aduser -SearchBase $ou -Filter * -Properties usercertificate |ForEach-Object{
  $_|select -ExpandProperty usercertificate | ForEach-Object{
      [System.Security.Cryptography.X509Certificates.X509Certificate2]$_  | select -Property Serialnumber, EnhancedKeyUsageList, notafter, notbefore, issuer, subject
    
 } | Export-Csv -NoTypeInformation $path\$($_.name)_certs.csv
 }

85998-ps1.png

Tip:Please change the OU name and domain name and export path based on your AD environment.

3.We can see one csv file for one user
86017-ps5.png

4.For example: we can see cert list for daisy11 and daisy22.
Daisy 11
85999-ps3.png

Daisy22
86000-ps4.png
You can try the PS command in your AD environment.

Hope the information above is helpful.

Should you have any question or concern, please feel free to let us know.



Best Regards,
Daisy Zhou

============================================
If the Answer is helpful, please click "Accept Answer" and upvote it.




ps2.png (38.8 KiB)
ps1.png (54.7 KiB)
ps5.png (14.5 KiB)
ps3.png (64.2 KiB)
ps4.png (27.3 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

RolandS-6043 avatar image
0 Votes"
RolandS-6043 answered

Hi @DaisyZhou-MSFT ,

Many Many Thanks to you , it looks great , it works Well.

when i need all Certificate Lines in one Excel sheet, which Line is to change ?

Many thanks in advanced

Br
Roland

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

DaisyZhou-MSFT avatar image
2 Votes"
DaisyZhou-MSFT answered DaisyZhou-MSFT edited

Hello @RolandS-6043,

Thank you for your update.

We can run PS script below.

 $ou = "ou=laps1,dc=b,dc=local"
 $path ="C:\certs" 
 get-aduser -SearchBase $ou -Filter * -Properties usercertificate |ForEach-Object{
  $_|select -ExpandProperty usercertificate | ForEach-Object{
      [System.Security.Cryptography.X509Certificates.X509Certificate2]$_  | select -Property Serialnumber, EnhancedKeyUsageList, notafter, notbefore, issuer, subject
    
 }
 } | Export-Csv -NoTypeInformation $path\certs.csv 


Tip: Change the last line.

The result (all users certificates within the OU in the same Excel file):
86722-vv1.png





Best Regards,
Daisy Zhou




============================================
If the Answer is helpful, please click "Accept Answer" and upvote it.


vv1.png (139.6 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

RolandS-6043 avatar image
0 Votes"
RolandS-6043 answered

Hi @DaisyZhou-MSFT,

Thanks a lot, it looks Good , one more think please can you add in the CSV Export please the Displayname from the AD User, like the Attached Picture , is eaisier to identify the User Object and their installed Certificates , and then it would be Perfect , and a lot of Thanks to you for your Support.



86940-image.png



image.png (3.8 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

RolandS-6043 avatar image
0 Votes"
RolandS-6043 answered

Hello @DaisyZhou-MSFT,

This is Perfect, Thank you for your Support.

I have adapted it to my environment and It works well.

Cool.

Have a nice Day.
Br
Roland

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.