How to Identify Missing and Active Tanium Clients

Introduction

This document describes a process by which you can compare a list of host names generated from one or more sources against a list of systems currently managed by Tanium. Instead of manually searching Tanium System Status for individual machine names, you can utilize Microsoft Excel to perform the analysis and quickly extract a list of unmanaged (or managed) endpoints.

For example, you could export a list of computers from an Windows Active Directory container and compare it against a list of Tanium managed endpoints. This solution supports lists containing up to 1,048,756 rows (current limit of Excel workbook rows). By using Excel, these comparisons can be completed quickly and efficiently. 

This process leverages an export of the Tanium System Status content and supports comparisons against the following criteria: Fully Qualified Domain Names (FQDNs); short names; or IP addresses. The Excel workbook utilizes variable names for sections of the Excel workbook and the formula uses VLOOKUP calls to perform the analysis. Once created, this workbook can be reused with future client data sets. You will simply need to export a current data set from Tanium System Status, and compare with your new data set. 

Exporting and Formatting System Status Data

In order to export data from Tanium, you will need sufficient privileges to access the System Status page. If you cannot access the Main Menu à Administration à System Status page in the Tanium console, you will need to request access from a Tanium administrator.

1.       Before exporting the data, ensure your user preferences are configured to export headers. In the upper righthand corner of the console, click on your username and select Preferences from the menu:

 

Figure       SEQ Figure \* ARABIC
   1    . Tanium Console User Preferences

Figure 1. Tanium Console User Preferences

 

2.       Check the box for “Copy/Export with headers by default

 

Figure       SEQ Figure \* ARABIC
   2    . Enable Copy/Export with headers by default

Figure 2. Enable Copy/Export with headers by default

 

3.       Once on the System Status page, modify the filter on the right side of the console. Change the filters to “30” and “days” instead of the default of “1” “Registration Intervals (4 minutes)”.

 

Figure       SEQ Figure \* ARABIC
   3    . Modify the filter interval

Figure 3. Modify the filter interval

 

4.       Export the file as a .CSV file.

 

Figure       SEQ Figure \* ARABIC
   4    . Export data as .CSV file   

Figure 4. Export data as .CSV file

 

 

5.       The export function will automatically name the file based on the time the export function was run. You can save the file with a different name.

 

Figure       SEQ Figure \* ARABIC
   5    . Export - File name Save As

Figure 5. Export - File name Save As

 

6.       The file will be downloaded via your browser. The .CSV file should be visible in your Downloads folder. Double-clicking the file should open it in Excel assuming Excel is installed on your workstation.

Once opened, immediately save the file as an Excel workbook (.xlsx extension)

 

Figure       SEQ Figure \* ARABIC
   6    . Open CSV file in Excel   

Figure 6. Open CSV file in Excel

 

Figure       SEQ Figure \* ARABIC
   7    . Save file as Excel Workbook (.XLSX)

Figure 7. Save file as Excel Workbook (.XLSX)

 

7.       Delete all columns after column C, leaving the Host Name, Network Location (from client), and Network Location (from server) columns.

8.       Move column A (Host Name) to column D.

9.       Create a new header for column A called “Short Name”

10.       We need a method to pull to the host “short name” from the list of fully qualified domain names in column D. This formula will calculate the short name for each “Host Name” found in column D.

Copy the following formula into cell A2:

=IF(ISNUMBER(FIND(".", D2)), LEFT(D2,FIND(".",D2) - 1), D2)

11.       Copy the formula all the way down for all remaining rows in column A.
Once completed, your worksheet should look like this:

 

Figure       SEQ Figure \* ARABIC
   8    . Insert Short Name Filter

Figure 8. Insert Short Name Filter

 

12.       Next, we will assign Excel names to sections of the worksheet. Once these sections are assigned names, they can be referenced by formulas on different worksheets of the workbook.

Highlight the entire table but exclude the header rows. In our example, cells A2 through D24 are selected. Once selected name the region REG_SHORT and press Enter to save the name.

 

Figure       SEQ Figure \* ARABIC
   9    . Worksheet Reference Assignment - REG_SHORT

Figure 9. Worksheet Reference Assignment - REG_SHORT

13.       We need to perform the same steps for 3 more worksheet regions. REG_CLIENT_IP will reference the range of cells in columns B through D. REG_SERVER_IP will be assigned to the data in columns C and D. Finally, the last section of data in Column D will simply be titled REG.

 

Figure       SEQ Figure \* ARABIC
   10    . Worksheet Reference Assignment - REG_CLIENT_IP

Figure 10. Worksheet Reference Assignment - REG_CLIENT_IP

Figure       SEQ Figure \* ARABIC
   11    . Worksheet Reference Assignment - REG_SERVER   

Figure 11. Worksheet Reference Assignment - REG_SERVER

 

Figure       SEQ Figure \* ARABIC
   12    . Worksheet Reference Assignment - REG

Figure 12. Worksheet Reference Assignment - REG

 

14.       Save the workbook.

IMPORTANT: It must be saved as an Excel workbook. A CSV file will not support the formulas and multi-worksheet solution described in this document.

Adding a List and Checking Names

The section describes how to crosscheck lists of IPs, hostnames, and short names against the Tanium data we imported into our workbook.

