Technical Tip: Exporting Endpoints from EMS and filtering columns with Python and Google Colab

Most of the time in ETAC, when encountering questions about this feature, questions were about how to collect FortiClient versions and when clients were last seen. Even when using filters in EMS, some organizations have over 1k+ endpoints and this can be difficult to read and use in EMS itself.

This may also be an easier way to simplify data than using Excel. Also, using Google Colab is a much simpler way to alter the data compared to installing jupyter notebooks. 

Related document:

Export endpoint information

Step 1: Export the CSV as in the documentation.

• The example information will be used with 'Boromir' and 'Legolas'.
• Copy/paste the information provided and save it as a CSV. 
• Open it in Excel, and the information is spread out and can be difficult to find:

Step 2: Google Colab and uploading the file.

• Go to https://research.google.com/colaboratory/
• Sign in with a Google account.
• Select 'New notebook':

• Select the 'folder' icon, 'right-click' the white space, and select 'Upload':

• It is possible to navigate to the CSV and then select 'Open'.

Step 4: Python.

• Import 2 libraries, OS and Pandas for this to work.
• OS will help to confirm that it can read the file, and Pandas will convert and display the data.
• Convert the CSV into a data frame, set the index to name (for organizational purposes).
• Select the desired columns (fct_version, last_seen - but can use any).
• Press the '>' (play button) icon on the left to run the code.
• To get a new cell, press '+ code':

Step 5: Exporting and redisplaying in Excel.

• There are two blue icons above, select the top one 'Convert this data frame to an interactive table'.
• Select 'Copy Table':

• Copy the table as CSV, paste and then save it to notepad as simplify.csv:

• Open in Excel and a much more easily manageable data set will be visible:

Note:

This solution is not supported by the TAC team. No further support or explanations will be provided by TAC.