Joining Two base searches with a join

[robertlynch2020]

Hi

I have a very large base search. Full of tokens that can be driven from the user dashboard.
It is built of 2 tstat commands doing a join.

The issue is the second tstats gets updated with a token and the whole search will re-run.
In the perfect world the top half does'tre-run and the second tstat re-use the 1st half's data from the original run and saves time

I have but in a cut down version of the base search. + the whole thing below it.
Can i get put a second base search inside the first base search, so it only reruns and does trigger the whole thing to re run?

  <search id="First_Base_Search">
    <query>| tstats summariesonly=$summariesonly_token$ ...... etc..
| join Machine_Name NPID type=$join_type_token$ 
[| tstats summariesonly=$summariesonly_token$ ......etc ] 
| lookup MXTIMING_lookup_Base ..etc..
</query>
    <earliest>$time_token.earliest$</earliest>
    <latest>$time_token.latest$</latest>
  </search>











  <search id="First_Base_Search">
    <query>| tstats summariesonly=$summariesonly_token$ max(MXTIMING.Elapsed) AS Elapsed max(MXTIMING.CPU) AS CPU max(MXTIMING.CPU_PER) AS CPU_PER values(MXTIMING.RDB_COM1) AS RDB_COM values(MXTIMING.RDB_COM_PER1) AS RDB_COM_PER max(MXTIMING.Memory) AS Memory max(MXTIMING.Elapsed_C) AS Elapsed_C values(source) AS source_MXTIMING avg(MXTIMING.Elapsed) AS average, count(MXTIMING.Elapsed) AS count, stdev(MXTIMING.Elapsed) AS stdev, median(MXTIMING.Elapsed) AS median, exactperc95(MXTIMING.Elapsed) AS perc95, exactperc99.5(MXTIMING.Elapsed) AS perc99.5, min(MXTIMING.Elapsed) AS min,earliest(_time) as start, latest(_time) as stop FROM datamodel=$MXTIMING_DATASET$ WHERE 

    host=$host_token$ 
AND MXTIMING.Elapsed &gt; $MAX_TIME$ 


GROUPBY _time MXTIMING.Machine_Name MXTIMING.Context+Command MXTIMING.NPID MXTIMING.Date MXTIMING.Time MXTIMING.MXTIMING_TYPE_DM source MXTIMING.UserName2 MXTIMING.source_path MXTIMING.Command3 MXTIMING.Context3 span=1s
| rename MXTIMING.Context+Command as Context+Command 
| rename MXTIMING.NPID as NPID 
| rename MXTIMING.MXTIMING_TYPE_DM as TYPE 
| rename MXTIMING.Date as Date 
| rename MXTIMING.Time as Time 
| rename MXTIMING.Machine_Name as Machine_Name 
| rename MXTIMING.UserName2 as UserName
| rename MXTIMING.source_path  as source_path
| eval Date=strftime(strptime(Date,"%Y%m%d"),"%d/%m/%Y") 
| eval Time = Date." ".Time
| eval FULL_EVENT=Elapsed_C 
| eval FULL_EVENT=replace(FULL_EVENT,"\d+.\d+","FULL_EVENT") 
| join Machine_Name NPID type=$join_type_token$ 
[| tstats summariesonly=$summariesonly_token$ count(SERVICE.NPID) AS count2 values(source) AS source_SERVICES FROM datamodel=SERVICE_V5 WHERE ( host=$host_token$ earliest=$service_earliest_time$ latest=$service_latest_time$) AND SERVICE.NICKNAME IN ($NICKNAME_TOKEN$)
GROUPBY SERVICE.Machine_Name SERVICE.NICKNAME SERVICE.NPID 
| rename SERVICE.NPID AS NPID 
| rename SERVICE.NICKNAME AS NICKNAME 
| rename SERVICE.Machine_Name as Machine_Name 
| table NICKNAME NPID source_SERVICES Machine_Name ] 
| lookup MXTIMING_lookup_Base Context_Command AS "Context+Command" Type as "TYPE" OUTPUT Tags CC_Description Threshold Alert  
| appendpipe
[ | where isnull(Threshold)
| rename TYPE AS BACKUP_TYPE
| eval TYPE="*"     
| lookup MXTIMING_lookup_Base Context_Command AS "Context+Command" Type as "TYPE" OUTPUT Tags CC_Description Threshold Alert
| rename BACKUP_TYPE AS TYPE]
| dedup Time, NPID,Context+Command
| where Elapsed > Threshold OR isnull('Threshold')
| fillnull Tags 
| eval Tags=if(Tags=0,"PLEASE_ADD_TAG",Tags) 
| makemv Tags delim=","
| eval Tags=split(Tags,",") 
| search Tags IN ($TAG_TOKEN$) 
| eval source_SERVICES_count=mvcount(split(source_SERVICES, " ")) | eval NICKNAME=if(source_SERVICES_count > 1, "MULTIPLE_OPTIONS_FOUND",NICKNAME)</query>
    <earliest>$time_token.earliest$</earliest>
    <latest>$time_token.latest$</latest>
  </search>

