• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3676
  • Last Modified:

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

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
Hendrik Wiese
Asked:
Hendrik Wiese
4 Solutions
 
SubsunCommented:
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
 
QlemoC++ DeveloperCommented:
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
 
SubsunCommented:
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
 
Joe KlimisCommented:
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
 
Hendrik WieseInformation Security ManagerAuthor Commented:
Perfect thanks everyone!!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now