SCCM report for computers where no user logged in for past 20 Days

This report gives you list of PCS where no user logged into the computer since 20 days.If the SCCM clients has any issues in sending inventory information to site server,you will see lot of computers though user logged into the computer.

select
a.Name0,a.UserName0,TimeStamp from v_GS_COMPUTER_SYSTEM a ,v_FullCollectionMembership b
where DATEDIFF(dd,a.TimeStamp,GetDate()) > 20 and  b.CollectionID = @ID and a.ResourceID=b.ResourceID

order by a.Name0

Prompt for ID: SELECT DISTINCT CollectionID, Name FROM v_Collection

8 Responses to "SCCM report for computers where no user logged in for past 20 Days"

  1. i have done one modificationa do you think its worth

    select
    a.Name0,a.UserName0,TimeStamp from v_GS_COMPUTER_SYSTEM a ,v_FullCollectionMembership b
    where DATEDIFF(dd,a.TimeStamp,GetDate()) > 20 and b.CollectionID = 'sms00001' and a.ResourceID=b.ResourceID
    and a.UserName0 is NULL
    order by a.UserName0

    Reply
  2. Hi Eswar,

    Could your please help me to know how to generate last login time with hostname based on the collection.

    I have a query in that we can generate the report based on the particular machine.

    Regards,
    Abdul

    Reply
  3. Hi Eswar,
    The qery select
    a.Name0,a.UserName0,TimeStamp from v_GS_COMPUTER_SYSTEM a ,v_FullCollectionMembership b
    where DATEDIFF(dd,a.TimeStamp,GetDate()) > 20 and b.CollectionID = @ID and a.ResourceID=b.ResourceID

    is not giving any result

    Reply

Post Comment