[robertlynch2020 · Accepted solution]

Hi

In fact i got the answer by creating one base search and using the answer to create a second search. Now i use the second search as as a prefilter.

  <search id="baseSearch">
    <query>| tstats summariesonly=$summariesonly_token$ max(MXTIMING.Elapsed) AS Elapsed max(MXTIMING.CPU) AS CPU max(MXTIMING.CPU_PER) AS CPU_PER values(MXTIMING.RDB_COM1) AS RDB_COM values(MXTIMING.RDB_COM_PER1) AS RDB_COM_PER max(MXTIMING.Memory) AS Memory max(MXTIMING.Elapsed_C) AS Elapsed_C values(source) AS source_MXTIMING avg(MXTIMING.Elapsed) AS average, count(MXTIMING.Elapsed) AS count, stdev(MXTIMING.Elapsed) AS stdev, median(MXTIMING.Elapsed) AS median, exactperc95(MXTIMING.Elapsed) AS perc95, exactperc99.5(MXTIMING.Elapsed) AS perc99.5, min(MXTIMING.Elapsed) AS min,earliest(_time) as start, latest(_time) as stop FROM datamodel=$MXTIMING_DATASET$ WHERE 

    host=$host_token$ 
AND MXTIMING.Elapsed > $MAX_TIME$ 


GROUPBY _time MXTIMING.Machine_Name MXTIMING.Context+Command MXTIMING.NPID MXTIMING.Date MXTIMING.Time MXTIMING.MXTIMING_TYPE_DM source MXTIMING.UserName2 MXTIMING.source_path MXTIMING.Command3 MXTIMING.Context3 span=1s
| rename MXTIMING.Context+Command as Context+Command 
| rename MXTIMING.NPID as NPID 
| rename MXTIMING.MXTIMING_TYPE_DM as TYPE 
| rename MXTIMING.Date as Date 
| rename MXTIMING.Time as Time 
| rename MXTIMING.Machine_Name as Machine_Name 
| rename MXTIMING.UserName2 as UserName
| rename MXTIMING.source_path  as source_path
| eval Date=strftime(strptime(Date,"%Y%m%d"),"%d/%m/%Y") 
| eval Time = Date." ".Time
| eval FULL_EVENT=Elapsed_C 
| eval FULL_EVENT=replace(FULL_EVENT,"\d+.\d+","FULL_EVENT")</query>
   <earliest>$time_token.earliest$</earliest>
    <latest>$time_token.latest$</latest>
 </search>

 <search base="baseSearch" id="Second_Base_Search">
    <query>[| tstats summariesonly=$summariesonly_token$ count(SERVICE.NPID) AS count2 values(source) AS source_SERVICES FROM datamodel=SERVICE_V5 WHERE ( host=$host_token$ earliest=$service_earliest_time$ latest=$service_latest_time$) AND SERVICE.NICKNAME IN ($NICKNAME_TOKEN$)
GROUPBY SERVICE.Machine_Name SERVICE.NICKNAME SERVICE.NPID 
| rename SERVICE.NPID AS NPID 
| rename SERVICE.NICKNAME AS NICKNAME 
| rename SERVICE.Machine_Name as Machine_Name 
| table NICKNAME NPID source_SERVICES Machine_Name ] 
| lookup MXTIMING_lookup_Base Context_Command AS "Context+Command" Type as "TYPE" OUTPUT Tags CC_Description Threshold Alert  
| appendpipe
[ | where isnull(Threshold)
| rename TYPE AS BACKUP_TYPE
| eval TYPE="*"     
| lookup MXTIMING_lookup_Base Context_Command AS "Context+Command" Type as "TYPE" OUTPUT Tags CC_Description Threshold Alert
| rename BACKUP_TYPE AS TYPE]
| dedup Time, NPID,Context+Command
| where Elapsed > Threshold OR isnull('Threshold')
| fillnull Tags 
| eval Tags=if(Tags=0,"PLEASE_ADD_TAG",Tags) 
| makemv Tags delim=","
| eval Tags=split(Tags,",") 
| search Tags IN ($TAG_TOKEN$) 
| eval source_SERVICES_count=mvcount(split(source_SERVICES, " ")) | eval NICKNAME=if(source_SERVICES_count > 1, "MULTIPLE_OPTIONS_FOUND",NICKNAME)</query>
 </search>

[logloganathan]

Could you please try this query

1. < search id="First_Base_Search">
2. < query>| tstats summariesonly=$summariesonly_token$ ...... etc.. 3 | join Machine_Name NPID type=$join_type_token$ 4 [| tstats summariesonly=$summariesonly_token$ ......etc ] 5 | lookup MXTIMING_lookup_Base ..etc.. 6 | join summariesonly 7 [ search | tstats summariesonly=$summariesonly_token$ max(MXTIMING.Elapsed) AS Elapsed max(MXTIMING.CPU) AS CPU max(MXTIMING.CPU_PER) AS CPU_PER values(MXTIMING.RDB_COM1) AS RDB_COM values(MXTIMING.RDB_COM_PER1) AS RDB_COM_PER max(MXTIMING.Memory) AS Memory max(MXTIMING.Elapsed_C) AS Elapsed_C values(source) AS source_MXTIMING avg(MXTIMING.Elapsed) AS average, count(MXTIMING.Elapsed) AS count, stdev(MXTIMING.Elapsed) AS stdev, median(MXTIMING.Elapsed) AS median, exactperc95(MXTIMING.Elapsed) AS perc95, exactperc99.5(MXTIMING.Elapsed) AS perc99.5, min(MXTIMING.Elapsed) AS min,earliest(_time) as start, latest(_time) as stop FROM datamodel=$MXTIMING_DATASET$ WHERE host=$host_token$ AND MXTIMING.Elapsed > $MAX_TIME$ GROUPBY _time MXTIMING.Machine_Name MXTIMING.Context+Command MXTIMING.NPID MXTIMING.Date MXTIMING.Time MXTIMING.MXTIMING_TYPE_DM source MXTIMING.UserName2 MXTIMING.source_path MXTIMING.Command3 MXTIMING.Context3 span=1s 8 | rename MXTIMING.Context+Command as Context+Command 9 | rename MXTIMING.NPID as NPID 10 | rename MXTIMING.MXTIMING_TYPE_DM as TYPE 11 | rename MXTIMING.Date as Date 12 | rename MXTIMING.Time as Time 13 | rename MXTIMING.Machine_Name as Machine_Name 14 | rename MXTIMING.UserName2 as UserName 15 | rename MXTIMING.source_path as source_path 16 | eval Date=strftime(strptime(Date,"%Y%m%d"),"%d/%m/%Y") 17 | eval Time = Date." ".Time 18 | eval FULL_EVENT=Elapsed_C 19 | eval FULL_EVENT=replace(FULL_EVENT,"\d+.\d+","FULL_EVENT") 20 | join Machine_Name NPID type=$join_type_token$ 21 [| tstats summariesonly=$summariesonly_token$ count(SERVICE.NPID) AS count2 values(source) AS source_SERVICES FROM datamodel=SERVICE_V5 WHERE ( host=$host_token$ earliest=$service_earliest_time$ latest=$service_latest_time$) AND SERVICE.NICKNAME IN ($NICKNAME_TOKEN$) GROUPBY SERVICE.Machine_Name SERVICE.NICKNAME SERVICE.NPID 22 | rename SERVICE.NPID AS NPID 23 | rename SERVICE.NICKNAME AS NICKNAME 24 | rename SERVICE.Machine_Name as Machine_Name 25 | table NICKNAME NPID source_SERVICES Machine_Name ] 26 | lookup MXTIMING_lookup_Base Context_Command AS "Context+Command" Type as "TYPE" OUTPUT Tags CC_Description Threshold Alert
   27 | appendpipe 28 [ | where isnull(Threshold) 29 | rename TYPE AS BACKUP_TYPE 30 | eval TYPE="*"
   31| lookup MXTIMING_lookup_Base Context_Command AS "Context+Command" Type as "TYPE" OUTPUT Tags CC_Description Threshold Alert 32 | rename BACKUP_TYPE AS TYPE] 33 | dedup Time, NPID,Context+Command 34 | where Elapsed > Threshold OR isnull('Threshold') 35 | fillnull Tags 36 | eval Tags=if(Tags=0,"PLEASE_ADD_TAG",Tags) 37 | makemv Tags delim="," 38 | eval Tags=split(Tags,",") 39 | search Tags IN ($TAG_TOKEN$) | eval source_SERVICES_count=mvcount(split(source_SERVICES, " ")) | eval NICKNAME=if(source_SERVICES_count > 1,"MULTIPLE_OPTIONS_FOUND",NICKNAME) ] 40 | table summariesonly source_SERVICES_count NICKNAME < /query> 41 < earliest>$time_token.earliest$< /earliest> 42 < latest>$time_token.latest$< /latest> 43 < /search>