This Splunk Query shows if there is a time offset on your Splunk servers. I borrowed and modified this one from the splunk clock skew search posted on www.bbosearch.com (another pretty awesome site like this one!). My version strips the unnecessary and renames some fields, but feel free to do what you want with it: […]
Detect ShellShock Attempts in Apache Logs
sourcetype=”apache:access” http_method=POST request=”*{ :;};*” OR request=”*/bin/*” A very simple query to detect attempts at running Linux commands on an Apache web server. The POST http request is where the command is injected. By searching the “request” field for tell tale signs of the attempt you can understand when and where the attack is occurring […]
Top 25 Most Prevailing Vulnerabilities with Patches Available (Multiple OSs)- Qualys
The following Splunk Search Queries within the Qualys Sourcetype list the top 25 most prevailing vulnerabilities that have patches available. The queries are separated by Operating System or Device Type: Linux eventtype=qualys_vm_detection_event SEVERITY > 3 STATUS=”ACTIVE” | regex OS=”^((?!\/).)*Linux((?!\/).)*$” | dedup HOST_ID QID | lookup qualys_kb_lookup QID OUTPUT TITLE SEVERITY VENDOR_REFERENCE | stats count by […]
License Usage by Pool per hour for last 24 hours
Have a misbehaving License Pool that the admin swears he fixed this morning? index=_internal source=*license_usage.log* type=Usage earliest=-24h| bucket _time span=1h | stats sum(b) as bytes by _time, pool | eval GB = round(bytes/1024/1024/1024,5) | fields _time pool GB
Use REST to gather Index Info
Here is some SPL to get useful information via REST on indexes within your Splunk environment: | REST /services/data/indexes | eval currentDBSizeMB=tostring(currentDBSizeMB, “commas”) | eval totalEventCount=tostring(totalEventCount, “commas”) | eval frozenTimePeriodInHours=(frozenTimePeriodInSecs/60/60) | table title splunk_server currentDBSizeMB frozenTimePeriodInHours maxTime minTime totalEventCount
User Info Dashboard – Using REST
I found this very useful user statistics/information splunk dashboard on www.function1.com/2016/06/rest-easy-with-the-splunk-rest-api. They have additional Splunk REST queries and examples worth checking out! <dashboard> <label>REST API: access control</label> <row> <panel> <single> <title>You are</title> <searchString>| rest /services/authentication/current-context | where NOT username=”splunk-system-user” | fields username</searchString> <earliestTime>0</earliestTime> <latestTime/> <option name=”drilldown”>none</option> […]
Overview of all medium to critical risks for Win20xx
Get an overview of all medium to critical risks for Windows 2008 / 2012: Windows 2008: sourcetype=”tenable:sc:vuln” cpe:/o:microsoft:windows_server_2008 | table ip, netbiosName, dnsName, severity.name, pluginName, solution, description | join ip type=inner max=0 [ search index=main sourcetype=”tenable:sc:vuln” baseScore>3 | table ip, netbiosName, dnsName, severity.name, pluginName, solution, description ] | sort by severity.name | chart count by […]
Sysmon – Find Processes with Renamed Executables
index=* sourcetype=”xmlwineventlog:microsoft-windows-sysmon/operational” EventCode=1 | rex field=Image “[\\\/](?<filename>[^\\\/]*)$” | eval filename=lower(filename)| stats dc(filename) as NumFilenames values(filename) as Filenames values(Image) as Images by Hashes | where NumFilenames>1
Sysmon – Outbound Connections by Process
sourcetype=”XmlWinEventLog:Microsoft-Windows-Sysmon/Operational” EventCode=3 Protocol=tcp Initiated=true | eval src=if(isnotnull(SourceHostname), SourceHostname+”:”+SourcePort, SourceIp+”:”+SourcePort) | eval dest=if(isnotnull(DestinationHostname), DestinationHostname+”:”+DestinationPort, DestinationIp+”:”+DestinationPort) | eval src_dest=src+ ” => ” + dest | stats values(src_dest) as Connection by ProcessGuid ProcessId User Computer Image
Sysmon – cmd line for non -local connections
sourcetype=”xmlwineventlog:microsoft-windows-sysmon/operational” EventCode=3 Protocol=tcp Initiated=true | where DestinationIp!=”127.0.0.1″ AND DestinationHostname!=SourceHostname| table _time User Computer ProcessId ProcessGuid DestinationHostname DestinationPort | join type=inner [ search sourcetype=”xmlwineventlog:microsoft-windows-sysmon/operational” EventCode=1 | table _time ProcessGuid ProcessId CommandLine]
Extract DNS Queries from netscaler syslog
To extract the DNS logging from netscaler:syslog you need the following regex: ^\s+(?P<date>[^:]+):(?P<time>[^ ]+)(?:[^:\n]*:){3}(?P<source_ip>[^#]+)(?:[^/\n]*/){8}\d+#(?P<dns>(?#)[_a-zA-Z0-9.-]+)(\.\/) You can now put it in the query and get an overview of all DNS queries. index=network_netscaler sourcetype=”citrix:netscaler:syslog” DNS | regex “^\s+(?P<date>[^:]+):(?P<time>[^ ]+)(?:[^:\n]*:){3}(?P<source_ip>[^#]+)(?:[^/\n]*/){8}\d+#(?P<dns>(?#)[_a-zA-Z0-9.-]+)(\.\/)” | table time, date, source_ip, dns
Find queues that are nearly full
Easily find Splunk Instances (both forwarders and full installs) that have queues that are over 80% full! index=_internal group=queue | eval percfull=((current_size_kb/max_size_kb)*100) | search percfull>80 | dedup host, name | table _time host name current_size_kb max_size_kb
Fishies! Fun Query and Easter Egg
Here is a fun query that you may have seen as an Easter egg in an app. I stumbled on this while cleaning up old saved searches. If you know the app comment below! FYI make sure you run this in real time otherwise you won’t see the fun part :) index=_* OR index=* […]
Top 25 Most Vulnerable Systems by OS – Qualys
The following Splunk Search Queries within the Qualys Sourcetype list the top 25 most vulnerable systems. The queries are separated by Operating System or Device Type: Linux eventtype=qualys_vm_detection_event SEVERITY > 3 | regex OS=”^((?!\/).)*Linux((?!\/).)*$” |dedup QID IP| stats count by IP | sort -count | head 25 Network (F5/Cisco/Firewall) eventtype=qualys_vm_detection_event SEVERITY > 3 | regex […]
Remediation Tracking Trend – Qualys
The following Splunk query will help determine remediation tracking trends within the Qualys Sourcetype: eventtype=”qualys_vm_detection_event” | stats count as eachCount |eval STATUS=”Total” | table STATUS eachCount| append [|search eventtype=”qualys_vm_detection_event”| stats count as eachCount by STATUS| eventstats sum(eachCount) as total | eval fixedPerc = ((eachCount/total)*100) | search STATUS=FIXED |table STATUS eachCount ] I take no credit […]
Pearson Coefficient of Two Fields
The following SPL query calculates the Pearson coefficient of two fields named x and y. index=* | fields x y | eval n=1 | eval xx=x*x | eval xy=x*y | eval yy=y*y | addcoltotals | tail 1 | eval rho_xy=(xy/n-x/n*y/n)/(sqrt(xx/n-(x/n)*(x/n))*sqrt(yy/n-(y/n)*(y/n))) | fields rho_xy
List Inputs using REST
As the title says. Pretty nice Splunk Search if you’ve forgotten what inputs you have configured and need a central place to list them. | rest /services/data/inputs/all | convert ctime(starttime) AS “Start Time” | convert ctime(endtime) AS “End Time” | table index interval source sourcetype title updated starttime endtime “Start Time” “End Time”
All indexes not explicitly granted to a role
| rest /servicesNS/-/-/data/indexes count=0 | stats max(isInternal) as internal, max(disabled) as disabled, max(isReadOnly) as readonly by title | fillnull | where internal=0 AND disabled=0 AND readonly=0 | fields title | rename title as index | join index type=left [ rest /servicesNS/-/-/authorization/roles count=0 splunk_server=local | fields title,srchIndexesAllowed | rename srchIndexesAllowed as index title as role | […]
Average Splunk Web requests by hour
This query is pretty awesome! It helped enlighten us to exactly when our splunk infrastructure is being hit with users index=_internal sourcetype=splunk_web_access [ rest / splunk_server=local | fields splunk_server | rename splunk_server as host ] | bin _time span=1d | stats count by date_hour _time | appendpipe [ fields _time | dedup _time | eval […]
Every index explicitly granted to a role
Self explanatory, maps roles to indexes. Useful if you have a lot of indexes! | rest /servicesNS/-/-/authorization/roles count=0 splunk_server=local | fields title,srchIndexesAllowed | rename srchIndexesAllowed as index title as role | mvexpand index | where NOT match(index,”.*\*.*”) I found this at: https://gist.github.com/acharlieh/3254a7ab13297c760376 Credit goes to acharlieh!
