Azeemering, Author at

Breathing Fire Dragon when Starting dbx_task_server

index=_internal sourcetype=dbx_server Starting dbx_task_server

1	index=_internal sourcetype=dbx_server Starting dbx_task_server

Will return events that display a little dragon ascii art:

             |\___/|
            (,\  /,)\
            /     /  \
           (@_^_@)/   \
            W//W_/     \
          (//) |        \
        (/ /) _|_ /   )  \
      (// /) '/,_ _ _/  (~^-.
    (( // )) ,-{        _    `.
   (( /// ))  '/\      /      |
   (( ///))     `.   {       }
    ((/ ))    .----~-.\   \-'
             ///.----..>   \
              ///-._ _  _ _}

|\___/|

(,\ /,)\

/ / \

(@_^_@)/ \

W//W_/ \

(//) | \

(/ /) _|_ / ) \

(// /) '/,_ _ _/ (~^-.

(( // )) ,-{ _ `.

(( /// )) '/\ / |

(( ///)) `. { }

((/ )) .----~-.\ \-'

///.----..> \

///-._ _ _ _}

Continue Reading →

Evaluate Fahrenheit to Celsius

Quick snippet to evaluate temperature Fahrentheit to Celsius:

| eval Temperature_Fahrenheit=Temperature_Celsius*1.8+32

1	\| eval Temperature_Fahrenheit=Temperature_Celsius*1.8+32

Continue Reading →

Find unused dashboards

_audit/ _internal/ Fun Stuff & Helpful Hints
Azeemering
7 2

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 →

Author: Azeemering

Breathing Fire Dragon when Starting dbx_task_server

Show your triggered alerts

Evaluate Fahrenheit to Celsius

Find unused dashboards

Check your strftime is correct in the props.conf

Saved Search Scheduler Activity

Show indexing queue sizes

Percentage of skipped searches

Retention Period in days per index

Bucket Count by indexer/index

Bucket Count by State over Index

Memory Usage (MB) per Splunk Process Class

Universal Forwarder Throughput Limit Hit Count

Top 10 Accessed Dashboards

Universal Forwarder Throughput Statistics

Timechart of the status of an Locked Out Account

Active Directory Password change attempts

Add a count of events by fieldname

List all fields for an index

Search for duplicate events in Splunk