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

  1. abdul says:

    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

  2. abdul says:

    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

  3. abdul says:

    Eswar ,

    I want the sql query to generate the hostname, username and user login time. coould you able to help to get this result

  4. nawaz.kazi says:

    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

Leave a Reply


2 + four =

© 2015 Eswar Koneti's Blog. All rights reserved.
%d bloggers like this: