3 Replies Latest reply on Apr 26, 2018 6:06 PM by Kai-Ming Cheah

    Delete Workbooks > 60 days through script

    Sunil Gudipati

      I am in the process of doing some automation for identifying the Workbooks older than 60 days and delete them using a Batch script or shell.

       

      Any recommendations is deeply appreciated.

        • 1. Re: Delete Workbooks > 60 days through script
          Kai-Ming Cheah

          We do something similar and here is the basic concept

           

          we use R Studio as the orchestration tool does the end to end process but you can use your preferred too.

           

          1.) connect to the Tableau Postgres,

          2.) finds the dashboards that haven't been used in 90 days ( see code below)

          3.) use this list to create a batch file with TAB CMD instructions to remove the delete the workbook and create a log

          tabcmd

           

          The batch file contents will something like this at end

           

           

          "c:\Program Files\Tableau\Tabcmd\Command Line Utility\tabcmd.exe" login -s https://yoursever -t Customer -u %username% --password-file H:\MyPwd.txt --no-certcheck  >> H:\90Day_%DATE:~-4%%DATE:~7,2%%DATE:~4,2%_Delete.log 2>&1

          "c:\Program Files\Tableau\Tabcmd\Command Line Utility\tabcmd.exe" delete "WORKBOOKNAME1" -r "PROJECT NAME"  --no-certcheck  >>   H:\90Day_%DATE:~-4%%DATE:~7,2%%DATE:~4,2%_Delete.log

          "c:\Program Files\Tableau\Tabcmd\Command Line Utility\tabcmd.exe" delete "WORKBOOKNAME2" -r "PROJECT NAME"  --no-certcheck  >>   H:\90Day_%DATE:~-4%%DATE:~7,2%%DATE:~4,2%_Delete.log

          "c:\Program Files\Tableau\Tabcmd\Command Line Utility\tabcmd.exe" delete "WORKBOOKNAME3" -r "PROJECT NAME"  --no-certcheck  >>   H:\90Day_%DATE:~-4%%DATE:~7,2%%DATE:~4,2%_Delete.log

           

          4.) execute the Batch file that was created.

          5.) send an email to each user notifying which dashboards have been deleted ( we got the email from the post gres query)

           

           

           

          here is the tableau postgres query

           

          select
            wbk.site_id,
            vs.views_workbook_id,
            wbk.name,
            wbk.workbook_url,
            wbk.owner_name,
            wbk.project_name,
            coalesce(su.email,wbk.owner_name) as email,
            max(vs.last_view_time) as last_view_time,
            max(vs.nviews) as views

            from public._views_stats vs
           
            left join public._workbooks wbk
            on vs.views_workbook_id = wbk.id
           
            left join public.workbooks wbks
            on vs.views_workbook_id = wbks.id
           
            left join public._system_users su
            on wbk.owner_name=su.name
             
            group by 1,2,3,4,5,6,7,8
           
            having max(vs.last_view_time) <= current_date -60
           
             ")

          • 2. Re: Delete Workbooks > 60 days through script
            Sunil Gudipati

            Hi Kai-Ming,

             

            thank you for your response, i am looking in to the script.

             

            Could you please explain, what does this part "2>&1" in the below command is expected to do?

             

            "c:\Program Files\Tableau\Tabcmd\Command Line Utility\tabcmd.exe" login -s https://yoursever -t Customer -u %username% --password-file H:\MyPwd.txt --no-certcheck  >> H:\90Day_%DATE:~-4%%DATE:~7,2%%DATE:~4,2%_Delete.log 2>&1

             

            Also please clarify the following :

             

            1. from the custom sql > Tableau report output, how does the batch command interpret the data workbook name?

            2. is this a manual task ?

            • 3. Re: Delete Workbooks > 60 days through script
              Kai-Ming Cheah

               

              1. from the custom sql > Tableau report output, how does the batch command interpret the data workbook name?

              2. is this a manual task ?

               

              I'm not 100% sure what you mean here... but will give it ago.

               

              Once you have the list of workbooks names, we parse the list and build the relevant commands into the Batch file.

              "c:\Program Files\Tableau\Tabcmd\Command Line Utility\tabcmd.exe" delete "WORKBOOKNAME1" -r "PROJECT NAME"  --no-certcheck  >>   H:\90Day_%DATE:~-4%%DATE:~7,2%%DATE:~4,2%_Delete.log

               

               

               

              Could you please explain, what does this part "2>&1" in the below command is expected to do?

               

              In your BATCH FILE/COMMAND SCRIPT add 2>&1  at the end of the log path

               

              This will captures ERRORS in your LOG OUTPUT

               

              ===== Creating new session


              Customer

              ===== Requesting '/workbooks/SalaryCreditSample.twbx' from
              the server...

                ***
              404 Not Found

              ===== Continuing previous session

              =====     Server: xxxxxxxxxx

              =====     Username: xxxxxxxxx

              =====     Site:   
              Customer

              ===== Requesting '/workbooks/xxxxxxxxxxxxx.twbx'
              from the server...

                ***
              404 Not Found

              "Tableau download End" Mon 31/07/2017
              14:58:09.23