Splunk Search

How to run multiple queries at once, with calculations?

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

0 Karma
1 Solution

twinspop
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

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

koushiknandan
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

javiergn
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

koushiknandan
New Member

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...