If this should be moved to the DBA exchange, I apologize. Feels more like linux than DB to me, so here goes:
I've got some machines that run scheduled cron jobs every night and email me the output. I do not want emails for things like this. In general I think the way we use email is broken, but that's another story.
So I started thinking that I could keep a central SQLite database that stored information about when the jobs started, and finished, and maybe even the output. Then I could just build a webpage that queries that and let's me know was going on last night.
So I came up with a simple schema and can run this command at the beginning of a script.
sqlite3 dbname.db "UPDATE data SET LastStart = DATETIME('NOW') WHERE TaskName = 'taskname'"
So now I have a record that states that my job started and at what time. Hooray. Then I can run a similar command to put the time that the job ends.
So. That works great if the database and the tasks are on the same machine. I go to another machine and need to update the sqlite database.... How can I do that efficiently?
I tried this
ssh email@example.com 'sqlite3 /home/aaron/dbname.db "UPDATE data SET LastStart = DATETIME('NOW') WHERE TaskName = 'taskname'"'
But that returns:
Error: no such column: NOW
I tried some variations but didn't get anywhere.
Am I close? Should I be doing something totally different? Am I reinventing the wheel?