Is this possible via API or in some other way? #1740

New Issue

OVERLORD · 2026-02-05T01:44:56+03:00

OVERLORD commented

2026-02-05 01:44:56 +03:00

Originally created by @techauthoruk on GitHub (May 18, 2020).

HI

I don't know if this is at all possible, but it would be great if it was. I need to email my users once a week (say on a Monday morning) to give them an update on any books / chapters / pages that have been updated in the previous week, along with a bookmark to the content location.

Is this at all possible, either natively, by scripting or by accessing the API? I can sort the sending of the email without a problem, but getting the content details out of bookstack is the bit that defeats me. Is this information held in the database?

I'm totally clueless about this, so really would appreciate anyone's input.

Many thanks

Mark
(a VERY happy BookStack user!)

Originally created by @techauthoruk on GitHub (May 18, 2020). HI I don't know if this is at all possible, but it would be great if it was. I need to email my users once a week (say on a Monday morning) to give them an update on any books / chapters / pages that have been updated in the previous week, along with a bookmark to the content location. Is this at all possible, either natively, by scripting or by accessing the API? I can sort the sending of the email without a problem, but getting the content details out of bookstack is the bit that defeats me. Is this information held in the database? I'm totally clueless about this, so really would appreciate anyone's input. Many thanks Mark (a VERY happy BookStack user!)

OVERLORD closed this issue

2026-02-05 01:44:58 +03:00

OVERLORD commented

2026-02-05 01:45:03 +03:00

@techauthoruk commented on GitHub (May 22, 2020):

Anyone have any thoughts on this? I have been looking at the revision history and that gives me the information I need (I think!), but how can I access this information - is it stored in a database somewhere?

Any help would be very gratefully received.

Mark

@techauthoruk commented on GitHub (May 22, 2020): Anyone have any thoughts on this? I have been looking at the revision history and that gives me the information I need (I think!), but how can I access this information - is it stored in a database somewhere? Any help would be very gratefully received. Mark

OVERLORD commented

2026-02-05 01:45:06 +03:00

@ssddanbrown commented on GitHub (May 22, 2020):

Hi @techauthoruk,

This is not something you could do yet via the API, since the API does not yet got down to page level. It will do in the future though.

