The streamstats count command creates a field called eventCount that displays the amount of events from the fieldname you specify: | streamstats count as eventCount by fieldname
Apache High Level Visitor Info
The following query gives a breakdown on traffic by clientip. I run this over all time so I can get detailed information on first visit versus latest visit as you can see below. sourcetype=access_combined (referer_domain!=”https://gosplunk.com” AND referer_domain!=”http://gosplunk.com”) | iplocation clientip | stats first(_time) as First_Visit latest(_time) as Last_Visit sum(eval(round(bytes/1024/1024,2))) as MB first(Country) as Country count […]
Direct and Referred Apache Web Traffic
The following query will show all traffic to an Apache web server that is direct, meaning no referring site. sourcetype=”access_combined” referer=”-” | stats count The following query will show all traffic that is NOT direct, meaning only referring sites. sourcetype=”access_combined” referer!=”-” | stats count The following query is the same as above, but with a […]
List all fields for an index
A few different queries / methods to list all fields for indexes. index=yourindex| fieldsummary | table field or index=yourindex | stats values(*) AS * | transpose | table column | rename column AS Fieldnames or index=yourindex | stats dc() as * | transpose or ;-) index=yourindex | table *
Concurrent Users on Apache Web
I’ve been working through this query and depending on the length of time you are looking back you can use one of the following two methods. Option 1 – Short time window (30 days or less) concurrent users for a span of 5 minutes. sourcetype=”access_combined” | timechart span=5m dc(clientip) as “Concurrent Users” Option 2 – […]
Find success login after 10 failures with streamstats
If you have the Authentication data model configured you can use the following search to quickly find successful logins after 10 failed attempts! | from datamodel:”Authentication”.”Authentication” | search action=failure or action=success | reverse | streamstats window=0 current=true reset_after=”(action=\”success\”)” count as failure_count by src | where action=”success” and failure_count > 10
Average Search Duration
Ever wonder how your search performance is across search heads? Try this query. Depending on your environment you’ll want to specify the host=* section to better represent your environment. Say if you have a naming convention that includes “shc” and a number representing searchheads in a cluster (distributed environment) you can use (host=shc1.fq.dn OR host=shc2.fq.dn […]
Search Traffic by Source IP
GoSplunk Admin Notes: If you have a data model enabled that matches the search below, this might work for you! | datamodel Network_Traffic All_Traffic search | search All_Traffic.src_ip=10.x.x.x | stats count by All_Traffic.src_ip, All_Traffic.dest,All_Traffic.action, dstcountry | dedup All_Traffic.dest
Search for duplicate events in Splunk
index=<indexname> | stats count values(host) values(source) values(sourcetype) values(index) by _raw | WHERE count>1
Search for all errors in splunkd
index=_internal sourcetype=”splunkd” log_level=”ERROR” | stats sparkline count dc(host) as hosts last(_raw) as last_raw_msg values(sourcetype) as sourcetype last(_time) as last_msg_time first(_time) as first_msg_time values(index) as index by punct | eval delta=round((first_msg_time-last_msg_time),2) | eval msg_per_sec=round((count/delta),2) | convert ctime(last_msg_time) ctime(first_msg_time) | table last_raw_msg count hosts sparkline msg_per_sec sourcetype index first_msg_time last_msg_time delta | sort -count
Detect Username Guessing Brute Force Attacks
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 […]
Compare Successful Internal Vs External Connections
This query will display a bar chart of all successful Internal vs External SSH connections. Useful for identifying any spikes in connectivity coming from within your network remit or outside of it. Simply change the CIDR matches to match your required LANs. “sshd” AND “Accepted password” | rex “[a-zA-z]{3}\s\d+\s\d+:\d+:\d+\s[a-zA-Z0-9-.]*\s[a-zA-z]{3}\s\d+\s\d+:\d+:\d+\s(?<hostname>.*)\ssshd\[\d+\]:\sAccepted\spassword\sfor\s(?<username>.*)\sfrom\s(?<sourceip>.*)\sport\s(?<sourceport>.*)\sssh2” | eval network=case(cidrmatch(“192.168.0.0/24″, sourceip),”Internal”, cidrmatch(“10.10.0.0/16″,sourceip),”Internal”, […]
Easter egg that created sample data
| windbag This command creates a set of sample data of 100 events
Top Offending SSH Failure by Source IP
This displays a list of failed attempts against each connecting IP. Can be used to detect brute force from a particular source IP. You can then put a block up via ACL or whatever method you chose to mitigate the issue. The NOT clause on the first line ignore all attempts to logon to “invalid […]
List of index available to your role
|tstats count WHERE index=* OR index=_ BY index Don’t forget time modifier is required
List skipped searches by name, reason
index=_internal sourcetype=scheduler savedsearch_name=* status=skipped | stats count by savedsearch_name reason Look at the reason to know how to TB.
Detect Scheduler Running Twice a Search
There is a bug that make a search being executed 2 times or more. index=_internal sourcetype=scheduler scheduled_time=* savedsearch_name=* |stats count by scheduled_time, savedsearch_name | where count > 1
Parsing Military Time Zones
Sorry but a query would not be elegant. TIME_FORMAT=%Y-%m-%dT%H:%M:%S.%3N%Z TZ_ALIAS = A=GMT+1:00, B=GMT+2:00, C=GMT+3:00, D=GMT+4:00, E=GMT+5:00, F=GMT+6:00, G=GMT+7:00, H=GMT+8:00, I=GMT+9:00, K=GMT+10:00, L=GMT+11:00, M=GMT+12:00
date_zone=local is bad
Impact: since there is no timezone, the logs will have the same timezone as the local user. Therefore in another timezone, the logs won’t have the same order. If no TZ is specified, perhaps we could hard code one. |tstats count where index=* date_zone=local by index, sourcetype
Basic binary conversion for IPv4 Mask
Given an IP network address and it’s netmask represented under integer format, the bellow search will create a CIDR representation from the lookup without using built-in tools. |inputlookup geoip | head 100 | eval mask_int=end_ip_int-start_ip_int | eval mask_bin=replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(substr(tostring(mask_int, “hex”), 3), “0”, “0000”), “1”, “0001”), “2”, “0010”), “3”, “0011”), “4”, “0100”), “5”, “0101”), “6”, “0110”), […]
