ESwar KONeti

All about Configmgr and its Connected Objects….

  • About Author
      View eswar koneti's LinkedIn profile
  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 249 other subscribers

  • Archives

  • Meta

  • #Configmgr Tweets

  • Copyright!

    All the blog posts in this website are owned by Eswar Koneti and may not be reused in any mode without prior approval of Eswar Koneti. You may quote one paragraph from the blog posts if you link to the original blog post.
    Happy Reading!

  • Awards


  • Follow Updates

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

Posted by Eswar Koneti on February 16th, 2011

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. 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. Eswar Koneti Says:

    did u also create prompt for collection ?@ID ?
    are you getting any error ?

  4. Eswar Koneti Says:

    can you post the error msgs ?

  5. 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

  6. Eswar Koneti Says:

    use the SQL views documentation to see where they exists,couple of blogs on the above hostname,username etc on this website.
    http://eskonr.com/2013/04/download-sccm-2012-sql-views-documentation/

  7. 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

  8. Eswar Koneti Says:

    yes,it is.aviod null values helps to to get right information.

Leave a Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

*