Technical Note: ZoneFox 3 - How to find the most commonly used applications in your organization (via the database)

[FortiKoala]

Description

How to find the most commonly used applications in your organization (via the database)

Scope

FAQ

Solution

The event data sent from the endpoint agents is stored in monthly user indices in Elasticsearch, which is the underlying database containing all the ZoneFox data.  If you have access to your Elasticsearch installation you can query an index directly to find the most common items recorded that month, including what users perform the most events, what machines are being used, the most commonly used applications, the most common action performed and on what resources.  To do this:

1. In a browser, connect to your Elasticsearch instance using the kopf utility, for example:  http://db.zonefox.example.com:9200/_plugin/kopf
2. Select rest on the toolbar
   
   
3. Enter the name of the index you want to query, for example for June 2017 this would look like
   and select POST from the dropdown list
   
4. In the body of the REQUEST enter the following query:
   

   {   "sort": [     {       "_version": "desc"     }   ],   "version": true,   "query": {     "bool": {       "must": {         "match_all": {}       }     }   },   "aggs": {     "evt_cdocs": {       "sum": {         "field": "_version"       }     },     "machines": {       "cardinality": {         "field": "m.original"       }     },     "users": {       "terms": {         "field": "u.original"       }     },     "app": {       "terms": {         "field": "app.original"       }     }   } }

   
   
5. Then click on the send button below the query text.

The output from this command will show the 10 most common events - based on the machine, user, application, action and resource, for example:

The output will also include the 10 most commonly occurring applications based on the document count:

You can then run a further query on a particular application to get the total event counter - which is a tally of how many times the same events using the application have occurred, i.e there will be one 'document' in the database for the temp1 user using explorer.exe to read the PersonnelA.xls spreadsheet, but that user may have done the same thing 8 times - so there will be 8 event timestamps recorded for that one document.

An example of that query is:

{   "sort": [     {       "_version": "desc"     }   ],   "version": true,   "query": {     "bool": {       "must": {         "term": {           "app.original": "explorer.exe"         }       }     }   },   "aggs": {     "evt_cdocs": {       "sum": {         "field": "_version"       }     },     "app": {       "terms": {         "field": "app.original"       }     }   } }

With the output containing: