Browse Live Discover and Response Queries by Category
Approved

This query is one you are recommended to read the full content of the post to use. It is not simply a copy and paste query, like others in the forum. It is valuable in the right situation 

Live Discovery - Need help to get current IP address

Hi, need some help on creating a query that will show me the current IP address the machine is connecting from.

Is there any nice easy way of doing this?

I've tried with: interface_addresses.address Network_IP,

But that returns the IP for all existing interfaces, not just the one that the machine is connected from.

Thanks in advance!

Diego Tavolari
  • in reply to Rutvij

    Make sure there is no error in typing, There will be a column name for that if you can just check https://osquery.io/schema/4.6.0/#interface_details 
    It works for me

  • in reply to Rutvij

    Errors out: no such column connection_status :(

  • If you're in need of extracting the External IPv4 address that your machine is masquerading behind you can use the following:

    DO NOTE:  You will need to sign up for a FREE API key to use this service, I will not reveal my actual API key.  Put your key where I have said Key_Goes_Here

    SELECT
json_extract(curl.result,'$.continent_name') continent,
json_extract(curl.result,'$.country_name') country,
json_extract(curl.result,'$.region_name') region,
json_extract(curl.result,'$.city') City,
json_extract(curl.result,'$.ip') Ext_IP,
json_extract(curl.result,'$.type') IP_Type
FROM curl
WHERE url = 'http:' || '/' || '/' || 'api.ipstack.com/' || (SELECT result FROM curl WHERE url = 'http:' || '/' || '/' || 'ipv4bot.whatismyipaddress.com') || '?access_key=key_goes_here'

  • Or A simple Query just to list the active Adapter connection

    SELECT ia.interface , address , mask , ia.type , ia.friendly_name  

from interface_addresses ia

JOIN interface_details id ON ia.interface = id.interface

WHERE connection_status = 2 AND mask LIKE '255.%%'

  • Hello Diego,

    Below is the query that lists the connection from and to your machine includes source and destination IPs. By selecting DateTime you can also see the connection history.

    Note you need to define the variable in Sophos Central. 

    Hope it helps.

    -- VARIABLE:   $$startTime$$        STRING
-- VARIABLE:   $$endTime$$          STRING
-- VARIABLE:   $$ipAddress$$        STRING

SELECT
   strftime('%Y-%m-%dT%H:%M:%SZ', datetime(snj.time,'unixepoch')) dateTime,
   u.username userName,
   spj.processName processName,
   CAST(spj.cmdline AS TEXT) cmdLine,
   snj.source,
   snj.sourcePort,
   snj.destination,
   snj.destinationPort
FROM sophos_network_journal snj
   LEFT JOIN sophos_process_journal spj USING (sophosPID)
   LEFT JOIN users u ON spj.sid = u.uuid
WHERE
   ( 
      snj.source LIKE '$$ipAddress$$'
      OR snj.destination LIKE '$$ipAddress$$'
   )
   AND snj.time > $$startTime$$
   AND snj.time < $$endTime$$
GROUP BY 
   snj.source,
   snj.sourceport,
   snj.destination,
   snj.destinationport

Unfiltered HTML