10 Replies Latest reply on Jun 5, 2014 11:23 PM by Yanick Girouard

    NSH Script  Property :: TYPE* not setting correctly?

    richard mcleod

      I'm looking to create Depot Smart Groups that would show all Type 1 NSH scripts, all Type 2 NSH scripts, etc... however what I'm seeing is that all NSH Scripts in the depot (regardless of their actual type) have their TYPE* property set to 1. Is this normal behaviour?

       

      Here is a sample

      Capture.PNG.png

        • 1. Re: NSH Script  Property :: TYPE* not setting correctly?
          Bill Robinson

          yes, 'TYPE*' here is the model type number, which for all nsh script objects is 1.

           

          you'd need to do a db query as the nsh script type is an 'option' associated w/ the script object that's stored in the db, and just to make it fun, type 2 is runscript, type 1 is 'run once' in the db, which is the inverse of the gui

          1 of 1 people found this helpful
          • 2. Re: NSH Script  Property :: TYPE* not setting correctly?
            richard mcleod

            Thanks Bill - Any hints as to which table this key information is tucked away in?

            • 3. Re: NSH Script  Property :: TYPE* not setting correctly?
              richard mcleod

              Think I found it...

               

              option_value in dbo.depot_object_option_value

               

              will post a full query when I get it all working correctly

              • 4. Re: NSH Script  Property :: TYPE* not setting correctly?
                Bill Robinson

                Yeah – you’re on the right track.  You don’t learn anything if I just give you the answer ☺

                • 5. Re: NSH Script  Property :: TYPE* not setting correctly?
                  richard mcleod

                  So here's what I've got... struggling to find the full 'File Location (//blfs/root_path/full/path/to/script/on/blfs.nsh)' but think the rest is correct so far

                   

                  select distinct depot_object.depot_object_id, depot_object.name, file_server.root_path, depot_object_option_value.option_value, depot_object.data_type_id

                   

                  from depot_object, file_server, file_location, depot_object_location, depot_object_option_value

                   

                  where depot_object.name = 'script_name.nsh' AND depot_object.is_latest_version = '1' AND file_server.file_server_id = file_location.file_server_id AND depot_object.depot_object_id = depot_object_option_value.depot_object_id AND file_location.file_location_id = depot_object_location.file_location_id AND depot_object_option_value.option_value = '1' AND depot_object.data_type_id = '1025';

                   

                   

                  right now I'm specifying the depot_object.name to reduce query time + unnecessary processing, that condition will be removed later.

                   

                  Had to make an assumption that depot_object.data_type_id is equal to the type of depot object, also assuming that '1025' = NSH Script.

                  • 6. Re: NSH Script  Property :: TYPE* not setting correctly?
                    richard mcleod

                    And now... a more complete & optimized version... still trying to get filename+path... getting there

                     

                    select distinct depot_object.depot_object_id as depotObjectID, depot_object.name as depotObjectName, file_server.root_path as rootPath, file_location.relative_path as relativePath, depot_object_option_value.option_value as scriptType, depot_object.data_type_id as depotType, JOB.name as jobName

                    from depot_object

                    join depot_object_option_value

                    on depot_object_option_value.depot_object_id = depot_object.depot_object_id

                    join JOB_DEPOT_OBJECT

                    on JOB_DEPOT_OBJECT.depot_object_id = depot_object.depot_object_id

                    join depot_object_location

                    on depot_object_location.depot_object_id = depot_object.depot_object_id

                    join file_location

                    on file_location.file_location_id = depot_object_location.file_location_id

                    join FILE_SERVER

                    on FILE_SERVER.file_server_id = file_location.file_server_id

                    join JOB

                    on JOB.job_id = JOB_DEPOT_OBJECT.job_id

                    where depot_object.is_latest_version = '1' AND depot_object_option_value.option_value = '1' AND depot_object.data_type_id = '1025';

                    • 7. Re: NSH Script  Property :: TYPE* not setting correctly?
                      richard mcleod

                      Bill Robinson - Are NSH Script types 3 and 4 also flipped in the database?

                       

                      If i want to capture type 3 i would set option_value = 4 and vice versa to capture type 4?

                      • 8. Re: NSH Script  Property :: TYPE* not setting correctly?
                        richard mcleod

                        One final update here...

                         

                        This is how NSH Script types map into the depot_object_option_value table (feel free to correct me if im wrong though!)

                         

                        Type 1 NSH Script = option_value = 2

                        Type 2 NSH Script = option_value = 1

                        Type 3 NSH Script = option_value = 4

                        Type 4 NSH Script = option_value = 3

                        • 9. Re: NSH Script  Property :: TYPE* not setting correctly?
                          Bill Robinson

                          I forget, look in the blcli NSHScript addNSHScriptToDepot command

                          • 10. Re: NSH Script  Property :: TYPE* not setting correctly?
                            Yanick Girouard

                            This might be what you need:

                             

                            select
                                script_name,
                                location,
                                case [400]
                                    when 1 then '2'
                                    when 2 then '1'
                                    when 3 then '4'
                                    when 4 then '3'
                                end as 'type',
                                [402] as 'charset',
                                case [403]
                                    when 'N' then 'No'
                                    when 'Y' then 'Yes'
                                end as 'allow_no_targets'
                            from (
                                select do.name script_name, dov.option_id, dov.option_value, '//'+fs.host+fs.root_path+fl.relative_path location from depot_object do
                                inner join depot_object_option_value dov on dov.depot_object_id = do.depot_object_id and dov.depot_object_version_id = do.depot_object_version_id
                                inner join depot_object_location dol on dol.depot_object_id = do.depot_object_id and dol.depot_object_version_id = do.depot_object_version_id
                                inner join file_location fl on fl.file_location_id = dol.file_location_id
                                inner join file_server fs on fs.file_server_id = fl.file_server_id
                                where do.object_type_id=1 and do.is_deleted=0 and do.is_latest_version=1
                            ) p
                            pivot (
                                max(option_value) for option_id in ([400],[402],[403])
                            ) pvt