Title
#sql
l

lvferdi

07/24/2020, 11:35 AM
I can share. I could make it shorter by using like statements but I actually wanted to be very specific. The query itself has 4490 characters (including white space)
11:37 AM
SELECT action, auid, pid, local_address, local_port, remote_address, remote_port, family protocol, path, time as timestampFROM socket_events WHERE success=1 AND path NOT IN ('/usr/bin/hostname', '/opt/symantec/wssa/wssad', '/Applications/Google <http://Chrome.app/Contents/Frameworks/Google|Chrome.app/Contents/Frameworks/Google> Chrome Framework.framework/Versions/84.0.4147.89/Helpers/Google Chrome <http://Helper.app/Contents/MacOS/Google|Helper.app/Contents/MacOS/Google> Chrome Helper', '/Applications/Google <http://Chrome.app/Contents/Frameworks/Google|Chrome.app/Contents/Frameworks/Google> Chrome Framework.framework/Versions/83.0.4103.116/Helpers/Google Chrome <http://Helper.app/Contents/MacOS/Google|Helper.app/Contents/MacOS/Google> Chrome Helper', '/Applications/BIG-IP Edge <http://Client.app/Contents/MacOS/BIG-IP|Client.app/Contents/MacOS/BIG-IP> Edge Client', '/Applications/Slack.app/Contents/MacOS/Slack', '/Applications/Slack.app/Contents/Frameworks/Slack <http://Helper.app/Contents/MacOS/Slack|Helper.app/Contents/MacOS/Slack> Helper', '/usr/libexec/syspolicyd', '/usr/libexec/trustd', '/Applications/zoom.us.app/Contents/MacOS/zoom.us', '/Library/Application Support/JamfProtect/JamfProtect.app/Contents/MacOS/JamfProtect', '/System/Library/Frameworks/CFNetwork.framework/Versions/A/Support/CFNetworkAgent', '/Applications/QualysCloudAgent.app/Contents/MacOS/qualys-cloud-agent', '/usr/sbin/mDNSResponder', '/usr/libexec/nsurlsessiond', '/usr/local/jamf/bin/jamf', '/Applications/QualysCloudAgent.app/Contents/MacOS/qualys-cloud-agent', '/opt/nxlog/bin/nxlog', '/System/Library/PrivateFrameworks/GeoServices.framework/Versions/A/XPCServices/com.apple.geod.xpc/Contents/MacOS/com.apple.geod', '/Library/Application Support/Cylance/Desktop/CylanceSvc.app/Contents/MacOS/CylanceSvc', '/Library/Endgame/esensor', '/usr/libexec/locationd', '/opt/EventReportingService.app/Contents/Resources/EventReportingHelper', '/Applications/Firefox.app/Contents/MacOS/firefox', '/usr/libexec/nsurlsessiond', '/Library/Application Support/Microsoft/MAU2.0/Microsoft <http://AutoUpdate.app/Contents/MacOS/Microsoft|AutoUpdate.app/Contents/MacOS/Microsoft> Update <http://Assistant.app/Contents/MacOS/Microsoft|Assistant.app/Contents/MacOS/Microsoft> Update Assistant', '/System/Library/Frameworks/AddressBook.framework/Versions/A/Helpers/AddressBookSourceSync.app/Contents/MacOS/AddressBookSourceSync', '/System/Library/PrivateFrameworks/CalendarAgent.framework/Executables/CalendarAgent', '/Applications/Visual Studio <http://Code.app/Contents/Frameworks/Code|Code.app/Contents/Frameworks/Code> Helper (Renderer).app/Contents/MacOS/Code Helper (Renderer)', '/Library/Application Support/Symantec WSS Agent/wssa-ui.app/Contents/MacOS/wssa-ui', '/Library/Application Support/JAMF/Jamf.app/Contents/MacOS/JamfDaemon.app/Contents/XPCServices/JamfProCommService.xpc/Contents/MacOS/JamfProCommService', '/usr/libexec/remindd', '/System/Library/PrivateFrameworks/CoreParsec.framework/parsecd', '/System/Library/PrivateFrameworks/CoreParsec.framework/parsec-fbf', '/Applications/Enterprise <http://Connect.app/Contents/SharedSupport/ecAgent.app/Contents/MacOS/ecAgent|Connect.app/Contents/SharedSupport/ecAgent.app/Contents/MacOS/ecAgent>', '/Applications/Visual Studio <http://Code.app/Contents/Frameworks/Code|Code.app/Contents/Frameworks/Code> <http://Helper.app/Contents/MacOS/Code|Helper.app/Contents/MacOS/Code> Helper', '/Library/Internet Plug-Ins/F5 SSL VPN Plugin.plugin/Contents/Helpers/svpn', '/System/Library/PrivateFrameworks/HelpData.framework/Versions/A/Resources/helpd', '/System/Library/PrivateFrameworks/SyncedDefaults.framework/Support/syncdefaultsd', '/System/Library/PrivateFrameworks/AppStoreDaemon.framework/Support/appstoreagent', '/Library/Google/GoogleSoftwareUpdate/GoogleSoftwareUpdate.bundle/Contents/Helpers/ksfetch', '/usr/libexec/UserEventAgent', '/System/Library/CoreServices/mapspushd', '/usr/sbin/mDNSResponder', '/System/Library/PrivateFrameworks/FamilyCircle.framework/Versions/A/Resources/familycircled', '/Applications/Postman.app/Contents/MacOS/Postman', '/Applications/Snagit <http://2019.app/Contents/MacOS/Snagit|2019.app/Contents/MacOS/Snagit> 2019', '/Applications/Snagit <http://2019.app/Contents/Library/LoginItems/SnagitHelper2019.app/Contents/MacOS/SnagitHelper2019|2019.app/Contents/Library/LoginItems/SnagitHelper2019.app/Contents/MacOS/SnagitHelper2019>', '/System/Library/PrivateFrameworks/CloudKitDaemon.framework/Support/cloudd', '/Applications/Atom.app/Contents/MacOS/Atom', '/System/Library/PrivateFrameworks/CommerceKit.framework/Versions/A/Resources/commerce', '/System/Library/PrivateFrameworks/ApplePushService.framework/apsd', '/System/Library/PrivateFrameworks/AOSKit.framework/Versions/A/XPCServices/com.apple.iCloudHelper.xpc/Contents/MacOS/com.apple.iCloudHelper', '/Library/PrivilegedHelperTools/com.capitalone.privileges.helper', '/usr/libexec/studentd', '/Applications/Safari.app/Contents/MacOS/Safari') AND remote_address NOT IN ('127.0.0.1', '169.254.169.254', '', '0000:0000:0000:0000:0000:0000:0000:0001', '::1', '0000:0000:0000:0000:0000:ffff:7f00:0001', 'unknown', '0.0.0.0', '0000:0000:0000:0000:0000:0000:0000:0000');
11:37 AM
message has been deleted
11:38 AM
when trying to paste this into osqueryi it prevents me from pasting in the entire command, only a portion of it gets pasted in and the I cannot type anymore
11:38 AM
also when run with osqueryd it just ignores the query with no errors raised
12:20 PM
osqueryi appears to cut off queries at 4095 characters
Stefano Bonicatti

