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
Parents

  • 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

Comment

  • 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

Children
No Data
Unfiltered HTML