Video Screencast Help
Search Video Help Close Back
to help
Not able to make it to Vision this year? Get a sampling in the Best of Vision on Demand group.

Need report showing a list of PCs having NOT logged in for over 60 days.....

Updated: 22 May 2010 | 6 comments
yusgab's picture
0 0 Votes
Login to vote

I ran some reports but I see all the logins from the login table for that PC...I want just the very last , most current login checked to see if it meets the 60 day check.....with downsizing IT wants to recover PCs just sitting out there, reporting back inventory, but not being used much if at all.  Thanks.

Comments

yusgab's picture
05
May
2009
1 Vote +1
Login to vote

Select vc.[name],

Select vc.[name], vc.[IsManaged], eulu.[User], eulu.[Last Logon] AS [Last Logon]
from vComputer vc
JOIN inv_aex_eu_logon_users eulu
on vc.guid = eulu._resourceguid
AND UPPER(vc.[User]) = UPPER(eulu.[User])
WHERE eulu.[User] IS NOT NULL
AND eulu.[User] <> ''
GROUP BY vc.Name, eulu.[User], eulu.[Last Logon], vc.IsManaged
HAVING DATEDIFF(DD, max(eulu.[Last Logon]), GETDATE()) > 90
ORDER By max(eulu.[Last Logon]) DESC

not getting the most current LAST LOGON being checked for over 90 days...

jharings's picture
05
May
2009
0 Votes 0
Login to vote

Exchange Users

I may be wrong, but wouldn't this be gathered by the Exchange inventory, which would be run on a different schedule than say, last logged on user from basic inventory?

Jim Harings
HP Enterprise Services
1st Rule of Connect Club: Mark the post that helped you the most as a 'solution'. 2nd Rule of Connect Club:You must talk about Connect club.

yusgab's picture
06
May
2009
0 Votes 0
Login to vote

Exchange inventory?

Not familiar with this topic.....please elaborate...thanks.

jharings's picture
06
May
2009
0 Votes 0
Login to vote

The query you posted

references data collected by the Exchange inventory .exe (regular inventory). The basic inventory also collects the logged on user,  and is used to help populate the primary user. This data is collected by basic inventory, which is much more frequent.

Jim Harings
HP Enterprise Services
1st Rule of Connect Club: Mark the post that helped you the most as a 'solution'. 2nd Rule of Connect Club:You must talk about Connect club.

KSchroeder's picture
07
May
2009
0 Votes 0
Login to vote

Inv_AeX_EU_Logon_Users runs

Inv_AeX_EU_Logon_Users runs every day I think (I'm not sure; we have completely hacked up the Out-of-box config for Inventory).

yusgab's report will find any machine where the primary user hasn't logged on in > 90 days, but I haven't analyzed it enough to know if it will tell you if ANY user hasn't logged on in that long.  It might also be worth adding the Inv_AeX_AC_Identification.[Client Date] column, which has the current date/time on the system when it last sent basic inventory (which should be every day).  I think this will work:

Select vc.[name], vc.[IsManaged], MAX(eulu.[Last Logon]), acid.[Client Date]
from vComputer vc
JOIN inv_aex_ac_identification acid
on vc.Guid = acid._ResourceGUid
JOIN Inv_AeX_eu_logon_users eulu
on vc.guid = eulu._resourceguid
WHERE eulu.[User] IS NOT NULL
AND eulu.[User] <> ''
AND DATEDIFF(DD, acid.[Client Date], GETDATE()) < 7
AND DATEDIFF(DD, eulu.[Last Logon], GETDATE()) > 90
GROUP BY vc.Name, vc.IsManaged, acid.[Client Date]

ORDER By vc.Name--, max(eulu.[Last Logon]) DESC

Thanks,
Kyle
Symantec Trusted Advisor

For Forum threads, please click "Mark as Solution" if answered.
For all content, please give a thumbs up if you agree with or support the post.

jharings's picture
08
May
2009
0 Votes 0
Login to vote

That may be

If the user inventory is run every day. I don't typically do that, as the logged on user data (from basic inventory) is gathered every day. Always happy to be corrected though.

Jim Harings
HP Enterprise Services
1st Rule of Connect Club: Mark the post that helped you the most as a 'solution'. 2nd Rule of Connect Club:You must talk about Connect club.