Power your Puppet reports with PowerBI in 10 minutes

See more posts about: Tips & How To and Tools & Integrations

Every Puppet practitioner can appreciate the wealth of information stored inside PuppetDB. Although Puppet Enterprise console offers some insights into the data, we sometimes need rich dashboards, possibilities to correlate data about facts, nodes and reports and drill-down features. In short, we wish there were a way to unleash the full power of PuppetDB data to create situational awareness about our estate.

This article will show that PowerBI is a very powerful companion to PuppetDB. If you are already using PowerBI, you can create a PuppetDB report in about 10 minutes.

Ingredients

For this to succeed, you will need some working components:

  1. A functioning PuppetDB with data about your Puppet estate
  2. An url pointing to it and accessible from your machine
  3. A valid token to access it
  4. A Windows machine for running PowerBI Desktop
  5. The PowerBI desktop app free download from Microsoft
  6. Optionally, a PowerBI server or another means of publishing your report for others to access.

Ask your Puppet admin for the ingredients 1, 2 and 3.

(Note that gathering the prerequisites does not count towards the 10 minutes.)

Install and start PowerBI Desktop

PowerBI Desktop is the free app from Microsoft which is your PowerBI development environment. It allows you to specify and test queries for data, transform the data and build models and reports. Install the app from this link.

Create a model and import PuppetDB data

Reporting in PowerBI is a 3-step process:

  1. Identify sources of data (datasources) and create queries which get the data.
  2. Transform the data and create a PowerBI model of the data. This is arguably the most important step. If your model is consistent and clear, you can easily create various reports and dashboards based on the same model.
  3. Create the reports and dashboards.

PowerBI supports a large variety of datasource types. We will choose the "Web" type which allows us to access an arbitrary API endpoint for data ingestion.

  1. Click Get Data | More, and select "Web"
  1. We need to specify a PuppetDB authentication token, so we need to select "Advanced":
  1. Fill out the form like so to specify the PuppetDB facts endpoint - we are importing facts of all nodes:

Note that the option X-Authentication is not available in the list so you have to type it yourself. Make sure to spell it exactly as shown, including the capitalization.

  1. Press "OK".

If you are presented with a message like this it means that you need to add Puppet's CA to the certificate trust store of your machine:

Please see the appendix of this blog to read how this is done. For now we assume all went well (you will need to confirm another dialog or two) and you are presented with a screen like this:

This means that we have already imported our PuppetDB data! We will make sense of the data during the next steps.

Transform PuppetDB data

The current import is quite raw. PuppetDB API responses are JSON objects, in this case it's an array of JSON objects, showing up as a long list of "Record"s with no visible information. We need to transform this array of records into a table format with fields and columns.

Fortunately, the transformation capabilities is the secret sauce of PowerBI which hopefully makes my claim "10 minutes" feasible.

  1. To properly name our new datasource, change its name from "v4?query=facts{}" to "Facts" in the Properties in the Query Settings column on the right. This is not strictly necessary but you will thank me later.
  2. Click the "To Table" button in the top left corner to convert the list of records to a table. Click "OK" on the "To Table" dialog. You are greeted with the following:

You can see that the list of records has been converted to a table of records. This step has also been recorded in the "Applied Steps" window on the right - we can always walk through that list to replay or modify the transformation chain.

  1. The single column of records is still not as useful as it could be. Click on the two arrows (the "Expand Columns" operation) in the column title:
  1. Unselect "Use original column name as prefix" in the following window - we don't need any prefixes - and click OK to expand the column.
  1. This transformation (See how the "Expanded Column1" appeared in the Applied Steps?) has created 4 columns: certname, environment, name and value. Although promising, we see that every node has a separate row in the table for each fact. To have an easy to use model we would need a table where every node has one row and all the facts have their corresponding columns.
  1. Select the name and the value columns (click on the name column title followed by shift-clicking the value column title)
  2. Click the Transform menu and click the "Pivot Column" button. If you don't see the button title, click on the button with this icon: Pivot Column button
  1. Open "Advanced options" in the following dialog and select "Don't Aggregate" in the "Aggregate Value Function" dropdown. Press OK.
  1. We now have a table with one row per node and every fact has its respective column:

