I have a PowerShell script which downloads audit logs from Azure. The Export-CSV function outputs the file to my local computer. My plan however is to run this script every night using Azure Data Factory and then output the log file directly to Data Lake Storage, not locally.
ADF > PowerShell Script > Data Lake Storage
I need to amend this script so that it either outputs the CSV file directly to Data Lake Storage OR it outputs it so that ADF can channel it to a sink (Data Lake Storage).
Set-ExecutionPolicy RemoteSigned
#This is better for scheduled jobs
$User = "[email protected]"
$PWord = ConvertTo-SecureString -String "XXXXXXXX" -AsPlainText -Force
$UserCredential = New-Object -TypeName "System.Management.Automation.PSCredential" -ArgumentList $User, $PWord
#This will prompt the user for credential
#$UserCredential = Get-Credential
$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://outlook.office365.com/powershell-liveid/ -Credential $UserCredential -Authentication Basic -AllowRedirection
Import-PSSession $Session
$startDate=(get-date).AddDays(-5)
$endDate=(get-date)
$scriptStart=(get-date)
$sessionName = (get-date -Format 'u')+'pbiauditlog'
# Reset user audit accumulator
$aggregateResults = @()
$i = 0 # Loop counter
Do {
$currentResults = Search-UnifiedAuditLog -StartDate $startDate -EndDate $enddate -SessionId $sessionName -SessionCommand ReturnLargeSet -ResultSize 1000 -RecordType PowerBIAudit
if ($currentResults.Count -gt 0) {
Write-Host (" Finished {3} search #{1}, {2} records: {0} min" -f [math]::Round((New-TimeSpan -Start $scriptStart).TotalMinutes,4), $i, $currentResults.Count, $user.UserPrincipalName )
# Accumulate the data
$aggregateResults += $currentResults
# No need to do another query if the # recs returned <1k - should save around 5-10 sec per user
if ($currentResults.Count -lt 1000) {
$currentResults = @()
} else {
$i++
}
}
} Until ($currentResults.Count -eq 0) # --- End of Session Search Loop --- #
$data=@()
foreach ($auditlogitem in $aggregateResults) {
$d=convertfrom-json $auditlogitem.AuditData
$datum = New-Object –TypeName PSObject
$d=convertfrom-json $auditlogitem.AuditData
$datum | Add-Member –MemberType NoteProperty –Name Id –Value $d.Id
$datum | Add-Member –MemberType NoteProperty –Name CreationDateTime –Value $auditlogitem.CreationDate
$datum | Add-Member –MemberType NoteProperty –Name CreationTimeUTC –Value $d.CreationTime
$datum | Add-Member –MemberType NoteProperty –Name RecordType –Value $d.RecordType
$datum | Add-Member –MemberType NoteProperty –Name Operation –Value $d.Operation
$datum | Add-Member –MemberType NoteProperty –Name OrganizationId –Value $d.OrganizationId
$datum | Add-Member –MemberType NoteProperty –Name UserType –Value $d.UserType
$datum | Add-Member –MemberType NoteProperty –Name UserKey –Value $d.UserKey
$datum | Add-Member –MemberType NoteProperty –Name Workload –Value $d.Workload
$datum | Add-Member –MemberType NoteProperty –Name UserId –Value $d.UserId
$datum | Add-Member –MemberType NoteProperty –Name ClientIP –Value $d.ClientIP
$datum | Add-Member –MemberType NoteProperty –Name UserAgent –Value $d.UserAgent
$datum | Add-Member –MemberType NoteProperty –Name Activity –Value $d.Activity
$datum | Add-Member –MemberType NoteProperty –Name ItemName –Value $d.ItemName
$datum | Add-Member –MemberType NoteProperty –Name WorkSpaceName –Value $d.WorkSpaceName
$datum | Add-Member –MemberType NoteProperty –Name DashboardName –Value $d.DashboardName
$datum | Add-Member –MemberType NoteProperty –Name DatasetName –Value $d.DatasetName
$datum | Add-Member –MemberType NoteProperty –Name ReportName –Value $d.ReportName
$datum | Add-Member –MemberType NoteProperty –Name WorkspaceId –Value $d.WorkspaceId
$datum | Add-Member –MemberType NoteProperty –Name ObjectId –Value $d.ObjectId
$datum | Add-Member –MemberType NoteProperty –Name DashboardId –Value $d.DashboardId
$datum | Add-Member –MemberType NoteProperty –Name DatasetId –Value $d.DatasetId
$datum | Add-Member –MemberType NoteProperty –Name ReportId –Value $d.ReportId
$datum | Add-Member –MemberType NoteProperty –Name OrgAppPermission –Value $d.OrgAppPermission
#option to include the below JSON column however for large amounts of data it may be difficult for PBI to parse
#$datum | Add-Member –MemberType NoteProperty –Name Datasets –Value (ConvertTo-Json $d.Datasets)
#below is a PowerShell statement to grab one of the entries and place in the DatasetName if any exist
foreach ($dataset in $d.datasets) {
$datum.DatasetName = $dataset.DatasetName
$datum.DatasetId = $dataset.DatasetId
}
$data+=$datum
}
$datestring = $startDate.ToString("yyyyMMdd")
$fileName = ("C:\Users\Client\Audit Logging\Logs\" + $datestring + ".csv")
Write-Host ("Writing to file {0}" -f $fileName)
$data | Export-csv -Path $fileName
Remove-PSSession -Id $Session.Id
I did start writing some code to connect to Data Lake Storage as follows, but not sure how to integrate this with the above Export-CSV function. How do I get the CSV file to be published to Data Lake Storage (as it won't be stored locally) or output so that ADF can direct it to a sink store?
# Variable Declaration
$rgName = "Audit"
$subscriptionID = "dabdhnca9-0742-48b2-98d5-af476d62c6bd"
$dataLakeStoreName = "pbiauditingstorage12"
$myDataRootFolder = "/auditlogs"
#$sourceFilesPath = "C:\Users\Downloads\datasets\"
# Log in to your Azure account
Login-AzureRmAccount
# List all the subscriptions associated to your account
Get-AzureRmSubscription
# Select a subscription
Set-AzureRmContext -SubscriptionId $subscriptionID
# See if folder exists.
# If a folder or item does not exiss, then you will see
# Get-AzureRmDataLakeStoreChildItem : Operation returned an invalid status code 'NotFound'
Get-AzureRmDataLakeStoreChildItem -AccountName $dataLakeStoreName -Path $myDataRootFolder
# Create new folder
New-AzureRmDataLakeStoreItem -Folder -AccountName $dataLakeStoreName -Path $myDataRootFolder/population
# Upload folder and its contents recursively and force ovewrite existing
Import-AzureRmDataLakeStoreItem -AccountName $dataLakeStoreName `
-Path $sourceFilesPath\ `
-Destination $myDataRootFolder `
-Recurse `
-Force
Please advise, many thanks!
$fileNamevariable from the first code sample as the-Pathparameter ofImport-AzureRmDataLakeStoreItemin the second? - Mathias R. Jessen