Computers without Antivirus

Introduction

If you have ever tried to create a report that displays items that don't have certain related items, then you know that this can be tricky and often require writing custom code. The mashup view feature in Explore Analytics provides an elegant solution to this problem and requires no special skills.

The desired view

In this example, we want a list of computers that do not have the antivirus software installed on them. If we wanted the list of computer that do have the software, it would be easy to get from a table of installed software, but the computers that do not have antivirus software are not in that table.

Outline of the solution

The solution is to create two lists and then create a mashup view of the two lists.

The first is the list of all the computers. This can be obtained from the configuration management table.
The second is the list of computers with antivirus software. This can be obtained from a table that holds information about installed software.

We then merge the two lists and sort it such that the computers without antivirus are listed first.

Using Pivot Views

We will create the desired list as a mashup view of two pivot views. The reason we're using pivot views is so that the rows will be merged together, and not just the columns. This is a key to solving our problem by merging together the two lists of computers such that we show each computer exactly once.

Listing Computers without Antivirus

Our first step is to create each input view. The mashup is created later, once the two views are ready.

The first view lists all computers. Remember that we use a pivot view to list the computers for the reason explained in the previous section.

We use the configuration item table. We filter to only show computers. We put the configuration item (computer) and asset tags fields in the rows. The result is a list of all the computers.

The second pivot view shows all the computers that have the antivirus installed. This view is based on the software usage table and selects software with the display name of "Antivirus".

Unlike the first view, the second view will show the count (typically 1, indicating that the antivirus is installed once per computer).

Creating the Mashup

Now that we have the two inputs, we can create the mashup view using the "New mashup view" dialog.

The result is shown below. When the views are merged, all the computers are listed. For computers with antivirus, the combined "Count" will be greater than 0. For computers without antivirus, the combined "Count" will be blank.

Before we sort the list by count, it looks like this