2 Replies Latest reply on May 11, 2011 2:59 PM by Yanick Girouard

    BL 7.6.x - MSSQL query to select all servers with properties (including decommisioned ones)

    Yanick Girouard

      Hi everyone,

       

      I have put together a pretty useful query to select all servers from our core database along with specific properties. The query shows all servers, including decommissioned ones (for history purposes). I thought it could be useful for others so I figured I'd post it here.

       

      You can easily adapt it by adding/removing some of the properties (highlighted in blue below) to include your own custom ones for examples. Here's the one I'm using:

       

      select distinct

          d1.name as 'target.name',

          (select distinct p.property_value from v_server_property as p where p.server_name = d1.name and p.property_name = 'CUSTOMER') as 'property.customer',

          (select distinct p.property_value from v_server_property as p where p.server_name = d1.name and p.property_name = 'z_CGI_CHARGEBACK_CODE') as 'property.chargeback_code',

          d2.description as 'property.description',

          (select distinct p.property_value from v_server_property as p where p.server_name = d1.name and p.property_name = 'AGENT_STATUS') as 'property.agent_status',

          (select distinct p.property_value from v_server_property as p where p.server_name = d1.name and p.property_name = 'RSCD_VERSION') as 'property.rscd_version',

          (select distinct p.property_value from v_server_property as p where p.server_name = d1.name and p.property_name = 'RSCD_DIR') as 'property.rscd_dir',

          (select distinct p.property_value from v_server_property as p where p.server_name = d1.name and p.property_name = 'Location') as 'property.location',

          (select distinct p.property_value from v_server_property as p where p.server_name = d1.name and p.property_name = 'ENVIRONMENT') as 'property.environment',

          (select distinct p.property_value from v_server_property as p where p.server_name = d1.name and p.property_name = 'OS') as 'property.os',

          (select distinct p.property_value from v_server_property as p where p.server_name = d1.name and p.property_name = 'OS_PLATFORM') as 'property.os_platform',

          (select distinct p.property_value from v_server_property as p where p.server_name = d1.name and p.property_name = 'OS_VERSION') as 'property.os_version',

          (select distinct p.property_value from v_server_property as p where p.server_name = d1.name and p.property_name = 'OS_PLATFORM') as 'property.os_platform',

          convert(varchar, d2.date_created, 120) as 'target.date_created',

          convert(varchar, d2.date_modified, 120) as 'target.date_modified',

          case d2.device_state_id

                  when 50 then 0

                  when 100 then 1

                  else null

          end as 'target.decommissioned',

          convert(varchar, getdate(), 120) as 'property.export_date'

      from device as d1

      inner join device as d2 on d2.device_id = (select top 1 device_id from device where name = d1.name order by date_created desc)

      order by 'target.name' asc