Terminal Commands

Questions & Suggestions
1

I would like to most efficiently accomplish with KM the execution of two commands with Terminal.

The first command opens SQLITE3:

sqlite3 /Users/username/Library/Messages/chat.db

that opens the sqlite3 app within Terminal and displays a prompt "sqlite>"

on this prompt, the command I need is:

SELECT ROWID, text, date FROM message WHERE handle_id=1392 ORDER BY rowid DESC LIMIT 10;

This command will generate text results which look like this:

119072|Your supplementary Citi Card ending with 2920 spent USD 10.65 at AMZN Digital 88 on 11/06/2022. The available limit is AED 161475.75|676603917833457024
119061|You spent AED 235.45 at DELIVEROO DMCC UA on 10/06/2022, on your Citi Card ending with 6425. The available spending limit is AED 161514.87.|676574351709470976

I need to capture this result into a KM variable and do some processing on it.

(What this does is get the last 10 text messages received from a specific sender by searching through the chat.db file that macOS stores text messages into.)

I don't really know how to use scripting (I am getting better on AppleScript but iMessage is not AppleScript friendly as I understand it).

I can probably accomplish this using brute-force KM by executing Terminal; Insert by Pasting the first command; wait for the SQLite prompt to come up; insert by pasting the SQLite command and scrape the results. Nowhere near elegant as KM probably can do this directly.

I would very much appreciate the help to do this properly by KM.

Alex

2

Wrap your query in single quotes and put it after the call to open the database:

sqlite3 /Users/username/Library/Messages/chat.db 'SELECT ROWID, text, date FROM message WHERE handle_id=1392 ORDER BY rowid DESC LIMIT 10;'

You can capture the output in a Keyboard Maestro variable and process it in later steps.

The documentation for executing a query this way is in sqlite's Command Line Shell documentation.

3

Thank you very much! Worked perfectly.