Solved

Query users in active directory - last logon, disabled etc

Posted on 2011-09-22
13
3,952 Views
Last Modified: 2012-05-12
Hi All,

Is it possible to query active directory to retrieve the following without 3rd party tools?

Last logon (date) enabled and disabled users.

Thanks
0
Comment
Question by:MJB2011
  • 4
  • 4
  • 3
  • +2
13 Comments
 
LVL 39

Assisted Solution

by:Krzysztof Pytko
Krzysztof Pytko earned 100 total points
ID: 36579235
Yes it is, but you need to do that on a DC or a workstation with Administrative Tools installed. Use DSQUERY command to achive that. Example syntax

enabled user accounts

dsquery * –filter "&(&(objectClass=User)(objectCategory=Person)(!userAccountControl:1.2.840.113556.1.4.803:=2))" –attr sAMAccountName,givenName,sn

disabled user accounts

dsquery * –filter "&(&(objectClass=User)(objectCategory=Person)(userAccountControl:1.2.840.113556.1.4.803:=2))" –attr sAMAccountName,givenName,sn

Last Logon Timestamp (but in long non-readable value)

dsquery * –filter "&(&(objectClass=User)(objectCategory=Person))" -attr sAMAccountName,givenName,sn,lastLogonTimestamp

For more readable output you need to use 3rd party tools like ADFIND or PowerShell

Regards,
Krzysztof
0
 

Author Comment

by:MJB2011
ID: 36579418
Thanks - but there must a gui based tool that can achieve this? im on a tight schedule!
0
 
LVL 39

Assisted Solution

by:Krzysztof Pytko
Krzysztof Pytko earned 100 total points
ID: 36579440
You can use for that Active DIrectory Users and Computers console and make LDAP query there. Use "Saved queries" to save your common LDAP requests.

You will find very good explanation at this page
http://www.petri.co.il/saved_queries_in_windows_2003_dsa.htm

Use only these parts for queries within ADUC

enabled users
"&(&(objectClass=User)(objectCategory=Person)(!userAccountControl:1.2.840.113556.1.4.803:=2))"

disabled users
"&(&(objectClass=User)(objectCategory=Person)(userAccountControl:1.2.840.113556.1.4.803:=2))"

with the lstLogonTimestamp it's problem to display that in GUI

Krzysztof
0
 

Author Comment

by:MJB2011
ID: 36579450
I really need a script that lists all users and when they last logged in?
0
 
LVL 39

Expert Comment

by:Krzysztof Pytko
ID: 36579466
Yes, to get actual data/value ou need to use script. In ADUC you can use basic query to check account which were not logged on to domain for 30/60/90 days (as I remember)

Krzysztof
0
 
LVL 37

Expert Comment

by:Neil Russell
ID: 36579552
Paste the code below into a text file, save as UserLogins.ps1 and run with powershell

clear
Import-Module ActiveDirectory

function Get-ADUserLastLogon([string]$userName)
{
  $dcs = Get-ADDomainController -Filter {Name -like "*"}
  $time = 0
  foreach($dc in $dcs)
  { 
    $hostname = $dc.HostName
    $user = Get-ADUser "$userName" | Get-ADObject -Properties lastLogon 
    if($user.LastLogon -gt $time) 
    {
      $time = $user.LastLogon
    }
  }
  $dt = [DateTime]::FromFileTime($time)
  Write-Host $username "last logged on at:" $dt }

$Users = Get-ADUser -Filter * 
Foreach ($auser in $Users)
{
	$MyName = $auser.SamAccountName
	Get-ADUserLastLogon -userName "$MyName"
}

Open in new window

0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:MJB2011
ID: 36579739
Sorry not familiar with powershell. How do i run and export to file?

thanks
0
 
LVL 37

Expert Comment

by:Neil Russell
ID: 36579952
Revised version below. Save it as c:\LastLogons.ps1 then from the C:> prompt on a server with powershell installed type

poweshell.exe  c:\lastlogons.ps1


