{"id":30447,"date":"2022-06-27T12:14:43","date_gmt":"2022-06-27T12:14:43","guid":{"rendered":"https:\/\/www.devopsschool.com\/blog\/?p=30447"},"modified":"2022-12-23T05:52:34","modified_gmt":"2022-12-23T05:52:34","slug":"splunk-tutorials-windows-query","status":"publish","type":"post","link":"https:\/\/www.devopsschool.com\/blog\/splunk-tutorials-windows-query\/","title":{"rendered":"Splunk Tutorials: Windows Query"},"content":{"rendered":"
source=wineventlog:application\r\nsource=wineventlog:security\r\nsource=wineventlog:system\r\n\r\n------Windows Query 1<\/span>----------------\r\nEvent Logs | System Logs | Warnings and<\/span> Errors\r\nThis will hit all of the host and<\/span> pull back the eventlogs and<\/span> group them by Message. You can change the source to what ever windows eventlogs you need\r\n\r\nhost=\"*\"<\/span> source=wineventlog:system NOT Type=Information | stats count by Message | sort -count | table count, Message\r\n\r\n------Windows Query 2<\/span>----------------\r\nThis Splunk Query will return<\/span> results for<\/span> any Windows Service that has started.\r\nCond - Ensure the Splunk App for<\/span> Windows is installed grab it here: https:\/\/apps.splunk.com\/app\/742\/<\/span>\r\n\r\nsourcetype=WinEventLog:Application EventCode=105<\/span> | eval<\/span> Date=strftime(_time, \"%Y\/%m\/%d\"<\/span>) | stats count by Date, SourceName, host | sort - Date | fields - count\r\n\r\n------Windows Query 3<\/span>----------------\r\nThis splunk query will return<\/span> results for<\/span> any Windows Service that has been stopped.\r\nEnsure the Splunk App for<\/span> Windows is installed grab it here: https:\/\/apps.splunk.com\/app\/742\/<\/span>\r\n\r\nsourcetype=WinEventLog:Application EventCode=108<\/span> | eval<\/span> Date=strftime(_time, \"%Y\/%m\/%d\"<\/span>) | stats count by Date, SourceName, host | sort - Date | fields - count\r\n\r\n------Windows Query 4<\/span>----------------\r\nThe following is a Splunk query that will display a timechart for<\/span> all successful logons to windows:\r\nsource=\"WinEventLog:security\"<\/span> EventCode=4624<\/span> Logon_Type IN (2<\/span>,7<\/span>,10<\/span>,11<\/span>) NOT user IN (\"DWM-*\"<\/span>, \"UMFD-*\"<\/span>)\r\n| timechart span=1<\/span>h count by host\r\n\r\n------Windows Query 5<\/span>----------------\r\nThe following Splunk query will show a timechart of failed logon attempts per host:\r\nsource=\"WinEventLog:security\"<\/span> EventCode=4625<\/span> \r\n| timechart span=1<\/span>h count by host\r\n\r\n------Windows Query 6<\/span>----------------\r\nThis query will show a timechart of the status of an Locked Out Account\r\n\r\nsourcetype=\"WinEventLog:Security\"<\/span> EventCode=4625<\/span> AND<\/span> Status=0xC0000234<\/span> | timechart count by user | sort -count\r\n\r\n------Windows Query 7<\/span>----------------\r\nThe 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<\/span> failed logins against 40<\/span> accounts.\r\n\r\nThis first checks for<\/span> all accounts having an account login failure of 4<\/span> or<\/span> more, it then checks for<\/span> the quantity of accounts that have failed by 4<\/span> or<\/span> more (5<\/span> in the below example). So if<\/span> someone attempts to login with 4<\/span> or<\/span> more different passwords unsuccessfully on 5<\/span> or<\/span> more accounts, the alarm will trip.\r\n\r\nsourcetype=windows EventCode=4625<\/span> OR<\/span> EventCode=4624<\/span> \r\n | bin _time span=5<\/span>m as<\/span> minute \r\n | rex \"Security ID:\\s*\\w*\\s*\\w*\\s*Account Name:\\s*(?<username>.*)\\s*Account Domain:\"<\/span> \r\n | stats count(Keywords) as<\/span> Attempts,\r\n count(eval<\/span>(match(Keywords,\"Audit Failure\"<\/span>))) as<\/span> Failed,\r\n count(eval<\/span>(match(Keywords,\"Audit Success\"<\/span>))) as<\/span> Success by minute username\r\n | where Failed>=4<\/span>\r\n | stats dc(username) as<\/span> Total by minute \r\n | where Total>5<\/span>\r\n\r\n------Windows Query 9<\/span>----------------\r\nThis eval<\/span> for<\/span> password can be easily used for<\/span> any field where a user can accidentally type in a password or<\/span> even worse both username\/password during login which generates a failed event.   Below example is for<\/span> Windows failed login.  The eval<\/span> will match 10<\/span> or<\/span> more characters with 1<\/span> uppercase, 1<\/span> lower case<\/span>, 1<\/span> digit and<\/span> 1<\/span> special character.  This search also assumes you have the \u201cUser_Name\u201d field being extracted from windows event logs.  Adjust as<\/span> needed.\r\n\r\nsource=WinEventLog:Security TaskCategory=Logon Keywords=\"Audit Failure\"<\/span> | eval<\/span> password=if<\/span>(match(User_Name, \"^(?=.*[a-z])(?=.*[A-Z])(?=.*[0-9])(?=.*[\\W])(?=.{10,})\"<\/span>), \"Yes\"<\/span>, \"No\"<\/span>) | stats count by password User_Name | search password=Yes\r\n\r\n------Windows Query 10<\/span>----------------\r\nThis Splunk Search Query will indicate any user who attempted to login to a disabled account.\r\n(Tested only on Windows 7<\/span> \/ Server 2008<\/span> and<\/span> newer Windows logs).\r\n\r\nsource=\"WinEventLog:security\"<\/span> EventCode=4625<\/span> (Sub_Status=\"0xc0000072\"<\/span> OR<\/span> Sub_Status=\"0xC0000072\"<\/span>) Security_ID!=\"NULL SID\"<\/span> Account_Name!=\"*$\"<\/span> | eval<\/span> Date=strftime(_time, \"%Y\/%m\/%d\"<\/span>)| rex \"Which\\sLogon\\sFailed:\\s+\\S+\\s\\S+\\s+\\S+\\s+Account\\sName:\\s+(?<facct>\\S+)\"<\/span> | eval<\/span> Date=strftime(_time, \"%Y\/%m\/%d\"<\/span>) | stats count by Date, facct, host, Keywords | rename facct as<\/span> \"Target Account\"<\/span> host as<\/span> \"Host\"<\/span> Keywords as<\/span> \"Status\"<\/span> count as<\/span> \"Count\"<\/span>\r\n\r\n\r\n------Windows Query 11<\/span>----------------\r\nUser Logon, Logoff, and<\/span> Duration\r\n\r\nsource=\"wineventlog:security\"<\/span> action=success Logon_Type=2<\/span> (EventCode=4624<\/span> OR<\/span> EventCode=4634<\/span> OR<\/span> EventCode=4779<\/span> OR<\/span> EventCode=4800<\/span> OR<\/span> EventCode=4801<\/span> OR<\/span> EventCode=4802<\/span> OR<\/span> EventCode=4803<\/span> OR<\/span> EventCode=4804<\/span> ) user!=\"anonymous logon\"<\/span> user!=\"DWM-*\"<\/span> user!=\"UMFD-*\"<\/span> user!=SYSTEM user!=*$ (Logon_Type=2<\/span> OR<\/span> Logon_Type=7<\/span> OR<\/span> Logon_Type=10<\/span>)\r\n| convert timeformat=\"%a %B %d %Y\"<\/span> ctime(_time) AS<\/span> Date \r\n| streamstats earliest(_time) AS<\/span> login, latest(_time) AS<\/span> logout by Date, host\r\n| eval<\/span> session_duration=logout-login \r\n| eval<\/span> h=floor(session_duration\/3600<\/span>) \r\n| eval<\/span> m=floor((session_duration-(h*3600<\/span>))\/60<\/span>) \r\n| eval<\/span> SessionDuration=h.\"h \"<\/span>.m.\"m \"<\/span> \r\n| convert timeformat=\" %m\/%d\/%y - %I:%M %P\"<\/span> ctime(login) AS<\/span> login \r\n| convert timeformat=\" %m\/%d\/%y - %I:%M %P\"<\/span> ctime(logout) AS<\/span> logout \r\n| stats count AS<\/span> auth_event_count, earliest(login) as<\/span> login, max(SessionDuration) AS<\/span> sesion_duration, latest(logout) as<\/span> logout, values(Logon_Type) AS<\/span> logon_types by Date, host, user\r\n\r\n------Windows Query 11<\/span>----------------\r\n\r\nThe following Splunk query will return<\/span> results for<\/span> concurrent logon sessions (in a Windows Environment) on any given server (or<\/span> multiple servers) with slight modification.\r\n\r\nFirst you must define the time span in which you consider \u201cconcurrent\u201d this is defined in the \u201cbucket\u201d section below and<\/span> the example uses a 30<\/span> minute range (widen or<\/span> narrow to fit your needs).\r\nSecondly this query does NOT define a host, to define a specific host insert \u201chost=yourhostname\u201d at the beginning of the query.\r\n\r\nsourcetype=\"WinEventLog:Security\"<\/span> EventCode=4624<\/span> (Logon_Type=10<\/span> OR<\/span> Logon_Type=2<\/span>) | bucket span=30<\/span>m _time | eval<\/span> Date=strftime(_time, \"%Y\/%m\/%d %H:%M:%S\"<\/span>) | rex \"New\\sLogon:\\s*Security\\sID:\\s+\\S*\\s+Account\\sName:\\s+(?<ACCT>\\S+)\"<\/span>  | stats count by ACCT, _time, host | where count>1<\/span> | sort - count\r\n\r\n------Windows Query 12<\/span>----------------\r\nThis Splunk search will show anytime the windows audit logs (event viewer logs) have been cleared or<\/span> deleted.\r\n\r\nEnsure the Splunk App for<\/span> Windows is installed, you can grab it here: https:\/\/apps.splunk.com\/app\/742\/<\/span>\r\n\r\nsource=WinEventLog:security (EventCode=1102<\/span> OR<\/span> EventCode=517<\/span>) | eval<\/span> Date=strftime(_time, \"%Y\/%m\/%d\"<\/span>) | stats count by Client_User_Name, host, index, Date | sort - Date | rename Client_User_Name as<\/span> \"Account Name\"<\/span>\r\n\r\n------Windows Query 13<\/span>----------------\r\nThis splunk query unmodified will return<\/span> results on any account regardless of duration, however it uses an \u201ceval<\/span> case<\/span>\u201d argument to determine what is \u201ccritical\u201d (such as<\/span> accounts deleted within a day of being created) or<\/span> what is simply note worthy (normal behavior).\r\n\r\nEnsure the Splunk App for<\/span> Windows is installed grab it here: https:\/\/apps.splunk.com\/app\/742\/<\/span>\r\n\r\nWindows Server 2008<\/span> and<\/span> Newer:\r\nsourcetype=WinEventLog:Security (EventCode=4726<\/span> OR<\/span> EventCode=4720<\/span>) |eval<\/span> Date=strftime(_time, \"%Y\/%m\/%d\"<\/span>) |rex \"Subject:\\s+\\w+\\s\\S+\\s+\\S+\\s+\\w+\\s\\w+:\\s+(?<SourceAccount>\\S+)\"<\/span> | rex \"Target\\s\\w+:\\s+\\w+\\s\\w+:\\s+\\S+\\s+\\w+\\s\\w+:\\s+(?<DeletedAccount>\\S+)\"<\/span> | rex \"New\\s\\w+:\\s+\\w+\\s\\w+:\\s+\\S+\\s+\\w+\\s\\w+:\\s+(?<NewAccount>\\S+)\"<\/span> | eval<\/span> SuspectAccount=coalesce(DeletedAccount,NewAccount) | transaction SuspectAccount startswith=\"EventCode=4720\"<\/span> endswith=\"EventCode=4726\"<\/span> |eval<\/span> duration=round(((duration\/60<\/span>)\/60<\/span>)\/24<\/span>, 2<\/span>) | eval<\/span> Age=case<\/span>(duration<=1<\/span>, \"Critical\"<\/span>, duration>1<\/span> AND<\/span> duration<=7<\/span>, \"Warning\"<\/span>, duration>7<\/span>, \"Normal\"<\/span>)| table Date, index, host, SourceAccount, SuspectAccount, duration, Age | rename duration as<\/span> \"Days Account was Active\"<\/span> | sort + \"Days Account was Active\"<\/span>\r\n\r\nWindows Server 2003<\/span> and<\/span> Older:\r\nsourcetype=WinEventLog:Security (EventCode=630<\/span> OR<\/span> EventCode=624<\/span>) |eval<\/span> Date=strftime(_time, \"%Y\/%m\/%d\"<\/span>) | transaction Target_Account_Name startswith=\"EventCode=624\"<\/span> endswith=\"EventCode=630\"<\/span> |eval<\/span> duration=round(((duration\/60<\/span>)\/60<\/span>)\/24<\/span>, 2<\/span>) | eval<\/span> Age=case<\/span>(duration<=1<\/span>, \"Critical\"<\/span>, duration>1<\/span> AND<\/span> duration<=7<\/span>, \"Warning\"<\/span>, duration>7<\/span>, \"Normal\"<\/span>)| table Date, index, host, Caller_User_Name, Target_Account_Name, duration, Age | rename duration as<\/span> \"Days Account was Active\"<\/span> | sort - Date\r\n\r\n------Windows Query 14<\/span>----------------\r\nThis splunk query will return<\/span> results for<\/span> failed logon attempts to accounts that do<\/span> not exist. This has been tested and<\/span> confirmed on Windows Server 2008<\/span> and<\/span> newer machines:\r\n\r\nsource=\"WinEventLog:security\"<\/span> sourcetype=\"WinEventLog:Security\"<\/span> EventCode=4625<\/span> Sub_Status=0xC0000064<\/span> |eval<\/span> Date=strftime(_time, \"%Y\/%m\/%d\"<\/span>) |rex \"Which\\sLogon\\sFailed:\\s+Security\\sID:\\s+\\S.*\\s+\\w+\\s\\w+\\S\\s.(?<uacct>\\S.*)\"<\/span> | stats count by Date, uacct, host | rename count as<\/span> \"Attempts\"<\/span> | sort - Attempts\r\n\r\n------Windows Query 15<\/span>----------------\r\nSplunk query for<\/span> all failed logon attempts within a windows environment.\r\n\r\nsourcetype=\"WinEventLog:Security\"<\/span> (\"EventCode=4625\"<\/span>) OR<\/span> (\"EventCode=529\"<\/span> OR<\/span> \"EventCode=530\"<\/span> OR<\/span> \"EventCode=531\"<\/span> OR<\/span> \"EventCode=532\"<\/span> OR<\/span> \"EventCode=533\"<\/span> OR<\/span> \"EventCode=534\"<\/span> OR<\/span> \"EventCode=535\"<\/span> OR<\/span> \"EventCode=536\"<\/span> OR<\/span> \"EventCode=537\"<\/span> OR<\/span> \"EventCode=539\"<\/span>) \r\n\r\n------Windows Query 16<\/span>----------------\r\nThe following query will return<\/span> the duration of user logon time between initial logon and<\/span> logoff events. I have a duration filter set to greater than 5<\/span> seconds to weed out any scripts that may quickly log on and<\/span> log off (change this as<\/span> needed to fit your environment).\r\n\r\nWindows 2008<\/span> and<\/span> newer:\r\nsource=WinEventLog:Security (EventCode=4624<\/span> OR<\/span> EventCode=4634<\/span>) (Logon_Type=2<\/span> OR<\/span> Logon_Type=10<\/span>) | eval<\/span> Date=strftime(_time, \"%Y\/%m\/%d\"<\/span>)| eval<\/span> LogonType=case<\/span>(Logon_Type=\"2\"<\/span>, \"Local Console Access\"<\/span>, Logon_Type=\"10\"<\/span>, \"Remote Desktop via Terminal Services\"<\/span>)| transaction host user startswith=EventCode=4624<\/span> endswith=EventCode=4634<\/span> | where duration > 5<\/span> | eval<\/span> duration = duration\/60<\/span> | eval<\/span> duration=round(duration,2<\/span>)| table host, user, LogonType duration, Date | rename duration as<\/span> \"Session Duration in Minutes\"<\/span> | sort - date\r\n\r\nWindows 2003<\/span> and<\/span> before:\r\nsource=WinEventLog:Security (EventCode=528<\/span> OR<\/span> EventCode=538<\/span>) (Logon_Type=2<\/span> OR<\/span> Logon_Type=10<\/span>) | eval<\/span> Date=strftime(_time, \"%Y\/%m\/%d\"<\/span>) | eval<\/span> LogonType=case<\/span>(Logon_Type=\"2\"<\/span>, \"Local Console Access\"<\/span>, Logon_Type=\"10\"<\/span>, \"Remote Desktop via Terminal Services\"<\/span>)| transaction host User startswith=EventCode=528<\/span> endswith=EventCode=538<\/span> | where duration > 5<\/span> | eval<\/span> duration = duration\/60<\/span> | eval<\/span> duration=round(duration,2<\/span>)| table host, User, LogonType, duration, Date | rename duration as<\/span> \"Session Duration in Minutes\"<\/span> | sort - date\r\n\r\n------Windows Query 17<\/span>----------------\r\nThis query searches many common EventCodes (EventID\u2019s) within a Windows environment for<\/span> suspicious behavior. The query can take some time to run due to it\u2019s length. Excellent for<\/span> high-level security insight.\r\n\r\n\r\nsource=\"wineventlog:security\"<\/span> user!=\"DWM-*\"<\/span> user!=\"UMFD-*\"<\/span> user!=SYSTEM user!=\"LOCAL SERVICE\"<\/span> user!=\"NETWORK SERVICE\"<\/span> user!=\"*$\"<\/span> user!=\"ANONYMOUS LOGON\"<\/span> user!=\"IUSR\"<\/span>\r\n| eval<\/span> Trigger=case<\/span>(EventCode=516<\/span>, \"Audit Logs Modified\"<\/span>,EventCode=517<\/span>, \"Audit Logs Modified\"<\/span>,EventCode=612<\/span>, \"Audit Logs Modified\"<\/span>,EventCode=623<\/span>, \"Audit Logs Modified\"<\/span>,EventCode=806<\/span>, \"Audit Logs Modified\"<\/span>,EventCode=807<\/span>, \"Audit Logs Modified\"<\/span>,EventCode=1101<\/span>, \"Audit Logs Modified\"<\/span>,EventCode=1102<\/span>, \"Audit Logs Modified\"<\/span>,EventCode=4612<\/span>, \"Audit Logs Modified\"<\/span>,EventCode=4621<\/span>, \"Audit Logs Modified\"<\/span>,EventCode=4694<\/span>, \"Audit Logs Modified\"<\/span>,EventCode=4695<\/span>, \"Audit Logs Modified\"<\/span>,EventCode=4715<\/span>, \"Audit Logs Modified\"<\/span>,EventCode=4719<\/span>, \"Audit Logs Modified\"<\/span>,EventCode=4817<\/span>, \"Audit Logs Modified\"<\/span>,EventCode=4885<\/span>, \"Audit Logs Modified\"<\/span>,EventCode=4902<\/span>, \"Audit Logs Modified\"<\/span>,EventCode=4906<\/span>, \"Audit Logs Modified\"<\/span>,EventCode=4907<\/span>, \"Audit Logs Modified\"<\/span>,EventCode=4912<\/span>, \"Audit Logs Modified\"<\/span>, EventCode=642<\/span>, \"Account Modification\"<\/span>,EventCode=646<\/span>, \"Account Modification\"<\/span>,EventCode=685<\/span>, \"Account Modification\"<\/span>,EventCode=4738<\/span>, \"Account Modification\"<\/span>,EventCode=4742<\/span>, \"Account Modification\"<\/span>,EventCode=4781<\/span>, \"Account Modification\"<\/span>, EventCode=1102<\/span>, \"Audit Logs Cleared\/Deleted\"<\/span>,EventCode=517<\/span>, \"Audit Logs Cleared\/Deleted\"<\/span>, EventCode=628<\/span>, \"Passwords Changed\"<\/span>,EventCode=627<\/span>, \"Passwords Changed\"<\/span>,EventCode=4723<\/span>, \"Passwords Changed\"<\/span>,EventCode=4724<\/span>, \"Passwords Changed\"<\/span>, EventCode=528<\/span>, \"Successful Logons\"<\/span>,EventCode=540<\/span>, \"Successful Logons\"<\/span>,EventCode=4624<\/span>, \"Successful Logons\"<\/span>, EventCode=4625<\/span>, \"Failed Logons\"<\/span>,EventCode=529<\/span>, \"Failed Logons\"<\/span>,EventCode=530<\/span>, \"Failed Logons\"<\/span>,EventCode=531<\/span>, \"Failed Logons\"<\/span>,EventCode=532<\/span>, \"Failed Logons\"<\/span>,EventCode=533<\/span>, \"Failed Logons\"<\/span>,EventCode=534<\/span>, \"Failed Logons\"<\/span>,EventCode=535<\/span>, \"Failed Logons\"<\/span>,EventCode=536<\/span>, \"Failed Logons\"<\/span>,EventCode=537<\/span>, \"Failed Logons\"<\/span>,EventCode=539<\/span>, \"Failed Logons\"<\/span>, EventCode=576<\/span>, \"Escalation of Privileges\"<\/span>,EventCode=4672<\/span>, \"Escalation of Privileges\"<\/span>,EventCode=577<\/span>, \"Escalation of Privileges\"<\/span>,EventCode=4673<\/span>, \"Escalation of Privileges\"<\/span>,EventCode=578<\/span>, \"Escalation of Privileges\"<\/span>,EventCode=4674<\/span>, \"Escalation of Privileges\"<\/span>) \r\n| stats earliest(_time) as<\/span> Initial_Occurrence latest(_time) as<\/span> Latest_Occurrence values(user) as<\/span> Users values(host) as<\/span> Hosts count sparkline by Trigger\r\n| sort - count\r\n| convert ctime(Initial_Occurrence) ctime(Latest_Occurrence)\r\n\r\n<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>","protected":false},"excerpt":{"rendered":"","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_kad_post_transparent":"","_kad_post_title":"","_kad_post_layout":"","_kad_post_sidebar_id":"","_kad_post_content_style":"","_kad_post_vertical_padding":"","_kad_post_feature":"","_kad_post_feature_position":"","_kad_post_header":false,"_kad_post_footer":false,"_kad_post_classname":"","_joinchat":[],"footnotes":""},"categories":[2],"tags":[],"class_list":["post-30447","post","type-post","status-publish","format-standard","hentry","category-uncategorised"],"_links":{"self":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/30447","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/comments?post=30447"}],"version-history":[{"count":1,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/30447\/revisions"}],"predecessor-version":[{"id":30448,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/30447\/revisions\/30448"}],"wp:attachment":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/media?parent=30447"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/categories?post=30447"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/tags?post=30447"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}