This is a simple tstats query shows all hosts and sourcetypes that have reported data, and shows the time in seconds since anything was sent. Be sure to run the query over a lengthy period of time in order to include machines that haven’t sent data for sometime. Don’t worry about the search-time so much, […]
Get Sourcetype and Index Info via TSTATS
Use the following simple tstats query to return the latest time events came in for a given index as well as list all sourcetypes for each index:
|
1 |
|tstats values(sourcetype) as Sourcetype latest(_time) as Time groupby index | convert ctime(Time) |
Universal Forwarder Throughput Limit Hit Count
This search counts the amount of times the UF’s throughput limit is hit. I also threw in a sparkline:
|
1 |
index=_internal sourcetype=splunkd "current data throughput" | rex "Current data throughput \((?<kb>\S+)" | eval rate=case(kb < 500, "256", kb > 499 AND kb < 520, "512", kb > 520 AND kb < 770 ,"768", kb>771 AND kb<1210, "1024", 1=1, ">1024") | stats count as Count sparkline as Trend by host, rate | where Count > 4 | rename host as "Host" rate as "Throughput rate(kb)" count as "Hit Count"| sort -"Throughput rate(kb)",-Count |
List All Hosts Associated with All Indexes
Using the Splunk Tstats command you can quickly list all hosts associated with all indexes:
|
1 |
|tstats values(host) where index=* by index |
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:
|
1 |
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.
Splunk Objects With Permissions Granted to Non-existent Roles
Useful search to show a bit of detail on roles and user permissions.
|
1 2 3 4 5 6 7 8 9 10 11 |
| rest /servicesNS/-/-/admin/directory count=0 splunk_server=local | fields eai:acl.app, eai:acl.owner, eai:acl.perms.*, eai:acl.sharing, eai:location, title | eval perms=mvappend('eai:acl.perms.read','eai:acl.perms.write') | fields - eai:acl.perms.* | mvexpand perms | where perms!="*" AND NOT [ | rest /servicesNS/-/-/authorization/roles count=0 splunk_server=local | fields title | rename title as perms ] |
I found this at: https://gist.github.com/acharlieh/3254a7ab13297c760376 Credit goes to acharlieh!
Every index explicitly granted to a role
Self explanatory, maps roles to indexes. Useful if you have a lot of indexes!
|
1 2 3 4 5 |
| 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!
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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 date_hour=mvrange(0,24,1) | eval count=0 | mvexpand date_hour ] | stats sum(count) as count by date_hour _time | stats avg(count) as avg by date_hour | sort date_hour |
I found this at: https://gist.github.com/acharlieh/3254a7ab13297c760376 Credit goes to acharlieh!
All indexes not explicitly granted to a role
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
| 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 | mvexpand index | where NOT match(index,".*\*.*") ] | search NOT role=* | fields index |
I found this at: https://gist.github.com/acharlieh/3254a7ab13297c760376 Credit goes to acharlieh!
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 […]
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:
|
1 |
host=* | stats dc(host) as host by date_month |
(Edits Made and query provided by the GoSplunk Ninja)
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:
|
1 |
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 | rename message as "Error Output" count as Count uniqhosts as "Number of Hosts" first as "First Occurance" last as "Most Recent Occurance" |
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.
|
1 |
| 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 user first(mem) AS mem first(mem_used) AS mem_used by splunk_server | fields splunk_server mem mem_used | eval pctmemused=round((mem_used/mem)*100)."%" | table splunk_server pctmemused | rename splunk_server as "Splunk Server" pctmemused as "Percent of Memory Used" |
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!
|
1 |
index=* |eventstats max(_time) as MAXt max(_indextime) as MAXit by host|eval timediff=round(MAXt - MAXit)| convert ctime(MAXit) as idxtime ctime(MAXt) as Date |dedup timediff host | table idxtime Date timediff host | sort - timediff | rename Date as "Timestamp of Event" idxtime as "Index Time Timestamp" |
Last Time a Forwarder Checked In
The following Splunk Search Query will return results based on the last time a forwarder (universal forwarder, heavy forwarder, or otherwise) checked in. The query is a modified version of a query that was packaged with the Deployment Monitor app.
|
1 |
index="_internal" source="*metrics.lo*" group=tcpin_connections NOT eventType=* | eval sourceHost=if(isnull(hostname), sourceHost,hostname) | eval connectionType=case(fwdType=="uf","universal forwarder", fwdType=="lwf", "lightweight forwarder",fwdType=="full", "heavy forwarder", connectionType=="cooked" or connectionType=="cookedSSL","Splunk forwarder", connectionType=="raw" or connectionType=="rawSSL","legacy forwarder")| eval build=if(isnull(build),"n/a",build) | eval version=if(isnull(version),"pre 4.2",version) | eval os=if(isnull(os),"n/a",os)| eval arch=if(isnull(arch),"n/a",arch) | eval lastReceived = case(kb>0, _time) | eval seconds=30 | eval event_count= tcp_eps * seconds | bin _time span=10m | stats first(connectionType) as connectionType first(version) as version max(_time) as lastConnected max(lastReceived) as lastReceived sum(kb) as kbTotal sum(event_count) as events sum(seconds) as secs by sourceHost _time | stats first(connectionType) as connectionType first(version) as version max(lastConnected) as lastConnected max(lastReceived) as lastReceived by sourceHost | eval LastReceived=strftime(lastReceived, "%Y/%m/%d %H:%M:%S") | fields sourceHost, connectionType, LastReceived | sort + LastReceived |