clear
Import-Module ActiveDirectory
$Lines = ""
function Get-ADUserLastLogon([string]$userName)
{
  $dcs = Get-ADDomainController -Filter {Name -like "*"}
  $time = 0
  foreach($dc in $dcs)
  { 
    $hostname = $dc.HostName
    $user = Get-ADUser "$userName" | Get-ADObject -Properties lastLogon 
    if($user.LastLogon -gt $time) 
    {
      $time = $user.LastLogon
    }
  }
  $dt = [DateTime]::FromFileTime($time)
  Set-Variable -name lines -Scope 1 -Value  "$lines `r`n $username ,last logged on at: $dt" 
  }

$Users = Get-ADUser -Filter * 
Foreach ($auser in $Users)
{
	$MyName = $auser.SamAccountName
	Get-ADUserLastLogon -userName "$MyName"
} 
$Lines | Out-File c:\lasLogons.csv

Open in new window

0
 
LVL 39

Expert Comment

by:Krzysztof Pytko
ID: 36580095
In 2003 PS is not installed by default :) Even native MS install does not suport those commands :)
So, you need to download Quest PowerShell module for AD or use 2008R2/Win 7 PowerShell to be able to use that script :]

The only one native solutions are:

-for GUI -> Active Directory Users and Computers console
-for CMD -> DS tools

Krzysztof
0
 
LVL 7

Expert Comment

by:ComputerBeast
ID: 36580104
Hi all,

Use the script:

Set objUser = GetObject("LDAP://cn=Ken Myer, ou=Finance, dc=fabrikam, dc=com")
Set objLastLogon = objUser.Get("lastLogonTimestamp")
Set objExcel = createobject("Excel.application")

intLastLogonTime = objLastLogon.HighPart * (2^32) + objLastLogon.LowPart
intLastLogonTime = intLastLogonTime / (60 * 10000000)
intLastLogonTime = intLastLogonTime / 1440

Wscript.Echo "Last logon time: " & intLastLogonTime + #1/1/1601#


'write to excel file

objexcel.Visible = True
objexcel.Workbooks.add
objexcel.Cells(1, 1).Value = "Testing"
objexcel.ActiveWorkbook.SaveAs("c:\exceltest.xls")
objexcel.Quit



Hope it works

Thank you
0
 
LVL 37

Expert Comment

