Client Management Suite

 View Only

  • 1.  Help customizing built in Inactive Machines report

    Trusted Advisor
    Posted Mar 05, 2012 03:12 PM

    We're using CMS 7.1 SP2.

    The Inactive Machines report is very helpful for us... it would also be helpful if one of the fields it brings up would be Primary User.

    I cloned the built in report so I am not working on the built in - but no idea where to start after I click edit and see the SQL.

    Can anyone help?

     

    Here's the existing query

     

    DECLARE @v1_TrusteeScope nvarchar(max)
    SET @v1_TrusteeScope = N'%TrusteeScope%'
     
    SELECT DISTINCT a1.[Guid] as [Guid], 
    a1.[Name] as [Name],
                            a1.[Domain] as [Domain] ,  
                            'Inactive' AS [Status],
    a1.[System Type] as [System Type],  
    a1.[OS Name] as [Operating System], 
    a1.[OS Version] as [Version],  
    ISNULL (d.[OS Revision],'') as [Service Pack],  
    CONVERT (VARCHAR(40),MAX(rs.ModifiedDate),120) as [Time Last Connected]   
    FROM dbo.vComputer a1  
    JOIN dbo.ScopeMembership sm
    ON sm.[ResourceGuid] = a1.Guid
    AND sm.[ScopeCollectionGuid] IN (SELECT * FROM dbo.fnGetTrusteeScopeCollections (@v1_TrusteeScope)) 
    JOIN dbo.vResourceUpdateSummary rs
    ON a1.[Guid] = rs.[ResourceGuid]
    LEFT JOIN dbo.Inv_AeX_AC_Identification d
    ON d._ResourceGuid = a1.Guid
    --WHERE a1.[System Type] LIKE 'Win%'    
    GROUP BY a1.[Name], 
    a1.Guid, 
    a1.[Domain],  
    a1.[OS Name],
    a1.[OS Version],
    d.[OS Revision],
    a1.[System Type]  
    HAVING DATEDIFF(day,max(rs.ModifiedDate),getdate()) >=  %Number of Days% 
    ORDER BY [Name]
      


  • 2.  RE: Help customizing built in Inactive Machines report

    Posted Mar 05, 2012 04:46 PM

    Add a1.[User], right after a1.[Name] as [Name],

    This pulls the Primary User from Inv_AeX_AC_Primary_User, which is included in the vComputer view as User.



  • 3.  RE: Help customizing built in Inactive Machines report

    Trusted Advisor
    Posted Mar 06, 2012 07:52 AM

    error I get is "This DataSource is not in a runnable state."

     

    here is an excerpt of what I have under data source - paramertised query

    "SELECT DISTINCT a1.[Guid] as [Guid],

    a1.[Name] as [Name],

     a1.[User], as [User],
     a1.[Domain] as [Domain] ,  
       'Inactive' AS [Status], "
     
    I also tried just
    a1.[Name] as [Name],
     a1.[User],
     
    but same error.  I understand pulling Primary user from  Inv_AeX_AC_Primary_Use - but should that database be part of the query somewhere?