17 Replies Latest reply on Sep 25, 2015 2:02 PM by Caton Guilbault

    Script JCA Downloads

    Caton Guilbault

      Hey Jivers,

       

      Has anyone figured out how to script DB downloads from the JCA?

       

      I would like to setup a script that can start downloading the DB's for multiple instances as soon as they have been refreshed on the JCA. Bonus points for connection management so the downloads don't fail randomly as they are apt to.

       

      CC:gummi, Ryan Sefko

        • Re: Script JCA Downloads

          I asked internally to see if there are any examples of customers doing this before. I'll update the thread if I get someone to bite or ask them to jump on the thread!

          • Re: Script JCA Downloads
            alkesh.shah@groupm.com

            We have built a couple of scripts to get daily downloads from JCA, one uses WGET with certain flags to ensure any interuption in connection continues to the file download on reconnect - bit like Resume in SFTP. We had to add extra code to manage date on naming files as some of our Instances are in US and others in Europe so mixed date format!  The second takes the downloaded postgres dmp and restores it to full DB using postgres to run the script on a windows box. The final output is then fed into Tableau8 on a nightly schedule. We are in middle of documenting scripts for our support team at present but there was a few months of learning here as we discovered both datacentres managed files differently (one over writes files, the other stores a weeks worth), sometimes the files are incomplete and worse the integrity of Analytics is questionable with miss-aligned tables for User Status showing Enabled users in Analytics when users are actually Disabled in Jive!! Jive do sort these issues quickly on raising cases.

            Ideally you want to be using the new Jive Cloud Analytics Service as that has better data gathering and can use APIs to access the data, eliminating the need for postgres and managing dump files, instead using CSV files as you select what you need. Greatest value is that the new Jive Analytics Service will have Incremental transaction log so initially you get a load of data but then its just topped up - The current Jive Analytics and Activity databases are written in full daily (we requested daily)! Note you have to be on Jive 7 with the new built in data gathering to be able to use the new CloudAlytics (as oudi called it this week!).

            Hope that helps. Will post more when documentation complete

            Al

              • Re: Script JCA Downloads
                Caton Guilbault

                Thanks for you insights Al!

                 

                We have a very similar setup, pulling down the data then restoring via postgres on a Windows box. A woefully underpowered one unfortunately so we are also exploring creating a batch file to selectively restore only the tables we query on a regular basis without indexes to speed things up.  We are looking to upgrade to ver.7 early next year so hopefully that will solve some of these problems (shoutout to josh.richau for the awesome work he has done on this topic).

                 

                I cant wait to see what you have developed over the past few months, hopefully it will save us repeating that process! =)

                • Re: Script JCA Downloads
                  Ted Hopton

                  Hey, Al, did you get a chance to post documentation about how you automated the daily downloads from the JCA? We are just now getting set up to do this and would be grateful if we could learn from your experience.

                  cc: jsamuel jkates

                  • Re: Script JCA Downloads
                    sunilsani

                    Hi Al,

                     

                    Can you please post the steps & scripts to automate the downloads

                     

                    Sunil

                      • Re: Re: Script JCA Downloads
                        Caton Guilbault

                        Here is an example of what we put together on a windows box. The boded pieces will need to be replaced with your info. The red section is the folder where you want the DMP to be stored and restored from. The name in blue is the name of the Database in postgres.

                         

                        for /F "tokens=1* delims= " %%A in ('date /T') do set CDATE=%%B

                        for /F "tokens=1,2 eol=/ delims=/ " %%A in ('date /T') do set mm=%%B

                        for /F "tokens=1,2 delims=/ eol=/" %%A in ('echo %CDATE%') do set dd=%%B

                        for /F "tokens=2,3 delims=/ " %%A in ('echo %CDATE%') do set yyyy=%%B

                        set date=%yyyy%%mm%%dd%

                        @echo on

                        cd "C:\Program Files (x86)\GnuWin32\bin"

                        wget.exe -O E:\DMPDownloads\tmoblecustomercommunity6-%date%-0000.dmp --user "JCA Username" --password "JCA Password" "https://cloud.jivesoftware.com/admin/downloadDbFile.jspa?customerInstallationId=13016&dbDownloadTypeCode=SYSTEM_PGDUMP&filename=tmoblecustomercommunity6-%date%-0000.dmp" --no-check-certificate

                        "C:\Program Files (x86)\PostgreSQL\9.0\bin\pg_restore" -U postgres -h localhost -O -c -d Support "E:\DMPDownloads\tmoblecustomercommunity6-%date%-0000.dmp"

                        PAUSE

                         

                         

                        This will download and restore a specific dmp but you can do more that one in a single batch file if you want to. We also setup scheduled tasks that reference each batch file and kicks them off when the files for that instance are available.

                          • Re: Re: Script JCA Downloads
                            smac

                            Hi Canton,

                             

                            I have the following error when I use the script above.  How do you do the - no check certificate.  Originally, when I did it the first time, it works.  After I change the password, I got this error.  So I am not sure if it is due to the password change or Jive block the download after the second try.

                             

                            This is the error.

                             

                            Resolving cloud.jivesoftware.com... 204.93.64.239

                            Connecting to cloud.jivesoftware.com|204.93.64.239|:443... connected.

                            ERROR: cannot verify cloud.jivesoftware.com's certificate, issued by `/C=US/O=Di

                            giCert Inc/OU=www.digicert.com/CN=DigiCert SHA2 High Assurance Server CA':

                              Self-signed certificate encountered.

                            To connect to cloud.jivesoftware.com insecurely, use `--no-check-certificate'.

                            Unable to establish SSL connection.

                             

                             

                            C:\gnuwin32\bin>--no-check-certificate

                            '--no-check-certificate' is not recognized as an internal or external command,

                            operable program or batch file.

                             

                            Thanks for your help.

                             

                            Stewart

                              • Re: Re: Script JCA Downloads
                                Caton Guilbault

                                Hi Stewart,

                                 

                                From past experience the first thing to check is making sure you have the updated password in your script, we had issues with password changes in the past.

                                 

                                I think the '--no-check-certificate'  needs to be included at the end of your wget line. Depending on the application you are using to write the scripts sometimes an extra line break will cause problems. I recommend Notepad++ as it has batch syntax highlighting.

                                 

                                Can you post the script your using it might help diagnose the problem.

                                1 person found this helpful
                              • Re: Script JCA Downloads
                                smac

                                Hi Canton,

                                 

                                When I run the line below, it ask for password. Do you have an updated postgres restore cmd line?

                                 

                                "C:\Program Files (x86)\PostgreSQL\9.0\bin\pg_restore" -U postgres -h localhost -O -c -d jivedb_comm "E:\DMPDownloads\tmoblecustomercommunity6-%date%-0000.dmp"


                                I tried this line below, but it's not working.


                                "C:\Program Files (x86)\PostgreSQL\9.0\bin\pg_restore" -U postgres -W password -h localhost -O -c -d jivedb_comm "E:\DMPDownloads\tmoblecustomercommunity6-%date%-0000.dmp"


                                Thanks,

                                Stewart

                          • Re: Script JCA Downloads
                            paytsea

                            Hi Canton,

                             

                            We have been using your instructions but keep running into the error "Connection Refused" when the program tries to access the Jive Page. Do you know of any reasons this would occur? We have tried a variety of our admin usernames and passwords, but all come back with the same error.

                             

                            Any help would be appreciated!

                            Sean and Alex at DISH

                              • Re: Script JCA Downloads
                                Caton Guilbault

                                Hey Sean,

                                 

                                Can you post the script your using so I can review it? Also what OS, postgre & wget versions are you using?

                                 

                                I'm using Win 7 64bit with "PostgreSQL 9.0.6 build 1500, 32-bit" and wet version 1.11.4-1

                                 

                                Make sure you are paying attention to the spacing in --no-check-certificate and look for extra line breaks as they will cause problems. I recommend Notepad++ to build your scripts as it has batch syntax highlighting.

                                 

                                Finally make sure the credentials you are using have full access to the JCA and that DMPs are available for the date your trying to pull. I've had problems before where our DMPs get set to weekly refreshes (the Jive default) which breaks the script since it looking for a DMP from the day its ran. If you don't need daily data you will need to adjust the tokens pieces at the start of the script.

                                 

                                Screenshot of what a successful connection,download & restore should look like:

                                DMPScriptResults.png