I am trying to calculate TPS with the help of the queries below:
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
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
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
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")
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")
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")
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.
I modified your query and it works as well, but I need to practice more.