?
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
Medium Priority
?
3,362 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 40

Accepted Solution

by:
Subsun earned 1200 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 71

Assisted Solution

by:Qlemo
Qlemo earned 600 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 1200 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 11

Assisted Solution

by:Joe Klimis
Joe Klimis earned 200 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

Are You Ready for GDPR?

With the GDPR deadline set for May 25, 2018, many organizations are ill-prepared due to uncertainty about the criteria for compliance. According to a recent WatchGuard survey, a staggering 37% of respondents don't even know if their organization needs to comply with GDPR. Do you?

Question has a verified solution.

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

Group policies can be applied selectively to specific devices with the help of groups. Utilising this, it is possible to phase-in group policies, over a period of time, by randomly adding non-members user or computers at a set interval, to a group f…
There are times when we need to generate a report on the inbox rules, where users have set up forwarding externally in their mailbox. In this article, I will be sharing a script I wrote to generate the report in CSV format.
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…
This tutorial will walk an individual through setting the global and backup job media overwrite and protection periods in Backup Exec 2012. Log onto the Backup Exec Central Administration Server. Examine the services. If all or most of them are stop…

719 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