MacOS.Applications.KnowledgeC

On macOS, the KnowledgeC DB can provide various details around application activities and usage, as well as device power status.

More information about this database can be found here:

https://www.mac4n6.com/blog/2018/8/5/knowledge-is-power-using-the-knowledgecdb-database-on-macos-and-ios-to-determine-precise-user-and-application-usage

name: MacOS.Applications.KnowledgeC
description: |
   On macOS, the KnowledgeC DB can provide various details around application activities and usage, as well as device power status.
   
   More information about this database can be found here: 
   
   https://www.mac4n6.com/blog/2018/8/5/knowledge-is-power-using-the-knowledgecdb-database-on-macos-and-ios-to-determine-precise-user-and-application-usage

reference:
  - https://www.mac4n6.com/blog/2018/8/5/knowledge-is-power-using-the-knowledgecdb-database-on-macos-and-ios-to-determine-precise-user-and-application-usage

type: CLIENT

author: Wes Lambert - @therealwlambert|@weslambert@infosec.exchange

parameters:
- name: KCDBGlob
  default: /private/var/db/CoreDuet/Knowledge/knowledgeC.db,/Library/Application Support/Knowledge/knowledgeC.db

precondition:
      SELECT OS From info() where OS = 'darwin'

sources:
  - name: Application Activities
    query: |
      LET KCDBList = SELECT OSPath
       FROM glob(globs=split(string=KCDBGlob, sep=","))

      LET KCDBAppActivities = SELECT *
       FROM sqlite(file=OSPath, query='''
        SELECT
            datetime(ZOBJECT.ZCREATIONDATE+978307200,'UNIXEPOCH', 'LOCALTIME') as "ENTRY CREATION", 
            ZOBJECT.ZSECONDSFROMGMT/3600 AS "GMT OFFSET",
            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",
            datetime(ZOBJECT.ZSTARTDATE+978307200,'UNIXEPOCH', 'LOCALTIME') as "START", 
            datetime(ZOBJECT.ZENDDATE+978307200,'UNIXEPOCH', 'LOCALTIME') as "END", 
            (ZOBJECT.ZENDDATE-ZOBJECT.ZSTARTDATE) as "USAGE IN SECONDS", 
            ZOBJECT.ZSTREAMNAME, 
            ZOBJECT.ZVALUESTRING,
            ZSTRUCTUREDMETADATA.Z_DKAPPLICATIONACTIVITYMETADATAKEY__ACTIVITYTYPE AS "ACTIVITY TYPE",  
            ZSTRUCTUREDMETADATA.Z_DKAPPLICATIONACTIVITYMETADATAKEY__TITLE as "TITLE", 
            ZSTRUCTUREDMETADATA.Z_DKAPPLICATIONACTIVITYMETADATAKEY__USERACTIVITYREQUIREDSTRING as "ACTIVITY STRING",
            datetime(ZSTRUCTUREDMETADATA.Z_DKAPPLICATIONACTIVITYMETADATAKEY__EXPIRATIONDATE+978307200,'UNIXEPOCH', 'LOCALTIME') as "EXPIRATION DATE"
        FROM ZOBJECT
        left join ZSTRUCTUREDMETADATA on ZOBJECT.ZSTRUCTUREDMETADATA = ZSTRUCTUREDMETADATA.Z_PK
        WHERE ZSTREAMNAME is "/app/activity" or ZSTREAMNAME is "/app/inFocus"''')
  
      SELECT timestamp(string=`ENTRY CREATION`) AS Timestamp,	
        `GMT OFFSET` AS OffsetGMT,	
        `DAY OF WEEK` AS DayOfWeek,	
        `START` AS Start,
        `END` AS End,
        `USAGE IN SECONDS` AS Usage,	
        ZSTREAMNAME AS StreamName,
        ZVALUESTRING AS StreamValue,	
        `ACTIVITY TYPE` AS ActivityType, 	
        TITLE AS Title,
        `ACTIVITY STRING` AS Activity,	
        `EXPIRATION DATE` AS ExpirationDate
      FROM foreach(row=KCDBList,query=KCDBAppActivities)