Stefano Bonicatti

07/24/2020, 12:28 PM
If I put the query into the osquery config as a scheduled query, then launch osquery with (I'm on Linux though, with an osquery I've built):
sudo osquery/osqueryd --disable_events=false --audit_allow_config=true --audit_allow_sockets=true --audit_persist=true --disable_audit=false --allow_unsafe --config_path=./osquery.conf --verbose
I can see that it's saying it's executing it, but no errors. That been said I had to correct the query because of
time as timestampFROM
which should be
time as timestamp FROM
12:28 PM
If you try to run the query via daemon launched from the command line, do you see it executing?
12:52 PM
@lvferdi It might be that typo, though I also see that it does print an error if I leave it:
E0724 14:50:50.897132 10115 scheduler.cpp:101] Error executing scheduled query socket_events: near "socket_events": syntax error
In the command line when I launch the daemon there and also in the logs in
/var/log/osquery/osqueryd.ERROR
l

lvferdi

07/24/2020, 12:56 PM
I did see the original error, happened during copy paste, but I wasn't able to run it mac, It cuts off the query on osqueryi. I'll reload it in my pack and see if I can get it to fire.
12:57 PM
here is when I try to use osqueryi
12:57 PM
you can see where it cuts off
12:58 PM
I'll try in the pack now
Stefano Bonicatti

Stefano Bonicatti

07/24/2020, 12:58 PM
Yeah that I have too.
12:59 PM
Though putting it as a scheduled query I can see it running and I receive results. There shouldn't be any difference of length between Linux and macOS
12:59 PM
to core that deals with this is the same
l

