Solved

Powershell - Export users not logged in for 90 days but include the exact number of days in the csv file

Posted on 2014-11-19
5
2,570 Views
Last Modified: 2014-11-20
Hi Guys,

I have compiled the following script to export all users that has not logged on for 90 days and does not have specific description values. The script works fine and exports all the data currently in the script.

THE SCRIPT
$CURRENTDATE=GET-DATE 
$NumberDays=90
$OU='domain.com/'
$OutputfilePath = "c:\90daysincelogon_"+([datetime]::Now).tostring("yyyy-MMM-dd")+".csv"

GET-QADUSER -SizeLimit 0 -SearchRoot $OU | Where-Object { $_.description -notlike "System Account*" -and $_.description -notlike "Vendor*" -and $_.LastLogon.AddDays($NumberDays) -lt $CURRENTDATE } | select name, LogonName, mail, description, LastLogon, LastLogonTimestamp, AccountIsDisabled, ParentContainerDN, PasswordStatus | export-csv $outputfilePath

Open in new window


Desired output and question
1. Why does the LastLogon and LastLogonTimestamp never mach?
2. I need a field next to LastLogon with the exact number of days since last logon for example 93 and the same for LastLogonTimestamp.

Thanks in advance
0
Comment
Question by:Hendrik Wiese
5 Comments
 
LVL 40

Accepted Solution

by:
Subsun earned 300 total points
ID: 40452419
You can add the number of days using a custom property,
Format to create a custom property is like...  
@{N="Days";E={((Get-Date) - $_.LastLogon).Days}}

For example..
$CURRENTDATE=GET-DATE 
$NumberDays=90
$OU='iemas.com/'
$OutputfilePath = "c:\90daysincelogon_"+([datetime]::Now).tostring("yyyy-MMM-dd")+".csv"

GET-QADUSER -SizeLimit 0 -SearchRoot $OU | Where-Object { $_.description -notlike "System Account*" -and $_.description -notlike "Vendor*" -and $_.LastLogon.AddDays($NumberDays) -lt $CURRENTDATE } | select name, LogonName, mail, description, LastLogon, @{N="Days";E={((Get-Date) - $_.LastLogon).Days}}, LastLogonTimestamp, AccountIsDisabled, ParentContainerDN, PasswordStatus | export-csv $outputfilePath

Open in new window


Regarding the question about LastLogon & LastLogonTimeStamp
LastLogon is the Non-Replicable attribute, meaning the value of this attribute is specific to a Domain Controller.
LastLogonTimeStamp is the Replicable attribute but this attribute is not updated every time a user successfully logs in. This attribute is updated only when its current value is older than the current time minus the value of the msDS-LogonTimeSyncInterval attribute

You can find more details from following articles..

http://blogs.technet.com/b/askds/archive/2009/04/15/the-lastlogontimestamp-attribute-what-it-was-designed-for-and-how-it-works.aspx

http://blogs.metcorpconsulting.com/tech/?p=283
0
 
LVL 69

Assisted Solution

by:Qlemo
Qlemo earned 150 total points
ID: 40452434
Adding those columns:
$CURRENTDATE=GET-DATE 
$NumberDays=90
$OU='domain.com/'
$OutputfilePath = "c:\90daysincelogon_"+([datetime]::Now).tostring("yyyy-MMM-dd")+".csv"

GET-QADUSER -SizeLimit 0 -SearchRoot $OU |
  ? { $_.description -notlike "System Account*"
            -and $_.description -notlike "Vendor*"} |
  select name, LogonName, mail, description, LastLogon, 
         @{n='LastLogonDays'; e={($currentDate-$_.LastLogon).Days}},
         LastLogonTimestamp,
         @{n='LastLogonTSDays'; e={($currentDate-$_.LastLogonTimeStamp).Days}},
         AccountIsDisabled, ParentContainerDN, PasswordStatus |
  ? { $_.LastLogonDays -gt $NumberDays } |
  export-csv $outputfilePath

Open in new window

0
 
LVL 40

Assisted Solution

by:Subsun
Subsun earned 300 total points
ID: 40452447
BTB you could also simplify the script using -NotLoggedOnFor parameter of Get-QADUser, which is more efficient.
GET-QADUSER -SizeLimit 0 -SearchRoot $OU -NotLoggedOnFor $NumberDays | Where-Object { $_.description -notlike "System Account*" -and $_.description -notlike "Vendor*"} | select name, LogonName, mail, description, LastLogon, @{N="Days";E={((Get-Date) - $_.LastLogon).Days}}, LastLogonTimestamp, AccountIsDisabled, ParentContainerDN, PasswordStatus | export-csv $outputfilePath

Open in new window

-NotLoggedOnFor  (From Help)
    Use this parameter to retrieve user accounts that have not been used to log on for at least the number of days specified by the parameter value. This parameter overrides the logon-related inactivity condition of the Inactive or InactiveFor parameter. Thus, if the NotLoggedOnFor value of 60 is supplied in conjunction with the InactiveFor value of 30, the cmdlet searches for accounts that are expired for 30 or more days, or have the password age of 30 or more days, or have not been used to log on for 60 or more days.
0
 
LVL 10

Assisted Solution

by:JoeKlimis
JoeKlimis earned 50 total points
ID: 40452459
Hi

If you don't have the quest tools , try the following

  $searcher = New-Object System.DirectoryServices.DirectorySearcher 
  $searcher.PageSize     = 200
  $searcher.SearchScope  = "subtree"
  #$searcher.SearchRoot = "LDAP://OU=yourusersOU,DC=yourdirectory,DC=yourdomain,DC=co,DC=uk"
  $searcher.Filter       = "(&(objectCategory=person)(objectClass=user))"
  $searcher.PropertiesToLoad.Add("sAMAccountName") > $Null
  $searcher.PropertiesToLoad.Add("lastLogon") > $Null
 
 $results = @()
 $users = $searcher.FindAll()
 Foreach($user in $users) 
{ 
$result = @()
$result = ""| select Username,LastLogon,Days
 if($user.properties.item("lastLogon") -ne 0) 
  { 
 [int32]$dateDiff  = (new-timespan -start ([datetime]::FromFileTime([int64]::Parse($user.properties.item("lastLogon"))) ) -end (get-date)).days
 $a = [datetime]::FromFileTime([int64]::Parse($user.properties.item("lastLogon")))  
 [string]$result.Username = $user.Properties.samaccountname
 $result.Lastlogon = $a
 $result.days = $dateDiff
 $results +=$result
 } 
  else 
  {  # "$($user.properties.item("samaccountname")),Never" #
   [string]$result.Username = $user.Properties.samaccountname
   $result.Lastlogon = $a
   $result.days = $dateDiff
   $results +=$result
   }
  
}
$results| where {$_.days -ge 90 }|  export-csv -notype results.csv

Open in new window

0
 
LVL 21

Author Closing Comment

by:Hendrik Wiese
ID: 40454502
Perfect thanks everyone!!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, I am going to show you how to simulate a multi-site Lab environment on a single Hyper-V host. I use this method successfully in my own lab to simulate three fully routed global AD Sites on a Windows 10 Hyper-V host.
This script can help you clean up your user profile database by comparing profiles to Active Directory users in a particular OU, and removing the profiles that don't match.
This tutorial will walk an individual through locating and launching the BEUtility application to properly change the service account username and\or password in situation where it may be necessary or where the password has been inadvertently change…
This tutorial will show how to configure a single USB drive with a separate folder for each day of the week. This will allow each of the backups to be kept separate preventing the previous day’s backup from being overwritten. The USB drive must be s…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question