Splunk Query

[whitecat001]

Can i get a Splunk query that shows the last logon date for a group of active directory service account 

Thanks 

[antoniolamonica]

index=wineventlog
sourcetype=WinEventLog
EventCode=4624
user="svc*"

| eval EventTime=_time
| eval EventTime=strftime(_time, "%m/%d/%Y %H:%M:%S %Z"

| stats latest(EventTime) as lastlogon by user
| table lastlogon, user

[emdaax]

Hi @whitecat001,

Assuming your Active Directory logs are being indexed under "index=windows" and you are forwarding the logon events EventCode=4624 (successful logons), you can use the following query:

index=windows* source="WinEventLog:Security" sourcetype=xmlwineventlog host=* user!="*$" EventCode=4624 dest_nt_domain=<your domain name> Logon_Type=5
| stats max(_time) as last_login by index, host, dest, dest_nt_domain, user, src_ip, Logon_Type
| eval last_login=strftime(last_login, "%Y-%m-%d %H:%M:%S")

The EventCode=4624  filters the logs to only include successful logon events.

You can use the field "Logon_Type", which points out how the user logged on.
There are a total of nine different types of logons, the most common logon types are: logon type 2 (interactive) and logon type 3 (network).

Any logon type other than 5 (which denotes a service startup) is a red flag.

Logon Type	Logon Title	Description
0	System	Used only by the System account, for example at system startup.
2	Interactive	A user logged on to this computer.
3	Network	A user or computer logged on to this computer from the network.
4	Batch	Batch logon type is used by batch servers, where processes may be executing on behalf of a user without their direct intervention.
5	Service	A service was started by the Service Control Manager.
7	Unlock	This workstation was unlocked.
8	NetworkCleartext	A user logged on to this computer from the network. The user's password was passed to the authentication package in its unhashed form. The built-in authentication packages all hash credentials before sending them across the network. The credentials do not traverse the network in plaintext (also called cleartext).
9	NewCredentials	A caller cloned its current token and specified new credentials for outbound connections. The new logon session has the same local identity, but uses different credentials for other network connections.
10	RemoteInteractive	A user logged on to this computer remotely using Terminal Services or Remote Desktop.
11	CachedInteractive	A user logged on to this computer with network credentials that were stored locally on the computer. The domain controller was not contacted to verify the credentials.
12	CachedRemoteInteractive	Same as RemoteInteractive. This is used for internal auditing.
13	CachedUnlock	Workstation logon.

best regards,

P.S.: Karma Points are always appreciated 😉

[PickleRick]

First and foremost - what data do you have in your Splunk?

[gcusello]

Hi @whitecat001 ,

you could try with something like this:

index=your_index
| stats latest(_time) AS _time BY Account_name

if you don't like to use the _time field, but you want to rename it, remember that _time is in epochtime and that's automaticay displayed in Human readable, if you rename, you have aso to convert in Human Readable format.

index=your_index
| stats latest(_time) AS latest BY Account_name
| eval latest=strftime(latest),"%Y-%m-%d %H:%M:%S")

Ciao.

Giuseppe

[whitecat001]

can i pls get another search that can show last logon date for active directory service account

[whitecat001]

its not working