bcgov
WEEKLY RUN UNTIL 04/30/2023 - QUERY FOR ALL ACTIVE SUBSCRIBERS AND THEIR SUBSCRIPTIONS IN THE BC Registry application
Recurring weekly request:
Each Monday we need to run a report of all accounts in the new BC Registry and which products they are subscribed to. This is requested by the OCM team, to be run weekly until end of July 2022.
@ozamani9 @rarmitag Would you please be able to run this report on Monday, May 29, 2022? Once my Postgres access is resolved, I plan to run it weekly. Thank you! @fareenr @trishreimer @lmcclung
Previous request - please ignore for now
We kindly need a re-run of the script included in Issue #11851 - I discussed with Linda assigning this issue directly to the SRE team. Once my Postgres access is granted, I am looking forward to running scripts like this in the future.
Removed query as requested.
@pwei1018 @ozamani9 @fareenr
@stevenc987 @ozamani9 This is the ticket we were talking about in the morning. Steven, it was mentioned that you might already have a script that might be adapted for the purposes of this ticket.
@Mihai-QuickSilverDev It seems you need this query to be run weekly till end of July, is it right? If so, we just run it weekly by manually. Is that OK?
Hi Steven, that would be fine. Could you please run it first on Thursday morning and attach the results and the SQL script to this ticket?
Thank you much!
Mihai
@stevenc987 @fareenr Hi Steve, thank you much for this first run of the report. I have updated the title to match the description - we need the report to show all subscriptions for active accounts, not just the Vital Stats ones. The subscriptions would be: Business Registry & Name Request, Business Search, PPR, Wills Registry, Rural Property Tax. id name email region bcol user id subsription 1 subsription 2 subsription 3 subsription 4, etc.
@stevenc987 Hi Steven, on Monday morning, could you please add a column to also show the BCOL account, where you show the BCOL ID - and run it again? There is a unique relationship between a BCOL ID and a BCOL account. Thank you so much Steven for your great help with this report. @fareenr @magszymanski
@stevenc987 we understand that not every BCROS account has linked a BCOL account, but knowing if they did will help us with our change management efforts to make sure every high-volume clients onboard onto the new application in time for upcoming released. Thank you!
@stevenc987 Hi Steve, I did not get the report. Would you kindly be able to send me the report at [email protected] ? Thank you!
@stevenc987 @fareenr Hi Steven, please find included here one more request from Fareen. We much appreciate your help in applying these conditions and running this report for us one more time. Thanks very much!
Hi Mihai,
I was wondering if it would be possible to expand on this report to list all administrators and Coordinators for ‘VS’-subscribed accounts, just as a one-time report? Using a very similar data query, it should be possible to list all account administrators email addresses for all subscribers of Wills (Product Code = ‘VS’, user role = ADMIN or COORD). I understand there will be duplicate rows as accounts can have multiple administrators.
I am looking for a list of all Account Administrators and Coordinators email addresses subscribed to Wills/VS product in order to prepare for the re-launch of Wills in July. That way the communication email can go to all account reps. We will need this report by July 5th if possible?
Please let me know if clarification is needed and if you can submit this IT Ops request as high priority?
Thanks, Fareen
@Mihai-QuickSilverDev @fareenr
I updated the query a bit to match your last requirements. However, it always responded with a timeout. It seems there were too large of results so I couldn't get any report for you base on the query. Would you like to narrow down the query?
@stevenc987 Okay, how about all accounts subscribed to Wills/ 'VS' product/service with the user role of 'Administrator' only?
@Mihai-QuickSilverDev Mihai, anymore request from this ticket? Can we move this to done? @stevenc987 As discussed in our meeting, please delete query detail, thanks
@jinghualicgi @stevenc987 @fareenr This ticket needs to remain open, as OCM needs us to run it once a week, until about end of September 2022. If the SQL is removed from this ticket, needs to remain easily accessible by any other Dev person, when Steven is not around.
yes, this report of all active accounts and subscriptions is still needed until end of Sept. The same SQL query can be run so that the report can be produced weekly as the OCM team does use it regularly. Thanks!
@pwei1018 Any suggestions where to put the query? Thanks Refer to Mihai's July20th comments
@pwei1018 why not one of the notebooks to save the output to a google sheet?
@thorwolpert We set the notebook for this kind of report. However, we haven't setup the scheduler to run the notebook. I am looking the notebook on GCP to do it.
@pwei1018 @fareenr Hi Patrick, could you kindly run the report (sql) associated with this ticket, and email me and Fareen the results? The sql should be stored in the DEV area as per the above notes in this ticket. Thank you!
@PCC199 Hi Paddy, at the OCM team request, this ticket needs to stay open until end of April, 2023.
This ticket has accomplished its purposes, closing it down. @jdyck-fw
@Mihai-QuickSilverDev OCM team will still need this report for upcoming releases but can we request it when needed (eg. sometime in mid-Aug for Wills Registry release) and refer to this ticket so that the same query can be used?
Hi @fareenr , yes, by all means. When that need arises we will create a new ticket and will reuse this ticket's script, or parts of it, as applicable. Thank you for pointing this out.