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,817 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

A procedure for exporting installed hotfix details of remote computers using powershell
This article runs through the process of deploying a single EXE application selectively to a group of user.
This tutorial will give a an overview on how to deploy remote agents in Backup Exec 2012 to new servers. Click on the Backup Exec button in the upper left corner. From here, are global settings for the application such as connecting to a remote Back…
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles to another domain controller. Log onto the new domain controller with a user account t…

828 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