The following Splunk REST query shows all roles, number of capabilities, and landing app for each user. | rest /services/authentication/users | eval name=coalesce(realname, title) | stats values(roles) as Role first(defaultApp) as “Landing App” count(capabilities) as “Number of Capabilities” by name
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 […]
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 – […]
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=* […]
List All Hosts Associated with All Indexes
Using the Splunk Tstats command you can quickly list all hosts associated with all indexes: |tstats values(host) where index=* by index
Track Remediation Progress by OS – Qualys
The following Splunk Search Queries within the Qualys Sourcetype track the remediation progress for a variety of operating systems. The queries are separated by Operating System or Device Type: OS & Device Agnostic eventtype=”qualys_vm_detection_event” STATUS =”FIXED” earliest=-30d@d | dedup HOST_ID, QID | stats count by QID Linux eventtype=qualys_vm_detection_event SEVERITY > 3 | regex OS=”^((?!\/).)*Linux((?!\/).)*$” |dedup […]
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 […]
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 […]
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 […]
High Severity Vulnerabilities – Qualys
The following Splunk query will show the percentage of high severity vulnerabilities within the Qualys Sourcetype: eventtype=”qualys_vm_detection_event” |eval Success= if(SEVERITY >3,1,0)|stats count as total sum(Success) as success|eval Per_high=(success/total)*100 I take no credit for this. These queries were discovered on Tarun Kumar’s blog.
New Vulnerabilities Detected Since Last Scan – Qualys
As the title suggests this Splunk Search will dedup results so you can better see changes in Vulnerability detection scan to scan within the Qualys Sourcetype: eventtype=”qualys_vm_detection_event” | dedup QID |stats count by SEVERITY I take no credit for this. These queries were discovered on Tarun Kumar’s blog.
Hosts Taking a Long Time to Scan – Qualys
The following Splunk query will show the hosts taking an abnormally lengthy time to scan (helps find that needle in a haystack) within the Qualys Sourcetype: sourcetype=”qualys:hostDetection” eventtype=qualys_host_summary_event SCAN_DURATION> 1800 | sort -SCAN_DURATION | table IP, DNS, OS, SCAN_DURATION I take no credit for this. These queries were discovered on Tarun Kumar’s blog.
Number of Vulnerabilities Detected – Qualys
The following Splunk query will show the number of vulnerabilities detected all severities and all types within the Qualys Sourcetype: eventtype=”qualys_vm_detection_event” STATUS=”NEW” | dedup QID |stats count by SEVERITY I take no credit for this. These queries were discovered on Tarun Kumar’s blog.
Qualys – Number of Hosts Scanned
The following Splunk query will show the number of hosts scanned within the Qualys Sourcetype: eventtype=”qualys_vm_detection_event” |eval Success= if(SEVERITY >3,1,0)|stats count as total sum(Success) as success|eval Per_high=(success/total)*100 | I take no credit for this. These queries were discovered on Tarun Kumar’s blog.
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 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
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 […]
