6 Replies Latest reply on Jun 15, 2016 6:57 AM by Prashant Kumbhare

    What is the best way to list large size depot objects

    Prashant Kumbhare

      We recently started having file server space issues. I was expecting to get the answers in communities but couldn't find much help there.

       

      To resolve these issues we need to identify depot objects that are occupying most space and out of them we need to delete those which are not modified recently(say since last 6 months). After looking into database, I wrote this query,which helped me getting: Object name, data modified and objects relative location on file server.

       

      select a.depot_object_id, a.name, a.date_created, a.date_modified, c.relative_path, a.is_deleted

      from depot_object a , depot_object_location b, file_location c

      where

      a.depot_object_id=b.depot_object_id and

      b.file_location_id=c.file_location_id

       

      Using this query I can get the size of objects from Fileserver which will give me largest depot objects.

       

      However, I couldn't find a way to get the absolute location of these objects in bladelogic console, so I can't delete the objects unless I search them in console one by one. Can someone please help me with this?

       

      Also, please let me know.

      1. Is there already a better way to achieve what I need?

      2. Is my approach correct? (I know it's too tedious, but I can automate this if I find a way to get absolute path for object).

       

      Additional info:

      1. We are running appserver version 8.2.01.397, soon will be upgraded to 8.7.

      2. BL DB and File server Housekeeping is enabled already, It is scheduled to run every weekend. Attached script.

        • 1. Re: What is the best way to list large size depot objects
          Santhosh Kurimilla

          I think, you may need to start exploring it in reverse way from File Server side :

          1. Go to the File Server's file system or directory
              (If you don't know it already, you can find it from nsh -> blasadmin -a show file all)

          2. You may find scripts, blpackages, installables, etc.. directories under the default File system path

          3. Try finding out the most space used objects under each directory.

          4. You may have most of the objects with high space usage under Installables, blpackages and files (if you take snapshots very frequently) folder. So, worth searching more there.

          5. Once you identify it, you may try to find the objects in the console by running the below query (of course, you have to join and enhance the query).

           

          select * from FILE_LOCATION where RELATIVE_PATH in ('installables/xxx_guid_xxx','installables/xxx_guid_xxx', installables/xxx_guid_xxx')

          select * from FILE_LOCATION where RELATIVE_PATH like blpackages/xxx_guid_xxx

          1 of 1 people found this helpful
          • 2. Re: What is the best way to list large size depot objects
            Prashant Kumbhare

            Thanks Santhosh for your reply. Approach you've suggested seems better, I'll try to use this one instead of mine.

             

            However queries that you've suggested(and also the one I have used) will only give me name of the object and not the absolute path in BL Console. It would be very tedious for us to delete all objects if count is very high, and would be more difficult if we have two objects with same name.

             

            I actually want to create a script out of all this information. So if I somehow get db key of the object or its absolute path in BL Console then I can probably delete the objects using BLCLI. (after fully testing it in staging environment ofcourse ).

             

            So, is there a way I can get dbkey or path for such objects?

            • 3. Re: What is the best way to list large size depot objects
              Santhosh Kurimilla

              Yes, I understand the efforts and knowledge required to write that complex SQL query to get the FULL path of the Depot objects in the console.  But, you may request BMC Team to write one for you as they must be having it in place.

               

              Bill, Akbar, Sean

              • 4. Re: What is the best way to list large size depot objects
                Bill Robinson

                it's not that complicated - just a couple joins.  for sql server change the || to +

                 

                 

                WITH temp1 (group_id, path) AS

                (

                            select group_id, cast(name as varchar(1000))

                               from blgroup bg1

                               where parent_group_id = 0

                               UNION ALL

                          select bg1.group_id,

                                     cast(bg2.path || '/' || cast(bg1.name as varchar(100)) as varchar(1000))

                               from blgroup bg1

                               inner join temp1 bg2 on bg1.parent_group_id = bg2.group_id

                )

                select temp1.path,do.name,do.is_deleted,o.name,fs.host,fs.root_path,fl.relative_path from file_location fl

                join depot_object_location dol on fl.file_location_id = dol.file_location_id

                join depot_object do on dol.depot_object_id = do.depot_object_id and dol.depot_object_version_id = do.depot_object_version_id

                join temp1 on do.group_id = temp1.group_id

                join object_type o on do.object_type_id = o.object_type_id

                join file_server fs on fl.file_server_id = fs.file_server_id;

                2 of 2 people found this helpful
                • 5. Re: What is the best way to list large size depot objects
                  Santhosh Kurimilla

                  I mean, a person like me, with less SQL Query knowledge, it is complicated.

                  • 6. Re: What is the best way to list large size depot objects
                    Prashant Kumbhare

                    Thanks Santhosh and Bill for your replies. I got the answer I needed.