How to append two different tables with different time intervals into a single table?

[sundarrajan]

Hi all.
Apologies for asking such an unclear and hazy question. I have a situation to show transactions in 2 different time references. The 1st part of the table where i am showing transaction at application level (APP name) with time bucket _time span=15m ** and I also need to show what is the peak transaction count in last 1sec for the identified APP name. I tried **appendcols and then had a separate search for peak value at bucket _time span=1s. But when i downloaded the report I couldnt get the table structure clearly. The last column is going beyond the time column as per the primary search.
So how to ensure, we get a peak value transaction as the later half despite showing value of count in a 15mins window?

[DalJeanis]

Did this solve your issue, or do you still need help?

[sundarrajan]

Dear DalJeanis,
Apologies for a delay in response. I tested the code, and it works well for me. Thanks for sharing the logic.

[DalJeanis · Accepted solution]

Try this...

your base search 
| table _time APP_name
| bin _time as bin1s span=1s 
| stats count as count1s by APP_name bin1s
| bin bin1s as bin15m span=15m
| stats sum(count1s) as count, max(count1s) as "peak second", by bin15m
| rename bin15m as _time

All the bin renames of _time are not actually necessary, but I thought they would make the code less confusing to you. The big concept here is, first bin and aggregate at the lower level, then select out the peak second when you are doing the higher-level aggregation.

Here's the code without any renames...

your base search 
| table _time APP_name
| bin _time span=1s 
| stats count as count1s by APP_name _time 
| bin _time  span=15m
| stats sum(count1s) as count, max(count1s) as "peak second", by _time

[sundarrajan]

Dear DalJeanis,

Thanks for the quick solution. It really works for me (2nd solution over 1st). Grouping by "bins" is not clearly working. Hence I preferred grouping by time.

[sundarrajan]

@DalJeanis
with increased data volume and with multiple fields, the initial part of the code "table _time Field1, Field2, Field3 makes the overall query to breach the search size limit, hence as an optimizing factor, by removing the part, still we get the same set of result. kindly let me know if this practice is allowed?