Forum Discussion

Olivierln's avatar
Olivierln
Frequent Visitor
2 years ago
Solved

Service statistic on Page usage

Dear colleagues,   My objective here is to build a report and semantic model that goes behind the classic "power bi usage reports".   As of know, we are using the following Activity Log (https://...
Need Help
Tips and Tricks
  • mariussve1's avatar
    mariussve1
    2 years ago

    Hi again,

    When you turn on logging to Log analytics workspace in Power BI it will log almost everything.

    You can customize the extract, so it returns exactly what you want. In my project I use this KQL:

    PowerBIDatasetsWorkspace
    | where TimeGenerated >= startofday(ago(1d)) and TimeGenerated < startofday(ago(0d))
    | extend ParsedContext = parse_json(ApplicationContext)
    | extend DatasetId = tostring(ParsedContext.DatasetId)
    | where isnotempty(DatasetId)
    | extend ReportId = tostring(ParsedContext.Sources[0].ReportId)
    | extend VisualId = tostring(ParsedContext.Sources[0].VisualId)
    | extend UserSession = tostring(ParsedContext.Sources[0].HostProperties.UserSession)
    | where isnotempty(UserSession)
    | extend DateTime = bin(TimeGenerated, 1m)
    | extend Date = format_datetime(TimeGenerated, 'yyyy-MM-dd')
    | summarize Sessions = dcount(UserSession) by
    Date,
    DateTime,
    PowerBIWorkspaceId,
    PowerBIWorkspaceName,
    DatasetId,
    ReportId,
    VisualId,
    ExecutingUser,
    UserSession

    Then you can connect this to the ActivityEvent API if you like. The KQL returns:


    As you can see, this returns one row for each sessionid pr visual id that the end user have visited 🙂 This is just what you want.

    Br
    Marius

Olivierln's avatar
Olivierln
Frequent Visitor
2 years ago

Hi, 
Thanks for you feedback. 
but here it seems like we are discussing the Metadata not the activity log ([email protected] views the page xxx of the report yyy), isn 'it ?

mariussve1's avatar
mariussve1
Solution Sage
2 years ago

Hi again,

When you turn on logging to Log analytics workspace in Power BI it will log almost everything.

You can customize the extract, so it returns exactly what you want. In my project I use this KQL:

PowerBIDatasetsWorkspace
| where TimeGenerated >= startofday(ago(1d)) and TimeGenerated < startofday(ago(0d))
| extend ParsedContext = parse_json(ApplicationContext)
| extend DatasetId = tostring(ParsedContext.DatasetId)
| where isnotempty(DatasetId)
| extend ReportId = tostring(ParsedContext.Sources[0].ReportId)
| extend VisualId = tostring(ParsedContext.Sources[0].VisualId)
| extend UserSession = tostring(ParsedContext.Sources[0].HostProperties.UserSession)
| where isnotempty(UserSession)
| extend DateTime = bin(TimeGenerated, 1m)
| extend Date = format_datetime(TimeGenerated, 'yyyy-MM-dd')
| summarize Sessions = dcount(UserSession) by
Date,
DateTime,
PowerBIWorkspaceId,
PowerBIWorkspaceName,
DatasetId,
ReportId,
VisualId,
ExecutingUser,
UserSession

Then you can connect this to the ActivityEvent API if you like. The KQL returns:


As you can see, this returns one row for each sessionid pr visual id that the end user have visited 🙂 This is just what you want.

Br
Marius