I can share I could make it shorter by using like statements

Question

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

lvferdi · Answer

```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 Chrome Framework framework Versions 84 0 4147 89 Helpers Google Chrome Chrome Helper Applications Google Chrome Framework framework Versions 83 0 4103 116 Helpers Google Chrome Chrome Helper Applications BIG IP Edge Edge Client Applications Slack app Contents MacOS Slack Applications Slack app Contents Frameworks 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 Update 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 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 Applications Visual Studio 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 2019 Applications Snagit 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 ```

lvferdi · Answer

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

lvferdi · Answer

also when run with osqueryd it just ignores the query with no errors raised

lvferdi · Answer

osqueryi appears to cut off queries at 4095 characters

Stefano Bonicatti · Answer

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`

Stefano Bonicatti · Answer

If you try to run the query via daemon launched from the command line do you see it executing

Stefano Bonicatti · Answer

< 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`

lvferdi · Answer

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

lvferdi · Answer

here is when I try to use osqueryi

lvferdi · Answer

you can see where it cuts off

lvferdi · Answer

I ll try in the pack now

Stefano Bonicatti · Answer

Yeah that I have too

Stefano Bonicatti · Answer

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

Stefano Bonicatti · Answer

to core that deals with this is the same

lvferdi · Answer

testing again few min

Stefano Bonicatti · Answer

yep Also what version of osquery

lvferdi · Answer

`I0724 09 03 18 399133 235912640 init cpp 343 osquery initialized version=4 4 0 `

lvferdi · Answer

here is the query as I have it in my pack and I m not seeing any queries for it with osqueryd on mac

lvferdi · Answer

``` 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 Chrome Framework framework Versions 84 0 4147 89 Helpers Google Chrome Chrome Helper Applications Google Chrome Framework framework Versions 83 0 4103 116 Helpers Google Chrome Chrome Helper Applications BIG IP Edge Edge Client Applications Slack app Contents MacOS Slack Applications Slack app Contents Frameworks 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 Update 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 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 Applications Visual Studio 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 2019 Applications Snagit 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 lt gt AND remote port = 0 AND pid gt 0 description The socket events table will give you every CONNECT BIND and CLOSE event platform darwin linux interval 60 ```

lvferdi · Answer

there are queries for my other events such as process and hardware but none for sockets

lvferdi · Answer

when I reduce the number of characters it runs

lvferdi · Answer

```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 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 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 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 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 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 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 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 ```

lvferdi · Answer

here is the output from osqueryd

lvferdi · Answer

you can see over 60 seconds went by timer on socket query and no events

Stefano Bonicatti · Answer

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

lvferdi · Answer

Sure One moment

lvferdi · Answer

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

lvferdi · Answer

``` 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 Chrome Framework framework Versions 84 0 4147 89 Helpers Google Chrome Chrome Helper Applications Google Chrome Framework framework Versions 83 0 4103 116 Helpers Google Chrome Chrome Helper Applications BIG IP Edge Edge Client Applications Slack app Contents MacOS Slack Applications Slack app Contents Frameworks 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 Update 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 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 Applications Visual Studio 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 2019 Applications Snagit 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 lt gt AND remote port = 0 AND pid gt 0 description The socket events table will give you every CONNECT BIND and CLOSE event platform darwin linux interval 60 ```

lvferdi · Answer

the query looks like

lvferdi · Answer

thanks for the help totally user error