This query will identify logon types within a windows environment and list the number of logons associated with each type. source=”WinEventLog:security” | eval LogonType=case(Logon_Type=”2″, “Local Console Access”, Logon_Type=”3″, “Accessing Network Folders or Files”, Logon_Type=”4″, “Scheduled Task, Batch File, or Script”, Logon_Type=”5″, “Service Account”, Logon_Type=”7″, “Local Console Unlock”, Logon_Type=”8″, “Network User Logon”, Logon_Type=”9″, “Program launched with […]
List of Sourcetypes Sent by Forwarder
The following Splunk search will show a list of sourcetypes (or perhaps sources I need to confirm) being sent by each Forwarder: index=”_internal” |where host!=splunk_server | stats values(series) as Sourcetypes by host
Failed Attempt to Login to a Disabled Account
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 | […]
Convert non timestamp time to Epoch
Scenario: You have a non timestamp field that you need to convert to epoch time to perform statistics on within splunk. Here’s how you do it: your search goes here |eval Epoch_Time=strptime(Field_Date, “%Y-%m-%d %H:%M:%S”)
Count of Attackers on Juniper Devices
The following is a Splunk search query that indicates potential “attacks” by source IP. Further investigation will be needed to determine accuracy of attacks. sourcetype = “juniper:idp” attack* | stats count by src_ip Credit given to bbosearch.
Top Visited Pages in IIS Web Logs
There are a number of ways to track user behavior within web logs. One such method is to use the JSESSIONID which in this query is used. The variable you can/will change in this query is the reference to JSESSIONID as to better align with your web logs and web site(s) in general. This working […]
Timestamp vs Indextime of Events (Diagnostic Query)
This query has in the past help me track down issues between forwarders and indexers, and even on occasion finding some time sync issues. Feel free to tweak, modify, and improve upon this query as I’m not 100% certain the math will work in your favor outside of highlighting (positive or negative) time differences! index=* […]
Hard Disk Usage and Information on Splunk Server
The following Splunk Query will utilize a “| REST” call to gather information related to disk usage on your Splunk server(s). The following has been modified from the “Distributed Management Console” to be more generic for a copy, paste, and search example. | rest splunk_server=* /services/server/status/partitions-space | eval free = if(isnotnull(available), available, free) | […]
Memory Usage and Information on Splunk Server
This Splunk Search Query will perform a rest call to indicate current memory consumption on the Splunk server(s) itself/themselves: *NOTE* The following has been modified from the “Distributed Management Console” to be more generic for a copy, paste, and search example. | rest splunk_server=* /services/server/status/resource-usage/hostwide | stats first(normalized_load_avg_1min) as load_average first(cpu_system_pct) as system, first(cpu_user_pct) as […]
Count of Splunk Errors Per Host
The following Splunk query will list the number of errors associated with each host over a given time range: index=_internal sourcetype=”splunkd” log_level=”ERROR” host!=splunk_server | stats count by host | sort – count
Detailed list of Errors Per Host
The following Splunk search will return a detailed list (by message) of errors associated with hosts running a universal forwarder: index=_internal sourcetype=”splunkd” log_level=”ERROR” | stats sparkline count dc(host) as uniqhosts last(message) as message last(_time) as last first(_time) as first by punct | convert ctime(last) ctime(first) | table message count uniqhosts sparkline first last | sort […]
Count of Host added to Splunk by Month
Can we get a Splunk Query that list of hosts added to Splunk in a month. Like Month 1 : 200 devices added Month 2: 400 devices added You would do this: host=* | stats dc(host) as host by date_month (Edits Made and query provided by the GoSplunk Ninja)
Qualys Hosts not Scanned in 30 days+
The following Splunk Search (query) is for Qualys and will show hosts that have not been scanned in 30 days or more. This query assumes that your index is defined as qualys. index=qualys HOSTVULN earliest=-30d@d STATUS=”RE-OPENED” | dedup HOST_ID, QID sortby +_time | join HOST_ID [ search index=qualys HOSTSUMMARY OS=”Windows*” NOT “Windows Server*” | where […]
RFQ (Request For Query) – Port Scan
I am looking for the query[timechartl] for example 50 port scan attempts with in a second against list of organization public facing IP address.
Malware Detection
I’m reposting this query I stumbled upon in a blog here. The description states that it can be used to detect malware reporting out to the web. Check out the article it’s a decent read. search.goes.here | convert mktime(_time) as epoch | sort 0 uri_host,client_ip,epoch | delta epoch as epoch_delta | search epoch_delta>0 epoch_delta<30 | chart […]
Forwarder Diagnostics – Last time Data Was Received by Index and Sourcetype
The following Splunk query was modified from the Splunk Health Overview app on Splunkbase. This particular Splunk search returns a list of hosts with their indexes and sourcetypes and determines when each last sent data. This query can help diagnose which Splunk Universal Forwarders may be having issues sending data, or simply stopped sending a […]
Number of mails sent over time (Postfix)
sourcetype=postfix_syslog host=insertHostnameHere status=sent | timechart span=1d count Total number of sent emails per day. Using it for Linux servers that use the Postfix mta.
Nr. of unique visitors per hour timechart
host=insertHostnameHere sourcetype=access_combined | timechart span=1h dc(sourceIP) If you have Fieldextraction for the visitor IP’s, in this example named ‘sourceIP’, this simple query will list the number of unique visitors per hour
Worldmap with unique visitors last 24 hours
sourcetype=access_combined | iplocation sourceIP | stats dc(sourceIP) by Country | geom geo_countries featureIdField=”Country” earliest=-24h Note that ‘sourceIP’ is the name for the IP field. You may already have another fieldname for that extraction. Tested on Splunk Light.
Convert Seconds to Hours Minutes Seconds HHMMSS
Take any field in splunk that outputs a value in seconds and change it to report in HH:MM:SS format: your.search.here | eval HHMMSS=tostring(Field_In_Seconds, “duration”) | table HHMMSS
