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 ?
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.
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 ?
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.
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; SELECT 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> FROM 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 ( SELECT server_name, 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, FROM v_server_property GROUP BY server_name ) AS properties ON d.name = properties.server_name WHERE d.device_state_id = 50 ORDER BY d.name
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?