lvferdi

07/24/2020, 12:59 PM
testing again.....few min
Stefano Bonicatti

Stefano Bonicatti

07/24/2020, 1:00 PM
yep! Also what version of osquery?
l

lvferdi

07/24/2020, 1:04 PM
I0724 09:03:18.399133 235912640 init.cpp:343] osquery initialized [version=4.4.0]
1:06 PM
here is the query as I have it in my pack and I'm not seeing any queries for it with osqueryd on mac
1:06 PM
"sockets": {
      "query": "SELECT \
      action, \
      auid, \
      pid, \
      local_address, \
      local_port, \
      remote_address, \
      remote_port, \
      family protocol, \
      path, \
      time as timestamp \
      FROM socket_events \
      WHERE success=1 \
      AND path NOT IN ('/usr/bin/hostname', '/opt/symantec/wssa/wssad', '/Applications/Google <http://Chrome.app/Contents/Frameworks/Google|Chrome.app/Contents/Frameworks/Google> Chrome Framework.framework/Versions/84.0.4147.89/Helpers/Google Chrome <http://Helper.app/Contents/MacOS/Google|Helper.app/Contents/MacOS/Google> Chrome Helper', '/Applications/Google <http://Chrome.app/Contents/Frameworks/Google|Chrome.app/Contents/Frameworks/Google> Chrome Framework.framework/Versions/83.0.4103.116/Helpers/Google Chrome <http://Helper.app/Contents/MacOS/Google|Helper.app/Contents/MacOS/Google> Chrome Helper', '/Applications/BIG-IP Edge <http://Client.app/Contents/MacOS/BIG-IP|Client.app/Contents/MacOS/BIG-IP> Edge Client', '/Applications/Slack.app/Contents/MacOS/Slack', '/Applications/Slack.app/Contents/Frameworks/Slack <http://Helper.app/Contents/MacOS/Slack|Helper.app/Contents/MacOS/Slack> Helper', '/usr/libexec/syspolicyd', '/usr/libexec/trustd', '/Applications/zoom.us.app/Contents/MacOS/zoom.us', '/Library/Application Support/JamfProtect/JamfProtect.app/Contents/MacOS/JamfProtect', '/System/Library/Frameworks/CFNetwork.framework/Versions/A/Support/CFNetworkAgent', '/Applications/QualysCloudAgent.app/Contents/MacOS/qualys-cloud-agent', '/usr/sbin/mDNSResponder', '/usr/libexec/nsurlsessiond', '/usr/local/jamf/bin/jamf', '/Applications/QualysCloudAgent.app/Contents/MacOS/qualys-cloud-agent', '/opt/nxlog/bin/nxlog', '/System/Library/PrivateFrameworks/GeoServices.framework/Versions/A/XPCServices/com.apple.geod.xpc/Contents/MacOS/com.apple.geod', '/Library/Application Support/Cylance/Desktop/CylanceSvc.app/Contents/MacOS/CylanceSvc', '/Library/Endgame/esensor', '/usr/libexec/locationd', '/opt/EventReportingService.app/Contents/Resources/EventReportingHelper', '/Applications/Firefox.app/Contents/MacOS/firefox', '/usr/libexec/nsurlsessiond', '/Library/Application Support/Microsoft/MAU2.0/Microsoft <http://AutoUpdate.app/Contents/MacOS/Microsoft|AutoUpdate.app/Contents/MacOS/Microsoft> Update <http://Assistant.app/Contents/MacOS/Microsoft|Assistant.app/Contents/MacOS/Microsoft> Update Assistant', '/System/Library/Frameworks/AddressBook.framework/Versions/A/Helpers/AddressBookSourceSync.app/Contents/MacOS/AddressBookSourceSync', '/System/Library/PrivateFrameworks/CalendarAgent.framework/Executables/CalendarAgent', '/Applications/Visual Studio <http://Code.app/Contents/Frameworks/Code|Code.app/Contents/Frameworks/Code> Helper (Renderer).app/Contents/MacOS/Code Helper (Renderer)', '/Library/Application Support/Symantec WSS Agent/wssa-ui.app/Contents/MacOS/wssa-ui', '/Library/Application Support/JAMF/Jamf.app/Contents/MacOS/JamfDaemon.app/Contents/XPCServices/JamfProCommService.xpc/Contents/MacOS/JamfProCommService', '/usr/libexec/remindd', '/System/Library/PrivateFrameworks/CoreParsec.framework/parsecd', '/System/Library/PrivateFrameworks/CoreParsec.framework/parsec-fbf', '/Applications/Enterprise <http://Connect.app/Contents/SharedSupport/ecAgent.app/Contents/MacOS/ecAgent|Connect.app/Contents/SharedSupport/ecAgent.app/Contents/MacOS/ecAgent>', '/Applications/Visual Studio <http://Code.app/Contents/Frameworks/Code|Code.app/Contents/Frameworks/Code> <http://Helper.app/Contents/MacOS/Code|Helper.app/Contents/MacOS/Code> Helper', '/Library/Internet Plug-Ins/F5 SSL VPN Plugin.plugin/Contents/Helpers/svpn', '/System/Library/PrivateFrameworks/HelpData.framework/Versions/A/Resources/helpd', '/System/Library/PrivateFrameworks/SyncedDefaults.framework/Support/syncdefaultsd', '/System/Library/PrivateFrameworks/AppStoreDaemon.framework/Support/appstoreagent', '/Library/Google/GoogleSoftwareUpdate/GoogleSoftwareUpdate.bundle/Contents/Helpers/ksfetch', '/usr/libexec/UserEventAgent', '/System/Library/CoreServices/mapspushd', '/usr/sbin/mDNSResponder', '/System/Library/PrivateFrameworks/FamilyCircle.framework/Versions/A/Resources/familycircled', '/Applications/Postman.app/Contents/MacOS/Postman', '/Applications/Snagit <http://2019.app/Contents/MacOS/Snagit|2019.app/Contents/MacOS/Snagit> 2019', '/Applications/Snagit <http://2019.app/Contents/Library/LoginItems/SnagitHelper2019.app/Contents/MacOS/SnagitHelper2019|2019.app/Contents/Library/LoginItems/SnagitHelper2019.app/Contents/MacOS/SnagitHelper2019>', '/System/Library/PrivateFrameworks/CloudKitDaemon.framework/Support/cloudd', '/Applications/Atom.app/Contents/MacOS/Atom', '/System/Library/PrivateFrameworks/CommerceKit.framework/Versions/A/Resources/commerce', '/System/Library/PrivateFrameworks/ApplePushService.framework/apsd', '/System/Library/PrivateFrameworks/AOSKit.framework/Versions/A/XPCServices/com.apple.iCloudHelper.xpc/Contents/MacOS/com.apple.iCloudHelper', '/Library/PrivilegedHelperTools/com.capitalone.privileges.helper', '/usr/libexec/studentd', '/Applications/Safari.app/Contents/MacOS/Safari')
       AND remote_address NOT IN ('127.0.0.1', '169.254.169.254', '', '0000:0000:0000:0000:0000:0000:0000:0001', '::1', '0000:0000:0000:0000:0000:ffff:7f00:0001', 'unknown', '0.0.0.0', '0000:0000:0000:0000:0000:0000:0000:0000') \
       AND remote_address <> \"\" \
       AND remote_port != 0 AND pid > 0;",
      "description": "The socket_events table will give you every CONNECT, BIND and CLOSE event",
      "platform": "darwin,linux",
      "interval": 60
    }
