This discussion has been locked.

You can no longer post new replies to this discussion. If you have a question you can start a new discussion

SQL Query

We're currently migrating from on-premises Enterprise Console to Sophos Central.

I have previously found it very useful to be able to do a simple SQL query against the Sophos back-end database to gather info about the managed computers in our environment. I don't suppose there's any way of doing this against the cloud version is there?

Here's the query I've been running:

'SELECT Name,Description,LastMessageTime,Deleted FROM ComputersAndDeletedComputers WHERE Deleted=0'

Thanks.

This thread was automatically locked due to age.

0 jak over 8 years ago

One simple report is to visit the following page in say Chrome:

https://cloud.sophos.com/manage/smart-computers

Open the Developer Tools (F12), filter for "bulk" in the Network tab and refresh the page. This should show just queries to a URL such as:

https://dzr-api-amzn-eu-west-1-9af7.api-upe.p.hmr.sophos.com/api/user-devices/v1/bulk-endpoints?endpoint_type=computer&limit=5000

The Response tab for that has the RAW JSON. You can convert JSON to CSV with Powershell. The times are in Unix Epoch though so they would need to be converted.

Could provide something quick otherwise see: https://community.sophos.com/kb/en-us/125398 for more info.

Regards,
Jak
- 0 Andrew Johnson over 8 years ago in reply to jak
  
  Thanks for the reply Jak.
  
  It's PowerShell I'll be using and I've handled JSON before but do NOT have any "developery" background. I've made myself an API key but am struggling with what to do next. The API documentation only refers to events, alerts and migration data. I had a bit of a play after trying to make sense of some of the Python script but to no avail. Here is what I tried (API Key details removed):
  
  $headers = @{
  'Content-Type' = 'application/json; charset=utf-8'
  'Accept' = 'application/json'
  'X-Locale' = 'en'
  'Authorization' = 'Basic ***'
  'x-api-key' = '***'
  }
  $url = 'https://api1.central.sophos.com/gateway/api/user-devices/v1/bulk-endpoints?endpoint_type=computer&limit=200'
  Invoke-WebRequest -Headers $headers -Uri $url
  
  This gave the following error:
  "Authorization header requires 'Credential' parameter. Authorization header requires 'Signature' parameter. Authorization header requires 'SignedHeaders' parameter. Authorization header requires existence of either a 'X-Amz-Date' or a 'Date' header."
  
  Is there an easy way to do this? All I need is to occasionally query out a list of the computer details from there in whatever format so I can do further stuff with it in PowerShell.
  
  Thanks!