How to run multiple queries at once, with calculations?

[koushiknandan]

I am trying to calculate TPS with the help of the queries below:

1. Start Time

   Query
   host=X source=Y.log "data available" | reverse | head 1 | table _time

   Result (without => table _time) - abcde.abcdefg.abcdefg#001(2426) 2016/04/07 13:41:59 -VWXYZ- {1:3} [job.abcde.abcdefg.abcdefg.abc:33] Data available

   Result (with => table _time), as time is actually needed. The timestamp of _time and the dataset is same - 2016-04-07 13:41:59

2. End Time

   Query
   host=X source=Z.log "ILIKEYO_KNOW" AND "WAR_RIGG_IS_BAD" AND "5.7_WOLFER" | head 1 | eval End_Time=strftime(_time, "%Y-%m-%d %H:%M:%S") | table _time, End_Time

   Result (without => table _time) - 2016-04-07T16:20:59,455 INFO [[abcdef4-abcdefghij].abcdefghijklm.nopqrstu.25] [AbcDefghi] ABCD EFGH : ILIKEYO_KNOW | ABCDE FGHI : WAR_RIGG_IS_BAD | ABCDEF GH : XYZ1190560709120516-88328[ABCDEFG HIJKLM : 5.7_WOLFER]

   Result (with => table _time), as time is actually needed. Used eval to remove milliseconds. The timestamp of _time and the dataset is same - 2016-04-07 16:20:59

3. Count of Unique Dataset

   Query
   host=X source=Y.log | stats count(ITIM_ID)

   Result - 100

Is it possible to run all the above 3 queries at one time, and have a similar output as per below:

Start Time - 2016-04-07 13:41:59
End Time - 2016-04-07 16:20:59
Count (ITIM_ID) - 100
TPS=(End Time-Start Time/Count(ITIM_ID)) - No. as per formulae

Koushik

[twinspop · Accepted solution]

I would use appendcols, and "stats min(_time)" or "stats max(_time)" instead of reverses, heads and tails:

host=X source=Y.log "data available" | stats min(_time) as EARLIEST
| appendcols [
    search host=X source=Z.log ILIKEYO_KNOW WAR_RIGG_IS_BAD 5.7_WOLFER | stats max(_time) as LATEST ] 
| appendcols [
    search host=X source=Y | stats count(ITIM_ID) as count ] 
| eval TPS=count/(LATEST-EARLIEST)
| eval EARLIEST=strftime(EARLIEST,"%Y-%m-%d %H:%M:%S")
| eval LATEST=strftime(LATEST,"%Y-%m-%d %H:%M:%S")

[koushiknandan]

I am trying to use the above query, and plot a graph for the TPS field.

So, if I draw a chart with the TPS values over a day (duration) with a span of 1 min/hour, it would show a line graph over a day (duration).

Tried the following, didn't work (Change between **). What am I doing wrong?

     host=X source=Y.log "data available" | stats min(_time) as EARLIEST
     | appendcols [
         search host=X source=Z.log ILIKEYO_KNOW WAR_RIGG_IS_BAD 5.7_WOLFER | stats max(_time) as LATEST ] 
     | appendcols [
         search host=X source=Y | stats count(ITIM_ID) as count ] 
     **| timechart span=1d avg(eval(TPS=count/(LATEST-EARLIEST)))**
     | eval EARLIEST=strftime(EARLIEST,"%Y-%m-%d %H:%M:%S")
     | eval LATEST=strftime(LATEST,"%Y-%m-%d %H:%M:%S")

[javiergn]

Take a look at the append command and see it that helps.

For example, if I wanted to combine the output of three different searches against the _internal index, I could do the following:

index=_internal source="/Applications/Splunk/var/log/splunk/metrics.log" | stats latest(_time) as _time
| append [
   | search index=_internal source="/Applications/Splunk/var/log/splunk/splunkd_ui_access.log" | reverse | head 1 | eval End_Time=strftime(_time, "%Y-%m-%d %H:%M:%S") | table _time, End_Time
]
| append [
   | search index=_internal source="/Applications/Splunk/var/log/splunk/splunkd_access.log" | stats count
]

Let me know if that's what you are looking for. Keep in mind you can also combine the final output from the query above by using eval or stats.

[koushiknandan]

I modified your query and it works as well, but I need to practice more.