iOS Unified Logs - Parsing ... all my SQL QUERIES!

This article documents all the SQL queries used in my parsing tool (iOS Unified Logs - Parsing) to extract iOS Unified Logs that I believe are relevant for forensic analysis. These queries are the result of many hours of work, research, and validation.

If you use them in your own tools, presentations, articles, thesis, or if you decide to improve or build upon them, please make sure to always credit the author (Lionel Notari) and the source (www.ios-unifiedlogs.com). I would really appreciate it! Thanks a lot!

iOS Unified Logs - SQL Queries

iOS Unified Logs - SQL Queries = [

 # LOCK / UNLOCK

-- springboard logs

SELECT *, 'Lock/Unlock' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(process) = 'springboard' AND (

    (LOWER(eventMessage) LIKE '%authentication request%' AND LOWER(eventMessage) LIKE '%haspasscode%') OR

    LOWER(eventMessage) LIKE '%unlock attempt succeeded%' OR

    LOWER(eventMessage) LIKE '%keybag state changed:%' OR

    LOWER(eventMessage) LIKE '%base unlock behavior received biometric event%' OR

    LOWER(eventMessage) LIKE '%Locking the device with lock button source%'

);

-- apsd logs

SELECT *, 'Lock/Unlock' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(process) = 'apsd'

  AND LOWER(eventMessage) LIKE '%screen did%';

-- bluetoothd logs

SELECT *, 'Lock/Unlock' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(process) = 'bluetoothd'

  AND LOWER(eventMessage) LIKE '%lock state changed%';

-- chronod logs

SELECT *, 'Lock/Unlock' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(process) = 'chronod'

  AND LOWER(eventMessage) LIKE '%transition%';

-- assistantd logs

SELECT *, 'Lock/Unlock' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(process) = 'assistantd'

  AND LOWER(eventMessage) LIKE '%aks: locked%';

        # iOS Unified Logs - HORIZONTAL SCROLLING

SELECT *, 'Scroll' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(process) = 'springboard'

  AND LOWER(eventMessage) LIKE '%sbrootfolderview%'

  AND LOWER(eventMessage) LIKE '%scroll%';

        # iOS Unified Logs - KEYBOARD

SELECT *, 'Keyboard' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(process) = 'audiomxd'

  AND LOWER(eventMessage) LIKE '%updated keyboard state%';

        # iOS Unified Logs - DICTATION

SELECT *, 'Dictation' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(eventMessage) LIKE '%uidictationconnection startdictationwithlanguagecode%'

   OR (LOWER(eventMessage) LIKE '%prepare audio provider with context%'

       AND LOWER(eventMessage) LIKE '%recordtype[csaudiorecordtypedictation]%')

   OR LOWER(eventMessage) LIKE '%dictation did begin%'

   OR LOWER(eventMessage) LIKE '%dictation did end%';

        # iOS Unified Logs - BATTERY

-- powerd logs

SELECT *, 'Battery' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(process) = 'powerd'

  AND LOWER(eventMessage) LIKE '%battery capacity change posted%';

-- springboard logs

SELECT *, 'Battery' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(process) = 'springboard'

  AND LOWER(eventMessage) LIKE '%found power source%battery provides time remaining%date of manufacture%';

-- poweruiagent logs

SELECT *, 'Battery' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(process) = 'poweruiagent' AND (

  LOWER(eventMessage) LIKE '%called for battery level%externalconnected%'

  OR LOWER(eventMessage) LIKE '%handle callback%sCharging%atteryLevel%'

);

-- symptomsd logs

SELECT *, 'Battery' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(process) = 'symptomsd'

  AND LOWER(eventMessage) LIKE '%power: battery-percentage%battery-absolute-capacity-mah%battery-raw-current-capacity%';

SELECT *, 'Battery' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(eventMessage) LIKE '%battery info changed to%'

   OR LOWER(eventMessage) LIKE '%battery capacity is now at%';

        # iOS Unified Logs - App State

-- springboard logs

SELECT *, 'App State' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(process) = 'springboard' AND (

  LOWER(eventMessage) LIKE '%icon tapped%' OR

  LOWER(eventMessage) LIKE '%bootstrapping application%' OR

  LOWER(eventMessage) LIKE '%setting process visibility to:%' OR

  LOWER(eventMessage) LIKE '%scene lifecycle state did change:%' OR

  LOWER(eventMessage) LIKE '%trusted open application request%' OR

  LOWER(eventMessage) LIKE '%sbiconview touches began with event%' OR

  LOWER(eventMessage) LIKE '%launchfromlocation:sbiconlocationroot%' OR

  LOWER(eventMessage) LIKE '%sbworkspaceterminateapplication:%killed from app switcher%' OR

  LOWER(eventMessage) LIKE '%changing icon manager content visibility to hidden%'

);

-- springboard logs

SELECT *, 'App State' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(process) = 'springboard' AND (

  LOWER(eventMessage) LIKE '%didremoveexternalforegroundapplicationscenehandle%' OR

  LOWER(eventMessage) LIKE '%focused scene identity did change to%' OR

  LOWER(eventMessage) LIKE '%rules: target changed from:%'

);

-- symptomsd logs

SELECT *, 'App State' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(process) = 'symptomsd' AND (

  LOWER(eventMessage) LIKE '%foreground: true%' OR

  LOWER(eventMessage) LIKE '%noting icon tapped%'

);

-- runningboardd logs

SELECT *, 'App State' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(process) = 'runningboardd'

  AND LOWER(eventMessage) LIKE '%received termination request from%bsprocesshandlepredicateimpl%processvisibility:%processstate%maxterminationresistance%';

-- contextstored logs

SELECT *, 'App State' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(process) = 'contextstored'

  AND LOWER(eventMessage) LIKE '%/device/app/infocus%';

        # iOS Unified Logs - HOME SCREEN / APP SWITCHER

-- springboard logs (home screen transitions)

SELECT *, 'HomeScreen/App Switcher' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(process) = 'springboard' AND (

  LOWER(eventMessage) LIKE '%begin requiring home screen content%' OR

  LOWER(eventMessage) LIKE '%restoring home screen%' OR

  LOWER(eventMessage) LIKE '%unlockedenvironmentmode: home-screen%' OR

  (LOWER(eventMessage) LIKE '%dispatch event:%'

   AND LOWER(eventMessage) LIKE '%transitionid%'

   AND LOWER(eventMessage) LIKE '%appswitcher%')

);

-- general eventMessages (not limited to a specific process)

SELECT *, 'HomeScreen/App Switcher' AS label

FROM "iOS Unified Logs - General"

WHERE

  LOWER(eventMessage) LIKE '%homescreen changed:%' OR

  LOWER(eventMessage) LIKE '%updated view state: home%' OR

  LOWER(eventMessage) LIKE '%toenvironmentmode: app-switcher%' OR

  LOWER(eventMessage) LIKE '%home gesture modifier%final response: appswitcher%';

-- proximitycontrold logs

SELECT *, 'HomeScreen/App Switcher' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(process) = 'proximitycontrold'

  AND LOWER(eventMessage) LIKE '%com.apple.springboard.home-screen%';

         # iOS Unified Logs - GESTURE

-- springboard logs

SELECT *, 'Gesture' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(process) = 'springboard' AND (

  LOWER(eventMessage) LIKE '%----swipe begin%' OR

  LOWER(eventMessage) LIKE '%----swipe end%' OR

  LOWER(eventMessage) LIKE '%window did become application key:%'

);

        # iOS Unified Logs - BRIGHTNESS

-- backboardd logs

SELECT *, 'Brightness' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(process) = 'backboardd' AND (

  LOWER(eventMessage) LIKE '%brightness change:%reason:brightnesssystemdidchange options%' OR

  LOWER(eventMessage) LIKE '%set brightnesssystem property:displaybrightness%targetbrightness to%'

);

        # iOS Unified Logs - TOUCHSCREEN

-- general logs

SELECT *, 'Touchscreen' AS label

FROM "iOS Unified Logs - General"

WHERE

  LOWER(eventMessage) LIKE '%incoming request : actionid 11%' OR

  LOWER(eventMessage) LIKE '%incoming request : actionid 40%' OR

  LOWER(eventMessage) LIKE '%touchstats%';

-- backboardd logs (detailed touchscreen interactions)

SELECT *, 'Touchscreen' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(process) = 'backboardd' AND (

  LOWER(eventMessage) LIKE '%contact % presence:%' OR

  (LOWER(eventMessage) LIKE '%dispatching event with % children%' AND

   LOWER(eventMessage) LIKE '%childeventmask%' AND

   LOWER(eventMessage) LIKE '%cancel=%' AND

   LOWER(eventMessage) LIKE '%touching%' AND

   LOWER(eventMessage) LIKE '%deviceid%') OR

  (LOWER(eventMessage) LIKE '%notify client com%' AND

   LOWER(eventMessage) LIKE '%attentionawareness%' AND

   LOWER(eventMessage) LIKE '%touch of%' AND

   LOWER(eventMessage) LIKE '%event%' AND

   LOWER(eventMessage) LIKE '%timestamp%')

);

-- accessibilityuiserver logs

SELECT *, 'Touchscreen' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(process) = 'accessibilityuiserver'

  AND LOWER(eventMessage) LIKE '%touchon:%';

        # iOS Unified Logs - FLASHLIGHT (ON / OFF)

-- springboard logs

SELECT *, 'Flashlight' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(process) = 'springboard' AND (

  LOWER(eventMessage) LIKE '%turnflashlightonforreason%' OR

  LOWER(eventMessage) LIKE '%turnflashlightoffforreason%' OR

  LOWER(eventMessage) LIKE '%setlevel:%' OR

  LOWER(eventMessage) LIKE '%posting level change to:%' OR

  LOWER(eventMessage) LIKE '%setflashlightlevel%'

);

        #  iOS Unified Logs - CALL

SELECT *, 'Call' AS label

FROM "iOS Unified Logs - General"

WHERE

  (LOWER(process) = 'mobilephone' AND (

    LOWER(eventMessage) LIKE '%resuming to tab type%' OR

    LOWER(eventMessage) LIKE '%activity continuity - activity needs saving%' OR

    LOWER(eventMessage) LIKE '%wrote out last tab type%' OR

    LOWER(eventMessage) LIKE '%setting contact with identifier%'

  )) OR

  (LOWER(process) = 'springboard' AND LOWER(eventMessage) LIKE '%trusted open application request%incallservice%') OR

  (LOWER(process) = 'callservicesd' AND (

    LOWER(eventMessage) LIKE '%call started%' OR

    LOWER(eventMessage) LIKE '%all calls ended%'

  )) OR

  LOWER(eventMessage) LIKE '%actionid 120%';

        #  iOS Unified Logs - TODAY VIEW/WIDGET

-- springboard logs

SELECT *, 'Today view/Widget' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(process) = 'springboard' AND (

  LOWER(eventMessage) LIKE '%today view overlay will appear%' OR

  LOWER(eventMessage) LIKE '%overlay today view did scroll%' OR

  LOWER(eventMessage) LIKE '%setting visibility of widget%' OR

  LOWER(eventMessage) LIKE '%received actions:%' OR

  LOWER(eventMessage) LIKE '%today view overlay did disappear%' OR

  LOWER(eventMessage) LIKE '%today view overlay did disappear, animated:%'

);

        #  iOS Unified Logs - SILENT MODE

-- audiomxd logs

SELECT *, 'Silent Mode' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(process) = 'audiomxd' AND (

  LOWER(eventMessage) LIKE '%silent mode state updated%' OR

  LOWER(eventMessage) LIKE '%silent mode update%'

);

-- backboardd logs

SELECT *, 'Silent Mode' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(process) = 'backboardd'

  AND LOWER(eventMessage) LIKE '%ringer state changed to:%';

-- springboard logs

SELECT *, 'Silent Mode' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(process) = 'springboard' AND (

  LOWER(eventMessage) LIKE '%activateringerhud:%' OR

  LOWER(eventMessage) LIKE '%setringersilent:%'

);

        #  iOS Unified Logs - VOLUME

SELECT *, 'Volume' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(process) = 'audiomxd' AND (

  (LOWER(eventMessage) LIKE '%volumecontrol%' AND LOWER(eventMessage) LIKE '%set volume%') OR

  (LOWER(eventMessage) LIKE '%volumecontrol%' AND LOWER(eventMessage) LIKE '%setting volume%') OR

  LOWER(eventMessage) LIKE '%volume operation.%'

);

-- springboard logs (volume interaction)

SELECT *, 'Volume' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(process) = 'springboard' AND (

  LOWER(eventMessage) LIKE '%volume changed to:%' OR

  LOWER(eventMessage) LIKE '%effectivolumechanged%' OR

  LOWER(eventMessage) LIKE '%volume increment%' OR

  LOWER(eventMessage) LIKE '%volume decrement%' OR

  LOWER(eventMessage) LIKE '%volumepress%' OR

  LOWER(eventMessage) LIKE '%volume change by delta%' OR

  LOWER(eventMessage) LIKE '%slider value changed:%' OR

  LOWER(eventMessage) LIKE '%button press noted: volume%'

);

        #  iOS Unified Logs - BOOT / SHUTDOWN

-- kernel logs

SELECT *, 'Boot/Shutdown' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(process) = 'kernel' AND (

  LOWER(eventMessage) LIKE '%iboot version%' OR

  LOWER(eventMessage) LIKE '%downloaded firmware%'

);

-- springboard logs

SELECT *, 'Boot/Shutdown' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(process) = 'springboard'

  AND LOWER(eventMessage) LIKE '%shutdown%';

-- locationd logs

SELECT *, 'Boot/Shutdown' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(process) = 'locationd'

  AND LOWER(eventMessage) LIKE '%locationd shutting down%';

        #  iOS Unified Logs - CAMERA

-- camera process logs

SELECT *, 'Camera' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(process) = 'camera' AND (

  LOWER(eventMessage) LIKE '%will change to: photo%' OR

  LOWER(eventMessage) LIKE '%called commitmomentcapture as photo%' OR

  LOWER(eventMessage) LIKE '%beginmomentcapturewithsettings%' OR

  LOWER(eventMessage) LIKE '%touchbeginscreen%' OR

  LOWER(eventMessage) LIKE '%gesturesdidbegin%' OR

  LOWER(eventMessage) LIKE '%touchendedshortpress%' OR

  LOWER(eventMessage) LIKE '%didshortpress%' OR

  LOWER(eventMessage) LIKE '%capturing with fallback orientation%' OR

  LOWER(eventMessage) LIKE '%persistencecontroller: added photo to library%' OR

  LOWER(eventMessage) LIKE '%persistencecontroller: added video to library%'

);

-- cameracaptured process logs

SELECT *, 'Camera' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(process) = 'cameracaptured' AND (

  LOWER(eventMessage) LIKE '%still image capture type:%' OR

  LOWER(eventMessage) LIKE '%iriswillbegincapture%' OR

  LOWER(eventMessage) LIKE '%temporary path:%'

);

-- assetsd process logs

SELECT *, 'Camera' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(process) = 'assetsd' AND (

  LOWER(eventMessage) LIKE '%created asset img_%' OR

  LOWER(eventMessage) LIKE '%photoiris video job%' OR

  LOWER(eventMessage) LIKE '%successfully paired%'

);

        #  iOS Unified Logs - NOTIFICATIONS

-- springboard logs

SELECT *, 'Notification' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(process) = 'springboard' AND (

  (LOWER(eventMessage) LIKE '%notification list%requests presenting options menu%') OR

  (LOWER(eventMessage) LIKE '%notification list%removing notification request%') OR

  (LOWER(eventMessage) LIKE '%group list%' AND LOWER(eventMessage) LIKE '%removing notification request%') OR

  LOWER(eventMessage) LIKE '%expanding notification group%' OR

  LOWER(eventMessage) LIKE '%notification cell executing default action%' OR

  LOWER(eventMessage) LIKE '%notification list setting cell with revealed actions%' OR

  LOWER(eventMessage) LIKE '%notification view controller will present long look%' OR

  LOWER(eventMessage) LIKE '%notification view controller will dismiss long look%' OR

  (LOWER(eventMessage) LIKE '%notification list removing notification request%' AND LOWER(eventMessage) LIKE'%long look dismissal%') OR

  LOWER(eventMessage) LIKE '%requests executing action reply for notification request%' OR

  LOWER(eventMessage) LIKE '%dispatcher will execute action reply for notification%'

);

-- springboard logs

SELECT *, 'Notification' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(process) = 'springboard' AND (

  LOWER(eventMessage) LIKE '%incremented notification center access to service extension%' OR

  LOWER(eventMessage) LIKE '%getting pending notification requests%' OR

  LOWER(eventMessage) LIKE '%load pending%'

);

-- serviceextension logs

SELECT *, 'Notification' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(process) = 'serviceextension' AND (

  LOWER(eventMessage) LIKE '%request replacement content for notification request%' OR

  LOWER(eventMessage) LIKE '%creating a user notification center%' OR

  LOWER(eventMessage) LIKE '%got % pending notification%'

);

        #  iOS Unified Logs - CONTROL CENTER

-- springboard logs

SELECT *, 'Control Center' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(process) = 'springboard' AND (

  LOWER(eventMessage) LIKE '%control center visible%' OR

  LOWER(eventMessage) LIKE '%control center window visibility%'

);

-- wifid logs

SELECT *, 'Control Center' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(process) = 'wifid'

  AND LOWER(eventMessage) LIKE '%control center launched%';

        # iOS Unified Logs - BACK TAP

-- accessibilityuiserver logs

SELECT *, 'Back Tap' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(process) = 'accessibilityuiserver' AND (

  LOWER(eventMessage) LIKE '%axphoenixanalyticseventtypedoubletap%' OR

  LOWER(eventMessage) LIKE '%event in time for event tap%'

);

        #  iOS Unified Logs - AIRPLANE MODE

SELECT *, 'Airplane Mode' AS label

FROM "iOS Unified Logs - General"

WHERE

  LOWER(eventMessage) LIKE '%airplane mode state%' OR

  LOWER(eventMessage) LIKE '%toggle airplane mode%' OR

  LOWER(eventMessage) LIKE '%airplane mode is unchanged%' OR

  LOWER(eventMessage) LIKE '%requesting operating mode change to lowpower [ airplane ]%' OR

  LOWER(eventMessage) LIKE '%ignoring serving system update as airplane mode is on%' OR

  LOWER(eventMessage) LIKE '%setting airplane mode on to%' OR

  LOWER(eventMessage) LIKE '%updated airplane mode:%' OR

  LOWER(eventMessage) LIKE '%default airplane mode power state :%' OR

  LOWER(eventMessage) LIKE '%airplane mode : %' OR

  LOWER(eventMessage) LIKE '%airplane mode is%' OR

  LOWER(eventMessage) LIKE '%setting value for /device/system/airplaneMode:%' OR

  LOWER(eventMessage) LIKE '%network state changed to connected to%' OR

  LOWER(eventMessage) LIKE '%checkairplanemodeenabledwithqueue%checking if airplane mode is enabled%' OR

  LOWER(eventMessage) LIKE '%airplane%enable%' OR

  LOWER(eventMessage) LIKE '%airplane%disable%' OR

  LOWER(eventMessage) LIKE '%airplane%enabled%';

);

        #  iOS Unified Logs - MOTION

