Tableau Workbooks

Tableau Desktop and Tableau Server are powerful tools that help you present the valuable data that BigFix generates in a meaningful and ingestible way. This chapter describes how to add an existing Tableau Workbook to your BigFix Insights implementation.

Before you begin

The following are the prerequisites to generate Tableau Workbooks:
  • Purchase appropriate licenses for Tableau. You need a Creator license to run the tasks listed in this topic.
  • Tableau Desktop and Tableau Server must be at version 2019.2 or greater to support the featured utilized within the provided Workbooks
  • Install the Tableau Server with access to the BigFix Insights database.
  • Install the Tableau Desktop with access to the BigFix Insights database.
  • Setup BigFix Insights.
  • Add a datasource to BigFix Insights.
  • Run an ETL and ensure that it is successful.

About this task

BigFix Insights include 4 Tableau Workbook samples:
  • Patch Rhythm
  • Device Inventory
  • Operating System Migrations
  • Deployment Progress

Procedure

  1. Within BigFix, take action on the appropriate Fixlet(s) located in the Insights' external site. These Fixlets download the necessary .sql and .twb files to your desired location.
    1. BigFix Insights – Tableau - PatchRhythm
    2. BigFix Insights – Tableau - DeviceInventory
    3. BigFix Insights – Tableau - Operating SystemMigrations
    4. BigFix Insights – Tableau - DeploymentProgress
  2. Open Microsoft SQL Server Management Studio (SSMS) and connect to the BigFix Insights database.
  3. Build the required Stored Procedures. You can build the required Stored Procedures as described below:
    1. Each sample report comes with 1 or more .sql files.
      • Patch Rhythm contains PatchRhythm.sql and PatchRhythm_1.sql
      • Device Inventory contains DeviceInventory.sql.
      • Operating System Migrations contains OperatingSystemMigrations.sql, OperatingSystemMigrations_1.sql, OperatingSystemMigrations_2.sql, OperatingSystemMigrations_3.sql, OperatingSystemMigrations_4.sql, and OperatingSystemMigrations_5.sql.
      • Deployment Progress contains DeploymentProgress.sql, DeploymentProgress_1.sql, and DeploymentProgress_2.sql.
    2. Open the .sql files in SSMS.
    3. Choose one of the open tabs in SSMS, select your BigFix Insights database in the Available Databases drop-down and click Execute.
    4. Repeat step 3 for each open tab.
      The Microsoft SQL Server setup is completed.
  4. Open the Tableau Workbooks. Open one of the .twb files in Tableau Desktop. The authentication dialog for the SQL server is displayed:

    If another dialog box is displayed, there is also an Edit connection link on that dialog box as well.

    Now you need to edit the connection settings for the Workbook to function correctly.

    1. Click Edit connection on the top right corner.
      The following dialog is displayed.

    2. Change the following four items that match your environment:
      Server
      Enter the IP address or the DNS name for your Microsoft SQL Server. HCL has provided all the Tableau Workbooks with the common DNS name of bigfix.insights.database for your convenience. You can either add this DNS record to your DNS servers or add it to the local HOSTS file on the computer on which you are running Tableau Desktop.
      Database
      Enter the name of the BigFix Insights database. This is the default database name used by WebUI when setting up BigFix Insights.
      Username
      The Tableau Workbooks provided by HCL utilizes SQL authentication. If you are using SQL authentication, enter the username.
      Password
      If you are using SQL authentication, enter the password.
      Note: If Windows Authentication is enabled on the SQL Server and the user running Tableau Desktop has access to the BigFix Insights database, then change the authentication to Use Windows Authentication (preferred). When publishing your workbooks to the Tableau Server, Tableau Server uses the Windows account by default to access your SQL Server. While using the Windows Authentication and if the Tableau Desktop user account is different than the user account you are accessing the SQL database with on the Tableau Server, please change the username and password manually from within the Tableau Server after publishing the workbook.
      After successfully completing the above data entry items, click Sign In. The report will continue to load. You are now ready to publish your workbook.
  5. Execute the following steps to publish your workbook:
    1. Open your Tableau Desktop and click Server > Publish Workbook
    2. The Publish Workbook to Tableau Server dialog is displayed:
    3. Select a project into which you want to publish the workbook.
    4. Select a name for the workbook.
      Note: If a name already exists, enter a new name. Publishing a workbook with a name that already exists prompts you to decide whether to overwrite the existing workbook.
    5. Click Publish.
      Once the workbook is published successfully, a web browser opens (you will be logged into Tableau Server automatically) to the newly published workbook for viewing.

Using the Stored Procedure samples

About this task

The Stored Procedures provided utilize a number of custom attributes that you define to suite your needs. For details, see Adding and editing custom attributes.

