Description: This query will generate a software matrix or viewing the versions and names of all software installed on windows hosts reporting to Splunk. It requires the Stanza [script://.\bin\win_installed_apps.bat] enabled in the Splunk_TA_Windows Add-on. We run this once a day and have a dashboard for viewing the data that’s hard set for the past 24 […]
Timestamps from the future.
Shows all hosts that are sending events with timestamps greater than 5 mins (300 seconds) from the current time. | metadata type=hosts | where lastTime>now()+300 | eval mins_in_future=(lastTime-now())/60 | eval years_in_future=mins_in_future/60/24/365 | fieldformat lastTime=strftime(lastTime,”%Y-%m-%d %H:%M:%S %Z”) | table lastTime, host, mins_in_future, years_in_future | sort – mins_in_future
List Notable events with closing history details
`notable` | stats latest(lastTime) as LastTimeSeen values(rule_name) as “Rule Name” values(comment) as “Historical Analysis” values(user) as User by _time event_id, urgency | eval LastTimeSeen=strftime(LastTimeSeen,”%+”)
identify knowledge objects, permissions and extractions
The following will: list all knowledge objects for your SH (or given search peer(s)) each objects name, type, app, permissions, sharing (e.g. global, app, private) and owner if props-extract: the props stanza, props type (e.g if its Inline or Transforms), props sourcetype and props value (e.g. the regex) if transforms-extract: the state (tf_disabled), format (tf_format), tf_fields […]
List permissions for Users, roles, allowed indexes and indexes searched by default
Ok that one is a big one so be prepared ;) The following will (on a SH / SH Cluster): list all users and their roles list inherited roles list all indexes allowed by the shown roles list all indexes allowed for inherited roles (one level!) inherited allowed indexes will show the originator (which inherited […]
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]
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 – 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
Overall CVSS score (tenable)
Tenable uses the CVSS scoring method for detected vulnerabilities. To have an overall CVSS, use the following query: sourcetype=”tenable:sc:vuln” ip=* |stats mean(baseScore) as base | eval base = round(base,2)
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
Linux Free Disk Space
The following Splunk query shows a percentage of free disk space over a period of time using timechart: index=os sourcetype=df PercentFreeSpace=* mount=”/” | timechart latest(PercentFreeSpace) by host
Linux Memory Usage
The following Splunk Search will show memory usage on a linux machine over a period of time using timechart: index=os sourcetype=top pctMEM=*| transaction host _time | streamstats window=1 global=f sum(pctMEM) as MEM | timechart latest(MEM) by host
Linux CPU Usage
The following query will output CPU usage per host over a period of time using timechart: index=os sourcetype=top pctCPU=* | transaction host _time | streamstats window=1 global=f sum(pctCPU) as CPU | timechart latest(CPU) by host
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”)
List of Indexes
This simple Splunk query will return results for indexes that the current user (typically you) have access to: *NOTE* depending on settings this may or may not return internal indexes. host=* | dedup index |table index
Rename _time field in a TimeChart
When running a timechart splunk search query you may wish to rename the field _time. In order to do this you must first save the search to a dashboard or report. Once saved edit the source and add the following in the panel: <option name=”charting.axisTitleX.text”>Date</option> This can be added right before the closing “</chart>” code.
Splunk License Usage Over the Last 30 Days
The following Splunk Search will show license usage over the past 30 days: index=_internal source=*license_usage.log type=”RolloverSummary” earliest=-30d@d | eval _time=_time – 43200 | bin _time span=1d | stats latest(b) AS b by slave, pool, _time | timechart span=1d sum(b) AS “volume” fixedrange=false | join type=outer _time [ search index=_internal source=*license_usage.log type=”RolloverSummary” earliest=-30d@d | eval _time=_time […]
Splunk License Gauge
This Splunk search query will show current license usage | rest splunk_server=local /services/licenser/pools | rename title AS Pool | search [rest splunk_server=local /services/licenser/groups | search is_active=1 | eval stack_id=stack_ids | fields stack_id] | join type=outer stack_id [rest splunk_server=local /services/licenser/stacks | eval stack_id=title | eval stack_quota=quota | fields stack_id stack_quota] | stats sum(used_bytes) as used max(stack_quota) […]
Clean or Delete Data in a given Source
The following Splunk query will wipe or clear a data source. Ensure you specify an index else it will wipe the given source for all indexes. source=path\to\sourcename | delete
List of Source Names and Frequency of Events
The following splunk query will output a list of all SourceNames in a windows environment and include a sparkline to indicate frequency: eventtype=”windows_events” sourcetype=”*EventLog:*” (host=”*” OR ComputerName=”*”) TaskCategory=”*” SourceName=”*” EventCode=”*” Type=”*” | stats sparkline as Activity, count by host | sort -count
