Azeemering, Author at

Find unused dashboards

_audit/ _internal/ Fun Stuff & Helpful Hints
Azeemering
6 1

Use this search to find unused dashboards:

| rest /servicesNS/-/-/data/ui/views splunk_server=* 
| search isDashboard=1 
| rename eai:acl.app as app 
| fields title app 
| join type=left title 
[| search index=_internal sourcetype=splunk_web_access host=* user=* 
| rex field=uri_path ".*/(?<title>[^/]*)$" 
| stats latest(_time) as Time latest(user) as user by title
] 
| where isnotnull(Time) 
| eval Now=now() 
| eval "Days since last accessed"=round((Now-Time)/86400,2) 
| sort - "Days since last accessed" 
| convert ctime(Time) 
| fields - Now

| rest /servicesNS/-/-/data/ui/views splunk_server=*

| search isDashboard=1

| rename eai:acl.app as app

| fields title app

| join type=left title

[| search index=_internal sourcetype=splunk_web_access host=* user=*

| rex field=uri_path ".*/(?<title>[^/]*)$"

| stats latest(_time) as Time latest(user) as user by title

]

| where isnotnull(Time)

| eval Now=now()

| eval "Days since last accessed"=round((Now-Time)/86400,2)

| sort - "Days since last accessed"

| convert ctime(Time)

| fields - Now

Admin Notes – Fantastic query! I modified the SPL slightly as I had an issue when I copied it to my two test environments.

Continue Reading →

Check your strftime is correct in the props.conf

A simple method on checking if your strftime (TIME_FORMAT=) in the props.conf matches your log file timestamp format. strftime(X,Y) This function takes a UNIX time value, X, as the first argument and renders the time as a string using the format specified by Y. The UNIX time must be in seconds. Use the first 10 […]

Continue Reading →

Saved Search Scheduler Activity

I use this query a lot to tune and adjust scheduling, find out what searches need attention:

index=_internal sourcetype=scheduler result_count | extract pairdelim=",", kvdelim="=", auto=f | stats avg(result_count) min(result_count) max(result_count), sparkline avg(run_time) min(run_time) max(run_time) sum(run_time) values(host) AS hosts count AS execution_count by savedsearch_name, app | join savedsearch_name type=outer [| rest /servicesNS/-/-/saved/searches | fields title eai:acl.owner cron_schedule dispatch.earliest_time dispatch.latest_time search | rename title AS savedsearch_name eai:acl.app AS App eai:acl.owner AS Owner cron_schedule AS "Cron Schedule" dispatch.earliest_time AS "Dispatch Earliest Time" dispatch.latest_time AS "Dispatch Latest Time"]| rename savedsearch_name AS "Saved Search Name" search AS "SPL Query" app AS App | makemv delim="," values(host) | sort - avg(run_time) | table "Saved Search Name", App, Owner, "SPL Query", "Dispatch Earliest Time" "Dispatch Latest Time" "Cron Schedule" hosts, execution_count, sparkline, *(result_count), sum(run_time) *(run_time)

index=_internal sourcetype=scheduler result_count | extract pairdelim=",", kvdelim="=", auto=f | stats avg(result_count) min(result_count) max(result_count), sparkline avg(run_time) min(run_time) max(run_time) sum(run_time) values(host) AS hosts count AS execution_count by savedsearch_name, app | join savedsearch_name type=outer [| rest /servicesNS/-/-/saved/searches | fields title eai:acl.owner cron_schedule dispatch.earliest_time dispatch.latest_time search | rename title AS savedsearch_name eai:acl.app AS App eai:acl.owner AS Owner cron_schedule AS "Cron Schedule" dispatch.earliest_time AS "Dispatch Earliest Time" dispatch.latest_time AS "Dispatch Latest Time"]| rename savedsearch_name AS "Saved Search Name" search AS "SPL Query" app AS App | makemv delim="," values(host) | sort - avg(run_time) | table "Saved Search Name", App, Owner, "SPL Query", "Dispatch Earliest Time" "Dispatch Latest Time" "Cron Schedule" hosts, execution_count, sparkline, *(result_count), sum(run_time) *(run_time)

Continue Reading →

Show indexing queue sizes

Use a linechart with this search to show you the indexing queue sizes:

