5 Replies Latest reply on Jun 18, 2013 10:04 AM by Michael Jantz


    Jim Campbell

      What is the best way to do this for non-Lifecycle/Virtualization properties?  Right now we have a job that runs every night to export a small number of custom properties that we have created for the Server object to a .csv file but it is starting to take a long time using the CLI as we add more and more servers.  I know I could query the server_prop view in the database but would prefer to not use that method if I don't have to.  I am also not keen on having to run ETL every night to be able to use BSARA.


      The script is executed as a 'separately against each host' script with one of our application servers as a target.  Right now the job looks something like:


      ALL_SERVERS=`blcli Server listAllServers`

      for SERVER in $ALL_SERVERS
      blcli_execute Server getFullyResolvedPropertyValue Property_Name_here
      blcli_storeenv Property_value_here
      echo $SERVER, $Property_Value_here >> //server/newcsvfile.csv



        • 1. Re: getBulkServerProperties
          Bill Robinson

          do you have bdssa in the environment?  that might be a faster way to get a bunch of properties out for a bunch of servers.


          also - what are you trying to do here once you get the properties ?

          • 2. Re: getBulkServerProperties
            Jim Campbell

            We have BDSSA but prefer not to run ETL every night.  If that is the only other option I would rather just query it directly from the database and just update the process if the database schema changes on an upgrade.  The idea is just to output it to a file for the time being.  Eventually we want to be using AO to propagate the value to a field in Remedy but that will be a while.

            • 3. Re: getBulkServerProperties
              Bill Robinson

              depending on the property type it may be fairly complicated to pull the value out of the db.  how many properties and which ones specifically are you talking about ?  and what are you doing w/ them ? - i mean what are you trying to populate in remedy and for what purpose ?

              • 4. Re: getBulkServerProperties
                Jim Campbell

                This appears to work fine for our purposes (at least for now, I know the schema can change for each version) :


                   Select device.name, a.property_value, b.property_value, os.name, device.os_version from device

                   join os on device.os_id = os.os_id

                   join v_server_property a on device.name = a.server_name

                   join v_server_property b on device.name = b.server_name

                   where device.device_state_id = 50

                   AND os.name = 'Windows'

                   AND a.property_name = 'Custom_Property_1'

                   and b.property_name = 'Custom_Property_2'


                The idea is to use Bladelogic properties for the maintenance windows (principally occupied by server OS/application patching) and to display these in Remedy as information for server owners.

                • 5. Re: Re: getBulkServerProperties
                  Michael Jantz

                  Joining against the view for each single property will slow down significantly with each added property.

                  We are using this statement, which seems to perform better.


                  USE bladelogic;
                      d.name as NAME,
                      a.name as AGENT_STATUS,
                      o.name as OS_TYPE,
                      d.os_version as Version,
                      properties.RSCD_VERSION as RSCD_VERSION,
                      properties.<prop_name1> as <prop_name1>,
                      properties.<prop_name2> as <prop_name2>
                      device AS d
                          LEFT JOIN agent_state AS a
                              ON d.agent_state_id = a.agent_state_id
                          LEFT JOIN os AS o
                              ON d.os_id = o.os_id
                          LEFT JOIN (
                                  MAX( CASE property_name WHEN '<prop_name1>' THEN property_value ELSE '' END ) <prop_name1>,
                                  MAX( CASE property_name WHEN '<prop_name2>' THEN property_value ELSE '' END ) <prop_name2>,
                                  MAX( CASE property_name WHEN 'RSCD_VERSION' THEN property_value ELSE '' END ) RSCD_VERSION,
                              GROUP BY
                          ) AS properties
                              ON d.name = properties.server_name
                      d.device_state_id = 50
                  ORDER BY


                  Relying on the provided view hopefully should be version stable - so far it survived two upgrades

                  Maybe someone really knowing SQL could speed things up?