Otherwise, everything goes into the database. There's many ways you could look for updated pages. You could just look at the pages table, There should be an updated_at column on there. You'd need to filter out any draft pages (Think it's a draft column on that table). Also, If you use permissions in your instance it will be tricky to manually calculate those in the same way as BookStack does.

Alternatively, as you found, you can look at page_revisions but many of the revisions may just be user-specific draft copies.

There's also the activity table which tracks most actions. You could look for activity where the key
is page_update (Or something along those lines.)

One you get the ids of pages, then the name can be read from the pages table. You can link to a page, without having to calculate the full URL slug, based upon their ID like so:

<bookstack_base_url>/link/<id>

# Example
https://demo.bookstackapp.com/link/50

@ssddanbrown commented on GitHub (May 22, 2020): Hi @techauthoruk, This is not something you could do yet via the API, since the API does not yet got down to page level. It will do in the future though. Otherwise, everything goes into the database. There's many ways you could look for updated pages. You could just look at the `pages` table, There should be an `updated_at` column on there. You'd need to filter out any draft pages (Think it's a `draft` column on that table). Also, If you use permissions in your instance it will be tricky to manually calculate those in the same way as BookStack does. Alternatively, as you found, you can look at page_revisions but many of the revisions may just be user-specific draft copies. There's also the `activity` table which tracks most actions. You could look for activity where the `key` is `page_update` (Or something along those lines.) One you get the ids of pages, then the name can be read from the `pages` table. You can link to a page, without having to calculate the full URL slug, based upon their ID like so: ```bash <bookstack_base_url>/link/<id> # Example https://demo.bookstackapp.com/link/50 ```

OVERLORD commented

2026-02-05 01:45:09 +03:00

@techauthoruk commented on GitHub (Jun 3, 2020):

@ssddanbrown Dan, thank you for the pointers, I now have this working really well by using a bash script to query the backend db and then email the results once a week.

A truncated version of the script if it might prove useful:

#!/bin/bash
RECIPIENTS=$(</root/bin/address.txt)
SENDER="documentation@domain.com"
SUBJECT="CMS Weekly Digest"
GREETING="Good morning,"
MAILBODY="The following content has been added or updated in the Content Manage$
CLOSEBODY="This is an automated email sent by the Content Management System$

echo "FROM:" $SENDER > /root/bin/mailtest
echo "TO:" $RECIPIENTS >> /root/bin/mailtest
echo "BCC:" $BCC >> /root/bin/mailtest
echo "SUBJECT:" $SUBJECT >> /root/bin/mailtest
echo "" >> /root/bin/mailtest
echo $GREETING >> /root/bin/mailtest
echo "" >> /root/bin/mailtest
echo $MAILBODY >> /root/bin/mailtest
echo "" >> /root/bin/mailtest
echo "select id as ID,name as NAME,updated_at as UPDATED, CONCAT('https://cms.s$
echo "" >> /root/bin/mailtest
echo "" >> /root/bin/mailtest
echo $CLOSEBODY >> /root/bin/mailtest
cat /root/bin/mailtest | /usr/sbin/sendmail -t

I'm not very good when it comes to scripting, but this seems to work ok.

On another point, are the user log ins stored anywhere in the backend? It would be really useful for me to know who has (or hasn't) logged in...is this possible?

Many thanks

Mark

@techauthoruk commented on GitHub (Jun 3, 2020): @ssddanbrown Dan, thank you for the pointers, I now have this working really well by using a bash script to query the backend db and then email the results once a week. A truncated version of the script if it might prove useful: ```bash #!/bin/bash RECIPIENTS=$(</root/bin/address.txt) SENDER="documentation@domain.com" SUBJECT="CMS Weekly Digest" GREETING="Good morning," MAILBODY="The following content has been added or updated in the Content Manage$ CLOSEBODY="This is an automated email sent by the Content Management System$ echo "FROM:" $SENDER > /root/bin/mailtest echo "TO:" $RECIPIENTS >> /root/bin/mailtest echo "BCC:" $BCC >> /root/bin/mailtest echo "SUBJECT:" $SUBJECT >> /root/bin/mailtest echo "" >> /root/bin/mailtest echo $GREETING >> /root/bin/mailtest echo "" >> /root/bin/mailtest echo $MAILBODY >> /root/bin/mailtest echo "" >> /root/bin/mailtest echo "select id as ID,name as NAME,updated_at as UPDATED, CONCAT('https://cms.s$ echo "" >> /root/bin/mailtest echo "" >> /root/bin/mailtest echo $CLOSEBODY >> /root/bin/mailtest cat /root/bin/mailtest | /usr/sbin/sendmail -t ``` I'm not very good when it comes to scripting, but this seems to work ok. On another point, are the user log ins stored anywhere in the backend? It would be really useful for me to know who has (or hasn't) logged in...is this possible? Many thanks Mark

OVERLORD commented

2026-02-05 01:45:10 +03:00

@ssddanbrown commented on GitHub (Jun 3, 2020):

On another point, are the user log ins stored anywhere in the backend? It would be really useful for me to know who has (or hasn't) logged in...is this possible?

@techauthoruk Not currently, but It has been requested to show last login on the user page, so probably something we'll add to the database at some point.

BookStack does track page-views at a per-user level, (In a views or page_views table maybe? can't remember the name right now). There's also the activity table which stores the user id. Checking both of those should give you a pretty good idea of who's active in the system.

@ssddanbrown commented on GitHub (Jun 3, 2020): > On another point, are the user log ins stored anywhere in the backend? It would be really useful for me to know who has (or hasn't) logged in...is this possible? @techauthoruk Not currently, but It has been requested to show last login on the user page, so probably something we'll add to the database at some point. BookStack does track page-views at a per-user level, (In a `views` or `page_views` table maybe? can't remember the name right now). There's also the `activity` table which stores the user id. Checking both of those should give you a pretty good idea of who's active in the system.

OVERLORD commented

2026-02-05 01:45:15 +03:00

@techauthoruk commented on GitHub (Jun 4, 2020):

@ssddanbrown Thanks Dan, I can't seem to find the views table (not saying it doesn't exist, just can't find it!). The activity table only seems to hold the user ID and a datstamp of when they updated or added content (I'm assuming this as there is only one person who adds content [me!] and all the table entries are for the same user ID).

All I really need is the user name and the date they last logged in...I will keep looking, but do you have any other comments/suggestions? If this info is not there, do you have any idea on timescale before the last user logon is added to the user page?

Thanks again

Mark

@techauthoruk commented on GitHub (Jun 4, 2020): @ssddanbrown Thanks Dan, I can't seem to find the `views` table (not saying it doesn't exist, just can't find it!). The `activity` table only seems to hold the user ID and a datstamp of when they updated or added content (I'm assuming this as there is only one person who adds content [me!] and all the table entries are for the same user ID). All I really need is the user name and the date they last logged in...I will keep looking, but do you have any other comments/suggestions? If this info is not there, do you have any idea on timescale before the last user logon is added to the user page? Thanks again Mark

OVERLORD commented

2026-02-05 01:45:22 +03:00

@ssddanbrown commented on GitHub (Jun 5, 2020):

All I really need is the user name and the date they last logged in...I will keep looking, but do you have any other comments/suggestions?

Logins are not tracked in the DB in any way. The views table really would be the best way, as you'd generally expect someone to view at least one page after logging in.

This site provides a good quick view of the tables (Although the relationship lines are a little off): https://drawsql.app/templates/diagrams/bookstack-mysql#

do you have any idea on timescale before the last user logon is added to the user page?

No timescale at the moment. Keep meaning to expand the activity within the next few releases but having real trouble finding spare time right now for BookStack development.

@ssddanbrown commented on GitHub (Jun 5, 2020): > All I really need is the user name and the date they last logged in...I will keep looking, but do you have any other comments/suggestions? Logins are not tracked in the DB in any way. The `views` table really would be the best way, as you'd generally expect someone to view at least one page after logging in. This site provides a good quick view of the tables (Although the relationship lines are a little off): https://drawsql.app/templates/diagrams/bookstack-mysql# > do you have any idea on timescale before the last user logon is added to the user page? No timescale at the moment. Keep meaning to expand the activity within the next few releases but having real trouble finding spare time right now for BookStack development.

OVERLORD commented

2026-02-05 01:45:24 +03:00

@techauthoruk commented on GitHub (Jun 8, 2020):

@ssddanbrown Dan, thank you for the direction. After much playing around I came up with this query which does what I need:

echo "SELECT views.user_id AS USER,users.name AS NAME,MAX(views.updated_at)AS UPDATED FROM views, users WHERE views.user_id = users.id AND views.user_id <> 0 GROUP BY views.user_id, users.name ORDER BY views.user_id, users.name" | mysql -u root -D bookstack >>root/bin/usertext

...which I use as part of a bash script to email the results once a week. I will close this thread now - thanks again for all the help.

@techauthoruk commented on GitHub (Jun 8, 2020): @ssddanbrown Dan, thank you for the direction. After much playing around I came up with this query which does what I need: ```sql echo "SELECT views.user_id AS USER,users.name AS NAME,MAX(views.updated_at)AS UPDATED FROM views, users WHERE views.user_id = users.id AND views.user_id <> 0 GROUP BY views.user_id, users.name ORDER BY views.user_id, users.name" | mysql -u root -D bookstack >>root/bin/usertext ``` ...which I use as part of a bash script to email the results once a week. I will close this thread now - thanks again for all the help.

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: starred/BookStack#1740