index=_internal source=*metrics.log group=queue (name=parsingqueue OR name=indexqueue OR name=typingqueue OR name=aggqueue) | timechart avg(current_size) by name

1	index=_internal source=*metrics.log group=queue (name=parsingqueue OR name=indexqueue OR name=typingqueue OR name=aggqueue) \| timechart avg(current_size) by name

Continue Reading →

Percentage of skipped searches

This query will give you a table with a percentage of skipped searches and an evaluation with 3 ranges

index=_internal sourcetype=scheduler | stats count as total, count(eval(status="skipped")) as skipped | eval pct=round(skipped/total * 100, 0) | rangemap field=pct low=0-10, elevated=10-20 severe=20-100 | eval pct = pct . "%" | fields pct, range

1	index=_internal sourcetype=scheduler \| stats count as total, count(eval(status="skipped")) as skipped \| eval pct=round(skipped/total * 100, 0) \| rangemap field=pct low=0-10, elevated=10-20 severe=20-100 \| eval pct = pct . "%" \| fields pct, range

Continue Reading →

Retention Period in days per index

This query will give you a table of all indexes and their respective retention period in days:

| rest splunk_server=* /services/data/indexes | join type=outer title [
| rest splunk_server=* /services/data/indexes-extended
] | eval retentionInDays=frozenTimePeriodInSecs/86400
| table title retentionInDays

| rest splunk_server=* /services/data/indexes | join type=outer title [

| rest splunk_server=* /services/data/indexes-extended

] | eval retentionInDays=frozenTimePeriodInSecs/86400

| table title retentionInDays

Continue Reading →

Bucket Count by indexer/index

This search displays the amount of buckets per indexer/index To learn more about the | dbinspect command go to: http://docs.splunk.com/Documentation/Splunk/7.1.2/SearchReference/Dbinspect

|dbinspect index=* | search index!=_* | chart dc(bucketId) over splunk_server by index

1	\|dbinspect index=* \| search index!=_* \| chart dc(bucketId) over splunk_server by index

Continue Reading →

Bucket Count by State over Index

This search counts the amount of buckets per state for each index. To learn more about | dbinspect go to: http://docs.splunk.com/Documentation/Splunk/7.1.2/SearchReference/Dbinspect

|dbinspect index=* | eval state=case(state=="warm" OR state=="hot","hot/warm",1=1, state) | chart dc(bucketId) over index by state

1	\|dbinspect index=* \| eval state=case(state=="warm" OR state=="hot","hot/warm",1=1, state) \| chart dc(bucketId) over index by state

Continue Reading →

Memory Usage (MB) per Splunk Process Class

Use the following search with a column chart visualisation. It will give you a good overview of what Splunk processes use the most memory:

index=_introspection sourcetype=splunk_resource_usage component=PerProcess host=* | eval process = 'data.process' | eval args = 'data.args' | eval sid = 'data.search_props.sid' | eval elapsed = 'data.elapsed' | eval mem_used = 'data.mem_used' | eval mem = 'data.mem' | eval pct_memory = 'data.pct_memory' | eval app = 'data.search_props.app' | eval type = 'data.search_props.type' | eval mode = 'data.search_props.mode' | eval user = 'data.search_props.user' | eval role = 'data.search_props.role' | eval process_class = case( process=="splunk-optimize","index service", process=="sh" OR process=="ksh" OR process=="bash" OR like(process,"python%") OR process=="powershell","scripted input", process=="mongod", "KVStore") | eval process_class = case( process=="splunkd" AND (like(args,"-p %start%") OR like(args,"service")),"splunkd server", process=="splunkd" AND isnotnull(sid),"search", process=="splunkd" AND (like(args,"fsck%") OR like(args,"recover-metadata%") OR like(args,"cluster_thing")),"index service", process=="splunkd" AND args=="instrument-resource-usage", "scripted input", (like(process,"python%") AND like(args,"%/appserver/mrsparkle/root.py%")) OR like(process,"splunkweb"),"Splunk Web", isnotnull(process_class), process_class) | eval process_class = if(isnull(process_class),"other",process_class) | stats latest(data.mem_used) AS resource_usage_dedup latest(process_class) AS process_class by data.pid, _time | stats sum(resource_usage_dedup) AS resource_usage by _time, process_class | timechart minspan=10s median(resource_usage) AS "Resource Usage" by process_class

