MacOS.Applications.NetworkUsage

On macOS, the NetUsage DB can provide various details around application network utilization. With this artifact, we can get an idea of what applications are utilizing the network for communications and to what degree. We can also identify if usage has occurred through a WIFI network or a wired network.

More information about this database can be found here:

http://www.mac4n6.com/blog/2019/1/6/network-and-application-usage-using-netusagesqlite-amp-datausagesqlite-ios-databases

name: MacOS.Applications.NetworkUsage
description: |
   On macOS, the NetUsage DB can provide various details around application network utilization. With this artifact, we can get an idea of what applications are utilizing the network for communications and to what degree. We can also identify if usage has occurred through a WIFI network or a wired network.
   
   More information about this database can be found here: 
   
   http://www.mac4n6.com/blog/2019/1/6/network-and-application-usage-using-netusagesqlite-amp-datausagesqlite-ios-databases
reference:
  - http://www.mac4n6.com/blog/2019/1/6/network-and-application-usage-using-netusagesqlite-amp-datausagesqlite-ios-databases

type: CLIENT

author: Wes Lambert - @therealwlambert|@weslambert@infosec.exchange

parameters:
- name: NetUsageGlob
  default: /private/var/networkd/netusage.sqlite,/private/var/networkd/db/netusage.sqlite

precondition:
      SELECT OS From info() where OS = 'darwin'

sources:
  - query: |
      LET NetUsageList = SELECT OSPath
       FROM glob(globs=split(string=NetUsageGlob, sep=","))

      LET NetUsageDetails = SELECT *
        FROM sqlite(file=OSPath, query='''
          SELECT
            DATETIME(ZPROCESS.ZTIMESTAMP + 978307200, 'unixepoch') AS "PROCESS TIMESTAMP",
            DATETIME(ZPROCESS.ZFIRSTTIMESTAMP + 978307200, 'unixepoch') AS "PROCESS FIRST TIMESTAMP",
            DATETIME(ZLIVEUSAGE.ZTIMESTAMP + 978307200, 'unixepoch') AS "LIVE USAGE TIMESTAMP",
            ZBUNDLENAME AS "BUNDLE ID",
            ZPROCNAME AS "PROCESS NAME",
            ZWIFIIN AS "WIFI IN",
            ZWIFIOUT AS "WIFI OUT",
            ZWWANIN AS "WWAN IN",
            ZWWANOUT AS "WWAN OUT",
            ZWIREDIN AS "WIRED IN",
            ZWIREDOUT AS "WIRED OUT",
            ZXIN AS "X IN",
            ZXOUT AS "X OUT",
            ZLIVEUSAGE.Z_PK AS "ZLIVEUSAGE TABLE ID" 
          FROM ZLIVEUSAGE 
            LEFT JOIN ZPROCESS ON ZPROCESS.Z_PK = ZLIVEUSAGE.ZHASPROCESS''')
  
      SELECT timestamp(string=`PROCESS TIMESTAMP`) AS Timestamp,	
        `PROCESS FIRST TIMESTAMP` AS FirstTimestamp,	
        `LIVE USAGE TIMESTAMP` AS LiveUsageTimestamp,
        `BUNDLE ID` AS BundleID,
        `PROCESS NAME` AS ProcessName,	
        `WIFI IN` AS WifiIn,
        `WIFI OUT` AS WifiOut,	
        `WIRED IN` AS WiredIn, 	
        `WIRED OUT` AS WiredOut,
        `X IN` AS _XIn,	
        `X OUT` AS _XOut,
        `ZLIVEUSAGE TABLE ID` AS LiveUsageTableID
      FROM foreach(row=NetUsageList,query=NetUsageDetails)