SOLVED Report to find last logon details of each computer using SCCM

[Biman Roy]

I can access both web and MMC console for SCCM. I need to know the a) software count b) the PC/server names using the software c) the person who last logged in to the PC/server.


What report should I look into?

 

[Prajwal Desai]

a) Software count - I did not get this. Please clarify.

b) PC/server names using the software - Report name - Computers with a specific product and Report Category - Software Companies and Products.

c) Person who logged in to last PC - You have to use the below query

SELECT     Name0 AS 'Computer', CONVERT(varchar(10), DATEADD(ms, lastLogon0 / CAST(10000 AS bigint) % 86400000, DATEADD(day,

                     lastLogon0 / CAST(864000000000 AS bigint) - 109207, 0)), 111) AS 'Last AD Logon'

FROM         v_R_System

WHERE     (lastLogon0 IS NOT NULL)

ORDER BY 'Last AD Logon'

 

[Biman Roy]

Thanks Prajwal,

Let me rephrase my query:


I can access both web and MMC console for SCCM. I need to know the a) softwares used in my site b) the PC/server names using those software c) the person who last logged in to the PC/servers – all in one report


What report should I look into?


I ran your query, and I got the below error:


Msg 207, Level 16, State 1, Line 1

Invalid column name 'lastLogon0'.

Msg 207, Level 16, State 1, Line 1

Invalid column name 'lastLogon0'.

Msg 207, Level 16, State 1, Line 1

Invalid column name 'lastLogon0'

 

[Prajwal Desai]

yes, even i got the same error. Can you try this query ?. This one worked for me

Select distinct
v_R_System.Netbios_Name0 AS "Computer Name",
v_GS_SYSTEM_CONSOLE_USER.SystemConsoleUser0 AS "Console User",
v_GS_SYSTEM_CONSOLE_USER.NumberOfConsoleLogons0 AS "Console Logons",
v_GS_SYSTEM_CONSOLE_USER.TotalUserConsoleMinutes0 AS "Total Minutes on Console",
v_GS_SYSTEM_CONSOLE_USER.LastConsoleUse0 AS "Last Console Use"
from v_R_System INNER JOIN
(SELECT
ResourceID,
SystemConsoleUser0,
NumberOfConsoleLogons0,
TotalUserConsoleMinutes0,
LastConsoleUse0,
ROW_NUMBER() OVER(PARTITION BY ResourceID ORDER BY LastConsoleUse0 DESC) AS Point
FROM
v_GS_SYSTEM_CONSOLE_USER) v_GS_SYSTEM_CONSOLE_USER
ON v_GS_SYSTEM_CONSOLE_USER.ResourceID = v_R_System.ResourceID
AND v_GS_SYSTEM_CONSOLE_USER.Point = 1
Order by v_R_System.Netbios_Name0

 

[Biman Roy]

Much better. How I do get software (e.g, MS office, visio) installed in each of those computers?

 

[Prajwal Desai]

b) PC/server names using the software - Report name - Computers with a specific product and Report Category - Software Companies and Products.

a) List of software - Report - Software 06A

 

[Biman Roy]

I am looking for a report which will give me the below fields. If one query serves the purpose then it will be good. Please let me know the queries.

The fields I need are:

Computer name
Computer OS version (windows 7 or 2008…)
IP
Lastlogin on console
Last date when the data was gathered from the computer
All the software containing the string “visio”​

THANKS VERY MUCH

 

[Biman Roy]

I have one more query need. How do I know which computers have not pushed their data in last 10 days. THANKS

 

[Prajwal Desai]

I believe you need to tweak the reports as you want to add fields in the report. See the below link
Link - https://msdn.microsoft.com/en-us/library/ee210533.aspx

 

[Prajwal Desai]

I have one more query need. How do I know which computers have not pushed their data in last 10 days. THANKS

which computers have not pushed their data in last 10 days ? What does this mean ?.

 

[Biman Roy]

Prajwal, I meant to ask "Which are the computers that have not pushed their data to SCCM server in last 10 days"

 

[Prajwal Desai]

Precisely you want to know how many clients have not reported to SCCM in last 10 days ?. Here is the link - http://ccmexec.com/2011/05/sccm-report-clients-that-have-not-reported-recently-per-collection/

 

[Biman Roy]

I copy-pasted the query in the above URL in "Create Query Wizard". But it reports syntax error. I chose "Object Type" "System Resource"

 

[Prajwal Desai]

did you read the line in the post "If you import the query on your own don’t forget to create a prompt called @varCollection." Have you done this ?. I would suggest to download the mof file first.

 

[Jatinder]

Hi, Is there any way to find user login on Win10 devices on particular date?

I need to generate 2 reports based on who logged on to each device on 2 different dates.

 

[Garth]

Hi, Is there any way to find user login on Win10 devices on particular date?

I need to generate 2 reports based on who logged on to each device on 2 different dates.

CM is not the tool for this. You want to query AD directly for this info.

 

[Jatinder]

CM is not the tool for this. You want to query AD directly for this info.

Thank you Garth, I will try some other tool then. Regards, Jatinder