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 Comments
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
yes,it is.aviod null values helps to to get right information.
Eswar ,
I want the sql query to generate the hostname, username and user login time. coould you able to help to get this result
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/
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
can you post the error msgs ?
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
did u also create prompt for collection ?@ID ?
are you getting any error ?