by:Neil Russell
ID: 36580127
Ooooh My bad!! I missed the 2003 tag! :(
0
 

Author Comment

by:MJB2011
ID: 36580378
I have found this script. Is there anyway to output this to a readable file rather than getting a million popups?

' LastLogonTimeStamp.vbs
' VBScript program to determine when each user in the domain last logged
' on. Domain must be at Windows Server 2003 Functional Level.
'
' ----------------------------------------------------------------------
' Copyright (c) 2007-2010 Richard L. Mueller
' Hilltop Lab web site - http://www.rlmueller.net
' Version 1.0 - March 24, 2007
' Version 1.1 - July 6, 2007 - Modify how IADsLargeInteger interface
'                              is invoked.
' Version 1.2 - November 6, 2010 - No need to set objects to Nothing.
'
' The lastLogonTimeStamp attribute is Integer8, a 64-bit number
' representing the date as the number of 100 nanosecond intervals since
' 12:00 am January 1, 1601. This value is converted to a date. The last
' logon date is in UTC (Coordinated Univeral Time). It must be adjusted
' by the Time Zone bias in the machine registry to convert to local
' time.
'
' You have a royalty-free right to use, modify, reproduce, and
' distribute this script file in any way you find useful, provided that
' you agree that the copyright owner above has no warranty, obligations,
' or liability for such use.

Option Explicit

Dim objRootDSE, adoConnection, adoCommand, strQuery
Dim adoRecordset, strDNSDomain, objShell, lngBiasKey
Dim lngBias, k, strDN, dtmDate, objDate
Dim strBase, strFilter, strAttributes, lngHigh, lngLow

' Obtain local Time Zone bias from machine registry.
' This bias changes with Daylight Savings Time.
Set objShell = CreateObject("Wscript.Shell")
lngBiasKey = objShell.RegRead("HKLM\System\CurrentControlSet\Control\" _
    & "TimeZoneInformation\ActiveTimeBias")
If (UCase(TypeName(lngBiasKey)) = "LONG") Then
    lngBias = lngBiasKey
ElseIf (UCase(TypeName(lngBiasKey)) = "VARIANT()") Then
    lngBias = 0
    For k = 0 To UBound(lngBiasKey)
        lngBias = lngBias + (lngBiasKey(k) * 256^k)
    Next
End If
Set objShell = Nothing

' Determine DNS domain from RootDSE object.
Set objRootDSE = GetObject("LDAP://RootDSE")
strDNSDomain = objRootDSE.Get("defaultNamingContext")
Set objRootDSE = Nothing

' Use ADO to search Active Directory.
Set adoCommand = CreateObject("ADODB.Command")
Set adoConnection = CreateObject("ADODB.Connection")
adoConnection.Provider = "ADsDSOObject"
adoConnection.Open "Active Directory Provider"
adoCommand.ActiveConnection = adoConnection

' Search entire domain.
strBase = "<LDAP://" & strDNSDomain & ">"

' Filter on all user objects.
strFilter = "(&(objectCategory=person)(objectClass=user))"

' Comma delimited list of attribute values to retrieve.
strAttributes = "distinguishedName,lastLogonTimeStamp"

' Construct the LDAP syntax query.
strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree"

' Run the query.
adoCommand.CommandText = strQuery
adoCommand.Properties("Page Size") = 100
adoCommand.Properties("Timeout") = 60
adoCommand.Properties("Cache Results") = False
Set adoRecordset = adoCommand.Execute

' Enumerate resulting recordset.
Do Until adoRecordset.EOF
   ' Retrieve attribute values for the user.
    strDN = adoRecordset.Fields("distinguishedName").Value
    ' Convert Integer8 value to date/time in current time zone.
    On Error Resume Next
    Set objDate = adoRecordset.Fields("lastLogonTimeStamp").Value
    If (Err.Number <> 0) Then
        On Error GoTo 0
        dtmDate = #1/1/1601#
    Else
        On Error GoTo 0
        lngHigh = objDate.HighPart
        lngLow = objDate.LowPart
        If (lngLow < 0) Then
            lngHigh = lngHigh + 1
        End If
        If (lngHigh = 0) And (lngLow = 0) Then
            dtmDate = #1/1/1601#
        Else
            dtmDate = #1/1/1601# + (((lngHigh * (2 ^ 32)) _
                + lngLow)/600000000 - lngBias)/1440
        End If
    End If
    ' Display values for the user.
    If (dtmDate = #1/1/1601#) Then
        Wscript.Echo strDN & ";Never"
    Else
        Wscript.Echo strDN & ";" & dtmDate
    End If
    adoRecordset.MoveNext
Loop

' Clean up.
adoRecordset.Close
adoConnection.Close
0
 
LVL 57

Accepted Solution

by:
Mike Kline earned 150 total points
ID: 36580409
By the way you asked for a GUI tool there is adinfo that is free and good  

http://www.cjwdev.co.uk/Software/ADReportingTool/Info.html

...but it is a third party tool and your original question said no third parties but figured I'd mention it since you asked about GUI tools tool

Thanks

Mike
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.

Join & Write a Comment

I know all systems administrator at some time or another has had to create a script to copy file from a server share to a desktop. Well now there is an easy way to do this in Group Policy. Using Group policy preferences is not hard. The first thing …
Do you have users whose passwords are expiring and they are constantly calling you?  Well I sure did and needed a way to put an end to this.  We have a lot of remote users which would not be notified that their passwords were expiring since they wer…
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…
This tutorial will walk an individual through the process of configuring their Windows Server 2012 domain controller to synchronize its time with a trusted, external resource. Use Google, Bing, or other preferred search engine to locate trusted NTP …

757 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now