knowledge_siri.txt

[Module Metadata]
AUTHOR=Sarah Edwards/mac4n6.com/@iamevltwin
MODULE_NOTES=Siri Usage

[Database Metadata]
DATABASE=knowledgeC.db
PLATFORM=IOS
VERSIONS=12

[Query Metadata]
QUERY_NAME=knowledge_siri
ACTIVITY=Siri Usage
KEY_TIMESTAMP=START

[SQL Query 12]
QUERY=
   SELECT
     ZOBJECT.ZVALUESTRING AS "APP NAME",  
      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.ZSTARTDATE+978307200,'UNIXEPOCH') as "START", 
      DATETIME(ZOBJECT.ZCREATIONDATE+978307200,'UNIXEPOCH') as "ENTRY CREATION", 
      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 =  "/siri/ui"