Recently I came across many issues in the production environment where hundreds of reports are running PowerBI Report Server 2016. The environment is up and running 24X7. Our users are located in multiple Geographies. The biggest challenge is that at a certain point in time (maybe in the middle of the night) they suddenly see an error popping up on the screen. And these errors can be very random and intermittent. So if you run the same report again the error may not come. How do we understand the root cause of the error and resolve it? These errors can be from SSAS cube or PowerBI itself and sometimes the main culprit may be SQL Server but unless we know for sure who is the real cause of the problem, we cannot take any action. So, there are multiple questions:
- How do we collect the error logs?
- Which tool is throwing this error?
- Is there any way to reproduce the error? Unless you know the issues you can not fix them.
Solution: The solution to this issue would be to collect the log at the time of the error. So first step would be to find the time when the error popped up on the user screen along with the error correct message. Also, note down the report name.
Steps to execute troubleshoot the error
- The best way to collect the log is LogParser Install the log parser on the Report server.
- Now create a PowerShell script as provided below.
$passwordFi1e = "C:\[path]\paswordfile.txt"
$username = "SERVICE ACCOUNT WHICH HAS A LOGIN PERMISSION ON THE REMOTE POWERBI SERVER IT SHOULD BE IN THIS FORMAT :DOMAIN NAME\USER NAME”
# First time create password file
if ( (Test-Path $passwordFi1e))
{
Read-Host -AsSecureString | convertfrom-securestring | out-file SpasswordFile
}
$password = cat $passwordFile convertto-securestring
$credential =New-object -typename System.Management.Automation.PSCredential -argumentlist $username $password
$servers = "HOST NAME OF THE SERVER WHERE POWERBI IS INSTALLED"
Invoke-Command -ComputerName $servers -Credential $credential -ScriptBlock {
$query =@"
select * into ERR0R.csv from 'C:\Program Fi1es\Microsoft Power BI Report Server\PBIRS\LogFiles\ReportingServicesService_2018_06_06*.log'
"@
$result = & 'C:\Program Files (x86)\Log Parser 2.2\LogParser.exe' $query -i:textline -o:CSV
$result
}
This is how this PowerShell script works
- First, it saves the password in the text file so you do not have to enter it multiple times.
- It connects to the remote server.
- Collect the log from the PowerBI Log file directory.
3. Now use this Powershell Script. This script will copy the file generated from the Log Parser tool:
$password = "C:\[path]\paswordfile.txt" $username = "SERVICE ACCOUNT WHICH HAS A LOGIN PERMISSION ON THE REMOTE POWERBI SERVER IT SHOULD BE IN THIS FORMAT :DOMAIN NAME\USER NAME" $password =Get-Content $passwordFile convertto-securestring -AsPlainText -force $credential= New-object -typename System.Management.Automation.PSCredential -argumentlist $username, $password $fileLIst = "File name used in the Above Script" #read-host | Out-File C:\share\pas.txt + $source = "c:\Program Files (x86)\Log Parser 2.2\" +$fileLIst # The destination location you want the file/folder(s) to be copied to $destination = "\\[YourMachineName]\Share" #This should be some shared path in your local system so program which is running the script can access it. $servers = "HOSTNAME OF POWERBI REPORT SERVER" Copy-Item \\$server\$source -Destination $destination -Verbose
There are many ways you can tweak this script. So if you want to generate all the Errors for a specific day you can tweak the query like this:
@”select * into ERR0R.csv from ‘C:\Program Fi1es\Microsoft Power BI Report Server\PBIRS\LogFiles\ReportingServicesService_2018_06_06*.log’ where text like ’%Error%’ “@
I hope this post helps!!