-- symptomsd logs

SELECT *, 'Motion' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(process) = 'symptomsd'

  AND LOWER(eventMessage) LIKE '%motion state transition%';

-- locationd logs

SELECT *, 'Motion' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(process) = 'locationd'

  AND LOWER(eventMessage) LIKE '%vehicularstarttime%';

-- wifid logs

SELECT *, 'Motion' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(process) = 'wifid'

  AND LOWER(eventMessage) LIKE '%motionstate:%';

-- carkitd logs

SELECT *, 'Motion' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(process) = 'carkitd' AND (

  LOWER(eventMessage) LIKE '%driving%' OR

  LOWER(eventMessage) LIKE '%do not disturb while driving%'

);

-- contextstored logs

SELECT *, 'Motion' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(process) = 'contextstored'

  AND LOWER(eventMessage) LIKE '%driving mode activated%';

-- routined logs

SELECT *, 'Motion' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(process) = 'routined'

  AND LOWER(eventMessage) LIKE '%pedestrianafterdriving%';

-- springboard logs

SELECT *, 'Motion' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(process) = 'springboard'

  AND LOWER(eventMessage) LIKE '%pocketstate changed%';

        #  iOS Unified Logs - ORIENTATION

-- backboardd logs

SELECT *, 'Orientation' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(process) = 'backboardd' AND (

  LOWER(eventMessage) LIKE '%effective device orientation changed%' OR

  LOWER(eventMessage) LIKE '%received orientation%'

);