1:07 PM
there are queries for my other events such as process and hardware but none for sockets
1:07 PM
when I reduce the number of characters it runs.
1:11 PM
osqueryd --flagfile ~/testing/osquery.flags --verbose
I0724 09:03:18.399133 235912640 init.cpp:343] osquery initialized [version=4.4.0]
I0724 09:03:18.431694 235912640 system.cpp:335] Found stale process for osqueryd (43553)
I0724 09:03:18.437916 235912640 system.cpp:367] Writing osqueryd pid (99210) to /var/osquery/osquery2.pid
I0724 09:03:18.438782 235912640 extensions.cpp:383] Could not autoload extensions: Failed reading: /var/osquery/extensions.load
I0724 09:03:18.438855 235912640 dispatcher.cpp:77] Adding new service: WatcherRunner (0x7fac52c0af08) to thread: 0x70000baf4000 (0x7fac52c0b350) in process 99210
I0724 09:03:18.439831 196034560 watcher.cpp:585] osqueryd watcher (99210) executing worker (99211)
I0724 09:03:18.460609 335216064 init.cpp:340] osquery worker initialized [watcher=99210]
I0724 09:03:18.461658 335216064 dispatcher.cpp:77] Adding new service: WatcherWatcherRunner (0x7fe97ca04598) to thread: 0x700009a21000 (0x7fe97ca04270) in process 99211
I0724 09:03:18.488477 335216064 rocksdb.cpp:131] Opening RocksDB handle: /var/osquery/osquery.db
I0724 09:03:19.271147 335216064 dispatcher.cpp:77] Adding new service: ExtensionWatcher (0x7fe97c81e978) to thread: 0x700009cb0000 (0x7fe97c81f0e0) in process 99211
I0724 09:03:19.271332 335216064 dispatcher.cpp:77] Adding new service: ExtensionRunnerCore (0x7fe97c81d6d8) to thread: 0x700009d33000 (0x7fe97c81eb60) in process 99211
I0724 09:03:19.271553 164835328 interface.cpp:268] Extension manager service starting: /var/osquery/osquery.em
I0724 09:03:19.271596 335216064 auto_constructed_tables.cpp:96] Removing stale ATC entries
I0724 09:03:19.964457 335216064 events.cpp:866] Event publisher not enabled: scnetwork: Publisher not used
I0724 09:03:19.965639 335216064 events.cpp:866] Event publisher not enabled: event_tapping: Publisher disabled via configuration
I0724 09:03:20.856827 335216064 main.cpp:103] Not starting the distributed query service: Distributed query service not enabled.
I0724 09:03:20.856854 165371904 events.cpp:785] Starting event publisher run loop: diskarbitration
I0724 09:03:20.856856 166432768 events.cpp:785] Starting event publisher run loop: fsevents
I0724 09:03:20.856870 167505920 events.cpp:785] Starting event publisher run loop: openbsm
I0724 09:03:20.856871 166969344 events.cpp:785] Starting event publisher run loop: iokit
I0724 09:03:20.856925 335216064 dispatcher.cpp:77] Adding new service: SchedulerRunner (0x7fe97ad39018) to thread: 0x70000a042000 (0x7fe97ad39f00) in process 99211
I0724 09:03:35.892136 168042496 scheduler.cpp:96] Executing scheduled query pack_events_mac_process: SELECT           pe.pid,           pe.parent,           pe.path,           pe.uid,           pe.euid,           pe.cwd,           pe.cmdline,           pe.overflows,           pe.uptime,           pe.time,           pe.status,           pe.env,           u.username,           h.md5,           h.sha1,           h.sha256,           (SELECT '1001') AS QID         FROM           process_events pe         JOIN users u USING (uid)         JOIN hash h USING (path)         WHERE pe.cmdline NOT LIKE '%/Applications/Account Migration <http://Tool.app/Contents/Resources/migrateToLocalUser.sh%';|Tool.app/Contents/Resources/migrateToLocalUser.sh%';>
I0724 09:03:36.074718 168042496 scheduler.cpp:157] Found results for query: pack_events_mac_process
I0724 09:04:30.982235 168042496 scheduler.cpp:96] Executing scheduled query pack_events_mac_process: SELECT           pe.pid,           pe.parent,           pe.path,           pe.uid,           pe.euid,           pe.cwd,           pe.cmdline,           pe.overflows,           pe.uptime,           pe.time,           pe.status,           pe.env,           u.username,           h.md5,           h.sha1,           h.sha256,           (SELECT '1001') AS QID         FROM           process_events pe         JOIN users u USING (uid)         JOIN hash h USING (path)         WHERE pe.cmdline NOT LIKE '%/Applications/Account Migration <http://Tool.app/Contents/Resources/migrateToLocalUser.sh%';|Tool.app/Contents/Resources/migrateToLocalUser.sh%';>
I0724 09:04:31.204226 168042496 scheduler.cpp:157] Found results for query: pack_events_mac_process
I0724 09:05:01.068428 168042496 database.cpp:140] Resetting the database plugin: rocksdb
I0724 09:05:01.098671 168042496 rocksdb.cpp:131] Opening RocksDB handle: /var/osquery/osquery.db
I0724 09:05:26.107506 168042496 scheduler.cpp:96] Executing scheduled query pack_events_mac_process: SELECT           pe.pid,           pe.parent,           pe.path,           pe.uid,           pe.euid,           pe.cwd,           pe.cmdline,           pe.overflows,           pe.uptime,           pe.time,           pe.status,           pe.env,           u.username,           h.md5,           h.sha1,           h.sha256,           (SELECT '1001') AS QID         FROM           process_events pe         JOIN users u USING (uid)         JOIN hash h USING (path)         WHERE pe.cmdline NOT LIKE '%/Applications/Account Migration <http://Tool.app/Contents/Resources/migrateToLocalUser.sh%';|Tool.app/Contents/Resources/migrateToLocalUser.sh%';>
I0724 09:05:26.262648 168042496 scheduler.cpp:157] Found results for query: pack_events_mac_process
I0724 09:06:01.155551 168042496 scheduler.cpp:96] Executing scheduled query pack_events_hardware: SELECT       h.action,       h.path,       h.type,       h.driver,       h.vendor,       h.vendor_id,       h.model,       h.model_id,       h.serial,       h.revision,       h.time,       h.eid,       (SELECT '1004') AS QID       FROM hardware_events h;
I0724 09:06:21.195711 168042496 scheduler.cpp:96] Executing scheduled query pack_events_mac_process: SELECT           pe.pid,           pe.parent,           pe.path,           pe.uid,           pe.euid,           pe.cwd,           pe.cmdline,           pe.overflows,           pe.uptime,           pe.time,           pe.status,           pe.env,           u.username,           h.md5,           h.sha1,           h.sha256,           (SELECT '1001') AS QID         FROM           process_events pe         JOIN users u USING (uid)         JOIN hash h USING (path)         WHERE pe.cmdline NOT LIKE '%/Applications/Account Migration <http://Tool.app/Contents/Resources/migrateToLocalUser.sh%';|Tool.app/Contents/Resources/migrateToLocalUser.sh%';>
I0724 09:06:21.357417 168042496 scheduler.cpp:157] Found results for query: pack_events_mac_process
I0724 09:07:16.318914 168042496 scheduler.cpp:96] Executing scheduled query pack_events_mac_process: SELECT           pe.pid,           pe.parent,           pe.path,           pe.uid,           pe.euid,           pe.cwd,           pe.cmdline,           pe.overflows,           pe.uptime,           pe.time,           pe.status,           pe.env,           u.username,           h.md5,           h.sha1,           h.sha256,           (SELECT '1001') AS QID         FROM           process_events pe         JOIN users u USING (uid)         JOIN hash h USING (path)         WHERE pe.cmdline NOT LIKE '%/Applications/Account Migration <http://Tool.app/Contents/Resources/migrateToLocalUser.sh%';|Tool.app/Contents/Resources/migrateToLocalUser.sh%';>
I0724 09:07:16.484997 168042496 scheduler.cpp:157] Found results for query: pack_events_mac_process
I0724 09:08:11.446853 168042496 scheduler.cpp:96] Executing scheduled query pack_events_mac_process: SELECT           pe.pid,           pe.parent,           pe.path,           pe.uid,           pe.euid,           pe.cwd,           pe.cmdline,           pe.overflows,           pe.uptime,           pe.time,           pe.status,           pe.env,           u.username,           h.md5,           h.sha1,           h.sha256,           (SELECT '1001') AS QID         FROM           process_events pe         JOIN users u USING (uid)         JOIN hash h USING (path)         WHERE pe.cmdline NOT LIKE '%/Applications/Account Migration <http://Tool.app/Contents/Resources/migrateToLocalUser.sh%';|Tool.app/Contents/Resources/migrateToLocalUser.sh%';>
I0724 09:08:11.616814 168042496 scheduler.cpp:157] Found results for query: pack_events_mac_process
I0724 09:09:06.587538 168042496 scheduler.cpp:96] Executing scheduled query pack_events_mac_process: SELECT           pe.pid,           pe.parent,           pe.path,           pe.uid,           pe.euid,           pe.cwd,           pe.cmdline,           pe.overflows,           pe.uptime,           pe.time,           pe.status,           pe.env,           u.username,           h.md5,           h.sha1,           h.sha256,           (SELECT '1001') AS QID         FROM           process_events pe         JOIN users u USING (uid)         JOIN hash h USING (path)         WHERE pe.cmdline NOT LIKE '%/Applications/Account Migration <http://Tool.app/Contents/Resources/migrateToLocalUser.sh%';|Tool.app/Contents/Resources/migrateToLocalUser.sh%';>
I0724 09:09:06.823925 168042496 scheduler.cpp:157] Found results for query: pack_events_mac_process
I0724 09:09:11.598686 168042496 scheduler.cpp:96] Executing scheduled query pack_events_user: SELECT       u.uid,       u.auid,       u.pid,       u.message,       u.type,       u.path,       u.address,       u.terminal,       u.time,       u.uptime,       u.eid,       (SELECT '1002') AS QID       from user_events u;
I0724 09:10:01.722512 168042496 scheduler.cpp:96] Executing scheduled query pack_events_mac_process: SELECT           pe.pid,           pe.parent,           pe.path,           pe.uid,           pe.euid,           pe.cwd,           pe.cmdline,           pe.overflows,           pe.uptime,           pe.time,           pe.status,           pe.env,           u.username,           h.md5,           h.sha1,           h.sha256,           (SELECT '1001') AS QID         FROM           process_events pe         JOIN users u USING (uid)         JOIN hash h USING (path)         WHERE pe.cmdline NOT LIKE '%/Applications/Account Migration <http://Tool.app/Contents/Resources/migrateToLocalUser.sh%';|Tool.app/Contents/Resources/migrateToLocalUser.sh%';>
1:11 PM
here is the output from osqueryd
1:11 PM
you can see over 60 seconds went by (timer on socket query) and no events
Stefano Bonicatti

