CREATE  view ca_am_asset_derived_status
as
SELECT
        ca_agent.object_uuid,   
        object_status = CASE
                /* NOTE: THE FOLLOWING CASE ORDER IS IMPORTANT AND MUST NOT BE CHANGED */

                /* default handling, if violation state is found, that what counts*/
                WHEN violation_state>0 THEN violation_state                                     
                /* check for legacy assets              */
                WHEN amlegacy_objects.object_uuid IS NOT NULL THEN -1
                /* check for external assets */
                WHEN (agent_type=11) THEN -3                            
                /* check for  agent with no AM plugin (have precedence over pre-registered) */
                WHEN (agent_type=1 AND agent_comp_id IS NULL) THEN -2   
                /* check for preregistered assets */
                WHEN (last_run_date IS NULL OR last_run_date<0) THEN -4
                /* if we reached here the asset is "operational" or "0" */
                ELSE 0
        END  
FROM
        /* join out agent list against the current violation state according to POLSEV */
        ca_agent LEFT JOIN
                (
                        /* following sub-select give us a list of all our assets and the policy violation state */
                        SELECT
                                ca_agent.object_uuid,isnull(MIN(polsev),0) AS violation_state
                        FROM
                                ca_agent LEFT OUTER JOIN POLILOG
                        ON
                                ca_agent.object_uuid=polilog.object_uuid
                        WHERE
                                polsev>0 AND agent_type IN (1,2,11)
                        GROUP BY
                                ca_agent.object_uuid
                ) t_violation_state
        ON
                ca_agent.object_uuid=t_violation_state.object_uuid

        /* join aginst the agent component table to check if the AM agent or AM proxy agent is registered for the asset */
        LEFT OUTER JOIN
                ca_agent_component
        ON
                ca_agent.object_uuid=ca_agent_component.object_uuid AND agent_comp_id in (80,86)

        /* join against a list of all our legacy assets  */
        LEFT OUTER JOIN
                amlegacy_objects
        ON
                ca_agent.object_uuid=amlegacy_objects.object_uuid
WHERE
        agent_type IN (1,2,11)
GO
GRANT SELECT ON  [dbo].[ca_am_asset_derived_status] TO [ams_group]
GRANT SELECT ON  [dbo].[ca_am_asset_derived_status] TO [ca_itrm_group]
GRANT SELECT ON  [dbo].[ca_am_asset_derived_status] TO [ca_itrm_group_ams]
GRANT SELECT ON  [dbo].[ca_am_asset_derived_status] TO [upmuser_group]
GO