index=_introspection sourcetype=splunk_resource_usage component=PerProcess host=* | eval process = 'data.process' | eval args = 'data.args' | eval sid = 'data.search_props.sid' | eval elapsed = 'data.elapsed' | eval mem_used = 'data.mem_used' | eval mem = 'data.mem' | eval pct_memory = 'data.pct_memory' | eval app = 'data.search_props.app' | eval type = 'data.search_props.type' | eval mode = 'data.search_props.mode' | eval user = 'data.search_props.user' | eval role = 'data.search_props.role' | eval process_class = case( process=="splunk-optimize","index service", process=="sh" OR process=="ksh" OR process=="bash" OR like(process,"python%") OR process=="powershell","scripted input", process=="mongod", "KVStore") | eval process_class = case( process=="splunkd" AND (like(args,"-p %start%") OR like(args,"service")),"splunkd server", process=="splunkd" AND isnotnull(sid),"search", process=="splunkd" AND (like(args,"fsck%") OR like(args,"recover-metadata%") OR like(args,"cluster_thing")),"index service", process=="splunkd" AND args=="instrument-resource-usage", "scripted input", (like(process,"python%") AND like(args,"%/appserver/mrsparkle/root.py%")) OR like(process,"splunkweb"),"Splunk Web", isnotnull(process_class), process_class) | eval process_class = if(isnull(process_class),"other",process_class) | stats latest(data.mem_used) AS resource_usage_dedup latest(process_class) AS process_class by data.pid, _time | stats sum(resource_usage_dedup) AS resource_usage by _time, process_class | timechart minspan=10s median(resource_usage) AS "Resource Usage" by process_class

Continue Reading →

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:

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

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

Continue Reading →

Top 10 Accessed Dashboards

Where “host=”your_sh_host”” you could specify a host, or put a wildcard * in place.

