Deployment State Compliance Question

  • Hello,
         I was given this query and asked to turn it into a report for subscription:
    select v_R_SYSTEM.ResourceID,
    v_R_SYSTEM.ResourceType,
    v_R_SYSTEM.Name,
    v_R_SYSTEM.vUniqueIdentifier,
    v_R_SYSTEM.ResourceDomainORWorkgroup,
    v_R_SYSTEM.Client
    from
    v_R_System inner join v_G_System_DCMDeploymentState on v_G_System_DCMDeploymentState.ResourceID = v_R_System.ResourceId WHERE BaselineID = 'ScopeId_xxxxxxxxxxxxxxxxxxxx' AND CollectionID = xxxxxx' AND ComplianceState = 3

    I've tried to find BaselineID, but don't see it any views.  Was this changed?  I also don't see a similarly named view for DeploymentState, unless v_CICurrentComplienceStatus is it?!?  Any suggestions are greatly appreciated.

  • maravig - Wednesday, October 18, 2017 6:29 AM

    Hello,
         I was given this query and asked to turn it into a report for subscription:
    select v_R_SYSTEM.ResourceID,
    v_R_SYSTEM.ResourceType,
    v_R_SYSTEM.Name,
    v_R_SYSTEM.vUniqueIdentifier,
    v_R_SYSTEM.ResourceDomainORWorkgroup,
    v_R_SYSTEM.Client
    from
    v_R_System inner join v_G_System_DCMDeploymentState on v_G_System_DCMDeploymentState.ResourceID = v_R_System.ResourceId WHERE BaselineID = 'ScopeId_xxxxxxxxxxxxxxxxxxxx' AND CollectionID = xxxxxx' AND ComplianceState = 3

    I've tried to find BaselineID, but don't see it any views.  Was this changed?  I also don't see a similarly named view for DeploymentState, unless v_CICurrentComplienceStatus is it?!?  Any suggestions are greatly appreciated.

    We would have no way of knowing if a view changed or what views contain what columns or where a certain view may be.
    If you can't see something, there is a possibility you don't have permissions.
    One thing you can try is to search the database for whatever column names you are trying to find. If you search on sys.columns, it has columns for both tables and views. You could search using something like:
    USE YourDatabaseName
    GO
    SELECT
        schema_name(o.schema_id) as SchemaName ,
        object_name(c.object_id) as TableOrView,
        c.[name] as ColumnName,
        o.modify_date
    FROM sys.columns c
    INNER JOIN sys.objects o
    ON c.object_id = o.object_id
    WHERE c.name LIKE '%ColumnNameToFind%'

    Sue

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply