- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Getting tstats lookups and supserchers working together
![robertlynch2020 robertlynch2020](https://community.splunk.com/legacyfs/online/avatars/345281.jpg)
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.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
![somesoni2 somesoni2](https://community.splunk.com/legacyfs/online/avatars/100305.jpg)
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"
![](/skins/images/5D2DD17C284106BFBF80528D01D8AA1A/responsive_peak/images/icon_anonymous_message.png)