Note: The data you import here could be from any source including:

  • A list of systems from an Active Directory container (domain, site, organizational unit)
  • A list of systems dumped from a DNS zone
  • A manually created list of systems
  • A list of IP addresses handed out by a DHCP server
  • A list of systems from a query against an ITAM/ITSM system

Regardless of the data source, the data must be formatted consistently with one entry per line. You may need to format it before copying into Excel, or you could try importing the data directly into a worksheet and let Excel format it depending on the file delimiters.

Note: Do not mix and match data with different delimiter types in the same file. You can only specify one type of delimiter when importing a file into Excel. Excel will not prevent you from importing a mash up of different data types, but the data will not format correctly in Excel. 

In the first example below, we’ll be using a list of short names.

Short Name Lookup

1.     Copy the list of names that you want to crosscheck. In the example below, a list of server names was manually generated. Only some of the servers in the sample list have a client. Using the named Excel ranges we created previously, we’ll create a formula to perform lookups against the short names we generated in Step 11 of the previous section.

In the example below, a small data set of short names and IP addresses has been imported into a second worksheet called Azure (to simulate a list of Azure based systems).

Figure       SEQ Figure \* ARABIC
   13    . List of Azure Systems

Figure 13. List of Azure Systems

 

2.       In this first example, we’ll performing a lookup of short names against our existing Tanium data. Cut the IP Address column and paste into Column A of a third worksheet. Call this third worksheet IP Address. We’ll be using this list of IP’s in the second example.

3.       To perform the lookup of our imported data against the Tanium data, we will need to use a formula. Since the ResourceName data looks for short names, we will be looking for Clients using our REG_SHORT data that we created in Section 1.  If they were IP addresses, we could use REG_CLIENT_IP and/or REG_SERVER_IP.  

In column B, we perform the VLOOKUP calls. In cell B2, add this formula:

    =VLOOKUP(A2, REG_SHORT, 4, FALSE)

Rows returned with an "#N/A" means the short name lookup was unsuccessful for that host name.  If the lookup is successful, it will output the 4th column (that's what the "4" is in the formula) in the REG_SHORT name range, which is the FQDN that Tanium has recorded (i.e., what the Computer Name Sensor outputs).

*NOTE: If the data source contained fully qualified names and you just wanted to check against Computer Names, you would run the VLOOKUP against REG and not REG_SHORT.  If you use REG, you have to use "1" instead of "4".  If you use REG_CLIENT_IP,  you would use "3" 

4.       Copy the formula from cell B2 all the way to the bottom of your imported data set.

 

Figure       SEQ Figure \* ARABIC
   14    . Lookup Results

Figure 14. Lookup Results

 

5.       To quickly identify all systems that have a Tanium client, you can use the Excel data filter function.
     A. Select the Data tab of the worksheet
     B. Select column B so that it is highlighted
     C. Select the Filter button.

 

Figure       SEQ Figure \* ARABIC
   15    . Selecting the data to filter

Figure 15. Selecting the data to filter

 

6.       Select the filter button at the top of Column B. Clear the checkbox for #NA. Click OK. This will leave you with just the systems that have had active Tanium registrations in the last 30 days, in comparison with the list of systems you provided.

 

Figure       SEQ Figure \* ARABIC
   16    . Exclude #N/A Results

Figure 16. Exclude #N/A Results

Figure       SEQ Figure \* ARABIC
   17    . Filtered Results

Figure 17. Filtered Results

 

In this case, we've found 6 Tanium Clients out of 19 Azure VMs.

If you need to send this end result to someone, the best way is to copy that list of output (with or without the #N/A) and paste just values into a new Excel document and send that, so none of the System Status or formulas get sent too.  

IP Address/Name Lookup

In the previous section, we saved the IP addresses from our original list to new worksheet in our workbook. We’ll be using the IP Address worksheet for this example. If you are performing this step from scratch with just a list of IP addresses, copy the list of IPs that you want to crosscheck. In the example below, a list of server names was manually generated. Only some of the servers in the sample list have a client. Using the named Excel ranges we created previously, we’ll create a formula to perform lookups against the IP addresses we defined as the REG_CLIENT_IP in step 14 of the previous section.

1.       To perform the lookup of our imported data against the Tanium data, we will need to use a formula. Since the ResourceName data looks like short names, we'll be looking for Clients using our REG_SHORT data that we created in Section 1.  If they were IP addresses, we could use REG_CLIENT_IP and/or REG_SERVER_IP.  

In column B, we'll do the VLOOKUP calls. In cell B2, add this formula:

    =VLOOKUP(A2, REG_CLIENT_IP, 3, FALSE)

If you see an "#N/A", it means it wasn't successful in finding the short name.  If it finds a Client, it will output the 4th column (that's what the "3" is in the formula) in the REG_SHORT name range, which is the FQDN that Tanium knows about (i.e., what the Computer Name Sensor outputs).

 

Figure       SEQ Figure \* ARABIC
   18    . Lookup by IP Address   

Figure 18. Lookup by IP Address

 

Figure       SEQ Figure \* ARABIC
   19    . Filtered IP Address Results

Figure 19. Filtered IP Address Results