none
Modify existing sccm report to include server description.

    Question

  • Hi guys,

    I was wondering if anyone with greater reporting experience out there could help me modify (or modify a cloned copy) of the standard Compliance 8 - Computers in a specific compliance state for an update list <secondary> report.  The report body with the 3 prompts are below:

    declare @CI_ID int; select @CI_ID=CI_ID from v_ConfigurationItems where CIType_ID=9 and CI_UniqueID=@AuthListID

    declare @StateID int
    select @StateID=StateID from v_StateNames sn where sn.StateName=@StateName and TopicType=300

    begin
    if (@__filterwildcard = '')
      select distinct CI_UniqueID as AuthListID, Title as Title from v_AuthListInfo order by Title
    else
      select distinct CI_UniqueID as AuthListID, Title as Title from v_AuthListInfo
        where ((CI_UniqueID like @__filterwildcard) or
             (Title like @__filterwildcard))
        order by Title
    end

    begin
     if (@__filterwildcard = '')
      select CollectionID as CollectionID, Name as CollectionName from v_Collection order by Name
     else
      select CollectionID as CollectionID, Name as CollectionName from v_Collection
      WHERE CollectionID like @__filterwildcard or Name like @__filterwildcard
      order by Name
    end

    begin
     if (@__filterwildcard = '')
     select StateName, StateDescription from v_StateNames sn where TopicType=300 and sn.StateID<3
     else
     select StateName, StateDescription from v_StateNames sn where TopicType=300 and sn.StateID<3
      and ((sn.StateName like @__filterwildcard) or (sn.StateID like @__filterwildcard))
    end

    I've created reports before pulling from the v_GS_operating_system table in the past for server description, which is what I want to add to this report. I find building reports in sql easier at first than doing it directly in sccm to see if it works.

    Thanks!


    Friday, July 26, 2013 4:15 PM

Answers

  • This seems like what you are looking for.

    declare @CI_ID int; select @CI_ID=CI_ID from v_ConfigurationItems where CIType_ID=9 and CI_UniqueID=@AuthListID

    declare @StateID int
    select @StateID=StateID from v_StateNames sn where sn.StateName=@StateName and TopicType=300

    select
     rs.Name0 as MachineName,
     rs.User_Name0 as LastLoggedOnUser,
     asite.SMS_Assigned_Sites0 as AssignedSite,
     rs.Client_Version0 as ClientVersion,GSOPERATING.Description0,
     ''
    from v_ClientCollectionMembers ccm
    join v_UpdateListStatus_Live cs on cs.CI_ID=@CI_ID and cs.ResourceID=ccm.ResourceID and isnull(cs.Status, 0)=@StateID
    join v_R_System rs on rs.ResourceID = ccm.ResourceID
    left join v_RA_System_SMSAssignedSites asite on asite.ResourceID = ccm.ResourceID
    left join v_GS_OPERATING_SYSTEM GSOPERATING on GSOPERATING.ResourceID = rs.ResourceID

    where ccm.CollectionID=@CollID


    Thank you,

    Friday, August 09, 2013 8:59 PM

All replies

  • Anyone have any input?
    Wednesday, July 31, 2013 7:36 PM
  • Anyone help with this?
    Thursday, August 08, 2013 5:59 PM
  • This seems like what you are looking for.

    declare @CI_ID int; select @CI_ID=CI_ID from v_ConfigurationItems where CIType_ID=9 and CI_UniqueID=@AuthListID

    declare @StateID int
    select @StateID=StateID from v_StateNames sn where sn.StateName=@StateName and TopicType=300

    select
     rs.Name0 as MachineName,
     rs.User_Name0 as LastLoggedOnUser,
     asite.SMS_Assigned_Sites0 as AssignedSite,
     rs.Client_Version0 as ClientVersion,GSOPERATING.Description0,
     ''
    from v_ClientCollectionMembers ccm
    join v_UpdateListStatus_Live cs on cs.CI_ID=@CI_ID and cs.ResourceID=ccm.ResourceID and isnull(cs.Status, 0)=@StateID
    join v_R_System rs on rs.ResourceID = ccm.ResourceID
    left join v_RA_System_SMSAssignedSites asite on asite.ResourceID = ccm.ResourceID
    left join v_GS_OPERATING_SYSTEM GSOPERATING on GSOPERATING.ResourceID = rs.ResourceID

    where ccm.CollectionID=@CollID


    Thank you,

    Friday, August 09, 2013 8:59 PM
  • That worked out great!  Thanks so much.
    Monday, August 12, 2013 7:48 PM