Splunk Search
Highlighted

How to run multiple queries at once, with calculations?

New Member

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 "ILIKEYOKNOW" AND "WARRIGGISBAD" AND "5.7WOLFER" | head 1 | eval EndTime=strftime(time, "%Y-%m-%d %H:%M:%S") | table _time, EndTime

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

    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 (ITIMID) - 100
TPS=(End Time-Start Time/Count(ITIM
ID)) - No. as per formulae

Koushik

0 Karma
Highlighted

Re: How to run multiple queries at once, with calculations?

SplunkTrust
SplunkTrust

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.

0 Karma
Highlighted

Re: How to run multiple queries at once, with calculations?

New Member

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

0 Karma
Highlighted

Re: How to run multiple queries at once, with calculations?

Influencer

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")

View solution in original post

0 Karma
Highlighted

Re: How to run multiple queries at once, with calculations?

New Member

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")
0 Karma