Uncategorised

Splunk Tutorials: Windows Query

June 27, 2022 Leave a Comment 
source=wineventlog:application
source=wineventlog:security
source=wineventlog:system
------Windows Query 1----------------
Event Logs | System Logs | Warnings and Errors
This will hit all of the host and pull back the eventlogs and group them by Message. You can change the source to what ever windows eventlogs you need
host="*" source=wineventlog:system NOT Type=Information | stats count by Message | sort -count | table count, Message
------Windows Query 2----------------
This Splunk Query will return results for any Windows Service that has started.
Cond - Ensure the Splunk App for Windows is installed grab it here: https://apps.splunk.com/app/742/
sourcetype=WinEventLog:Application EventCode=105 | eval Date=strftime(_time, "%Y/%m/%d") | stats count by Date, SourceName, host | sort - Date | fields - count
------Windows Query 3----------------
This splunk query will return results for any Windows Service that has been stopped.
Ensure the Splunk App for Windows is installed grab it here: https://apps.splunk.com/app/742/
sourcetype=WinEventLog:Application EventCode=108 | eval Date=strftime(_time, "%Y/%m/%d") | stats count by Date, SourceName, host | sort - Date | fields - count
------Windows Query 4----------------
The following is a Splunk query that will display a timechart for all successful logons to windows:
source="WinEventLog:security" EventCode=4624 Logon_Type IN (2,7,10,11) NOT user IN ("DWM-*", "UMFD-*")
| timechart span=1h count by host
------Windows Query 5----------------
The following Splunk query will show a timechart of failed logon attempts per host:
source="WinEventLog:security" EventCode=4625 
| timechart span=1h count by host
------Windows Query 6----------------
This query will show a timechart of the status of an Locked Out Account
sourcetype="WinEventLog:Security" EventCode=4625 AND Status=0xC0000234 | timechart count by user | sort -count
------Windows Query 7----------------
The below will detect a form of brute force which most will miss. Whereas other scripts detect multiple logins against a single account, they fail to detect 4 failed logins against 40 accounts.
This first checks for all accounts having an account login failure of 4 or more, it then checks for the quantity of accounts that have failed by 4 or more (5 in the below example). So if someone attempts to login with 4 or more different passwords unsuccessfully on 5 or more accounts, the alarm will trip.
sourcetype=windows EventCode=4625 OR EventCode=4624 
| bin _time span=5m as minute 
| rex "Security ID:\s*\w*\s*\w*\s*Account Name:\s*(?<username>.*)\s*Account Domain:" 
| stats count(Keywords) as Attempts,
count(eval(match(Keywords,"Audit Failure"))) as Failed,
count(eval(match(Keywords,"Audit Success"))) as Success by minute username
| where Failed>=4
| stats dc(username) as Total by minute 
| where Total>5
------Windows Query 9----------------
This eval for password can be easily used for any field where a user can accidentally type in a password or even worse both username/password during login which generates a failed event.   Below example is for Windows failed login.  The eval will match 10 or more characters with 1 uppercase, 1 lower case, 1 digit and 1 special character.  This search also assumes you have the “User_Name” field being extracted from windows event logs.  Adjust as needed.
source=WinEventLog:Security TaskCategory=Logon Keywords="Audit Failure" | eval password=if(match(User_Name, "^(?=.*[a-z])(?=.*[A-Z])(?=.*[0-9])(?=.*[\W])(?=.{10,})"), "Yes", "No") | stats count by password User_Name | search password=Yes
------Windows Query 10----------------
This Splunk Search Query will indicate any user who attempted to login to a disabled account.
(Tested only on Windows 7 / Server 2008 and newer Windows logs).
source="WinEventLog:security" EventCode=4625 (Sub_Status="0xc0000072" OR Sub_Status="0xC0000072") Security_ID!="NULL SID" Account_Name!="*$" | eval Date=strftime(_time, "%Y/%m/%d")| rex "Which\sLogon\sFailed:\s+\S+\s\S+\s+\S+\s+Account\sName:\s+(?<facct>\S+)" | eval Date=strftime(_time, "%Y/%m/%d") | stats count by Date, facct, host, Keywords | rename facct as "Target Account" host as "Host" Keywords as "Status" count as "Count"
------Windows Query 11----------------
User Logon, Logoff, and Duration
source="wineventlog:security" action=success Logon_Type=2 (EventCode=4624 OR EventCode=4634 OR EventCode=4779 OR EventCode=4800 OR EventCode=4801 OR EventCode=4802 OR EventCode=4803 OR EventCode=4804 ) user!="anonymous logon" user!="DWM-*" user!="UMFD-*" user!=SYSTEM user!=*$ (Logon_Type=2 OR Logon_Type=7 OR Logon_Type=10)
| convert timeformat="%a %B %d %Y" ctime(_time) AS Date 
| streamstats earliest(_time) AS login, latest(_time) AS logout by Date, host
| eval session_duration=logout-login 
| eval h=floor(session_duration/3600) 
| eval m=floor((session_duration-(h*3600))/60) 
| eval SessionDuration=h."h ".m."m " 
| convert timeformat=" %m/%d/%y - %I:%M %P" ctime(login) AS login 
| convert timeformat=" %m/%d/%y - %I:%M %P" ctime(logout) AS logout 
| stats count AS auth_event_count, earliest(login) as login, max(SessionDuration) AS sesion_duration, latest(logout) as logout, values(Logon_Type) AS logon_types by Date, host, user
------Windows Query 11----------------
The following Splunk query will return results for concurrent logon sessions (in a Windows Environment) on any given server (or multiple servers) with slight modification.
First you must define the time span in which you consider “concurrent” this is defined in the “bucket” section below and the example uses a 30 minute range (widen or narrow to fit your needs).
Secondly this query does NOT define a host, to define a specific host insert “host=yourhostname” at the beginning of the query.
sourcetype="WinEventLog:Security" EventCode=4624 (Logon_Type=10 OR Logon_Type=2) | bucket span=30m _time | eval Date=strftime(_time, "%Y/%m/%d %H:%M:%S") | rex "New\sLogon:\s*Security\sID:\s+\S*\s+Account\sName:\s+(?<ACCT>\S+)"  | stats count by ACCT, _time, host | where count>1 | sort - count
------Windows Query 12----------------
This Splunk search will show anytime the windows audit logs (event viewer logs) have been cleared or deleted.
Ensure the Splunk App for Windows is installed, you can grab it here: https://apps.splunk.com/app/742/
source=WinEventLog:security (EventCode=1102 OR EventCode=517) | eval Date=strftime(_time, "%Y/%m/%d") | stats count by Client_User_Name, host, index, Date | sort - Date | rename Client_User_Name as "Account Name"
------Windows Query 13----------------
This splunk query unmodified will return results on any account regardless of duration, however it uses an “eval case” argument to determine what is “critical” (such as accounts deleted within a day of being created) or what is simply note worthy (normal behavior).
Ensure the Splunk App for Windows is installed grab it here: https://apps.splunk.com/app/742/
Windows Server 2008 and Newer:
sourcetype=WinEventLog:Security (EventCode=4726 OR EventCode=4720) |eval Date=strftime(_time, "%Y/%m/%d") |rex "Subject:\s+\w+\s\S+\s+\S+\s+\w+\s\w+:\s+(?<SourceAccount>\S+)" | rex "Target\s\w+:\s+\w+\s\w+:\s+\S+\s+\w+\s\w+:\s+(?<DeletedAccount>\S+)" | rex "New\s\w+:\s+\w+\s\w+:\s+\S+\s+\w+\s\w+:\s+(?<NewAccount>\S+)" | eval SuspectAccount=coalesce(DeletedAccount,NewAccount) | transaction SuspectAccount startswith="EventCode=4720" endswith="EventCode=4726" |eval duration=round(((duration/60)/60)/24, 2) | eval Age=case(duration<=1, "Critical", duration>1 AND duration<=7, "Warning", duration>7, "Normal")| table Date, index, host, SourceAccount, SuspectAccount, duration, Age | rename duration as "Days Account was Active" | sort + "Days Account was Active"
Windows Server 2003 and Older:
sourcetype=WinEventLog:Security (EventCode=630 OR EventCode=624) |eval Date=strftime(_time, "%Y/%m/%d") | transaction Target_Account_Name startswith="EventCode=624" endswith="EventCode=630" |eval duration=round(((duration/60)/60)/24, 2) | eval Age=case(duration<=1, "Critical", duration>1 AND duration<=7, "Warning", duration>7, "Normal")| table Date, index, host, Caller_User_Name, Target_Account_Name, duration, Age | rename duration as "Days Account was Active" | sort - Date
------Windows Query 14----------------
This splunk query will return results for failed logon attempts to accounts that do not exist. This has been tested and confirmed on Windows Server 2008 and newer machines:
source="WinEventLog:security" sourcetype="WinEventLog:Security" EventCode=4625 Sub_Status=0xC0000064 |eval Date=strftime(_time, "%Y/%m/%d") |rex "Which\sLogon\sFailed:\s+Security\sID:\s+\S.*\s+\w+\s\w+\S\s.(?<uacct>\S.*)" | stats count by Date, uacct, host | rename count as "Attempts" | sort - Attempts
------Windows Query 15----------------
Splunk query for all failed logon attempts within a windows environment.
sourcetype="WinEventLog:Security" ("EventCode=4625") OR ("EventCode=529" OR "EventCode=530" OR "EventCode=531" OR "EventCode=532" OR "EventCode=533" OR "EventCode=534" OR "EventCode=535" OR "EventCode=536" OR "EventCode=537" OR "EventCode=539") 
------Windows Query 16----------------
The following query will return the duration of user logon time between initial logon and logoff events. I have a duration filter set to greater than 5 seconds to weed out any scripts that may quickly log on and log off (change this as needed to fit your environment).
Windows 2008 and newer:
source=WinEventLog:Security (EventCode=4624 OR EventCode=4634) (Logon_Type=2 OR Logon_Type=10) | eval Date=strftime(_time, "%Y/%m/%d")| eval LogonType=case(Logon_Type="2", "Local Console Access", Logon_Type="10", "Remote Desktop via Terminal Services")| transaction host user startswith=EventCode=4624 endswith=EventCode=4634 | where duration > 5 | eval duration = duration/60 | eval duration=round(duration,2)| table host, user, LogonType duration, Date | rename duration as "Session Duration in Minutes" | sort - date
Windows 2003 and before:
source=WinEventLog:Security (EventCode=528 OR EventCode=538) (Logon_Type=2 OR Logon_Type=10) | eval Date=strftime(_time, "%Y/%m/%d") | eval LogonType=case(Logon_Type="2", "Local Console Access", Logon_Type="10", "Remote Desktop via Terminal Services")| transaction host User startswith=EventCode=528 endswith=EventCode=538 | where duration > 5 | eval duration = duration/60 | eval duration=round(duration,2)| table host, User, LogonType, duration, Date | rename duration as "Session Duration in Minutes" | sort - date
------Windows Query 17----------------
This query searches many common EventCodes (EventID’s) within a Windows environment for suspicious behavior. The query can take some time to run due to it’s length. Excellent for high-level security insight.
source="wineventlog:security" user!="DWM-*" user!="UMFD-*" user!=SYSTEM user!="LOCAL SERVICE" user!="NETWORK SERVICE" user!="*$" user!="ANONYMOUS LOGON" user!="IUSR"
| eval Trigger=case(EventCode=516, "Audit Logs Modified",EventCode=517, "Audit Logs Modified",EventCode=612, "Audit Logs Modified",EventCode=623, "Audit Logs Modified",EventCode=806, "Audit Logs Modified",EventCode=807, "Audit Logs Modified",EventCode=1101, "Audit Logs Modified",EventCode=1102, "Audit Logs Modified",EventCode=4612, "Audit Logs Modified",EventCode=4621, "Audit Logs Modified",EventCode=4694, "Audit Logs Modified",EventCode=4695, "Audit Logs Modified",EventCode=4715, "Audit Logs Modified",EventCode=4719, "Audit Logs Modified",EventCode=4817, "Audit Logs Modified",EventCode=4885, "Audit Logs Modified",EventCode=4902, "Audit Logs Modified",EventCode=4906, "Audit Logs Modified",EventCode=4907, "Audit Logs Modified",EventCode=4912, "Audit Logs Modified", EventCode=642, "Account Modification",EventCode=646, "Account Modification",EventCode=685, "Account Modification",EventCode=4738, "Account Modification",EventCode=4742, "Account Modification",EventCode=4781, "Account Modification", EventCode=1102, "Audit Logs Cleared/Deleted",EventCode=517, "Audit Logs Cleared/Deleted", EventCode=628, "Passwords Changed",EventCode=627, "Passwords Changed",EventCode=4723, "Passwords Changed",EventCode=4724, "Passwords Changed", EventCode=528, "Successful Logons",EventCode=540, "Successful Logons",EventCode=4624, "Successful Logons", EventCode=4625, "Failed Logons",EventCode=529, "Failed Logons",EventCode=530, "Failed Logons",EventCode=531, "Failed Logons",EventCode=532, "Failed Logons",EventCode=533, "Failed Logons",EventCode=534, "Failed Logons",EventCode=535, "Failed Logons",EventCode=536, "Failed Logons",EventCode=537, "Failed Logons",EventCode=539, "Failed Logons", EventCode=576, "Escalation of Privileges",EventCode=4672, "Escalation of Privileges",EventCode=577, "Escalation of Privileges",EventCode=4673, "Escalation of Privileges",EventCode=578, "Escalation of Privileges",EventCode=4674, "Escalation of Privileges") 
| stats earliest(_time) as Initial_Occurrence latest(_time) as Latest_Occurrence values(user) as Users values(host) as Hosts count sparkline by Trigger
| sort - count
| convert ctime(Initial_Occurrence) ctime(Latest_Occurrence)
Rajesh Kumar
Follow me
Latest posts by Rajesh Kumar (see all)
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x