Export Powershell Output to CSV - Add Date To Each Row?

[FN-GM]

Hello,

 

I am making various script to automate tasks. i would like to log data pulled from AD, place it into a CSV. I am using this code

 

Get-ADUser -Filter * -SearchBase "OU=Finance,OU=UserAccounts,DC=FABRIKAM,DC=COM" | Export-CSV filename.csv -Append -NoTypeInformation 

 

I would like to add the current date and time into each row, this is so we will know when the data was pulled.

 

Can anyone assist please? I am only finding how to name the file with the current date rather than adding it into the CSV.

 

Thanks

[Bedders]

There's a way to do it using an extra step in Powershell, by importing the created CSV and adding the date column: https://techwizard.cloud/2021/03/31/powershell-tip-add-extra-column-to-existing-csv/

 

Step 1: Import CSV file into a variable $data

$data = Import-CSV c:\csvfile.csv

Step 2: Get the date/time

$getdate = get-date -format D

Step 3: ADD the computed date value in step 2 in select expression and export as new csv file.

$data | Select-Object *, @{n=”Date”;e={$getdate}} | Export-CSV c:\newcsvfile.csv -NoTypeInformation

 

 

As for adding it to the current search query, would something like this work? I haven't tested it and have no idea

 

$GetDate= Get-Date -format D
Get-ADUser -Filter * -SearchBase "OU=Finance,OU=UserAccounts,DC=FABRIKAM,DC=COM" | Select-Object -Property @{n="Date"; e={$GetDate}} | Export-CSV filename.csv -Append -NoTypeInformation

[psydii]

The trick Bedders used there is creating a calculated property, a walk through on how that works/how to parse that is here: https://adamtheautomator.com/powershell-select-object/

 

Full documentation can be got at the command line: get-help *calculated*

 

or on the web here: https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.core/about/about_calculated_properties?view=powershell-7.3

[ThomL]

Are you planning to wipe the csv daily or run comparisons on timestamps and user DNs? Using append on Export-CSV is going to stack all of the AD results repeatedly in the CSV file, adding a timstamp each time - which is fine if that's what you had planned or you're cleaning the csv file/comparing it with something etc.

 

Another option is to compare the csv file to the results from AD, then append only the new items from AD to the csv file with the required timestamp:

$csvFilePath = "C:\filename.csv"
$ADou = "OU=Finance,OU=UserAccounts,DC=FABRIKAM,DC=COM"

if(Test-Path $csvFilePath) {
   $CSVdata = Import-Csv $csvFilePath | select DistinguishedName -ExpandProperty DistinguishedName
   $ADdata = Get-ADUser -Filter * -SearchBase $ADou | select DistinguishedName -ExpandProperty DistinguishedName
   Compare-Object -ReferenceObject $CSVdata -DifferenceObject $ADdata | select InputObject -ExpandProperty InputObject | ForEach-Object {
       Get-ADUser -identity $_ | Select @{Name = "Data Pulled"; Expression = {Get-Date -Format o}}, * | Export-CSV C:\NAI\filename.csv -Append -NoTypeInformation
   }
} else {
   Get-ADUser -Filter * -SearchBase $ADou | Select @{Name = "Data Pulled"; Expression = {Get-Date -Format o}}, * | Export-CSV $csvFilePath -Append -NoTypeInformation
}

 

Code execution outline:

 

Code checks if the csv files exists, if csv file exists:

Import the user DNs from csv

Get user DNs from AD,

Compared csv DNs to AD DNs, for each DN missing from CSV:

Export user info to csv file, with timestamp

 

 

If csv file didn't exist:

Create csv file with info from AD

[FN-GM]

Are you planning to wipe the csv daily or run comparisons on timestamps and user DNs? Using append on Export-CSV is going to stack all of the AD results repeatedly in the CSV file, adding a timstamp each time - which is fine if that's what you had planned or you're cleaning the csv file/comparing it with something etc.

 

 

I plan to keep adding to the CSV. I basically want a record of the data that’s been processed.

 

I have some good replies and will play with the shortly.

 

Thanks all for the replies.

[FN-GM]

$GetDate= Get-Date -format D
Get-ADUser -Filter * -SearchBase "OU=Finance,OU=UserAccounts,DC=FABRIKAM,DC=COM" | Select-Object -Property @{n="Date"; e={$GetDate}} | Export-CSV filename.csv -Append -NoTypeInformation

 

This did the trick. I used this to add to the search and dumped it all into a CSV file.

 

This will be used in a series of templates to automate our Active Directory activities and this has been key. Thanks to everyone who helped.