-- springboard logs

SELECT *, 'Orientation' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(process) = 'springboard' AND (

  LOWER(eventMessage) LIKE '%interface orientation%' OR

  (LOWER(eventMessage) LIKE '%switcherorientation%' AND LOWER(eventMessage) LIKE '%orientation%')

        #  iOS Unified Logs - BACKLIGHT

-- springboard logs

SELECT *, 'Backlight' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(process) = 'springboard'

  AND LOWER(eventMessage) LIKE '%animating backlight to factor%';

        #  iOS Unified Logs - TIME / TIMEZONE

-- generic date/time change logs

SELECT *, 'Date/Time' AS label

FROM "iOS Unified Logs - General"

WHERE

  LOWER(eventMessage) LIKE '%setting manual time%' OR

  LOWER(eventMessage) LIKE '%AutomaticTimeZone%' OR

  LOWER(eventMessage) LIKE '%system wallclock time adjusted%';

-- mobiletimerd logs

SELECT *, 'Date/Time' AS label

FROM "iOS Unified Logs - General"

WHERE process = 'mobiletimerd' AND (

  (LOWER(eventMessage) LIKE '%mttimelistener%' AND LOWER(eventMessage) LIKE '%timezone%') OR

  (LOWER(eventMessage) LIKE '%releasing power assert for%' AND LOWER(eventMessage) LIKE'%significanttimechange%')

);

-- springBoard logs

SELECT *, 'Date/Time' AS label

FROM "iOS Unified Logs - General"

WHERE process = 'springBoard' AND

  LOWER(eventMessage) LIKE '%handle significant time change%' AND

  LOWER(eventMessage) LIKE '%clients%';

-- preferences logs

SELECT *, 'Date/Time' AS label

FROM "iOS Unified Logs - General"

WHERE process = 'preferences' AND (

  (LOWER(eventMessage) LIKE '%commitnewselectionvalue%' AND LOWER(eventMessage) LIKE '%general%') OR

  (LOWER(eventMessage) LIKE '%primarysettingslistmodel%' AND LOWER(eventMessage) LIKE '%nil%') OR

  (LOWER(eventMessage) LIKE '%activating connection%' AND LOWER(eventMessage) LIKE'%com.apple.timezoneupdates.tzd.server%')

);

-- timed logs

SELECT *, 'Date/Time' AS label

FROM "iOS Unified Logs - General"

WHERE process = 'timed' AND (

  (LOWER(eventMessage) LIKE '%received automatic time zone%' AND LOWER(eventMessage) LIKE '%disabled%') OR

  (LOWER(eventMessage) LIKE '%received automatic time zone%' AND LOWER(eventMessage) LIKE '%enabled%') OR

  (LOWER(eventMessage) LIKE '%tmsetmanualtime%' AND LOWER(eventMessage) LIKE '%included mach time when not expected%') OR

  (LOWER(eventMessage) LIKE '%setting manual time with dictionary%' AND LOWER(eventMessage) LIKE'%tmcurrenttime%')

);

-- mobileassetd logs

SELECT *, 'Date/Time' AS label

FROM "iOS Unified Logs - General"

WHERE process = 'mobileassetd' AND

  LOWER(eventMessage) LIKE '%madanalyticsmanager shouldrecordeventforassettype%' AND

  LOWER(eventMessage) LIKE '%timezoneupdate%';

-- locationd logs

SELECT *, 'Date/Time' AS label

FROM "iOS Unified Logs - General"

WHERE process = 'locationd' AND

  LOWER(eventMessage) LIKE '%utomatic time zone%' AND

  LOWER(eventMessage) LIKE '%mode%';

-- dasd logs

SELECT *, 'Date/Time' AS label

FROM "iOS Unified Logs - General"

WHERE process = 'dasd' AND

  LOWER(eventMessage) LIKE '%time change%' AND

  LOWER(eventMessage) LIKE '%clock shifted by%';

        #  iOS Unified Logs - WI-FI

-- springboard and preferences logs

SELECT *, 'WiFi' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(process) IN ('springboard', 'preferences') AND (

  LOWER(eventMessage) LIKE '%wifi state changed%' OR

  LOWER(eventMessage) LIKE '%wifi is associated%' OR

  LOWER(eventMessage) LIKE '%updatecurrentnetwork:%' OR

  LOWER(eventMessage) LIKE '%copy password for network%' OR

  LOWER(eventMessage) LIKE '%fetching password from keychain%' OR

  LOWER(eventMessage) LIKE '%password is nil%' OR

  LOWER(eventMessage) LIKE '%provided to % parameters%'

);

-- wifid logs

SELECT *, 'WiFi' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(process) = 'wifid' AND (

  LOWER(eventMessage) LIKE '%link went down%' OR

  LOWER(eventMessage) LIKE '%manual association%' OR

  LOWER(eventMessage) LIKE '%link up to%' OR

  LOWER(eventMessage) LIKE '%associate manual association requestion from%' OR

  LOWER(eventMessage) LIKE '%wifiidssyncengine knownnetworkslistchanged%' OR

  LOWER(eventMessage) LIKE '%wifidevicemanagerknownnetworksuitabilitycheck:%' OR

  LOWER(eventMessage) LIKE '%knownnetworkslistchanged%' OR

  LOWER(eventMessage) LIKE '%begin req [assoc]%' OR

  LOWER(eventMessage) LIKE '%wfmacrandomisation%' OR

  LOWER(eventMessage) LIKE '%wifimanagerprivatemacupdateproperty wfmacrandomisation : updated property <linkdowntimestamp> of network%' OR

  LOWER(eventMessage) LIKE '%reloadnetworksdisableduntil%' OR

  LOWER(eventMessage) LIKE '%wifimanagerreloadnetworksdisabledUntil: adding%' OR

  LOWER(eventMessage) LIKE '%total connection time%'

);

-- kbd logs

SELECT *, 'WiFi' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(process) = 'kbd' AND (

  LOWER(eventMessage) LIKE '%passkey keychain records%' OR

  LOWER(eventMessage) LIKE '%password-manager%'

);

-- configd logs

SELECT *, 'WiFi' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(process) = 'configd'

  AND LOWER(eventMessage) LIKE '%ssid is now%';

-- springboard logs

SELECT *, 'WiFi' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(process) = 'springboard' AND (

  LOWER(eventMessage) LIKE '%updatecurrentnetwork: network%' OR

  LOWER(eventMessage) LIKE '%wifi is associated%' OR

  LOWER(eventMessage) LIKE '%wifi state changed from%' OR

  LOWER(eventMessage) LIKE '%isnetworkobserver: set network type%' OR

  LOWER(eventMessage) LIKE '%updatecurrentnetwork: current network has been set%' OR

  LOWER(eventMessage) LIKE '%signal strength bars changed to%'

);

-- preferences logs

SELECT *, 'WiFi' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(process) = 'preferences'

  AND LOWER(eventMessage) LIKE '%etworklinkqualitydidchangenotification%';

-- itunestored logs

SELECT *, 'WiFi' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(process) = 'itunestored'

  AND LOWER(eventMessage) LIKE '%set network type "wifi"%';

-- rapportd logs

SELECT *, 'WiFi' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(process) = 'rapportd'

  AND LOWER(eventMessage) LIKE '%sysmon: wifi state changed%';

-- networkserviceproxy logs

SELECT *, 'WiFi' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(process) = 'networkserviceproxy'

  AND LOWER(eventMessage) LIKE '%wi-fi network%is active';

        #  iOS Unified Logs - BLUETOOTH

-- bluetoothd logs

SELECT *, 'Bluetooth' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(process) = 'bluetoothd' AND (

  LOWER(eventMessage) LIKE '%discovered device%' OR

  LOWER(eventMessage) LIKE '%device found:%' OR

  LOWER(eventMessage) LIKE '%running sdp%' OR

  LOWER(eventMessage) LIKE '%numeric comparison request%' OR

  LOWER(eventMessage) LIKE '%pairing started%' OR

  LOWER(eventMessage) LIKE '%pairing complete%' OR

  LOWER(eventMessage) LIKE '%device connected%' OR

  LOWER(eventMessage) LIKE '%incoming sdp%'

);

-- preferences logs

SELECT *, 'Bluetooth' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(process) = 'preferences'

  AND LOWER(eventMessage) LIKE '%user confirmation request%';

-- springboard logs

SELECT *, 'Bluetooth' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(process) = 'springboard' AND (

  LOWER(eventMessage) LIKE '%current bluetooth state :%' OR

  LOWER(eventMessage) LIKE '%bluetooth state updated to%' OR

  LOWER(eventMessage) LIKE '%toggle bluetooth state%'

);

-- carkitd logs

SELECT *, 'Bluetooth' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(process) = 'carkitd'

  AND LOWER(eventMessage) LIKE '%sending new bluetooth state%';

-- findmydevice logs

SELECT *, 'Bluetooth' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(process) = 'findmydevice'

  AND LOWER(eventMessage) LIKE '%sending new bluetooth state%';

-- sharingd logs

SELECT *, 'Bluetooth' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(process) = 'sharingd'

  AND LOWER(eventMessage) LIKE '%device connected:%';

-- accessoryd logs

SELECT *, 'Bluetooth' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(process) = 'accessoryd'

  AND LOWER(eventMessage) LIKE '%adding accessory info:%';

-- audioaccessoryd logs

SELECT *, 'Bluetooth' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(process) = 'audioaccessoryd'

  AND LOWER(eventMessage) LIKE '%bluetooth state changed:%';

-- rapportdd logs

SELECT *, 'Bluetooth' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(process) = 'rapportdd'

  AND LOWER(eventMessage) LIKE '%bluetooth state changed:%';

        #  iOS Unified Logs - AUDIO OUTPUTS

-- audiomxd logs

SELECT *, 'Audio Output' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(process) = 'audiomxd' AND (

  LOWER(eventMessage) LIKE '%vaemconfigurepvmsettings%' OR

  LOWER(eventMessage) LIKE '%btaudioavnotificationmonitor%' OR

  LOWER(eventMessage) LIKE '%route changed.%' OR

  LOWER(eventMessage) LIKE '%currently activating endpoint%' OR

  LOWER(eventMessage) LIKE '%route picked%' OR

  LOWER(eventMessage) LIKE '%logendpointid:%'

);

        #  iOS Unified Logs - SIRI / VOICE

-- assistantd logs

SELECT *, 'Siri' AS label

FROM "iOS Unified Logs - General"

WHERE LOWER(process) = 'assistantd'

  AND LOWER(eventMessage) LIKE '%psc request for utterance%';

-- general Siri logs

SELECT *, 'Siri' AS label

FROM "iOS Unified Logs - General"

WHERE

  LOWER(eventMessage) LIKE '%best conf result sessionid:%' OR

  LOWER(eventMessage) LIKE '%end of sentence likelihood is:%' OR

  LOWER(eventMessage) LIKE '%signal energy (db)%' OR

  LOWER(eventMessage) LIKE '%assistant is presenting%' OR

  LOWER(eventMessage) LIKE '%start wake gesture updates%' OR

  LOWER(eventMessage) LIKE '%#speechrequest id:%' OR

  LOWER(eventMessage) LIKE '%audio playback finished for request_id:%' OR

  LOWER(eventMessage) LIKE '%#success #speechrequest%' OR

  LOWER(eventMessage) LIKE '%speechrecognized%';

    ]

License & Credits (again)

These queries are published by Lionel Notari on www.ios-unifiedlogs.com

If you reuse or adapt them, please credit the author and source.

For commercial reuse, contact the author.