How to Display Modified Time and Modified By Fields Correctly with Powershell

Question

I have a script that outputs to CSV all items/files from all Lists and Libraries. In addition, it displays the current and previous versions of an item/file. This also displays which user modified the file for each version and also displays the date/time the file was modified for every version:

function Get-DocInventory([string]$siteUrl) {
$web = Get-SPWeb "http://contoso.com/sites/Depts3/HBG"
foreach ($list in $web.Lists) {


foreach ($item in $list.Items) {
foreach($version in $item.Versions){
$data = @{
"Version" = $version.VersionLabel
"List Name" = $list.Title
"Created By" = $item["Author"]
"Created Date" = ($item["Created"] -as [datetime]).DateTime
"Modified By" = $version["Editor"]
"Modified Date" = ($version["Modified"] -as [datetime]).DateTime
"Item Name" = $item.Name

}
New-Object PSObject -Property $data | Select "List Name", "Item Name", "Version", "Created By", "Created Date", "Modified By", "Modified Date"
}
}
$web.Dispose();
}
}




Get-DocInventory  | Export-Csv -NoTypeInformation -Path C:\GenerateReport.csv

Below is a sample of what the script outputs:

And below is an excel example of what I see when I go to the Version History of the Lions.pdf file:

I have 2 problems:

• The column Modified By is displaying email and domain username which is not required.
• My script displays the column Modified Date as 5 hours ahead. The script output for Version 1 of Lions.pdf displays time as 11:23 AM. But when I go to Version History of Lions.pdf it says that Version 1 was modified at 6:23 AM. The time discrepancy is the same across the board as 5 hours ahead which is incorrect.

I can't seem to figure out what I am doing wrong here. Can someone please assist in the issue I am having? My main concern is the time and I would greatly appreciate any assistance.

Answer 1

User Fields

Normally in server side code, accessing a user field value using array notation (eg myItem["Author"]) would return an object that you would cast to an appropriate type (Microsoft.SharePoint.SPFieldUserValue). Powershell, however, automatically casts these values to strings, which is why you're getting undesirable values.

Fortunately, there's a way around it! First, get the field itself as an object, then pass the field value into the field's GetFieldValue() method.

$userField = $item.Fields.GetField("Author");
$authorObject = $userField.GetFieldValue($item["Author"]);

You'll then have an object with properties you can access to retrieve the desired values, such as LookupValue for the user's display name on the site.

$authorName = $authorObject.LookupValue;

Date Fields

Date fields are little easier, because Powershell will actually hand them over to you as DateTime objects.

To format a DateTime object, you can just call .ToString() and pass in a parameter indicating the desired format.

$data = @{
    ...
    "Modified Date" = $item["Modified"].ToString("MM/dd/yyyy h:mm tt");
    ...
}

Time Discrepancy

This is most likely attributable to your local timezone. The times displayed on a SharePoint site via the browser are determined by your PC's timezone settings. The times actually saved to SharePoint's underlying database are determined by the server's timezone settings.

To reconcile them, you can use the DateTime object's .ToUniversalTime() method to get a corresponding DateTime object in UTC time (and optionally offset it as desired until it aligns with the local timezone and daylight savings adjustments).

Edit: I'm guessing you're in US Eastern time (UTC-5) and it's giving you results in UTC time (Universal Coordinated Time, which is 5 hours ahead of you except during daylight saving time). You should be able to offset the hours manually to account for this.

$localOffset = -5;
$modified = $version["Modified"] -as [datetime];
if($modified.IsDaylightSavingTime()){$localOffset += 1;}
$modifiedLocal = $modified.addHours(-$localOffset);