Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

User activities by Powershell

Hi All,   Is there a way to extract user activities more than 1 day by using Powershell.   Thanks in advance
Need Help
  • arvindsingh802's avatar
    arvindsingh802
    4 years ago

    #2. Authenticate to Power BI
    $PBIAdminUPN="userName"
    $PBIAdminPW="Password"

    $SecPasswd = ConvertTo-SecureString $PBIAdminPW -AsPlainText -Force
    $myCred = New-Object System.Management.Automation.PSCredential($PBIAdminUPN,$SecPasswd)

    Connect-PowerBIServiceAccount -Credential $myCred

    $AuditType = 'PowerBIAudit'

    $RetrieveDate = Get-Date
    $RetrieveDate = $RetrieveDate.AddDays(-2) #replace -2 with days you want to extract

    for($i = 0; $i -lt 1; $i++){ #repalce 1 with days you want
    $RetrieveDate = $RetrieveDate.AddDays(+1)
    $RetrieveYearStr = $RetrieveDate.ToString('yyyy')
    $RetrieveMonthStr = $RetrieveDate.ToString('MM')
    $RetrieveDayStr = $RetrieveDate.ToString('dd')

    $StartDT = $RetrieveYearStr + '-' + $RetrieveMonthStr + '-' + $RetrieveDayStr + 'T00:00:00.000'
    $EndDT = $RetrieveYearStr + '-' + $RetrieveMonthStr + '-' + $RetrieveDayStr + 'T23:59:59.999'

    $datestring = $RetrieveDate.ToString('yyyyMMdd')
    $ActivityLogsPath = ('D:\Audit\PowerBIAudit_' + $datestring + '.csv')

    #4. Export out current date activity log events to CSV file

    $ActivityLogs = Get-PowerBIActivityEvent -StartDateTime $StartDT -EndDateTime $EndDT | ConvertFrom-Json

    $ActivityLogSchema = $ActivityLogs | `
    Select-Object `
    Id,CreationTime,CreationTimeUTC,RecordType,Operation,OrganizationId,UserType,UserKey,Workload,UserId,ClientIP,UserAgent,Activity,ItemName,WorkSpaceName,DashboardName,DatasetName,ReportName,WorkspaceId,ObjectId,DashboardId,DatasetId,ReportId,OrgAppPermission,CapacityId,CapacityName,AppName,IsSuccess,ReportType,RequestId,ActivityId,AppReportId,DistributionMethod,ConsumptionMethod, `
    @{Name="RetrieveDate";Expression={$RetrieveDate}}

    $ActivityLogSchema | Export-Csv $ActivityLogsPath
    Write-Host ('Created file {0}' -f $ActivityLogsPath)
    }

    use this code, replace username and password with yours and days with your requirement

    Thanks to GilbertQ for initial code.

     

    If this post helps, then please consider Accept it as the solution, Appreciate your Kudos!

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Thanks for the response, if you have loping code please send me otherwise is there any website I will find the code.

 

arvindsingh802's avatar
arvindsingh802
Community Champion
4 years ago

#2. Authenticate to Power BI
$PBIAdminUPN="userName"
$PBIAdminPW="Password"

$SecPasswd = ConvertTo-SecureString $PBIAdminPW -AsPlainText -Force
$myCred = New-Object System.Management.Automation.PSCredential($PBIAdminUPN,$SecPasswd)

Connect-PowerBIServiceAccount -Credential $myCred

$AuditType = 'PowerBIAudit'

$RetrieveDate = Get-Date
$RetrieveDate = $RetrieveDate.AddDays(-2) #replace -2 with days you want to extract

for($i = 0; $i -lt 1; $i++){ #repalce 1 with days you want
$RetrieveDate = $RetrieveDate.AddDays(+1)
$RetrieveYearStr = $RetrieveDate.ToString('yyyy')
$RetrieveMonthStr = $RetrieveDate.ToString('MM')
$RetrieveDayStr = $RetrieveDate.ToString('dd')

$StartDT = $RetrieveYearStr + '-' + $RetrieveMonthStr + '-' + $RetrieveDayStr + 'T00:00:00.000'
$EndDT = $RetrieveYearStr + '-' + $RetrieveMonthStr + '-' + $RetrieveDayStr + 'T23:59:59.999'

$datestring = $RetrieveDate.ToString('yyyyMMdd')
$ActivityLogsPath = ('D:\Audit\PowerBIAudit_' + $datestring + '.csv')

#4. Export out current date activity log events to CSV file

$ActivityLogs = Get-PowerBIActivityEvent -StartDateTime $StartDT -EndDateTime $EndDT | ConvertFrom-Json

$ActivityLogSchema = $ActivityLogs | `
Select-Object `
Id,CreationTime,CreationTimeUTC,RecordType,Operation,OrganizationId,UserType,UserKey,Workload,UserId,ClientIP,UserAgent,Activity,ItemName,WorkSpaceName,DashboardName,DatasetName,ReportName,WorkspaceId,ObjectId,DashboardId,DatasetId,ReportId,OrgAppPermission,CapacityId,CapacityName,AppName,IsSuccess,ReportType,RequestId,ActivityId,AppReportId,DistributionMethod,ConsumptionMethod, `
@{Name="RetrieveDate";Expression={$RetrieveDate}}

$ActivityLogSchema | Export-Csv $ActivityLogsPath
Write-Host ('Created file {0}' -f $ActivityLogsPath)
}

use this code, replace username and password with yours and days with your requirement

Thanks to GilbertQ for initial code.

 

If this post helps, then please consider Accept it as the solution, Appreciate your Kudos!

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Arvindsingh,

    If i run the above powershell script i am getting individual csv files with PowerBIAudit_respective dates, but my requirement is instead of creating individual csv files, i want single csv file with combined csv files with name as PBI_Audit_Logs.

    Thanks in Advance

    • lbendlin's avatar
      lbendlin
      Super User
      4 years ago

      You can use Powershell to combine CSV files

      Remove-Item $Target
Get-Content "$Path\$file1" | Add-Content $Target
Get-Content "$Path\$file2" | Select -Skip 1 | Add-Content $Target
Get-Content "$Path\$file3" | Select -Skip 1 | Add-Content $Target

       

      etc.

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        If you dont mine can u send me the combined powershell script to extract audit logs for more than 24 hours. Thanks in advance.

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Is there a way to call all objects instead of having to select specific ones?

     

    • lbendlin's avatar
      lbendlin
      Super User
      4 years ago

      no, but again you can loop through your list of objects, same as you loop through your list of days.