You can also remove or reorder columns as you see fit.

  1. Finally, choose File | Save and Apply to save the model you just created.

Congratulations! You have just created a model consisting of one data source called Facts. We could go ahead and add new data sources to the model, like Nodes or Reports, and create relationships between them, but we will leave that as an exercise for you, dear reader. You can find many PowerBI tutorials on YouTube, such as this video.

Create an interactive report

Now we have created a model, we can easily create a report using PowerBI Desktop.

Add a table

  1. Click on the "Table" icon table icon in the Visualizations panel - an empty table will appear on the canvas:
  1. Now, select the fields you want to see in the table. You see all the fields from your Facts model on the right. You can either click the fields one by one, or drag and drop them to the "Add data fields here" space in the "Values" section Visualization column. Your table will look something like this:
  1. You can rearrange columns by dragging them into a different order in the "Values" section.

Add a pie chart

  1. Make room on the canvas by dragging the table down so there is space above the table. Click on the empty space to make sure the table is de-selected. Note: this is an important step, if you omit this, the next widget will replace the table which is currently selected. If you are like me, you will make this mistake a lot. No worries, just press Ctrl-Z to go back a step and restore the table.

  2. Click on the pie-chart icon pie chart in the Visualization panel.

  1. A grey pie chart appears in the space above the table. Now drag the field "operatingsystem" both to the Details and Values area in the Visualizations panel. You will see a pie chart like this:

You can edit the appearance of the chart in the Format section (paint roller icon paint roller) of the Visualizations panel.

Interact with the report

The most interesting feature of the reports is, however, the interactivity and filtering. Because all of your visualizations are based on the same model, they are all connected.

Click on one of the operating system segments in the pie chart. You will see that the table now only shows the nodes with the selected operating system:

You can add other visualizations and also filter the report using all the available fields in the model using the Filters panel.

Save the report

You can save the model and the report in a ".pbix" file by choosing Save from the File menu. You can use that file for publishing the report in the PowerBI Report Server.

Conclusion

We have seen how to create a powerful interactive report using a PuppetDB facts query. We can use the report to discover patterns and correlations in our estate by filtering on interesting facts.

Possible next steps could be:

  1. Expand the model by adding new entities, for example Nodes or Reports. By adding node information we gain insight in things like the date of the last report timestamp and status so we can correlate that with the facts
  2. Publish the reports to Web if they are open to public
  3. Publish the reports to a PowerBI server in your organization
  4. Create a dashboard

Learn more

Appendix: establishing trust with your PuppetDB certificate

If your PuppetDB certificate is a self-signed one, PowerBI Desktop may not trust it out of the box. In that case you will see a dialog like this when you try to connect to PuppetDB to ingest data:

If this is the case, you will need to add the certificate chain of your Puppet CA as trusted CAs into your trust store on the machine you use PowerBI on.

How?

  1. Get the Puppet CA certificate chain from the Puppet admin. Note: typically you will get 1 .pem (text) file with two certificates in it. Using Notepad, make sure to split this into 2 text files with 1 certificate in each.

  2. Run the Microsoft Management Console and add the Certificates snap-in:

  1. Right-click "Trusted Root Certification Authorities" and select All Tasks | Import:
  1. Select "Local Machine" in the wizard that appears and click next
  2. Click Browse in the file dialog
  3. Select the text file of one of the certificates and click Next
  4. Click Next on the trust store selection dialog
  1. Finally, click "Finish" on the last dialog:
  1. Repeat the above process for the second Puppet root certificate.

After you have added both certificates from the Puppet root certificate chain, your PowerBI desktop should not have any problems connecting with PuppetDB.

Puppet sites use proprietary and third-party cookies. By using our sites, you agree to our cookie policy.