Shodan Add-On for Google Spreadsheets

I like to periodically do Shodan searches on a variety of services and see what sort of organizations run them the most, which ports are popular or see which version of Apache dominates the market (“2.2.3” is leading at the moment for Apache while “6.0” is the preferred choice for Microsoft IIS). For the most part that involves writing a script in Python to access the API, output a CSV and then import that into Google Spreadsheet to create pretty charts. I finally got tired of having to import CSV and decided that it’s time to integrate the ability to search Shodan into Google Spreadsheet. And it turns out that’s fairly easy to do! They have a simple Javascript API to create new macros and it took just a few minutes to get an initial prototype working. If you want to skip ahead, you can check out the code and instructions at https://gist.github.com/achillean/8367958

There are 3 new macros that the Shodan add-on for Google Spreadsheets provides:

SHODAN_COUNT

SHODAN_COUNT lets you get the total number of results for a given search query. For example:

=SHODAN_COUNT(“microsoft-iis/6.0”)

The above macro will fill the current cell with the total number of results of web servers running on Microsoft-IIS 6.0. The code that provides this macro is below:


/**
 * Search the Shodan database using the given query. Returns the number of matches.
 */
function SHODAN_COUNT(query) {
 var API_KEY = 'YOUR API KEY';

 var url = 'https://api.shodan.io/shodan/host/count?key=' + API_KEY + '&query=' + query;
 var response = UrlFetchApp.fetch(url);
 var data = Utilities.jsonParse(response.getContentText());

 return data.total;
};

SHODAN_FACET_KEYS and SHODAN_FACET_VALUES

These 2 macros are very useful, but they’re slightly awkward to use. I couldn’t find a way for a macro to create 2 columns/ rows so I had to separate the operation into 2 macros instead of just 1.

A facet provides you with aggregate information about a property. In practical terms, this is what gives you the Top 5 Countries, Most Popular Web Servers and other similar breakdowns. For example, try running a search such as:

=SHODAN_FACET_KEYS(“mongodb”, “domain”, 20)

This will provide you with a breakdown of the Top 20 Domains running MongoDB, though the above will only return the name and not the number for each domain. To do that you will need to use the other macro called SHODAN_FACET_VALUES:

=SHODAN_FACET_VALUES(“mongodb”, “domain”, 20)

The parameters for both macros should always be identical. If you enter the first macro into one cell, and the 2nd macro into the cell below then you will end up with 2 rows that contain a breakdown which you can then easily convert into a chart.

Below is the relevant code that provides the 2 macros:

/**
 * Return the names of the facet values for a given search. Optionally, you can also provide a total number of
 * facet results that should be returned. Use this method alongside SHODAN_FACET_VALUES to get the corresponding
 * values for a facet name. Most filters can be used as facets.
 */
function SHODAN_FACET_KEYS(query, facet, count) {
 var API_KEY = 'YOUR API KEY';

 if (!count) {
 count = 10;
 }

 var url = 'https://api.shodan.io/shodan/host/count?key=' + API_KEY + '&query=' + query + '&facets=' + facet + ':' + count;
 var response = UrlFetchApp.fetch(url);
 var data = Utilities.jsonParse(response.getContentText());

 var values = [];
 for (var i = 0; i < data.facets[facet].length; i++) {
 values.push(data.facets[facet][i].value);
 }

 return values;
};

/**
 * Return the values of the facet values for a given search. Optionally, you can also provide a total number of
 * facet results that should be returned. Use this method alongside SHODAN_FACET_KEYS to get the corresponding
 * names for a facet value. Most filters can be used as facets.
 */
function SHODAN_FACET_VALUES(query, facet, count) {
 var API_KEY = 'YOUR API KEY';

 if (!count) {
 count = 10;
 }

 var url = 'https://api.shodan.io/shodan/host/count?key=' + API_KEY + '&query=' + query + '&facets=' + facet + ':' + count;
 var response = UrlFetchApp.fetch(url);
 var data = Utilities.jsonParse(response.getContentText());

 var values = [];
 for (var i = 0; i < data.facets[facet].length; i++) {
 values.push(data.facets[facet][i].count);
 }

 return values;
};

Installation

  1. Create a Google Spreadsheet that you want to use for Shodan research
  2. Click on “Tools > Script Editor” in the menu
  3. A new tab will be opened
  4. Click on “Spreadsheet” on the left side
  5. Copy the code located at: https://gist.github.com/achillean/8367958
  6. Paste the code into the script editor
  7. Click on “File > Save” or “CTRL + S” to save the code
  8. Done! You can now use the Shodan macros inside your Shodan research spreadsheet

I hope these macros will be useful to others, and if you have ideas for new macros or other integrations that you’d like to see please let me know!

PS: I recommend trying out the new Google Sheets, it’s much nicer to work with.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s