Insights 21.10+ Dashboards Show "Trouble Loading Data"

News Knowledge Base
, , , , , , , ,
1

Resolution when Insights 21.10+ dashboards show "Trouble loading data" .

Issue Description: Insights 21.10+ dashboards show "Trouble loading data" .

Root Cause: This issue can happen in few scenarios but it means that the looker backend of Insights is not able to fetch data. In most scenarios this means something related to the SQL connection from the looker container to the database is failing.

Diagnosing

  1. Login to looker UI. See the sections at the bottom for how to do this. Note: If the browser displays a warning related to the certificate when access the Looker UI, then go to the section: Insights Front End Not Loading Data. It most likely means the computer accessing Insights does not trust the certificate used for the SSL connection on the backend.
  2. After logging in, go to the admin controls -> Database connections
    1. For 22.4+ this is located on the left side of the landing page.
image.png
  1. After selecting Admin, scroll down to the section 'Database'
  2. In this section will be a text for 'Connections'. Click this.
    • For 21.10 the Admin control will be on the top ribbon.
image.png
  1. In the drop down menu that appears, scroll down to the section 'Database'
  2. In this section will be a text for 'Connections'. Click this.
  1. Once the connections page loads, there will be a connection called 'insights_connection'. Under the column 'Actions' there will be a test button, click this.
image.png
  1. Observe the results of the test. Most likely it will fail. Take a screenshot of the results to share with our support team if needed.
  2. Click the 'Edit' button. This will load a page containing the connection properties. Take a screenshot incase our support team requests that information.
  3. If the test succeeds, go to the Section Insights Front End Not Loading Data.
  4. If the test failed, address the error message by how it presented itself. A few notes:
    1. Make sure the SQL server name uses the FQDN. If it is not using the FQDN the domain will not be resolvable because in the /etc/resolv.conf no search field is defined.
    2. When the Insights Frontend starts up (the windows component) it configures the SQL connection for looker. That means that if using SQL Authentication, the looker connection should always have the exact same credentials as the windows component. On the Insights Machine, if using SQL Authentication, try doing an IISRESET. This will set the looker password back to what is used by the windows components.
    3. If the error message is something like, "Cannot connect: connection refused: Java::ComMicrosoftSqlserverJdbc::SQLServerException: The TCP/IP connection to the host orchestrator.local, port 1433 has failed. Error: "connect timed out. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall." go to the section Debugging Connection Refused
  5. If the error is not clear and is not a connection refused, open a ticket with UiPath. Make sure to share the screenshots related to the connection test (both the results of the test and the configuration).

