Getting tstats lookups and supserchers working together

[robertlynch2020]

Hi

I have a working tstat query and a working lookup query.
I am trying to us a substring to bring them together. I want to pass information from the lookup to the tstats.

This is the basic tstat.
| tstats summariesonly=true avg(All_TPS_Logs.duration) AS Average_TPS ,earliest(_time) as Start, latest(_time) as Stop FROM datamodel=MLC_TPS_DEBUG4 WHERE (nodename=All_TPS_Logs host=EXCESS_WORKFLOWS_UOB) NOT All_TPS_Logs.overflow=true All_TPS_Logs.fullyQualifiedMethod=murex.limits.engine.stream.histo.TPSHistoCounter#COMMIT earliest=10/11/2016:11:00:00 latest=10/13/2016:11:12:00 BY All_TPS_Logs.fullyQualifiedMethod

RESULT
All_TPS_Logs.fullyQualifiedMethod Average_TPS Start Stop
murex.limits.engine.stream.histo.TPSHistoCounter#COMMIT 44215.741873 1473760462 1476302078

I add in the earliest and latest to control the time from the query. (This works)
| tstats summariesonly=true avg(All_TPS_Logs.duration) AS average ,earliest(_time) as Start, latest(_time) as Stop FROM datamodel=MLC_TPS_DEBUG4 WHERE (nodename=All_TPS_Logs host=EXCESS_WORKFLOWS_UOB) NOT All_TPS_Logs.overflow=true All_TPS_Logs.fullyQualifiedMethod=murex.limits.engine.stream.histo.TPSHistoCounter#COMMIT earliest=10/11/2016:11:00:00 latest=10/13/2016:11:12:00 BY All_TPS_Logs.fullyQualifiedMethod

RESULT (the average has changed as we have moved time on the search)
All_TPS_Logs.fullyQualifiedMethod average Start Stop
murex.limits.engine.stream.histo.TPSHistoCounter#COMMIT 1121886.932 1476180959 1476302078

However i have multiple of earliest and latest stored in a "lookup". So i want to pass in multiple of them into tstat to give me multiple rows in the answer.

Basic lookup (I have them stored in epoc as Start and Stop)
| inputlookup Saved_Tests.csv | where Host="EXCESS_WORKFLOWS_UOB" | where Status="GOLD" | eval Start = strftime(Start, "%Y-%d-%m:%H:%M:%S") | eval Stop= strftime(Stop, "%Y-%d-%m:%H:%M:%S") | table MX_Build_ID ID Start Stop

RESULT (Average_TPS and All_TPS_logs.fullQualifiedMethod are empty as they need to come from the tstat)
MX_Build_ID ID Start Stop Average_TPS All_TPS_Logs.fullyQualifiedMethod
3700334-160923-1106-2100451 259 2016-07-10:19:40:00 2016-08-10:03:16:00

3700334-160923-1106-2100451 258 2016-06-10:17:30:00 2016-07-10:00:00:00

3637272-160804-1733-2013125 215 2016-25-08:17:00:00 2016-25-08:18:37:50

So i need to pass the start and stop from the lookup into the "tstat" and for the tstat to give me back an average for that time period.
I have tired to use a substring for this below hower it is not producing any results and i am unsure what is the issue.

so i dont know if i should but the tstats first and use the inputlookup as a subsearch or visaversa etc...

Below is what i hvae, however i hvae tried many different things,....

| inputlookup Saved_Tests.csv | where Host="EXCESS_WORKFLOWS_UOB" | where Status="GOLD" | eval Start3 = strftime(Start, "%Y-%d-%m:%H:%M:%S") | eval Stop3= strftime(Stop, "%Y-%d-%m:%H:%M:%S") | table MX_Build_ID ID Start Stop Average_TPS All_TPS_Logs.fullyQualifiedMethod | search [| tstats summariesonly=true avg(All_TPS_Logs.duration) AS average ,earliest(_time) as Start, latest(_time) as Stop FROM datamodel=MLC_TPS_DEBUG4 WHERE (nodename=All_TPS_Logs host=EXCESS_WORKFLOWS_UOB) NOT All_TPS_Logs.overflow=true All_TPS_Logs.fullyQualifiedMethod=murex.limits.engine.stream.histo.TPSHistoCounter#COMMIT earliest=Start3 latest=Stop3 BY All_TPS_Logs.fullyQualifiedMethod ]

ERROR is = Invalid value "Start3" for time term 'earliest'

If i replace start3 with a value is get no row returned
| inputlookup Saved_Tests.csv | where Host="EXCESS_WORKFLOWS_UOB" | where Status="GOLD" | eval Start3 = strftime(Start, "%Y-%d-%m:%H:%M:%S") | eval Stop3= strftime(Stop, "%Y-%d-%m:%H:%M:%S") | table MX_Build_ID ID Start Stop Average_TPS All_TPS_Logs.fullyQualifiedMethod | search [| tstats summariesonly=true avg(All_TPS_Logs.duration) AS average ,earliest(_time) as Start, latest(_time) as Stop FROM datamodel=MLC_TPS_DEBUG4 WHERE (nodename=All_TPS_Logs host=EXCESS_WORKFLOWS_UOB) NOT All_TPS_Logs.overflow=true All_TPS_Logs.fullyQualifiedMethod=murex.limits.engine.stream.histo.TPSHistoCounter#COMMIT earliest=10/11/2016:11:00:00 latest=10/13/2016:11:12:00 BY All_TPS_Logs.fullyQualifiedMethod ]

o results.

[somesoni2]

Give this a try (adjust maxsearches based on number of rows you have in the lookup)

| inputlookup Saved_Tests.csv | where Host="EXCESS_WORKFLOWS_UOB" | where Status="GOLD" | eval Start = strftime(Start, "%Y-%d-%m:%H:%M:%S") | eval Stop= strftime(Stop, "%Y-%d-%m:%H:%M:%S") | table MX_Build_ID ID Start Stop
| map maxsearches=100 search="| tstats summariesonly=true avg(All_TPS_Logs.duration) AS average FROM datamodel=MLC_TPS_DEBUG4 WHERE (nodename=All_TPS_Logs host=EXCESS_WORKFLOWS_UOB) NOT All_TPS_Logs.overflow=true All_TPS_Logs.fullyQualifiedMethod=murex.limits.engine.stream.histo.TPSHistoCounter#COMMIT earliest=$Start$ latest=$Stop$ BY All_TPS_Logs.fullyQualifiedMethod | eval Start=\"$Start$\" | eval Stop=\"$Stop$\" | eval MX_Build_ID=\"$MX_Build_ID$\" | eval ID=\"$ID$\" | table table MX_Build_ID ID Start Stop Average_TPS All_TPS_Logs.fullyQualifiedMethod"