Interactive-Power-BI-Scripts/Activity-Log-View.ipynb

{
 "metadata": {
  "kernelspec": {
   "name": ".net-csharp",
   "display_name": ".NET (C#)",
   "language": "C#"
  },
  "language_info": {
   "name": "C#"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2,
 "cells": [
  {
   "cell_type": "markdown",
   "source": [
    "# Activity Log - View\n",
    "\n",
    "**Author**: Melissa Coates  \n",
    "\n",
    "**Last updated and tested**: September 29, 2022 \n",
    "\n",
    "**Purpose:** This notebook contains multiple types of ad hoc scripts to view the [Power BI activity log](https://docs.microsoft.com/en-us/power-bi/admin/service-admin-auditing):\n",
    "\n",
    "- View activity log: one type of activity, for one day\n",
    "- View activity log: all activities, for one user, for one day\n",
    "\n",
    "**Type of authentication**: You can log in with either:\n",
    "\n",
    "- A domain user account (requires Power BI administrator permissions), or\n",
    "- A service principal (with permission to run admin APIs)\n",
    "\n",
    "**How data is being accessed:** The notebook uses:\n",
    "\n",
    "- Power BI Management Module for authentication: Connect-PowerBIServiceAccount\n",
    "- Power BI Management Module for extracting the activity log data: Get-PowerBIActivityEvent\n",
    "\n",
    "**Type of script**: Interactive, ad hoc (non-scheduled). This notebook is a guided learning experience to get familiar with how to query the activity log. It's not a production-ready script. This script accompanies the Auditing & Monitoring content found in the [Power BI Deployment and Governance](https://www.coatesds.com/power-bi-deployment-and-governance) course. \n",
    "\n",
    "This notebook is not designed to do a 'Run all.' Rather, it's designed to be run cell-by-cell, based on the scenario. More information, including prerequisites, is included at the bottom of this notebook."
   ],
   "metadata": {
    "azdata_cell_guid": "d6b85f37-9b57-4549-a2f2-927bbf49e438"
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "## **Log in to the Power BI Service**\n",
    "\n",
    "* * *\n",
    "\n",
    "**Update before executing:**\n",
    "\n",
    "- **Type of authentication:** \n",
    "    - **Line 2 - Choice for type of auth:** Input either '**User**' or '**User\\_MFA**' or '**SP**' without the quotes. This tells the script whether or not to log in as a user or a service principal.\n",
    "        \n",
    "    - - **User**: If multi-factor authentication is not enabled for the user account. The notebook will prompt you to type in your password.\n",
    "            - **User\\_MFA**: If multi-factor authentication is enabled for the user account. You'll be directed to a web browser to complete the login.\n",
    "            - **SP**: If you're using a service principal.\n",
    "- **If using user authentication:**\n",
    "    - **Line 3 - Email address:** Input the email address for the user. This user must be a Power BI administrator.\n",
    "- **If using service principal authentication:**\n",
    "    - **Line 4 - Azure AD app ID**: Input the app ID (aka client ID). \n",
    "    - **Line 5 - Azure tenant ID**: Input the tenant ID (aka directory ID).\n",
    "\n",
    "**Interactive prompts:**\n",
    "\n",
    "The script will interactively prompt you for the sensitive information (because hard-coding of passwords and secrets isn't secure):\n",
    "\n",
    "- **If using user authentication:**\n",
    "    - It will ask for the user's password.\n",
    "- **If using service principal authentication:**\n",
    "    - It will ask for the app secret.\n",
    "\n",
    "**Comments:**\n",
    "\n",
    "- This script uses the PowerShell cmdlet: [<u>Connect-PowerBIServiceAccount</u>](https://docs.microsoft.com/en-us/powershell/module/microsoftpowerbimgmt.profile/connect-powerbiserviceaccount).\n",
    "- After signing in, the access token is active for one hour.\n",
    "- If you belong to more than one tenant, add the Tenant parameter (to Connect-PowerBIServiceAccount) to specify a tenant ID when you log in."
   ],
   "metadata": {
    "language": "dotnet-interactive.pwsh",
    "azdata_cell_guid": "e543543d-5964-4f86-9916-33864487c416"
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "#-----------------INPUT AREA--------------------------------------------\r\n",
    "[string]$TypeOfAuth = 'User'  #Options: User --or-- User_MFA --or-- SP\r\n",
    "[string]$DomainUserEmailAddr = 'user@domain.com'\r\n",
    "[string]$AzureADAppID = ''\r\n",
    "[string]$AzureTenantID = ''\r\n",
    "#-----------------------------------------------------------------------\r\n",
    "\r\n",
    "if ($TypeOfAuth -eq 'User')\r\n",
    "{\r\n",
    "    Write-Verbose \"Domain user authentication will be used.\" -Verbose\r\n",
    "    [securestring]$DomainUserPW = Read-Host -Prompt \"Input password for $DomainUserEmailAddr\" -AsSecureString\r\n",
    "    [pscredential]$CredentialObj = New-Object System.Management.Automation.PSCredential($DomainUserEmailAddr, $DomainUserPW)  \r\n",
    "    Connect-PowerBIServiceAccount -Credential $CredentialObj\r\n",
    "}\r\n",
    "elseif ($TypeOfAuth -eq 'User_MFA')\r\n",
    "{\r\n",
    "    Write-Verbose \"Domain user with multi-factor authentication will be used.\" -Verbose\r\n",
    "    Connect-PowerBIServiceAccount\r\n",
    "}\r\n",
    "elseif ($TypeOfAuth -eq 'SP')\r\n",
    "{\r\n",
    "    Write-Verbose \"Service principal authentication will be used.\" -Verbose\r\n",
    "    [securestring]$AzureADAppSecret = Read-Host -Prompt \"Input secret for $AzureADAppID\" -AsSecureString\r\n",
    "    [pscredential]$CredentialObj = New-Object System.Management.Automation.PSCredential($AzureADAppID, $AzureADAppSecret)  \r\n",
    "    Connect-PowerBIServiceAccount -Credential $CredentialObj -ServicePrincipal -Tenant $AzureTenantID\r\n",
    "}\r\n",
    "else \r\n",
    "{\r\n",
    "    Write-Verbose \"There's a problem. The Type of Auth variable input was $TypeOfAuth. Expected values are User or SP.\" -Verbose\r\n",
    "}"
   ],
   "outputs": [],
   "metadata": {
    "azdata_cell_guid": "a5d752f5-7d23-492a-b3a2-22b24e26881a",
    "language": "dotnet-interactive.pwsh",
    "dotnet_interactive": {
     "language": "pwsh"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "## **View Activity Log: One Type of Activity, One Day**\n",
    "\n",
    "* * *\n",
    "\n",
    "The next script will allow you to view the raw data for one type of activity, for a specific day. This is useful when you're looking for information about a particular type of event.\n",
    "\n",
    "**Update before executing:**\n",
    "\n",
    "- **Line 2 -** **Name of the activity:** This script displays data for one specific event, such as the 'ShareReport' or 'ViewReport' events. Use the \"Operation Name\" from this [reference](https://docs.microsoft.com/en-us/power-bi/admin/service-admin-auditing#operations-available-in-the-audit-and-activity-logs).\n",
    "- **Line 3 - **Date of the activity:** Format is YYYY-MM-DD. Data up to 30 days in the past may be retrieved.**\n",
    "\n",
    "**Comments:**\n",
    "\n",
    "- **Cmdlet used:** This script uses the PowerShell cmdlet: [<u>Get-PowerBIActivityEvent</u>](https://docs.microsoft.com/en-us/powershell/module/microsoftpowerbimgmt.admin/get-powerbiactivityevent).\n",
    "- **Time zones:** All activity events are based on UTC datetime.\n",
    "- **Date range:** Within a single execution of this cmdlet, the start and end datetime must be within the same UTC day."
   ],
   "metadata": {
    "language": "dotnet-interactive.pwsh",
    "azdata_cell_guid": "cc6d86bb-ef3e-47e1-aeb5-947575330afe"
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "#-----------------INPUT AREA-----------------\r\n",
    "[string]$ActivityType = 'ViewReport'\r\n",
    "[string]$ActivityDate = '2022-09-20' #YYYY-MM-DD\r\n",
    "#--------------------------------------------\r\n",
    "\r\n",
    "Get-PowerBIActivityEvent -StartDateTime ($ActivityDate+'T00:00:00.000') -EndDateTime ($ActivityDate+'T23:59:59.999') -ActivityType $ActivityType"
   ],
   "outputs": [],
   "metadata": {
    "language": "dotnet-interactive.pwsh",
    "azdata_cell_guid": "28e1313c-8f3f-4352-b239-77363d48e72b",
    "dotnet_interactive": {
     "language": "pwsh"
    },
    "tags": []
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "## **View Activity Log: All Activities, One User, One Day**\n",
    "---\n",
    "The next script will allow you to view all activities, for a specific user, for a specific day. This is useful when you're looking for information about a certain person.\n",
    "\n",
    "**Update before executing:**\n",
    "\n",
    "- **Line 2 - User who performed the activity:** This is the email address of the user who performed the activity.\n",
    "- **Line 3 - Date of the activity:** Format is YYYY-MM-DD. Data up to 30 days in the past may be retrieved.\n",
    "\n",
    "**Comments**:\n",
    "\n",
    "- **Cmdlet used:** This script uses the PowerShell cmdlet: [<u>Get-PowerBIActivityEvent</u>](https://docs.microsoft.com/en-us/powershell/module/microsoftpowerbimgmt.admin/get-powerbiactivityevent). \n",
    "- **Time zones:** All activity events are based on UTC datetime.\n",
    "- **Date range:** Within a single execution of this cmdlet, the start and end datetime must be within the same UTC day."
   ],
   "metadata": {
    "language": "dotnet-interactive.pwsh",
    "azdata_cell_guid": "8ac77514-c066-4261-850e-7e32839e4ff9"
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "#-----------------INPUT AREA-----------------\r\n",
    "[string]$UserWhoPerformedActivity = 'user@domain.com'\r\n",
    "[string]$ActivityDate = '2022-09-20' #YYYY-MM-DD\r\n",
    "#--------------------------------------------\r\n",
    "\r\n",
    "Get-PowerBIActivityEvent -StartDateTime ($ActivityDate+'T00:00:00.000') -EndDateTime ($ActivityDate+'T23:59:59.999') -User $UserWhoPerformedActivity"
   ],
   "outputs": [],
   "metadata": {
    "language": "dotnet-interactive.pwsh",
    "azdata_cell_guid": "e1eabad5-75d0-4aa3-a108-eb4793038533",
    "dotnet_interactive": {
     "language": "pwsh"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "## **Disconnect from the Power BI Service**\n",
    "\n",
    "* * *\n",
    "\n",
    "It's useful to add this at the end of automation scripts, as a matter of good housekeeping. If you don't log out (or refresh your access token), you'll be automatically disconnected after 1 hour."
   ],
   "metadata": {
    "language": "dotnet-interactive.pwsh",
    "azdata_cell_guid": "1ea49a6b-1eb0-443b-93a9-94dc8c08284c"
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "Disconnect-PowerBIServiceAccount"
   ],
   "outputs": [],
   "metadata": {
    "language": "dotnet-interactive.pwsh",
    "azdata_cell_guid": "56e947c3-0511-419a-b1cd-bb43f0fc9b03",
    "dotnet_interactive": {
     "language": "pwsh"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "## **More Information About This File**\n",
    "\n",
    "* * *\n",
    "\n",
    "## <span style=\"font-size:14px;\">Prerequisites for running this script:</span>\n",
    "\n",
    "- The [Power BI Management module](https://www.powershellgallery.com/packages/MicrosoftPowerBIMgmt) for PowerShell needs to be installed.\n",
    "    \n",
    "- The [.NET Interactive Notebooks](https://marketplace.visualstudio.com/items?itemName=ms-dotnettools.dotnet-interactive-vscode) extension needs to be installed and enabled in VS Code and/or Azure Data Studio.\n",
    "    \n",
    "- Either a user account with Power BI administrator permissions, or a service principal with permission to run admin APIs.\n",
    "    \n",
    "\n",
    "This script **interactively prompts** for the sensitive information to be passed into a credential object. Therefore, it's only useful for ad hoc purposes. A different technique is used for securely storing the credentials when a script is to be operationalized and scheduled.\n",
    "\n",
    "The original / most recent version of this notebook can be found on Melissa Coates' [Power-BI-Admin repository in GitHub](https://github.com/sqlchick/Power-BI-Admin)."
   ],
   "metadata": {
    "language": "dotnet-interactive.pwsh",
    "azdata_cell_guid": "9a4e2b5e-10e8-4ba3-85aa-353e2ebf432c"
   }
  }
 ]
}