Monitoring all of my process and queue items

Hi all,
I am having troubles monitoring all of my jobs and queue items.
I want to make a dashboard via power bi and i am looking for SQL queries that can help me monitor all the queue items i have left and the jobs that had errors etc…
Thank you all for your help!
Amit

Hi @Amit_Bendet,

From reading your query, I am assuming you have an on-premises installation of Orchestrator. If yes, the default logs will be written to you SQL database (UiPath), there you will find many tables, I cant remember how many, but there are a lot.

For example, there is one called Logs, one called Processes, one called Users etc. Each of these tables has a foreign key which you can use.

In PowerBi you can query individual tables either rom UI or PowerQuery with data source as SQL.
For example, we use Logs table to get logs written by our robots. The Logs table is quite easy

Three things to make the user experience smooth

  1. Do most of the processing already in SQL for each of the tables. PowerBI fetches the required data only
  2. Ensure the user login is a read only user in the database, that way your PowerBI dashboard is a consumer of the data
  3. You have to make a data source choice, direct query vs. download entire dataset (this can slow down your reports as the size of your SQL table will keep increasing). Some PowerBI report servers also have a limit of 1gb when you download the entire dataset. Direct query also has negatives, as each time the user wants to refresh, the data is polled from SQL and if the query results are large this can slow down your dashboard.

Hi @jeevith thank you for your quick replay!
I on using the on-premises installation as you mention above, I just need a sql queries so I can add the to the power bi.
Do you have something like that?

Hi @Amit_Bendet,

As I said, when you use data source as SQL you get to select specific tables from your UiPath database. You do not have to write queries to start with. PowerBI parses the table you chose and you can perform all transformations and load the data to the Data model, all from the UI of Power BI. So I do not think you need write any queries other than transforming the table in PowerBI. You will have access to foreign keys in the data model and can connect them to other tables in the data modelling phase in PowerBI. To get your dashboard up and running as the 1st version , I suggest you rely on PowerBI generic ETL flow with SQL as source, UI or PowerQuery as the transformation and loading.

But if you want to do the data processing already in a SQL table then yes, you will have to write queries to fetch values from multiple tables. This is not necessary and can easily be made in the next iteration of your dashboard.

@jeevith Ok I got you :slight_smile:
Isn’t running the query every 15 min will make a problem with the sql server?

I did not get what you mean here. PowerBI reports can be customized to refresh on schedule or refresh only when the user wants to refresh the dashboard.

In short

  1. If you use DirectQuery and use a refresh schedule or a button that works on a user input will trigger an update. This can happen at any period of time.
  2. If you chose to download the dataset, then you need to have periodic update of your dataset in your schedule
1 Like