Stefano Bonicatti

07/24/2020, 1:22 PM
Hum, I'm not able to run that query if it contains those backslashes, though it loudly complains about not being able to parse the config. Can we start with a very simple config? Remove any other query and just put that one, test it if it works and if it doesn't, instead of copy pasting, could you upload the config file here?
l

lvferdi

07/24/2020, 1:27 PM
Sure. One moment
1:44 PM
ok...completely a pebkac error. I had changed my path to pack file for testing and I was editing the old path. Sooooo...once I changed the path to the correct one and placed the query in the pack file, started osqueryd --verbose and I see the data.
1:44 PM
"sockets": {
      "query": "SELECT \
      action, \
      auid, \
      pid, \
      local_address, \
      local_port, \
      remote_address, \
      remote_port, \
      family protocol, \
      path, \
      time as timestamp \
      FROM socket_events \
      WHERE success=1 \
      AND path NOT IN ('/usr/bin/hostname', '/opt/symantec/wssa/wssad', '/Applications/Google <http://Chrome.app/Contents/Frameworks/Google|Chrome.app/Contents/Frameworks/Google> Chrome Framework.framework/Versions/84.0.4147.89/Helpers/Google Chrome <http://Helper.app/Contents/MacOS/Google|Helper.app/Contents/MacOS/Google> Chrome Helper', '/Applications/Google <http://Chrome.app/Contents/Frameworks/Google|Chrome.app/Contents/Frameworks/Google> Chrome Framework.framework/Versions/83.0.4103.116/Helpers/Google Chrome <http://Helper.app/Contents/MacOS/Google|Helper.app/Contents/MacOS/Google> Chrome Helper', '/Applications/BIG-IP Edge <http://Client.app/Contents/MacOS/BIG-IP|Client.app/Contents/MacOS/BIG-IP> Edge Client', '/Applications/Slack.app/Contents/MacOS/Slack', '/Applications/Slack.app/Contents/Frameworks/Slack <http://Helper.app/Contents/MacOS/Slack|Helper.app/Contents/MacOS/Slack> Helper', '/usr/libexec/syspolicyd', '/usr/libexec/trustd', '/Applications/zoom.us.app/Contents/MacOS/zoom.us', '/Library/Application Support/JamfProtect/JamfProtect.app/Contents/MacOS/JamfProtect', '/System/Library/Frameworks/CFNetwork.framework/Versions/A/Support/CFNetworkAgent', '/Applications/QualysCloudAgent.app/Contents/MacOS/qualys-cloud-agent', '/usr/sbin/mDNSResponder', '/usr/libexec/nsurlsessiond', '/usr/local/jamf/bin/jamf', '/Applications/QualysCloudAgent.app/Contents/MacOS/qualys-cloud-agent', '/opt/nxlog/bin/nxlog', '/System/Library/PrivateFrameworks/GeoServices.framework/Versions/A/XPCServices/com.apple.geod.xpc/Contents/MacOS/com.apple.geod', '/Library/Application Support/Cylance/Desktop/CylanceSvc.app/Contents/MacOS/CylanceSvc', '/Library/Endgame/esensor', '/usr/libexec/locationd', '/opt/EventReportingService.app/Contents/Resources/EventReportingHelper', '/Applications/Firefox.app/Contents/MacOS/firefox', '/usr/libexec/nsurlsessiond', '/Library/Application Support/Microsoft/MAU2.0/Microsoft <http://AutoUpdate.app/Contents/MacOS/Microsoft|AutoUpdate.app/Contents/MacOS/Microsoft> Update <http://Assistant.app/Contents/MacOS/Microsoft|Assistant.app/Contents/MacOS/Microsoft> Update Assistant', '/System/Library/Frameworks/AddressBook.framework/Versions/A/Helpers/AddressBookSourceSync.app/Contents/MacOS/AddressBookSourceSync', '/System/Library/PrivateFrameworks/CalendarAgent.framework/Executables/CalendarAgent', '/Applications/Visual Studio <http://Code.app/Contents/Frameworks/Code|Code.app/Contents/Frameworks/Code> Helper (Renderer).app/Contents/MacOS/Code Helper (Renderer)', '/Library/Application Support/Symantec WSS Agent/wssa-ui.app/Contents/MacOS/wssa-ui', '/Library/Application Support/JAMF/Jamf.app/Contents/MacOS/JamfDaemon.app/Contents/XPCServices/JamfProCommService.xpc/Contents/MacOS/JamfProCommService', '/usr/libexec/remindd', '/System/Library/PrivateFrameworks/CoreParsec.framework/parsecd', '/System/Library/PrivateFrameworks/CoreParsec.framework/parsec-fbf', '/Applications/Enterprise <http://Connect.app/Contents/SharedSupport/ecAgent.app/Contents/MacOS/ecAgent|Connect.app/Contents/SharedSupport/ecAgent.app/Contents/MacOS/ecAgent>', '/Applications/Visual Studio <http://Code.app/Contents/Frameworks/Code|Code.app/Contents/Frameworks/Code> <http://Helper.app/Contents/MacOS/Code|Helper.app/Contents/MacOS/Code> Helper', '/Library/Internet Plug-Ins/F5 SSL VPN Plugin.plugin/Contents/Helpers/svpn', '/System/Library/PrivateFrameworks/HelpData.framework/Versions/A/Resources/helpd', '/System/Library/PrivateFrameworks/SyncedDefaults.framework/Support/syncdefaultsd', '/System/Library/PrivateFrameworks/AppStoreDaemon.framework/Support/appstoreagent', '/Library/Google/GoogleSoftwareUpdate/GoogleSoftwareUpdate.bundle/Contents/Helpers/ksfetch', '/usr/libexec/UserEventAgent', '/System/Library/CoreServices/mapspushd', '/usr/sbin/mDNSResponder', '/System/Library/PrivateFrameworks/FamilyCircle.framework/Versions/A/Resources/familycircled', '/Applications/Postman.app/Contents/MacOS/Postman', '/Applications/Snagit <http://2019.app/Contents/MacOS/Snagit|2019.app/Contents/MacOS/Snagit> 2019', '/Applications/Snagit <http://2019.app/Contents/Library/LoginItems/SnagitHelper2019.app/Contents/MacOS/SnagitHelper2019|2019.app/Contents/Library/LoginItems/SnagitHelper2019.app/Contents/MacOS/SnagitHelper2019>', '/System/Library/PrivateFrameworks/CloudKitDaemon.framework/Support/cloudd', '/Applications/Atom.app/Contents/MacOS/Atom', '/System/Library/PrivateFrameworks/CommerceKit.framework/Versions/A/Resources/commerce', '/System/Library/PrivateFrameworks/ApplePushService.framework/apsd', '/System/Library/PrivateFrameworks/AOSKit.framework/Versions/A/XPCServices/com.apple.iCloudHelper.xpc/Contents/MacOS/com.apple.iCloudHelper', '/Library/PrivilegedHelperTools/com.capitalone.privileges.helper', '/usr/libexec/studentd', '/Applications/Safari.app/Contents/MacOS/Safari') \
      AND remote_address NOT IN ('127.0.0.1', '169.254.169.254', '', '0000:0000:0000:0000:0000:0000:0000:0001', '::1', '0000:0000:0000:0000:0000:ffff:7f00:0001', 'unknown', '0.0.0.0', '0000:0000:0000:0000:0000:0000:0000:0000') \
      AND remote_address <> \"\" \
      AND remote_port != 0 AND pid > 0;",
      "description": "The socket_events table will give you every CONNECT, BIND and CLOSE event",
      "platform": "darwin,linux",
      "interval": 60
    }
1:44 PM
the query looks like
1:44 PM
thanks for the help, totally user error