Community:Search Report: How To Create a Line Chart of Search Duration in Timeline

From Splunk Wiki

Jump to: navigation, search

How To Create a Line Chart of Search Duration in Timeline

This example is to learn Splunk search and report. A result of chart has some lack of information or readability concern when it comes to analyse overall search duration or each search duration. Also, it is not easy to analyze which search took longer than others when there are 100 of searches. Therefore, it may not be practical in real world. However, it is still a good example to practice Splunk search and report.

# Goal:
#    Present duration time of each Splunk search as a line (y-axis) in time-line (x-axis).

Source data: 
   - Index db         : Splunk internal db, called _audit, 
   - Referenced Events: action is search, and start of a search is indicated by "info=granted" and end of a search is indicated by "info=completed". A search is identified by search_id field

Example of Events:

Audit:[timestamp=06-30-2013 10:42:39.700, user=admin, action=search, info=granted , search_id='1372614159.17953', search='surrounding id=51:2613923 index=_audit 
searchkeys="" timeBefore=86400 timeAfter=86400 maxresults=10 timestamp=1372614116.743953 bucket="_audit~51~F296BF70-D29D-4739-8CFD-B596ADCDF0C9" filter=""', 
autojoin='0', buckets=0, ttl=30, max_count=10, maxtime=8640000, enable_lookups='1', extra_fields='', apiStartTime='ZERO_TIME', apiEndTime='ZERO_TIME', savedsearch_name=""][n/a]
Audit:[timestamp=06-30-2013 10:42:57.158, user=admin, action=search, info=completed, search_id='1372614159.17953', total_run_time=0.17, event_count=10,
 result_count=10, available_count=10, scan_count=0, drop_count=0, exec_time=1372614159, api_et=N/A, api_lt=N/A, search_et=N/A, search_lt=N/A, is_realtime=0, 

Sample Chart:
LineChart of search duration in timeline 1.jpg

# Solution Example 1

index=_audit  action=search search_id='scheduler_* (info=granted OR info=completed) 
    | fields search_id, info, _time
    | stats max(_time) as maxtime, min(_time) as mintime by search_id
    | eval runtime=maxtime-mintime
    | where runtime>5
    | sort mintime
    | streamstats count as runnum
    | eval combinedt=mintime.",".maxtime 
    | makemv delim="," combinedt
    | mvexpand combinedt
    | eval _time=combinedt
    | timechart useother=f span=1s limit=10 first(runnum) by search_id

# Solution Example 2

index=_audit action=search search_id='scheduler_* (info=granted OR info=completed) 
    | fields _time, search_id, info 
    | eventstats count by search_id 
    | where count>1 
    | eval fun=if(info=="granted",1,0) 
    | sort search_id, -info 
    | streamstats sum(fun) as rank  
    | timechart useother=f max(rank) by search_id

# A Sample of Solution 3
# (Tested in v5.0.3)

- Note 
   1. This sample solution includes redundant searches such as ID>10 and table to filter only required fields
   2. This could be achieved without "transaction" command. It could be simpler with "stats"

index=_audit action=search ( info=granted OR info=completed ) NOT REST: NOT search_id=*subsearch_* 
   | eval TT=_time 
   | transaction search_id 
   | search duration>10 
   | table _time TT search_id 
   | streamstats count(search_id) AS ID 
   | mvexpand TT 
   | search ID<10 
   | eval _time=TT 
   | table _time ID search_id 
   | timechart  values(ID) AS ID by search_id 

Example of Result (Basically this chart is the same chart as the sample chart above):
Note: "Last 5 minutes" as a Time-Range) LineChart of search duration in timeline.jpg

# A similar chart is used in "Splunk Search App -> Status -> Index Activity -> Index Health"

Example Search Index Health.jpg

Personal tools
Hot Wiki Topics

About Splunk >
  • Search and navigate IT data from applications, servers and network devices in real-time.
  • Download Splunk