### Install Timesketch Import Client Source: https://github.com/ec-digit-csirc/sysdiagnose/blob/main/scripts/timesketch/README.md Installs the necessary client library within a virtual environment to enable data ingestion. ```bash pip install timesketch-import-client ``` -------------------------------- ### Install SAF dependencies on Linux Source: https://github.com/ec-digit-csirc/sysdiagnose/blob/main/README.md Set up a virtual environment and install required packages including graphviz. ```bash python3 -m venv venv source venv/bin/activate pip install . sudo apt install graphviz ``` -------------------------------- ### Install libimobiledevice Dependencies Source: https://github.com/ec-digit-csirc/sysdiagnose/blob/main/docs/commands.md Install the necessary packages to interact with iOS devices via command line. ```bash apt-get install libimobiledevice-dev libimobiledevice-doc libimobiledevice6 libplist-doc ideviceinstaller libimobiledevice-utils python-imobiledevice python-plist libplist-utils ``` -------------------------------- ### Start existing instance Source: https://github.com/ec-digit-csirc/sysdiagnose/blob/main/scripts/splunk/README.md Resumes a previously stopped environment. ```bash docker-compose start ``` -------------------------------- ### Install SAF and Dependencies Source: https://context7.com/ec-digit-csirc/sysdiagnose/llms.txt Install SAF using pip in a Python 3.11+ virtual environment. Includes commands for system dependencies on Linux and macOS, and optional installation for the Mandiant UnifiedLogs parser. ```bash # Create virtual environment and install python3 -m venv venv source venv/bin/activate pip install . ``` ```bash # Install required system dependencies (Linux) sudo apt install graphviz ``` ```bash # Install required system dependencies (macOS) brew install graphviz libmagic ``` ```bash # Optional: Install Mandiant UnifiedLogs parser for Linux systems git clone https://github.com/mandiant/macos-UnifiedLogs cd macos-UnifiedLogs/examples/unifiedlog_iterator/ cargo build --release sudo cp ../target/release/unifiedlog_iterator /usr/local/bin/ ``` -------------------------------- ### Build and install macos-UnifiedLogs for Linux Source: https://github.com/ec-digit-csirc/sysdiagnose/blob/main/README.md Downloads, compiles, and installs the Mandiant UnifiedLogs iterator tool. ```bash git clone https://github.com/mandiant/macos-UnifiedLogs cd macos-UnifiedLogs/examples/unifiedlog_iterator/ cargo build --release sudo cp ../target/release/unifiedlog_iterator /usr/local/bin/ ``` -------------------------------- ### Example JSONL Log Entries for Troubleshooting Source: https://github.com/ec-digit-csirc/sysdiagnose/blob/main/README.md These log entries demonstrate the format and content of the JSONL log files generated during parser and analyser execution. They help in identifying the start and end of parser/analyser tasks and any intermediate messages. ```json {"levelname": "INFO", "module": "main", "message": "Parser 'demo_parser' started", "taskName": null, "parser": "demo_parser", "datetime": "2025-01-28T12:16:59.153931+01:00", "timestamp": 1738063019.153931} ``` ```json {"levelname": "INFO", "module": "demo_parser", "message": "Processing file ./cases/1/data/sysdiagnose_2024.09.18_11-33-57+0200_iPhone-OS_iPhone_21G93/demo_input_file.txt, new entry added", "taskName": null, "log_file": "./cases/1/data/sysdiagnose_2024.09.18_11-33-57+0200_iPhone-OS_iPhone_21G93/demo_input_file.txt", "entry": "{}", "datetime": "2025-01-28T12:16:59.201914+01:00", "timestamp": 1738063019.201914} ``` ```json {"levelname": "WARNING", "module": "demo_parser", "message": "Empty entry.", "taskName": null, "datetime": "2025-01-28T12:16:59.202071+01:00", "timestamp": 1738063019.202071} ``` ```json {"levelname": "INFO", "module": "main", "message": "Parser 'demo_parser' finished successfully", "taskName": null, "parser": "demo_parser", "result": 0, "datetime": "2025-01-28T12:16:59.202495+01:00", "timestamp": 1738063019.2024949} ``` -------------------------------- ### Start the workshop environment Source: https://github.com/ec-digit-csirc/sysdiagnose/blob/main/scripts/splunk/README.md Initializes the containers in detached mode. ```bash docker-compose up -d ``` -------------------------------- ### SQL Query for Application Installs Source: https://github.com/ec-digit-csirc/sysdiagnose/blob/main/src/sysdiagnose/utils/apollo_modules/knowledge_app_install.txt Retrieves application installation records from the knowledgeC database, joining ZOBJECT with ZSTRUCTUREDMETADATA and ZSOURCE tables. ```sql SELECT DATETIME(ZOBJECT.ZSTARTDATE+978307200,'UNIXEPOCH') AS "START", DATETIME(ZOBJECT.ZENDDATE+978307200,'UNIXEPOCH') AS "END", ZOBJECT.ZVALUESTRING AS "BUNDLE ID", ZSTRUCTUREDMETADATA.Z_DKAPPINSTALLMETADATAKEY__PRIMARYCATEGORY AS "APP CATEGORY", ZSTRUCTUREDMETADATA.Z_DKAPPINSTALLMETADATAKEY__SUBCATEGORIES AS "APP SUBCATEGORY", ZSTRUCTUREDMETADATA.Z_DKAPPINSTALLMETADATAKEY__TITLE AS "APP NAME", ZSTRUCTUREDMETADATA.Z_DKAPPINSTALLMETADATAKEY__ISINSTALL AS "IS INSTALL", CASE ZOBJECT.ZSTARTDAYOFWEEK WHEN "1" THEN "Sunday" WHEN "2" THEN "Monday" WHEN "3" THEN "Tuesday" WHEN "4" THEN "Wednesday" WHEN "5" THEN "Thursday" WHEN "6" THEN "Friday" WHEN "7" THEN "Saturday" END "DAY OF WEEK", ZOBJECT.ZSECONDSFROMGMT/3600 AS "GMT OFFSET", DATETIME(ZOBJECT.ZCREATIONDATE+978307200,'UNIXEPOCH') AS "ENTRY CREATION", ZOBJECT.ZUUID AS "UUID", ZSTRUCTUREDMETADATA.ZMETADATAHASH, ZOBJECT.Z_PK AS "ZOBJECT TABLE ID" FROM ZOBJECT LEFT JOIN ZSTRUCTUREDMETADATA ON ZOBJECT.ZSTRUCTUREDMETADATA = ZSTRUCTUREDMETADATA.Z_PK LEFT JOIN ZSOURCE ON ZOBJECT.ZSOURCE = ZSOURCE.Z_PK WHERE ZSTREAMNAME = "/app/install" ``` -------------------------------- ### Install SAF dependencies on MacOS Source: https://github.com/ec-digit-csirc/sysdiagnose/blob/main/README.md Set up a virtual environment and install required packages on Apple Silicon systems. ```bash python3 -m venv venv source venv/bin/activate pip3 install . brew install graphviz # Or your prefered way of installing graphviz binaries brew install libmagic ``` -------------------------------- ### Example log-parse.jsonl Entries Source: https://context7.com/ec-digit-csirc/sysdiagnose/llms.txt Example entries from the log-parse.jsonl file, showing INFO level logs with details about parser execution. ```json {"levelname": "INFO", "module": "main", "message": "Parser 'ps' started", "parser": "ps", "datetime": "2024-09-18T12:16:59.153931+01:00"} {"levelname": "INFO", "module": "ps", "message": "Processing file ./cases/1/data/.../ps.txt", "datetime": "2024-09-18T12:16:59.201914+01:00"} {"levelname": "INFO", "module": "main", "message": "Parser 'ps' finished successfully", "parser": "ps", "result": 0, "datetime": "2024-09-18T12:16:59.202495+01:00"} ``` -------------------------------- ### Start Timesketch Services Source: https://github.com/ec-digit-csirc/sysdiagnose/blob/main/scripts/timesketch/README.md Initializes the Timesketch environment using Docker Compose. ```bash cd timesketch sudo docker compose up -d ``` -------------------------------- ### Install Rust cargo Source: https://github.com/ec-digit-csirc/sysdiagnose/blob/main/README.md Installs the cargo package manager required for building Rust projects on Ubuntu. ```bash sudo apt install cargo ``` -------------------------------- ### ps.jsonl Output Example Source: https://context7.com/ec-digit-csirc/sysdiagnose/llms.txt Example of the JSONL output format for process information (ps). Each line represents a distinct event. ```json { "datetime": "2024-09-18T11:33:57.000000+02:00", "message": "Process kernel_task [0] running as root", "timestamp_desc": "process running", "module": "ps", "data": { "user": "root", "pid": 0, "ppid": 0, "cpu": 0.0, "mem": 0.0, "vsz": 0, "rss": 0, "tt": "??", "stat": "DLs", "started": "Thu12AM", "time": "256:23.45", "command": "kernel_task", "timestamp_info": "sysdiagnose creation time" } } ``` -------------------------------- ### apps.json Output Example Source: https://context7.com/ec-digit-csirc/sysdiagnose/llms.txt Example of the JSON output format for the apps analyser. It details found data sources and associated services or libraries for each application. ```json { "com.apple.mobilesafari": { "found": ["accessibility-tcc", "logarchive"], "services": ["kTCCServicePhotos"] }, "com.whatsapp.WhatsApp": { "found": ["itunesstore", "brctl", "logarchive"], "libraries": ["library-id-123"] }, "com.spotify.client": { "found": ["accessibility-tcc", "logarchive"], "services": ["kTCCServiceMediaLibrary"] } } ``` -------------------------------- ### Configure Colima VM Resources Source: https://github.com/ec-digit-csirc/sysdiagnose/blob/main/scripts/elk/README.md Commands to stop, reconfigure, and start a Colima VM with the required hardware resources, followed by container initialization. ```bash # stop current Colima VM colima stop # start Colima with more resources (example: 8 CPUs, 32 GB RAM, 120 GB disk) colima start --cpu 8 --memory 32 --disk 120 # verify Colima is running colima status # Create the containers docker compose up -d ``` -------------------------------- ### crashlogs.jsonl Output Example Source: https://context7.com/ec-digit-csirc/sysdiagnose/llms.txt Example of the JSONL output format for crash logs. Each line contains details about a specific crash. ```json { "datetime": "2024-09-15T14:22:33.123456+02:00", "message": "Crashlog: SpringBoard", "timestamp_desc": "crashlog", "module": "crashlogs", "data": { "name": "SpringBoard", "incident_id": "ABC123-DEF456", "report": { "procName": "SpringBoard", "reason": "SIGABRT" } } } ``` -------------------------------- ### Process Matrix Output Format Source: https://context7.com/ec-digit-csirc/sysdiagnose/llms.txt Example text output for the process matrix analysis. ```text // Example: ps_matrix.txt output +-------+------------------+------+------+ | pid | cmd | pid | ppid | |-------+------------------+------+------| | 0 | kernel_task | True | True | | 1 | launchd | True | True | | 123 | suspicious_proc | True | None != None != 1 | +-------+------------------+------+------+ ``` -------------------------------- ### wifi_geolocation.gpx Output Example Source: https://context7.com/ec-digit-csirc/sysdiagnose/llms.txt Example of the GPX output format for WiFi geolocation data. It includes waypoints with latitude, longitude, time, and descriptive information about the WiFi network. ```xml HomeNetwork BSSID: aa:bb:cc:dd:ee:ff Channel: 6 LocationAccuracy: 65.0 Reason for Adding: UserAdded OfficeWiFi ``` -------------------------------- ### SQL Query for Smart Top Off Engagement Source: https://github.com/ec-digit-csirc/sysdiagnose/blob/main/src/sysdiagnose/utils/apollo_modules/knowledge_charging_smart_top_off_engagement.txt Extracts start dates, days of the week, GMT offsets, and creation dates from the ZOBJECT table filtered by the smartTopOffEngagement stream. ```sql SELECT DATETIME(ZOBJECT.ZSTARTDATE+978307200,'UNIXEPOCH') AS "START", CASE ZOBJECT.ZSTARTDAYOFWEEK WHEN "1" THEN "Sunday" WHEN "2" THEN "Monday" WHEN "3" THEN "Tuesday" WHEN "4" THEN "Wednesday" WHEN "5" THEN "Thursday" WHEN "6" THEN "Friday" WHEN "7" THEN "Saturday" END "DAY OF WEEK", ZOBJECT.ZSECONDSFROMGMT/3600 AS "GMT OFFSET", DATETIME(ZOBJECT.ZCREATIONDATE+978307200,'UNIXEPOCH') AS "ENTRY CREATION", ZOBJECT.ZUUID AS "UUID", ZOBJECT.Z_PK AS "ZOBJECT TABLE ID" FROM ZOBJECT WHERE ZSTREAMNAME = "/charging/smartTopOffEngagement" ``` -------------------------------- ### Get App Info (iOS 10) Source: https://github.com/ec-digit-csirc/sysdiagnose/blob/main/src/sysdiagnose/utils/apollo_modules/powerlog_app_info.txt Fetches application details for iOS 10, including name, executable, bundle ID, versions, and deletion status. Note that 'APP TYPE' is not included in this specific query for iOS 10. ```sql SELECT DATETIME(TIMESTAMP, 'UNIXEPOCH') AS TIMESTAMP, APPNAME AS "APP NAME", APPEXECUTABLE AS "APP EXECUTABLE NAME", APPBUNDLEID AS "BUNDLE ID", APPBUILDVERSION AS "APP BUILD VERSION", APPBUNDLEVERSION AS "APP BUNDLE VERSION", CASE APPDELETEDDATE WHEN 0 THEN "NOT DELETED" ELSE DATETIME(APPDELETEDDATE, 'UNIXEPOCH') END "APP DELETED DATE", ID AS "PLAPPLICATIONAGENT_EVENTNONE_ALLAPPS TABLE ID" FROM PLAPPLICATIONAGENT_EVENTNONE_ALLAPPS ``` -------------------------------- ### List available parsers Source: https://github.com/ec-digit-csirc/sysdiagnose/blob/main/README.md Display all supported parsers and their descriptions. ```bash $ sysdiag list parsers Parser Name Parser Description --------------------- --------------------------------------------------------------------- all Run all parsers accessibility_tcc Parsing Accessibility TCC logs appinstallation Parsing app installation logs brctl Parsing brctl files containermanager Parsing containermanagerd logs file crashlogs Parsing crashes folder demo_parser Demo parsers itunesstore Parsing iTunes store logs lockdownd Parsing lockdownd logs file logarchive Parsing system_logs.logarchive folder mobileactivation Parsing mobileactivation logs file mobileinstallation Parsing mobile_installation logs file networkextension Parsing networkextension plist file networkextensioncache Parsing networkextensioncache plist file olddsc Parsing olddsc files plists Parsing any pslist into json powerlogs Parsing powerlogs database ps Parsing ps.txt file psthread Parsing ps_thread.txt file remotectl_dumpstate Parsing remotectl_dumpstate file containing system information security_sysdiagnose Parsing security-sysdiagnose.txt file containing keychain information shutdownlogs Parsing shutdown.log file spindumpnosymbols Parsing spindump-nosymbols file swcutil Parsing swcutil_show file sys Parsing SystemVersion plist file taskinfo Parsing taskinfo txt file uuid2path Parsing UUIDToBinaryLocations plist file wifi_known_networks Parsing Known Wifi Networks plist file wifinetworks Parsing com.apple.wifi plist files wifiscan Parsing wifi_scan files wifisecurity Parsing WiFi Security logs ``` -------------------------------- ### List existing cases Source: https://github.com/ec-digit-csirc/sysdiagnose/blob/main/README.md Display all cases currently managed by the framework. ```bash $ sysdiag cases Case ID acquisition date Serial number Unique device ID iOS Version Tags ------------------- ------------------------- --------------- ---------------------------------------- ------------- ------ public 2023-05-24T13:29:15-07:00 F4GT2K24HG7K e22f7f830e5dcc1287a1690a2622c2b12afaa33c ``` -------------------------------- ### List Available Parsers Source: https://context7.com/ec-digit-csirc/sysdiagnose/llms.txt Display all available parsers along with their descriptions. The 'all' parser runs all available parsers. ```bash sysdiag list parsers ``` -------------------------------- ### SQL Query for Application In Focus Source: https://github.com/ec-digit-csirc/sysdiagnose/blob/main/src/sysdiagnose/utils/apollo_modules/knowledge_app_inFocus.txt Retrieves application usage metrics including start/end times, bundle IDs, and launch reasons from the /app/inFocus stream. ```sql SELECT DATETIME(ZOBJECT.ZSTARTDATE+978307200,'UNIXEPOCH') AS "START", DATETIME(ZOBJECT.ZENDDATE+978307200,'UNIXEPOCH') AS "END", ZOBJECT.ZVALUESTRING AS "BUNDLE ID", (ZOBJECT.ZENDDATE-ZOBJECT.ZSTARTDATE) AS "USAGE IN SECONDS", (ZOBJECT.ZENDDATE-ZOBJECT.ZSTARTDATE)/60.00 AS "USAGE IN MINUTES", ZSTRUCTUREDMETADATA .Z_DKAPPLICATIONMETADATAKEY__LAUNCHREASON AS "LAUNCH REASON", ZSTRUCTUREDMETADATA .Z_DKAPPLICATIONMETADATAKEY__EXTENSIONCONTAININGBUNDLEIDENTIFIER AS "EXTENSION CONTAINING BUNDLE ID", ZSTRUCTUREDMETADATA .Z_DKAPPLICATIONMETADATAKEY__EXTENSIONHOSTIDENTIFIER AS "EXTENSION HOST ID", CASE ZOBJECT.ZSTARTDAYOFWEEK WHEN "1" THEN "Sunday" WHEN "2" THEN "Monday" WHEN "3" THEN "Tuesday" WHEN "4" THEN "Wednesday" WHEN "5" THEN "Thursday" WHEN "6" THEN "Friday" WHEN "7" THEN "Saturday" END "DAY OF WEEK", ZOBJECT.ZSECONDSFROMGMT/3600 AS "GMT OFFSET", DATETIME(ZOBJECT.ZCREATIONDATE+978307200,'UNIXEPOCH') AS "ENTRY CREATION", ZOBJECT.ZUUID AS "UUID", ZSTRUCTUREDMETADATA.ZMETADATAHASH, ZOBJECT.Z_PK AS "ZOBJECT TABLE ID" FROM ZOBJECT LEFT JOIN ZSTRUCTUREDMETADATA ON ZOBJECT.ZSTRUCTUREDMETADATA = ZSTRUCTUREDMETADATA.Z_PK LEFT JOIN ZSOURCE ON ZOBJECT.ZSOURCE = ZSOURCE.Z_PK WHERE ZSTREAMNAME IS "/app/inFocus" ``` -------------------------------- ### YARA Scan Output Format Source: https://context7.com/ec-digit-csirc/sysdiagnose/llms.txt Example JSON structure for YARA scan results. ```json // Example: yarascan.jsonl output { "datetime": "2024-09-18T11:33:57.000000+02:00", "message": "YARA rule SuspiciousProcess from suspicious.yar matched in parsed_data/logarchive.jsonl", "timestamp_desc": "Extracted datetime from JSON entry in logarchive.jsonl", "module": "yarascan", "data": { "target_file": "parsed_data/logarchive.jsonl", "yara_rule_file": "suspicious.yar", "yara_rule": "SuspiciousProcess", "yara_rule_meta": {}, "yara_rule_tags": [], "yara_rule_match_details": [ "Id: '$a', XOR: 'False', plaintext: 'exploit', offset: '12345', excerpt: '...found exploit code...'" ] } } ``` -------------------------------- ### List images Source: https://github.com/ec-digit-csirc/sysdiagnose/blob/main/scripts/splunk/README.md Displays the images currently used by the project. ```bash docker-compose images ``` -------------------------------- ### Extract Outbound Start Dates and Visit Details (iOS 12) Source: https://github.com/ec-digit-csirc/sysdiagnose/blob/main/src/sysdiagnose/utils/apollo_modules/routined_cloud_visit_outbound_start.txt This query retrieves outbound start and stop dates, duration, coordinates, place information, data point counts, and associated address details. It joins multiple tables including ZRTLEARNEDVISITMO, ZRTDEVICEMO, ZRTLEARNEDPLACEMO, ZRTLEARNEDTRANSITIONMO, and ZRTADDRESSMO. Use this query for iOS version 12. ```SQL SELECT DATETIME(ZRTLEARNEDTRANSITIONMO.ZSTARTDATE + 978307200, 'UNIXEPOCH') AS "OUTBOUND START DATE", DATETIME(ZRTLEARNEDTRANSITIONMO.ZSTOPDATE + 978307200, 'UNIXEPOCH') AS "OUTBOUND STOP DATE", (ZRTLEARNEDTRANSITIONMO.ZSTOPDATE-ZRTLEARNEDTRANSITIONMO.ZSTARTDATE)/60.00 AS "OUTBOUND TIME (MINUTES)", ZRTLEARNEDVISITMO.ZLOCATIONLATITUDE || ", " || ZRTLEARNEDVISITMO.ZLOCATIONLONGITUDE AS "COORDINATES", ZRTLEARNEDVISITMO.ZPLACE AS "PLACE ID", ZRTLEARNEDVISITMO.ZDATAPOINTCOUNT AS "DATA POINT COUNT", ZRTADDRESSMO.ZCOUNTRY AS "COUNTRY", ZRTADDRESSMO.ZCOUNTRYCODE AS "COUNTRY CODE", ZRTADDRESSMO.ZPOSTALCODE AS "POSTAL CODE", ZRTADDRESSMO.ZLOCALITY AS "LOCALITY", ZRTADDRESSMO.ZSUBLOCALITY AS "SUBLOCALITY", ZRTADDRESSMO.ZTHOROUGHFARE AS "THROROUGHFARE", ZRTADDRESSMO.ZSUBTHOROUGHFARE AS "SUBTHOROUGHFARE", ZRTADDRESSMO.ZSUBADMINISTRATIVEAREA AS "SUBADMINISTRATIVE AREA", CAST(ZRTADDRESSMO.ZAREASOFINTEREST AS TEXT) AS "AREA OF INTEREST", ZRTADDRESSMO.ZOCEAN AS "OCEAN", ZRTADDRESSMO.ZINLANDWATER AS "INLAND WATER", ZRTADDRESSMO.ZISLAND AS "ISLAND", ZRTLEARNEDVISITMO.ZLOCATIONUNCERTAINTY AS "LOCATION UNCERTAINTY", ZRTLEARNEDVISITMO.ZCONFIDENCE AS "CONFIDENCE", DATETIME(ZRTLEARNEDVISITMO.ZENTRYDATE + 978307200, 'UNIXEPOCH') AS "VISIT ENTRY", DATETIME(ZRTLEARNEDVISITMO.ZEXITDATE + 978307200, 'UNIXEPOCH') AS "VISIT EXIT", DATETIME(ZRTLEARNEDVISITMO.ZCREATIONDATE + 978307200, 'UNIXEPOCH') AS "VISIT CREATION", DATETIME(ZRTLEARNEDVISITMO.ZEXPIRATIONDATE + 978307200, 'UNIXEPOCH') AS "VISIT EXPIRATION", ZRTDEVICEMO.ZDEVICECLASS AS "DEVICE CLASS", ZRTDEVICEMO.ZDEVICEMODEL AS "DEVICE MODEL", ZRTDEVICEMO.ZDEVICENAME AS "DEVICE NAME", DATETIME(ZRTLEARNEDPLACEMO.ZCREATIONDATE + 978307200, 'UNIXEPOCH') AS "LEARNED PLACE CREATION", DATETIME(ZRTLEARNEDPLACEMO.ZEXPIRATIONDATE + 978307200, 'UNIXEPOCH') AS "LEARNED PLACE EXPIRATION", DATETIME(ZRTADDRESSMO.ZCREATIONDATE + 978307200, 'UNIXEPOCH') AS "ADDRESS CREATION" FROM ZRTLEARNEDVISITMO LEFT JOIN ZRTDEVICEMO ON ZRTLEARNEDVISITMO.ZDEVICE = ZRTDEVICEMO.Z_PK LEFT JOIN ZRTLEARNEDPLACEMO ON ZRTLEARNEDPLACEMO.Z_PK = ZRTLEARNEDVISITMO.ZPLACE LEFT JOIN ZRTLEARNEDTRANSITIONMO ON ZRTLEARNEDTRANSITIONMO.Z_PK = ZRTLEARNEDVISITMO.ZINBOUND LEFT JOIN ZRTADDRESSMO ON ZRTADDRESSMO.ZMAPITEM = ZRTLEARNEDPLACEMO.ZMAPITEM WHERE "OUTBOUND START DATE" NOT NULL ``` -------------------------------- ### Run Apps Analyser Source: https://context7.com/ec-digit-csirc/sysdiagnose/llms.txt Analyzes installed applications after parsing all data. The results are saved in a JSON file. ```bash sysdiag -c 1 parse all sysdiag -c 1 analyse apps ``` -------------------------------- ### Extract Outbound Start Dates and Visit Details (iOS 13, 14) Source: https://github.com/ec-digit-csirc/sysdiagnose/blob/main/src/sysdiagnose/utils/apollo_modules/routined_cloud_visit_outbound_start.txt This query retrieves outbound start and stop dates, duration, coordinates, place information, data point counts, and associated address details. It joins multiple tables including ZRTLEARNEDVISITMO, ZRTDEVICEMO, ZRTLEARNEDPLACEMO, ZRTLEARNEDTRANSITIONMO, ZRTADDRESSMO, and ZRTMAPITEMMO. Use this query for iOS versions 13 and 14. ```SQL SELECT DATETIME(ZRTLEARNEDTRANSITIONMO.ZSTARTDATE + 978307200, 'UNIXEPOCH') AS "OUTBOUND START DATE", DATETIME(ZRTLEARNEDTRANSITIONMO.ZSTOPDATE + 978307200, 'UNIXEPOCH') AS "OUTBOUND STOP DATE", (ZRTLEARNEDTRANSITIONMO.ZSTOPDATE-ZRTLEARNEDTRANSITIONMO.ZSTARTDATE)/60.00 AS "OUTBOUND TIME (MINUTES)", ZRTLEARNEDVISITMO.ZLOCATIONLATITUDE || ", " || ZRTLEARNEDVISITMO.ZLOCATIONLONGITUDE AS "COORDINATES", ZRTLEARNEDVISITMO.ZPLACE AS "PLACE ID", ZRTLEARNEDVISITMO.ZDATAPOINTCOUNT AS "DATA POINT COUNT", ZRTADDRESSMO.ZCOUNTRY AS "COUNTRY", ZRTADDRESSMO.ZCOUNTRYCODE AS "COUNTRY CODE", ZRTADDRESSMO.ZPOSTALCODE AS "POSTAL CODE", ZRTADDRESSMO.ZLOCALITY AS "LOCALITY", ZRTADDRESSMO.ZSUBLOCALITY AS "SUBLOCALITY", ZRTADDRESSMO.ZTHOROUGHFARE AS "THROROUGHFARE", ZRTADDRESSMO.ZSUBTHOROUGHFARE AS "SUBTHOROUGHFARE", ZRTADDRESSMO.ZSUBADMINISTRATIVEAREA AS "SUBADMINISTRATIVE AREA", CAST(ZRTADDRESSMO.ZAREASOFINTEREST AS TEXT) AS "AREA OF INTEREST", ZRTADDRESSMO.ZOCEAN AS "OCEAN", ZRTADDRESSMO.ZINLANDWATER AS "INLAND WATER", ZRTADDRESSMO.ZISLAND AS "ISLAND", ZRTLEARNEDVISITMO.ZLOCATIONUNCERTAINTY AS "LOCATION UNCERTAINTY", ZRTLEARNEDVISITMO.ZCONFIDENCE AS "CONFIDENCE", DATETIME(ZRTLEARNEDVISITMO.ZENTRYDATE + 978307200, 'UNIXEPOCH') AS "VISIT ENTRY", DATETIME(ZRTLEARNEDVISITMO.ZEXITDATE + 978307200, 'UNIXEPOCH') AS "VISIT EXIT", DATETIME(ZRTLEARNEDVISITMO.ZCREATIONDATE + 978307200, 'UNIXEPOCH') AS "VISIT CREATION", DATETIME(ZRTLEARNEDVISITMO.ZEXPIRATIONDATE + 978307200, 'UNIXEPOCH') AS "VISIT EXPIRATION", ZRTDEVICEMO.ZDEVICECLASS AS "DEVICE CLASS", ZRTDEVICEMO.ZDEVICEMODEL AS "DEVICE MODEL", ZRTDEVICEMO.ZDEVICENAME AS "DEVICE NAME", DATETIME(ZRTLEARNEDPLACEMO.ZCREATIONDATE + 978307200, 'UNIXEPOCH') AS "LEARNED PLACE CREATION", DATETIME(ZRTLEARNEDPLACEMO.ZEXPIRATIONDATE + 978307200, 'UNIXEPOCH') AS "LEARNED PLACE EXPIRATION", DATETIME(ZRTADDRESSMO.ZCREATIONDATE + 978307200, 'UNIXEPOCH') AS "ADDRESS CREATION", ZRTLEARNEDVISITMO.ZLOCATIONLATITUDE AS "LATITUDE", ZRTLEARNEDVISITMO.ZLOCATIONLONGITUDE AS "LONGITUDE", ZRTMAPITEMMO.ZLATITUDE || ", " || ZRTMAPITEMMO.ZLONGITUDE AS "MAP ITEM COORDINATES", DATETIME(ZRTMAPITEMMO.ZCREATIONDATE + 978307200, 'UNIXEPOCH') AS "MAP ITEM CREATION DATE", DATETIME(ZRTMAPITEMMO.ZEXPIRATIONDATE + 978307200, 'UNIXEPOCH') AS "MAP ITEM EXPIRATION DATE", ZRTMAPITEMMO.ZLATITUDE AS "MAP ITEM LATITUTE", ZRTMAPITEMMO.ZLONGITUDE AS "MAP ITEM LONGITUDE", ZRTMAPITEMMO.ZUNCERTAINTY AS "UNCERTAINTY", ZRTMAPITEMMO.ZDISPLAYLANGUAGE AS "MAP ITEM LANGUAGE", ZRTMAPITEMMO.ZNAME AS "MAP ITEM NAME", HEX(ZRTMAPITEMMO.ZGEOMAPITEMHANDLE) AS "MAP ITEM GEOMAPITEM (HEX PROTOBUF)", ZRTLEARNEDVISITMO.Z_PK AS "ZRTLEARNEDVISITMO TABLE ID" FROM ZRTLEARNEDVISITMO LEFT JOIN ZRTDEVICEMO ON ZRTLEARNEDVISITMO.ZDEVICE = ZRTDEVICEMO.Z_PK LEFT JOIN ZRTLEARNEDPLACEMO ON ZRTLEARNEDPLACEMO.Z_PK = ZRTLEARNEDVISITMO.ZPLACE LEFT JOIN ZRTLEARNEDTRANSITIONMO ON ZRTLEARNEDTRANSITIONMO.Z_PK = ZRTLEARNEDVISITMO.ZINBOUND LEFT JOIN ZRTADDRESSMO ON ZRTADDRESSMO.ZMAPITEM = ZRTLEARNEDPLACEMO.ZMAPITEM LEFT JOIN ZRTMAPITEMMO ON ZRTMAPITEMMO.Z_PK = ZRTLEARNEDPLACEMO.ZMAPITEM WHERE "OUTBOUND START DATE" NOT NULL ``` -------------------------------- ### Run All Analysers CLI Source: https://context7.com/ec-digit-csirc/sysdiagnose/llms.txt Commands to execute all available parsers and analysers. ```bash # First ensure all parsers have run sysdiag -c 1 parse all # Run all analysers sysdiag -c 1 analyse all # Run all analysers except specific ones sysdiag -c 1 analyse all -x yarascan ``` -------------------------------- ### SQL Query for Airplane Mode Source: https://github.com/ec-digit-csirc/sysdiagnose/blob/main/src/sysdiagnose/utils/apollo_modules/knowledge_system_airplane_mode.txt Extracts start and end times, duration, and metadata for airplane mode events from the ZOBJECT table. ```sql SELECT DATETIME(ZOBJECT.ZSTARTDATE+978307200,'UNIXEPOCH') AS "START", DATETIME(ZOBJECT.ZENDDATE+978307200,'UNIXEPOCH') AS "END", (ZOBJECT.ZENDDATE - ZOBJECT.ZSTARTDATE) AS "USAGE IN SECONDS", (ZOBJECT.ZENDDATE - ZOBJECT.ZSTARTDATE)/60.00 AS "USAGE IN MINUTES", ZOBJECT.ZVALUEDOUBLE AS "VALUE", CASE ZOBJECT.ZSTARTDAYOFWEEK WHEN "1" THEN "Sunday" WHEN "2" THEN "Monday" WHEN "3" THEN "Tuesday" WHEN "4" THEN "Wednesday" WHEN "5" THEN "Thursday" WHEN "6" THEN "Friday" WHEN "7" THEN "Saturday" END "DAY OF WEEK", ZOBJECT.ZSECONDSFROMGMT/3600 AS "GMT OFFSET", DATETIME(ZOBJECT.ZCREATIONDATE+978307200,'UNIXEPOCH') AS "ENTRY CREATION", ZOBJECT.ZUUID AS "UUID", ZOBJECT.Z_PK AS "ZOBJECT TABLE ID" FROM ZOBJECT LEFT JOIN ZSTRUCTUREDMETADATA ON ZOBJECT.ZSTRUCTUREDMETADATA = ZSTRUCTUREDMETADATA.Z_PK LEFT JOIN ZSOURCE ON ZOBJECT.ZSOURCE = ZSOURCE.Z_PK WHERE ZSTREAMNAME = "/system/airplaneMode" ``` -------------------------------- ### Extract Transition and Visit Details Source: https://github.com/ec-digit-csirc/sysdiagnose/blob/main/src/sysdiagnose/utils/apollo_modules/routined_cloud_visit_inbound_start.txt This query retrieves transition data, including start and stop dates, duration in minutes, coordinates, place ID, data point count, location uncertainty, confidence, and visit details. It joins ZRTLEARNEDVISITMO with ZRTDEVICEMO, ZRTLEARNEDPLACEMO, and ZRTLEARNEDTRANSITIONMO. This is useful for analyzing movement patterns and visit durations. ```sql SELECT DATETIME(ZRTLEARNEDTRANSITIONMO.ZSTARTDATE + 978307200, 'UNIXEPOCH') AS "INBOUND START DATE", DATETIME(ZRTLEARNEDTRANSITIONMO.ZSTOPDATE + 978307200, 'UNIXEPOCH') AS "INBOUND STOP DATE", (ZRTLEARNEDTRANSITIONMO.ZSTOPDATE-ZRTLEARNEDTRANSITIONMO.ZSTARTDATE)/60.00 AS "INBOUND TIME (MINUTES)", ZRTLEARNEDVISITMO.ZLOCATIONLATITUDE || ", " || ZRTLEARNEDVISITMO.ZLOCATIONLONGITUDE AS "COORDINATES", ZRTLEARNEDVISITMO.ZPLACE AS "PLACE ID", ZRTLEARNEDVISITMO.ZDATAPOINTCOUNT AS "DATA POINT COUNT", ZRTLEARNEDVISITMO.ZLOCATIONUNCERTAINTY AS "LOCATION UNCERTAINTY", ZRTLEARNEDVISITMO.ZCONFIDENCE AS "CONFIDENCE", DATETIME(ZRTLEARNEDVISITMO.ZENTRYDATE + 978307200, 'UNIXEPOCH') AS "VISIT ENTRY", DATETIME(ZRTLEARNEDVISITMO.ZEXITDATE + 978307200, 'UNIXEPOCH') AS "VISIT EXIT", DATETIME(ZRTLEARNEDVISITMO.ZCREATIONDATE + 978307200, 'UNIXEPOCH') AS "VISIT CREATION", DATETIME(ZRTLEARNEDVISITMO.ZEXPIRATIONDATE + 978307200, 'UNIXEPOCH') AS "VISIT EXPIRATION", ZRTDEVICEMO.ZDEVICECLASS AS "DEVICE CLASS", ZRTDEVICEMO.ZDEVICEMODEL AS "DEVICE MODEL", ZRTDEVICEMO.ZDEVICENAME AS "DEVICE NAME", DATETIME(ZRTLEARNEDPLACEMO.ZCREATIONDATE + 978307200, 'UNIXEPOCH') AS "LEARNED PLACE CREATION", DATETIME(ZRTLEARNEDPLACEMO.ZEXPIRATIONDATE + 978307200, 'UNIXEPOCH') AS "LEARNED PLACE EXPIRATION", DATETIME(ZRTLEARNEDPLACEMO.ZMAPITEMCREATIONDATE + 978307200, 'UNIXEPOCH') AS "MAP ITEM CREATION", HEX(ZRTLEARNEDPLACEMO.ZMAPITEMGEOMAPITEMHANDLE) AS "PLACE NAME BLOB (HEX)", HEX(ZRTLEARNEDPLACEMO.ZMAPITEMGEOMAPITEM) AS "PLACE GEO BLOB (HEX)", ZRTLEARNEDVISITMO.ZLOCATIONLATITUDE AS "LATITUDE", ZRTLEARNEDVISITMO.ZLOCATIONLONGITUDE AS "LONGITUDE", ZRTLEARNEDVISITMO.Z_PK AS "ZRTLEARNEDVISITMO TABLE ID" FROM ZRTLEARNEDVISITMO LEFT JOIN ZRTDEVICEMO ON ZRTLEARNEDVISITMO.ZDEVICE = ZRTDEVICEMO.Z_PK LEFT JOIN ZRTLEARNEDPLACEMO ON ZRTLEARNEDPLACEMO.Z_PK = ZRTLEARNEDVISITMO.ZPLACE LEFT JOIN ZRTLEARNEDTRANSITIONMO ON ZRTLEARNEDTRANSITIONMO.Z_PK = ZRTLEARNEDVISITMO.ZINBOUND WHERE "INBOUND STOP DATE" NOT NULL ``` -------------------------------- ### Query Inferred Motion from knowledgeC.db Source: https://github.com/ec-digit-csirc/sysdiagnose/blob/main/src/sysdiagnose/utils/apollo_modules/knowledge_device_inferred_motion.txt Extracts start and end times, duration, and motion values from the ZOBJECT table filtered by the /inferred/motion stream. ```sql SELECT DATETIME(ZOBJECT.ZSTARTDATE+978307200,'UNIXEPOCH') AS "START", DATETIME(ZOBJECT.ZENDDATE+978307200,'UNIXEPOCH') AS "END", ZOBJECT.ZVALUEINTEGER AS "VALUE", (ZOBJECT.ZENDDATE - ZOBJECT.ZSTARTDATE) AS "USAGE IN SECONDS", (ZOBJECT.ZENDDATE - ZOBJECT.ZSTARTDATE)/60.00 AS "USAGE IN MINUTES", CASE ZOBJECT.ZSTARTDAYOFWEEK WHEN "1" THEN "Sunday" WHEN "2" THEN "Monday" WHEN "3" THEN "Tuesday" WHEN "4" THEN "Wednesday" WHEN "5" THEN "Thursday" WHEN "6" THEN "Friday" WHEN "7" THEN "Saturday" END "DAY OF WEEK", ZOBJECT.ZSECONDSFROMGMT/3600 AS "GMT OFFSET", DATETIME(ZOBJECT.ZCREATIONDATE+978307200,'UNIXEPOCH') AS "ENTRY CREATION", ZOBJECT.ZUUID AS "UUID", ZOBJECT.Z_PK AS "ZOBJECT TABLE ID" FROM ZOBJECT LEFT JOIN ZSTRUCTUREDMETADATA ON ZOBJECT.ZSTRUCTUREDMETADATA = ZSTRUCTUREDMETADATA.Z_PK LEFT JOIN ZSOURCE ON ZOBJECT.ZSOURCE = ZSOURCE.Z_PK WHERE ZSTREAMNAME = "/inferred/motion" ``` -------------------------------- ### List Available Analysers Source: https://context7.com/ec-digit-csirc/sysdiagnose/llms.txt Displays all available analysers along with their descriptions. This command helps in understanding the analytical capabilities of the tool. ```bash sysdiag list analysers ``` -------------------------------- ### SQL Query for Health Flights Climbed Source: https://github.com/ec-digit-csirc/sysdiagnose/blob/main/src/sysdiagnose/utils/apollo_modules/health_flights.txt Extracts start and end dates, quantity of flights climbed, and duration from the SAMPLES and QUANTITY_SAMPLES tables. ```sql SELECT DATETIME(SAMPLES.START_DATE + 978307200, 'UNIXEPOCH') AS "START DATE", DATETIME(SAMPLES.END_DATE + 978307200, 'UNIXEPOCH') AS "END DATE", QUANTITY AS "FLIGHTS CLIMBED", (SAMPLES.END_DATE-SAMPLES.START_DATE) AS "TIME IN SECONDS", SAMPLES.DATA_ID AS "SAMPLES TABLE ID" FROM SAMPLES LEFT OUTER JOIN QUANTITY_SAMPLES ON SAMPLES.DATA_ID = QUANTITY_SAMPLES.DATA_ID WHERE SAMPLES.DATA_TYPE = 12 ``` -------------------------------- ### Query Visit Details and Device Class Source: https://github.com/ec-digit-csirc/sysdiagnose/blob/main/src/sysdiagnose/utils/apollo_modules/routined_cloud_visit_exit.txt This query extracts visit entry and exit times, duration in minutes, coordinates, place IDs, and data point counts. It also includes device class, model, and name, along with learned place creation and expiration dates. The query joins ZRTLEARNEDVISITMO with ZRTDEVICEMO and ZRTLEARNEDPLACEMO. ```sql SELECT DATETIME(ZRTLEARNEDVISITMO.ZENTRYDATE + 978307200, 'UNIXEPOCH') AS "VISIT ENTRY", DATETIME(ZRTLEARNEDVISITMO.ZEXITDATE + 978307200, 'UNIXEPOCH') AS "VISIT EXIT", (ZEXITDATE-ZENTRYDATE)/60.00 AS "VISIT TIME (MINUTES)", ZRTLEARNEDVISITMO.ZLOCATIONLATITUDE || ", " || ZRTLEARNEDVISITMO.ZLOCATIONLONGITUDE AS "COORDINATES", ZRTLEARNEDVISITMO.ZPLACE AS "PLACE ID", ZRTLEARNEDVISITMO.ZDATAPOINTCOUNT AS "DATA POINT COUNT", ZRTLEARNEDVISITMO.ZLOCATIONUNCERTAINTY AS "LOCATION UNCERTAINTY", ZRTLEARNEDVISITMO.ZCONFIDENCE AS "CONFIDENCE", DATETIME(ZRTLEARNEDVISITMO.ZCREATIONDATE + 978307200, 'UNIXEPOCH') AS "VISIT CREATION", DATETIME(ZRTLEARNEDVISITMO.ZEXPIRATIONDATE + 978307200, 'UNIXEPOCH') AS "VISIT EXPIRATION", ZRTDEVICEMO.ZDEVICECLASS AS "DEVICE CLASS", ZRTDEVICEMO.ZDEVICEMODEL AS "DEVICE MODEL", ZRTDEVICEMO.ZDEVICENAME AS "DEVICE NAME", DATETIME(ZRTLEARNEDPLACEMO.ZCREATIONDATE + 978307200, 'UNIXEPOCH') AS "LEARNED PLACE CREATION", DATETIME(ZRTLEARNEDPLACEMO.ZEXPIRATIONDATE + 978307200, 'UNIXEPOCH') AS "LEARNED PLACE EXPIRATION", DATETIME(ZRTLEARNEDPLACEMO.ZMAPITEMCREATIONDATE + 978307200, 'UNIXEPOCH') AS "MAP ITEM CREATION", HEX(ZRTLEARNEDPLACEMO.ZMAPITEMGEOMAPITEMHANDLE) AS "PLACE NAME BLOB (HEX)", HEX(ZRTLEARNEDPLACEMO.ZMAPITEMGEOMAPITEM) AS "PLACE GEO BLOB (HEX)", ZRTLEARNEDVISITMO.ZLOCATIONLATITUDE AS "LATITUDE", ZRTLEARNEDVISITMO.ZLOCATIONLONGITUDE AS "LONGITUDE", ZRTLEARNEDVISITMO.Z_PK AS "ZRTLEARNEDVISITMO TABLE ID" FROM ZRTLEARNEDVISITMO LEFT JOIN ZRTDEVICEMO ON ZRTLEARNEDVISITMO.ZDEVICE = ZRTDEVICEMO.Z_PK LEFT JOIN ZRTLEARNEDPLACEMO ON ZRTLEARNEDPLACEMO.Z_PK = ZRTLEARNEDVISITMO.ZPLACE ``` -------------------------------- ### Query Inbound Transition and Visit Data Source: https://github.com/ec-digit-csirc/sysdiagnose/blob/main/src/sysdiagnose/utils/apollo_modules/routined_cloud_visit_inbound_stop.txt Extracts transition timing and visit confidence metrics, filtering for records with valid inbound start dates. ```sql SELECT DATETIME(ZRTLEARNEDTRANSITIONMO.ZSTARTDATE + 978307200, 'UNIXEPOCH') AS "INBOUND START DATE", DATETIME(ZRTLEARNEDTRANSITIONMO.ZSTOPDATE + 978307200, 'UNIXEPOCH') AS "INBOUND STOP DATE", (ZRTLEARNEDTRANSITIONMO.ZSTOPDATE-ZRTLEARNEDTRANSITIONMO.ZSTARTDATE)/60.00 AS "INBOUND TIME (MINUTES)", ZRTLEARNEDVISITMO.ZLOCATIONLATITUDE || ", " || ZRTLEARNEDVISITMO.ZLOCATIONLONGITUDE AS "COORDINATES", ZRTLEARNEDVISITMO.ZPLACE AS "PLACE ID", ZRTLEARNEDVISITMO.ZDATAPOINTCOUNT AS "DATA POINT COUNT", ZRTLEARNEDVISITMO.ZLOCATIONUNCERTAINTY AS "LOCATION UNCERTAINTY", ZRTLEARNEDVISITMO.ZCONFIDENCE AS "CONFIDENCE", DATETIME(ZRTLEARNEDVISITMO.ZENTRYDATE + 978307200, 'UNIXEPOCH') AS "VISIT ENTRY", DATETIME(ZRTLEARNEDVISITMO.ZEXITDATE + 978307200, 'UNIXEPOCH') AS "VISIT EXIT", DATETIME(ZRTLEARNEDVISITMO.ZCREATIONDATE + 978307200, 'UNIXEPOCH') AS "VISIT CREATION", DATETIME(ZRTLEARNEDVISITMO.ZEXPIRATIONDATE + 978307200, 'UNIXEPOCH') AS "VISIT EXPIRATION", ZRTDEVICEMO.ZDEVICECLASS AS "DEVICE CLASS", ZRTDEVICEMO.ZDEVICEMODEL AS "DEVICE MODEL", ZRTDEVICEMO.ZDEVICENAME AS "DEVICE NAME", DATETIME(ZRTLEARNEDPLACEMO.ZCREATIONDATE + 978307200, 'UNIXEPOCH') AS "LEARNED PLACE CREATION", DATETIME(ZRTLEARNEDPLACEMO.ZEXPIRATIONDATE + 978307200, 'UNIXEPOCH') AS "LEARNED PLACE EXPIRATION", DATETIME(ZRTLEARNEDPLACEMO.ZMAPITEMCREATIONDATE + 978307200, 'UNIXEPOCH') AS "MAP ITEM CREATION", HEX(ZRTLEARNEDPLACEMO.ZMAPITEMGEOMAPITEMHANDLE) AS "PLACE NAME BLOB (HEX)", HEX(ZRTLEARNEDPLACEMO.ZMAPITEMGEOMAPITEM) AS "PLACE GEO BLOB (HEX)", ZRTLEARNEDVISITMO.ZLOCATIONLATITUDE AS "LATITUDE", ZRTLEARNEDVISITMO.ZLOCATIONLONGITUDE AS "LONGITUDE", ZRTLEARNEDVISITMO.Z_PK AS "ZRTLEARNEDVISITMO TABLE ID" FROM ZRTLEARNEDVISITMO LEFT JOIN ZRTDEVICEMO ON ZRTLEARNEDVISITMO.ZDEVICE = ZRTDEVICEMO.Z_PK LEFT JOIN ZRTLEARNEDPLACEMO ON ZRTLEARNEDPLACEMO.Z_PK = ZRTLEARNEDVISITMO.ZPLACE LEFT JOIN ZRTLEARNEDTRANSITIONMO ON ZRTLEARNEDTRANSITIONMO.Z_PK = ZRTLEARNEDVISITMO.ZINBOUND WHERE "INBOUND START DATE" NOT NULL ```