Insights Front End Not Loading Data

  1. If the database connection succeeded but data is not loading, it most likely means that the certificate on the Linux backend is not trusted by the computer that is accessing Insights.
  2. To validate this do the following (NOTE: This step is only relevant for 22.4+):
    1. On the machine where data is not loading, try going to the following URL: https://:9999, where is replaced by the looker DNS name.
    2. If a certificate warning is displayed, take a screenshot to share with support. The message displayed by a browser are usually pretty clear, so address the issue as it presents itself.
    3. Additionally, see if the issue happens when logging into the Insights portal from the Insights Windows Machine. The Insights installer validates the certificate is trusted before installing so its unlikely the issue would occur on the Insights Windows machine.
    4. If there is a certificate warning and more help is needed in addressing the issue, open a ticket with UiPath and take a screenshot of the warning.
    5. To change the Insights certificate in 22.4+ see: Modifying Insights Configurations
  3. Make sure that Orchestrator is writing to the database that Insights is fetching data. This can be done by comparing the connection strings used by each application.
    1. Validating Orchestrator connection string.
      • On the Orchestrator server, open the file: "C:\Program Files (x86)\UiPath\Orchestrator\UiPath.Orchestrator.dll.config"
      • Under the section 'connectionStrings' look for the connection string 'Insights'. (Just search for '
      • See How To Generate A HAR File
      • Make sure to enable the trace and then reload the page where the error is seen
      • Once this is done, in the debug console, go to the tab called 'Console'. Take a screenshot of the console screen (make sure to capture any error messages).
      • Once this is done, open a ticket with UiPath. Make sure to include:
        • The har file.
        • Screenshot of Console tab.
        • Screenshot of the test results and the connection settings.
        • Event logs from the Insights Windows Server.

Debugging Connection Refused

  1. If using 21.10.X and Windows Authentication for SQL, then the issue is a Kerberos connection issue. Go to the section Debugging Kerberos SQL Authentication.
  2. If using 21.10 with SQL Authentication, open a ticket with UiPath. It might be that security software is specifically blocking the outbound connection of the the docker container that looker runs in. It would be worth while to check with your security team. If the frontend loads, it means that specifically the docker components are being blocked.
  3. For 22.4, when using SQL authentication, if the looker instance cannot connect, it is highly likely that its being blocked by the network. There are a few checks that can be done:
    1. Login to the Linux machine.
    2. First check that the iptables are not blocking the connection.
      • Run: sudo iptables -S OUTPUT
      • It should return "-P OUTPOUT ACCEPT"
      • If the output is different, make a note but continue with the next steps. It could be that your linux admin needs to make some configuration changes to the server.
    3. Install the preferred TCP debugging tool (we will use nc in this example)
      • For installing netcat run: sudo yum install nc
    4. Run: nc -v
      • i.e. nc -v sql.test.uipath 1433
    5. If the connection is successful it should say: Ncat: Connect to :
    6. If it does not connect, it means that the connection is blocked by the network or some other mechanism, but that it is not an application issue. At this point, reach out to your network and linux admins and let them know that the linux server cannot connect to SQL. They should be able to diagnose the issue. Make sure to share the results of the above tests.
    7. Finally, if the connection does succeed, open a ticket with UiPath. Include the following:
      • On the Linux machine run and capture the output of the command: sudo docker info
      • On the Linux machine run and capture the output of the command: sudo iptables -L
      • Share the results of the netcat test.
      • Share the results of the connection test.

Debugging Kerberos SQL Authentication

  1. If Kerberos authentication is failing to the SQL server, the error will present itself as connection refused message, which can be misleading.
  2. It is worth checking if the Service Account password recently changed. If it did, the Windows password needs to be updated using the installer. See Modifying Insights Configurations ( Make sure to look at the docs specific to your version)
  3. The first step in diagnosing the issue is to verify the SPN of the SQL server, matches what is being used by the connection string.
    1. To help explain these steps, we will use an example scenario where in the looker UI we saw the SQL connection defined as: Remote Host: insights.uipath.devtest, Port:1433, Database: UiPathInsights
    2. Looker UI will construct the SPN used to connect to the DB as follows (these settings come from the insights_connection that was captured in earlier steps):
      • MSSQLSvc/:
      • i.e. MSSQLSvc/insights.uipath.devtest:1433
    3. On a windows machine (it will be easiest to use the Insights Windows Machine) run the command: setspn -l (or if the sql server runs as a service account, but the service account name. i.e. setspn -l UIPATH\svc-sql)
    4. The above command should return SPNs registered for the SQL server. Verify an SPN that matches the SPN being constructed by looker. Using the example scenario it is expected to see the following:
image.png
  1. If the results of setspn -l, do not return the SPN being used by the values defined in the looker UI, then connection will not succeed. So using above example, if the Remote host is changed to Insights the connection would fail, because now the SPN would be MSSQLSvc/insights:1433, which is not listed in the results.
  2. If setspn -l does not return the expected SPN because looker UI is not using the FQDN of the server, then modify the installation so that the insights components are using the FQDN of the SQL server. See Modifying Insights Configurations (Make sure to look at the docs specific to your version)
  3. If setspn -l does not return the expected result, the spn can be added. See Register A Service Principal Name For Kerberos Connections
    • SPN registration should be automatic. This scenario might be encountered when the automatically registered SPN does not match what is expected. Adding additional SPNs is perfectly safe (as long as the connection string is correct)
  4. If the SPN is correct, then either, there really is a connection issue (for which see the section Debugging Connection Refused) or there is some other Kerberos related error. At this point open a ticket with UiPath.

Accessing the backend 21.10 MSI

  1. Login to the Insights server. (Its best to login as the account that installed Insights)
  2. Open powershell as an admin.
  3. Run the following command: Unprotect-CmsMessage -Path "$HOME\_insights\LookerSecret"
    1. This will fetch the login password for the backend.
    2. If the path is not found, then it could be that the user who is running the command did not install Insights.
    3. If the path is not found, use the following command to find where the location of the LookerSecret is located (ran from powershell): docker inspect -f '{{ (index .Mounts 0).Source }}' looker-container
    4. Replace the '$HOME\_insights' with the path returned by the command.
      • i.e. If the returned value were: c:\users\admin_1\_insights
      • Then the password fetch command would become: Unprotect-CmsMessage -Path "c:\users\admin_1\_insights\LookerSecret"
  4. The above command will return two values, we want the UiPathInsightsLookerAdmin value. Copy the password that is returned (it will always start with 1qW@)
  5. Copy the password in the password manager or password storing solution.
  6. Go to the following URL: https://:9999/login replacing with your Insights URL.
  7. This will take us to the login page. The user name is: admin@uipath.com and the password is the password returned from the powershell command.
  8. Its recommend that the backend password gets saved somewhere for quick access in the future.

Accessing the backend 22.4+

  1. Before going through these steps, ensure to know what the pass phrase is for the GPG key (this was generated during installation and should have been recorded).
  2. Login to the Looker backend Linux machine.
  3. Either make sure to login as the person who did the install, or switch to the user who did the install.
    1. Switching users can be done with the command: su
    2. Or if the password is not known, use sudo su
  4. If unsure of which user installed Insights, use the following command to find the user: sudo docker inspect -f '{{ (index .Mounts 0).Source }}' looker-container
    • For example if the command returned: /home/admin_1/insights, the user who installed insights would be admin_1. So we would switch to that user with sudo su admin_1
  5. Run the following command to get the password: pass ls "Insights/looker-password" | sed 's/^/1qW@/'
    1. When running the command, a prompt for the GPG pass phrase defined at installation time is observed
    2. The sed command adds the string 1qW@, which is added during install time to satisfy password requirements
  6. Copy the password in the password manager or password storing solution
  7. Go to the following URL in a browser: https://:9999/login, replacing with the FQDN of the Linux instance (or whatever DNS name that was used for the linux engine). If the linux DNS name is not known, do the following:
    1. On the Linux machine run: hostname -f. Try the returned value. This may not work if a load balancer is used or if using a custom domain
    2. If it does not work, then login to the Insights Windows Server and open the file: "C:\Program Files (x86)\UiPath Insights\Insights\appsettings.json". In the file search for the string: "embed_url". The defined value is the looker URL.
      • For example, in our test environment search for embed_url goes to a line saying: "embed_url": "https://looker.local:9999". The url would be looker.local.
  8. Once on the login screen for looker, use the recorded password and the username admin@uipath.com.

Accessing the Backend AutomationSuite

  1. Login to one of the Server nodes of Automation Suite.
  2. Enable kubectl: Accessing Automation Suite- Enabling-kubectl
  3. Run the following command:
  • kubectl -n uipath get secrets insights-generated-secrets -o "jsonpath={.data['LookerPassword']}" | echo $(base64 -d) | sed 's/^/1qW@/'
  1. The returned value is the password.
  2. Go to the URL: https://insights./login , where is the base URL for automation suite. (i.e. https://insights.automationsuite.uipath.com/login).