Powershell Date Formatting

[Dunnes2002]

Hi,
I’m using PowerShell to retrieve some API info from Power BI, the API detail has a date/time creation date however due to the limitation in the Power Bi service we could do with the date also split in a true date variable and a time variable. I know very little about PowerShell but i tried to change the format but hit a number of errors. The code is below, could anyone help with how i would achieve it?
Its the Creation date that i have created but i get a rather long error message

Retrieving log entries from blob content created between 07/28/2025 10:00:01 and 2025-07-28T11:00:00
System.Management.Automation.CommandNotFoundException: The term ‘CreationTime’ is not recognized as a name of a cmdlet, function, script file, or executable program.
Check the spelling of the name, or if a path was included, verify that the path is correct and try again.
at System.Management.Automation.CommandDiscovery.LookupCommandInfo(String commandName, CommandTypes commandTypes, SearchResolutionOptions searchResolutionOptions, CommandOrigin commandOrigin, ExecutionContext context)
at System.Management.Automation.ExecutionContext.CreateCommand(String command, Boolean dotSource)
at System.Management.Automation.PipelineOps.AddCommand(PipelineProcessor pipe, CommandParameterInternal commandElements, CommandBaseAst commandBaseAst, CommandRedirection redirections, ExecutionContext context)
at System.Management.Automation.PipelineOps.InvokePipeline(Object input, Boolean ignoreInput, CommandParameterInternal pipeElements, CommandBaseAst pipeElementAsts, CommandRedirection commandRedirections, FunctionContext funcContext)
at System.Management.Automation.Interpreter.ActionCallInstruction`6.Run(InterpretedFrame frame)
at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
Loading latest results into BI Streaming dataset
Writing current endTime: 2025-07-28T11:00:00, to file: .\lastEndTime.txt for retrieval next run

param(
    [switch]$startSubscription,
    [switch]$stopSubscription,
    [switch]$listSubscriptions,
    [switch]$passThru
)

#Azure AD Application Details
$appId = 'zzzzzzzzzzzzzzzzzzzzz'
$tenantId = 'zzzzzzzzzzzzzzzzzzzzzzzzzzzz'
$domain = 'zzzzzzzzzzzzzzzzzzzzzzzzzz'
$clientSecret = "zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz"
#Power BI Streaming Dataset Details
$powerBiEndpoint = "zzzzzzzzzzzzzzzzzzzzzzzzzzz"

function Get-MgmtAccessToken($appId, $domain, $clientSecret) {
    $resource = 'https://manage.office.com' 
    $clientSecret = [uri]::EscapeDataString($clientSecret)
    $uri = "https://login.windows.net/{0}/oauth2/token" -f $domain
    $body = "grant_type=client_Credentials&resource=$resource&client_id=$appId&client_secret=$clientSecret"
    $response = Invoke-RestMethod -Uri $uri -ContentType "application/x-www-form-urlencoded" -Body $body -Method "POST"
    $AccessToken = $response.access_token
    return $AccessToken
}
function New-AuditLogSubscription( $tenantId, $AccessToken) {
    $uri = "https://manage.office.com/api/v1.0/$($tenantId)/activity/feed/subscriptions/start?contentType=Audit.General&PublisherIdentifier=$($tenantId)"
    $response = Invoke-RestMethod -Uri $uri -ContentType "application/x-www-form-urlencoded" -Headers @{'authorization'="Bearer $($AccessToken)"} -Method "POST"
    return $response
}
function Stop-AuditLogSubscription( $tenantId, $AccessToken) {
    $uri = "https://manage.office.com/api/v1.0/$($tenantId)/activity/feed/subscriptions/stop?contentType=Audit.General&PublisherIdentifier=$($tenantId)"
    $response = Invoke-RestMethod -Uri $uri -ContentType "application/x-www-form-urlencoded" -Headers @{'authorization'="Bearer $($AccessToken)"} -Method "POST"
    return $response
}
function Get-AuditLogSubscriptions( $tenantId, $AccessToken) {
    $uri = "https://manage.office.com/api/v1.0/$($tenantId)/activity/feed/subscriptions/list?PublisherIdentifier=$($tenantId)"
    $response = Invoke-RestMethod -Uri $uri -ContentType "application/x-www-form-urlencoded" -Headers @{'authorization'="Bearer $($AccessToken)"} -Method "GET"
    return $response
}
function Get-AuditLogSubscriptionContent( $tenantId, $AccessToken, $startTime, $endTime ) {
    $uri = "https://manage.office.com/api/v1.0/$($tenantId)/activity/feed/subscriptions/content?contentType=Audit.General&startTime=$($startTime)&endTime=$($endTime)"
    $response = Invoke-RestMethod -Uri $uri -ContentType "application/x-www-form-urlencoded" -Headers @{'authorization'="Bearer $($AccessToken)"} -Method "GET"
    return $response
}
function Get-AuditLogSubscriptionContentBlob( $tenantId, $contentUri, $AccessToken ) {
    $uri = $contentUri
    $response = Invoke-RestMethod -Uri $uri -ContentType "application/x-www-form-urlencoded" -Headers @{'authorization'="Bearer $($AccessToken)"} -Method "GET"
    return $response
}
function Get-AuditLogEntries( $tenantId, $AccessToken, $startTime, $endTime) {
    $Content = Get-AuditLogSubscriptionContent -tenantId $tenantId -AccessToken $AccessToken -startTime $startTime -endTime $endTime
    $allBlobContent = @()
    foreach($item in $Content){
        $blobContent = Get-AuditLogSubscriptionContentBlob -tenantId $tenantId -AccessToken $AccessToken -contentUri $($item.contentUri)
        $allBlobContent += $blobContent
    }
    return $allBlobContent
}
function Push-ResultsToPowerBI( $powerBiEndpoint, $PowerBIEntries) {
    write-output "Loading latest results into BI Streaming dataset"
    foreach($entry in $PowerBIEntries){
        $payload = @{
        "CreationTime" = $entry.CreationTime
        "CreationDate" = $entry.CreationDate
        "UserId" = $entry.UserId
        "Operation" = $entry.Operation
        "Workload" = $entry.Workload
        "Activity" = $entry.Activity
        "ReportName" = $entry.ReportName
        "WorkSpaceName" = $entry.WorkSpaceName
        "DatasetName" = $entry.DatasetName
        "DashboardName" = $entry.DashboardName
        "UserAgent" = $entry.UserAgent
        "ClientIP" = $entry.ClientIP
        "DistributionMethod" = $entry.DistributionMethod
        "ConsumptionMethod" = $entry.ConsumptionMethod
        }
        write-output "Pushing: $($entry.CreationTime), $($entry.CreationDate),$($entry.UserId), $($entry.Operation), $($entry.Workload), $($entry.Activity), $($entry.ReportName), $($entry.WorkSpaceName), $($entry.DatasetName), $($entry.DashboardName)"
Start-Sleep -Seconds 3 
        $response = Invoke-RestMethod -Method Post -Uri "$powerBiEndpoint" -Body (ConvertTo-Json @($payload))
    }
}

#### Main Code Begins Here ####

$AccessToken = Get-MgmtAccessToken -appId $appId -domain $domain -clientSecret $clientSecret

if($startSubscription){ New-AuditLogSubscription -tenantId $tenantId -AccessToken $AccessToken }
if($stopSubscription){ Stop-AuditLogSubscription -tenantId $tenantId -AccessToken $AccessToken }
if($listSubscriptions){ Get-AuditLogSubscriptions -tenantId $tenantId -AccessToken $AccessToken }

#Get Start Date and End Date for Content Retrieval.  Default StartTime is 1 day ago.  Default EndTime is now.  Retain latest EndTime in a local file.
$lastendTimePath = ".\lastEndTime.txt"
$endTime = (Get-Date).date.AddHours(((Get-Date).hour))  #Get-Date
$endTimeString = Get-Date $endTime.ToUniversalTime() -Format "yyyy-MM-ddTHH:mm:ss"
try {
    $lastendTime = ($endTime).AddHours(-1).addseconds(1) #Get-Content -Path $lastendTimePath -ErrorAction SilentlyContinue
} catch {
    write-output "No last endtime file found at $lastendTimePath"
    $lastendTime = $null
}
if($lastendTime){
    $startTimeString = $lastendTime
} else {
    $startTime = ($endTime.ToUniversalTime()).AddDays(-1)
    $startTimeString = Get-Date $startTime -Format "yyyy-MM-ddTHH:mm:ss"
}

write-output "Retrieving log entries from blob content created between $startTimeString and $endTimeString"

$Entries = Get-AuditLogEntries -tenantId $tenantId -AccessToken $AccessToken -startTime $startTimeString -endTime $endTimeString
$PowerBIEntries = $Entries | sort CreationTime | Where-Object{$_.Workload -like "PowerBI"} | Select-Object CreationTime,  (CreationTime).date CreationDate,UserId, Operation, Workload, Activity, ReportName, WorkSpaceName, DatasetName, DashboardName, UserAgent, ClientIP, DistributionMethod, ConsumptionMethod
Push-ResultsToPowerBI -powerBiEndpoint $powerBiEndpoint -PowerBIEntries $PowerBIEntries
if($passThru){ $PowerBIEntries }

write-output "Writing current endTime: $($endTimeString), to file: $($lastendTimePath) for retrieval next run"
$endTimeString | Set-Content -Path $lastendTimePath -Force

thanks
Mike

[matt-bloomfield]

Hi, welcome to the forum

Good job with formatting the code, but for future reference please format output and error messages as code too, it helps greatly with readability.

The error is because you have CreationTime in brackets:

Select-Object CreationTime,  (CreationTime).date CreationDate

You can get the date property with:

Select-Object {$_.CreationTime.Date}

But note this includes the time reset to 0000 hours, which is probably not what you want.

The best way is to use a calculated property:

Select-Object @{l='CreationDateDate';e={$_.CreationTime.ToString('yyyy-MM-dd')}}

You can change the parameter value according to your date formatting requirements.

You can further improve readability by declaring the properties to select as an array:

$Properties = @(
    'CreationTime'
    @{l='CreationDate';e={$_.CreationTime.ToString('yyyy-MM-dd')}}
    'UserId'
    'Operation'
    'Workload'
    'Activity'
    'ReportName'
    'WorkSpaceName'
    'DatasetName'
    'DashboardName'
    'UserAgent'
    'ClientIP'
    'DistributionMethod'
    'ConsumptionMethod'
)

Select-Object $Properties

[Dunnes2002]

excellent thank you for the reply, I managed to get the calculated property working and bringing back just the date. still a bit of work to do but that’s to do with the streaming dataset in Power BI!!