Procedure

  1. All of the Stored Procedures require a time zone attribute. This allows your reports to be displayed in the time zone of your choice.
    Attribute Name = time zone and Attribute Value = Pacific Standard Time. Choose your desired time zone from the list of available time zones supported by your version of Microsoft SQL Server. If a time zone is not provided, the reports defaults to UTC time zone.
  2. The default severity order for the Patch Rhythm report is the number of relevant patches per severity in descending order, then by the name of the severity in alphabetical order. You could customize the sort order by creating one or more custom attributes.
    Attribute Name = Critical and Attribute Value = 1 and Category = severity_order_by. The Category is required by all applicable entries in the custom attributes table to let the Stored Procedure determine which entries to read. Critical identifies the severity provided by BigFix to assign this sort order value. The 1 represents the value the Stored Procedure will use to perform the sort. The lower the value, the higher the severity that shows up in your list of severities. You can assign the same value to multiple severities. The Stored Procedure then drops down to sorting alphabetically for those severities.
  3. The Patch Rhythm report also supports renaming severities from the default name. A vendor may label their severities one way, where another vendor labels their severities in another. This custom attribute allows you to fix the issue.
    Attribute Name = n/a, Attribute Value = N/A and Catetory = severity_rename. The category is required by all applicable entries in the custom attributes table to let the Stored Procedure determine which entries to read. In this example, change the label n/a to N/A for readability purposes. If this entry is not added to the custom attributes table, then you would see two rows representing n/a and N/A as separate severities. Adding this custom attribute combines these two values into a single row.
  4. The Device Inventory report supports shortening long operating system names.
    Attribute Name = Win10 10.0.18362.592 (1903), Attribute Value = Win10 (1903) and Category = os_shortener. The Category is required by all applicable entries in the custom attributes table to let the Stored Procedure determine which entries to read.
  5. The Device Inventory report also support grouping operating systems into appropriate Families.
    Attribute Name = Win10 (1903), Attribute Value = Windows, and Category = os_families. The Category is required by all applicable entries in the custom attributes table to let the Stored Procedure determine which entries to read. Based on this entry, the Stored Procedure assigns the shortened operating system name to a Windows grouping. You would repeat this entry for Linux, Mac OS X, and/or any other operating systems your BigFix environment supports.
  6. The Operating System Migrations report also utilizes the os_shortener and os_families custom attributes.
  7. The Operating System Migrations report includes the support for defining supported and non-supported operating systems. By default, this Stored Procedure looks at the Fixlets for unsupported operating systems and check applicability to determine if an operating system is supported by the vendor or not. Currently, these Fixlets are only associated with Microsoft operating system. You can define your own vendor support status utilizing the following custom attribute.
    Attribute Name = Win7, Attribute Value = 0, and and Category = os_vendor_support. The Category is required by all applicable entries in the custom attributes table to let the Stored Procedure determine which entries to read. If your operating system matches Win7, then this report displays Win7 as not supported by the vendor. This example assumes you have [os_families]Win7 defined. The day Windows 7 was listed as end-of-life, BigFix did not have a matching unsupported Fixlet so that the Stored Procedure could mark all Windows 7 devices as not supported. Adding this custom attribute allowed the report to display all Windows 7 devices as not supported. Currently, BigFix does include the unsupported Fixlet for Windows 7, so this custom attribute for that purpose is no longer necessary. If you set the Attribute value to 1, then the operating system is marked as supported, even if there is an existing unsupported Fixlet that is applicable. This would be used if you have purchased extended support from Microsoft for Windows 7.
  8. The Deployment Progress report also utilizes the os_shortener and os_families Category entries.
  9. The Deployment Progress report includes the support for identifying which one of the 30+ Action Result Statuses are deemed Success, Pending, or Failure. If you do not predefine these statuses, then the Action Result is labeled as [unknown].
    To view the complete list of all possible Action Result statuses and their descriptions, view the action_state_strings table in the BigFix Insights database. All IDs less than 0 are automatically defined as Failure.
    • Attribute Name = Success, Attribute Value = 0|3|5|17, and Category = action_result_status. These Action Result Status Id’s are defined by HCL as Success in this Stored Procedure. You should see four different IDs delimited by a pipe character, “|”. You can modify these IDs as you see fit. Each Id represents the status a device has reported back to the BigFix server during an Action Deployment.
    • Attribute Name = Pending, Attribute Value = 1|2|6|7|8|9|10|11|12|13|14|15|16|19|20|21|22|24|25|27|28|29|30|33|34|35|36|37, and Category = action_result_status. These Action Result Status IDs are defined by HCL as Pending in this Stored Procedure.
    • Attribute Name = Failure, Attribute Value = 4|18|23|26|31|32, and Category = action_result_status. These Action Result Status IDs are defined by HCL as Failure in this Stored Procedure.