Windows service account login attempts

Data required

Microsoft: Windows security logs

Procedure

Verify that you deployed the add-on to the search heads and Splunk Universal Forwarders on the monitored systems. For more information, see About installing Splunk add-ons.
Verify that you have enabled the WinEventLog://Security input on all Active Directory domain controllers.
Run the following search. You can optimize it by specifying an index and adjusting the time range.

eventtype=windows_logon_failure OR eventtype=windows_logon_success user=svc*
|fields user, status, _time,Logon_Type
|eval Logon_User=lower(user)
|stats count BY _time,status,Logon_User,Logon_Type
|eventstats sum(count) AS l_cnt BY Logon_User,Logon_Type
|eval Logon_Types=case(Logon_Type="0","(".l_cnt.") Local-System",Logon_Type="2","(".l_cnt.") Interactive",Logon_Type="3","(".l_cnt.") Network",Logon_Type="4","(".l_cnt.") Batch",Logon_Type="5","(".l_cnt.") Service",Logon_Type="7","(".l_cnt.") Screen Unlock",Logon_Type="8","(".l_cnt.") Network Cleartext",Logon_Type="9","(".l_cnt.") New Credentials-RunAs",Logon_Type="10","(".l_cnt.") Remote Interactive",Logon_Type="11","(".l_cnt.") Cached Interactive",Logon_Type="255" OR Logon_Type="0xFF","(".l_cnt.") Failed")
|stats max(_time) AS latest, min(_time) AS earliest, count AS sesscount, values(Logon_Types) AS Session_Types, count(eval(status="success")) AS success_count, count(eval(status="failure")) AS fail_count BY Logon_User
|eval First_Attempt=strftime(earliest, "%m/%d/%Y %H:%M:%S"),Last_Attempt=strftime(latest, "%m/%d/%Y %H:%M:%S"),Fail_Percent=round((fail_count/sesscount)*100,2),Total_Attempts=fail_count+success_count,Success_Percent=round((success_count/sesscount)*100,2),Fail_Percent=Fail_Percent."% (".tostring(fail_count,"commas").")"
|sort 0 -Fail_Percent, -Total_Attempts
|eval Success_Percent=Success_Percent."% (".tostring(success_count,"commas").")",Total_Attempts=tostring(Total_Attempts,"commas")
|table Logon_User Total_Attempts Fail_Percent Success_Percent First_Attempt Last_Attempt,Session_Types

Search explanation

The table provides an explanation of what each part of this search achieves. You can adjust this query based on the specifics of your environment.

Splunk Search	Explanation
`eventtype=windows_logon_failure OR eventtype=windows_logon_success`	Search for only Windows logon events that are a success or failure. These event types are defined in the Splunk Add-on for Microsoft Windows.
`user=svc*`	Search only users with svc at the start of the user name. These are service accounts.
`\|fields user, status, _time,Logon_Type`	Show only the fields listed.
`\|eval Logon_User=lower(user)`	Normalize logon names to all lowercase to make comparisons easier.
`\|stats count BY _time,status,Logon_User,Logon_Type`	Calculate an initial count to 1 for fields in the BY clause. This sets up the running total for the `eventstats` command coming up next.
`\|eventstats sum(count) AS l_cnt BY Logon_User,Logon_Type`	Calculate a running total of `eventtype` (success or failure) for the user by logon type.
\|eval Logon_Types=case(Logon_Type="0","(".l_cnt.") Local-System",Logon_Type="2","(".l_cnt.") Interactive",Logon_Type="3","(".l_cnt.") Network",Logon_Type="4","(".l_cnt.") Batch",Logon_Type="5","(".l_cnt.") Service",Logon_Type="7","(".l_cnt.") Screen Unlock",Logon_Type="8","(".l_cnt.") Network Cleartext",Logon_Type="9","(".l_cnt.") New Credentials-RunAs",Logon_Type="10","(".l_cnt.") Remote Interactive",Logon_Type="11","(".l_cnt.") Cached Interactive",Logon_Type="255" OR Logon_Type="0xFF","(".l_cnt.") Failed")	Format the output that shows the logon type descriptor and the count of the type returned by the search. The count is surrounded by parenthesis.
`\|stats max(_time) AS latest, min(_time) AS earliest, count AS sesscount, values(Logon_Types) AS Session_Types, count(eval(status="success")) AS success_count, count(eval(status="failure")) AS fail_count BY Logon_User`	Aggregate time to latest and earliest using `max` and `min` functions. Gather the corresponding logon type for each logon user. Lastly, show the count of each logon type per user.
`\|eval First_Attempt=strftime(earliest, "%m/%d/%Y %H:%M:%S"), Last_Attempt=strftime(latest, "%m/%d/%Y %H:%M:%S"), Fail_Percent=round((fail_count/sesscount)100,2), Total_Attempts=fail_count+success_count, Success_Percent=round((success_count/sesscount)100,2), Fail_Percent=Fail_Percent."% (".tostring(fail_count,"commas").")"`	Format latest and earliest time value from epoch to calendar format. Count sessions, success, and failures. Calculate the percent of failures and percent of successes of session count. Format the "out of" failed percent value with the percent sign and show the total failed count to the right in parentheses.
`\|sort 0 -Fail_Percent, -Total_Attempts`	Sort with the highest failed percentage and highest number of total attempts first, overriding the default limit indicated by the 0.
`\|eval Success_Percent=Success_Percent."% (".tostring(success_count,"commas").")", Total_Attempts=tostring(Total_Attempts,"commas")`	Format the success percent output to show the percent sign and show the total count in parenthesis.
`\|table Logon_User Total_Attempts Fail_Percent Success_Percent First_Attempt Last_Attempt Session_Types`	Display the results in a table with columns in the order shown.

Next steps

Sample results are displayed in the following table and give an easy-to-read summary of logon activity for service accounts. Some of the values could be used for alerting, such as too many failed logins as a percentage, failed logons during certain times, and failures on certain machines. Most of these are security or compliance related, but they could also inform troubleshooting. For example, failed updates or installation could be correlated to failed logons.

`Logon_User`	`Total_ Attempts`	`Fail_ Percent`	`Success_ Percent`	`First_ Attempt`	`Last_ Attempt`	`Session_ Types`
`svc_ldap`	`1`	`100.00% (1)`	`0.00% (0)`	`9/20/20 1:35`	`9/20/20 1:35`	`(1) Remote Interactive`
`svc_oracle`	`1`	`100.00% (1)`	`0.00% (0)`	`9/20/20 3:31`	`9/20/20 3:31`	`(1) Remote Interactive`
`svc_iis`	`190`	`0.53% (1)`	`99.47% (189)`	`9/19/20 13:01`	`9/20/20 13:38`	`(200) Remote Interactive`
`svc_salesforce`	`196`	`0.00% (0)`	`100.00% (196)`	`9/19/20 13:06`	`9/20/20 13:40`	`(211) Remote Interactive`

Finally, you might be interested in other processes associated with the Monitoring Windows account access use case.