index="_internal" source=*access.log user!="-" */app/* (host="your_sh_host") 
| rex field=referer "/en-US/app/(?<app>[^/]+)/(?<dashboard>[^?/\s]+)" 
| top 10 dashboard

index="_internal" source=*access.log user!="-" */app/* (host="your_sh_host")

| rex field=referer "/en-US/app/(?<app>[^/]+)/(?<dashboard>[^?/\s]+)"

| top 10 dashboard

Continue Reading →

Universal Forwarder Throughput Statistics

This search creates a table to list all Universal Forwarders. There is also an eval in there that classifies hosts based on their average Kbps. You can modify this as needed.

index=_internal source=*metrics.log group=tcpin_connections splunk_server=* 
| eval host=if(isnull(hostname), sourceHost,hostname) 
| search (host=*) AND (host!="(ALL)")
| eval connectionType=case(fwdType=="uf","univ fwder", fwdType=="lwf", "lightwt fwder",fwdType=="full", "heavy fwder", connectionType=="cooked" or connectionType=="cookedSSL","Splunk fwder", connectionType=="raw" or connectionType=="rawSSL","legacy fwder") 
| eval Ver=if(isnull(version),"pre 4.2",version) 
| fields connectionType sourceIp host kb tcp_eps tcp_KBps splunk_server Ver
| stats min(_time) as first_time, max(_time) as last_time, max(tcp_KBps) as max_tcp_KBps, avg(tcp_KBps) as avg_tcp_KBps, avg(tcp_eps) as avg_tcp_eps, sum(kb) as sum_kb by connectionType sourceIp host Ver 
| dedup host
| eval sum_mb = round(sum_kb/1024,0) 
| fields - sum_kb 
| eval avg_tcp_KBps = round(avg_tcp_KBps,0)
| eval max_tcp_KBps = round(max_tcp_KBps,0)
| eval class=case(
avg_tcp_KBps>0 AND avg_tcp_KBps<=128, "Standard Hosts",
avg_tcp_KBps>128 AND avg_tcp_KBps<=256, "Better Hosts",
avg_tcp_KBps>256, "Special Hosts")
| convert ctime(*time) 
| rename first_time as "First seen", last_time as "Last seen", avg_tcp_KBps as "AVG KB/s", avg_tcp_eps as "AVG Events/s per 30 seconds", sum_mb as "Total MB", max_tcp_KBps as "Peak Kbps" connectionType AS "Forwarder Type" sourceIp as "Source IP" host AS "Host" Ver As "Splunk Version"
| sort - "Peak Kbps"

index=_internal source=*metrics.log group=tcpin_connections splunk_server=*

| eval host=if(isnull(hostname), sourceHost,hostname)

| search (host=*) AND (host!="(ALL)")

| eval connectionType=case(fwdType=="uf","univ fwder", fwdType=="lwf", "lightwt fwder",fwdType=="full", "heavy fwder", connectionType=="cooked" or connectionType=="cookedSSL","Splunk fwder", connectionType=="raw" or connectionType=="rawSSL","legacy fwder")

| eval Ver=if(isnull(version),"pre 4.2",version)

| fields connectionType sourceIp host kb tcp_eps tcp_KBps splunk_server Ver

| stats min(_time) as first_time, max(_time) as last_time, max(tcp_KBps) as max_tcp_KBps, avg(tcp_KBps) as avg_tcp_KBps, avg(tcp_eps) as avg_tcp_eps, sum(kb) as sum_kb by connectionType sourceIp host Ver

| dedup host

| eval sum_mb = round(sum_kb/1024,0)

| fields - sum_kb

| eval avg_tcp_KBps = round(avg_tcp_KBps,0)

| eval max_tcp_KBps = round(max_tcp_KBps,0)

| eval class=case(

avg_tcp_KBps>0 AND avg_tcp_KBps<=128, "Standard Hosts",

avg_tcp_KBps>128 AND avg_tcp_KBps<=256, "Better Hosts",

avg_tcp_KBps>256, "Special Hosts")

| convert ctime(*time)

| rename first_time as "First seen", last_time as "Last seen", avg_tcp_KBps as "AVG KB/s", avg_tcp_eps as "AVG Events/s per 30 seconds", sum_mb as "Total MB", max_tcp_KBps as "Peak Kbps" connectionType AS "Forwarder Type" sourceIp as "Source IP" host AS "Host" Ver As "Splunk Version"

| sort - "Peak Kbps"

Continue Reading →

Timechart of the status of an Locked Out Account

This query will show a timechart of the status of an Locked Out Account

sourcetype="WinEventLog:Security" EventCode=4625 AND Status=0xC0000234 | timechart count by user | sort -count

1	sourcetype="WinEventLog:Security" EventCode=4625 AND Status=0xC0000234 \| timechart count by user \| sort -count

Continue Reading →

Active Directory Password change attempts

Use the following search to create a stacked barchart of AD Password change attempts:

source="WinEventLog:Security" "EventCode=4723" src_user!="*$" src_user!="_svc_*" | eval daynumber=strftime(_time,"%Y-%m-%d") | chart count by daynumber, status | eval daynumber = mvindex(split(daynumber,"-"),2)

1	source="WinEventLog:Security" "EventCode=4723" src_user!="$" src_user!="_svc_" \| eval daynumber=strftime(_time,"%Y-%m-%d") \| chart count by daynumber, status \| eval daynumber = mvindex(split(daynumber,"-"),2)

Continue Reading →

Add a count of events by fieldname

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

1	\| streamstats count as eventCount by fieldname

Continue Reading →

List all fields for an index

A few different queries / methods to list all fields for indexes.

index=yourindex| fieldsummary | table field

1	index=yourindex\| fieldsummary \| table field

index=yourindex | stats values(*) AS * | transpose | table column | rename column AS Fieldnames

1	index=yourindex \| stats values() AS \| transpose \| table column \| rename column AS Fieldnames

index=yourindex | stats dc() as * | transpose

1	index=yourindex \| stats dc() as * \| transpose

or ;-)

index=yourindex | table *

1	index=yourindex \| table *

Continue Reading →

Search for duplicate events in Splunk

index=<indexname> | stats count values(host) values(source) values(sourcetype) values(index) by _raw | WHERE count>1

1	index=<indexname> \| stats count values(host) values(source) values(sourcetype) values(index) by _raw \| WHERE count>1

Continue Reading →

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

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

Continue Reading →

Easter egg that created sample data

 | windbag

| windbag

This command creates a set of sample data of 100 events

Continue Reading →

Author: Azeemering