Deployment State Compliance Question

Transact-SQL
1

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.

2

Are you using the right database and schema?

Please prefix field references with tablename (or alias) in the where statement. That way you always know where you found the field and you avoid breaking the Query, once you add table which has same field names.

3

yes, correct database and schema. This query language was sent to me as is from SCCM 2012. I'm just trying to re-code it into a report. All the fields I have except BaselineID which I suspect is known as something else. Perhaps CI_ID? I'm guessing.

4

Your guess is better than mine - I don't know your data.

5

I would query the sys.columns (system) table, with some/several! wildcards to try to find which table/view the column is in.

6 
select t.name, c.name from sys.tables t 
join sys.columns c on t.object_id = c.object_id where c.name like '%pumpkin pie%'

select t.name, c.name from sys.tables t
 join sys.columns c on t.object_id = c.object_id where c.name like '%roob Barb pie%'

OR

SELECT TOP (1000) [TABLE_CATALOG]
      ,[TABLE_SCHEMA]
      ,[TABLE_NAME]
      ,[VIEW_DEFINITION]
      ,[CHECK_OPTION]
      ,[IS_UPDATABLE]
  FROM [INFORMATION_SCHEMA].[VIEWS]
where VIEW_DEFINITION like '%shabang%'