appendcols to take values from my first search for each row

[robertlynch2020]

Hi

I need my appendcols to take values from my first search. Specifically two values of time produce in the first search Start_epoc and Stop_epoc. For each row as the first search will produce multiple rows, and i need the second search to produce the same amount.

Then i want to use them in the second search like below.

earliest=$Start_epoc$ latest=$Stop_epoc$.

| inputlookup Saved_Tests.csv 
| where Host="UBS-RC_QCST_MASTER" 
| where 1=1 
| search Dev_Optimization="*" 
| search Functional_Optimization="*" 
| eval Start_epoc=Start 
| eval Stop_epoc=Stop 
| convert ctime(Start) 
| convert ctime(Stop) 
| table ID, host,  Start_epoc , Stop_epoc 
| head 1001 
| sort 0 - by ID |  appendcols [| tstats count where index="mlc_live" host=UBS-RC_QCST_MASTER sourcetype="MX_TIMING2" earliest=$Start_epoc$ latest=$Stop_epoc$   by _indextime host 
| stats sum(count) as No_Of_MXTIMING_lines  by host 
| table No_Of_MXTIMING_lines ]

[somesoni2 · Accepted solution]

Considering sources for both your searches are faster (lookup table file and tstats query), you can use map command like this

| inputlookup Saved_Tests.csv 
 | where Host="UBS-RC_QCST_MASTER" 
 | where 1=1 
 | search Dev_Optimization="*" 
 | search Functional_Optimization="*" 
 | eval Start_epoc=Start 
 | eval Stop_epoc=Stop 
 | convert ctime(Start) 
 | convert ctime(Stop) 
 | table ID, host,  Start_epoc , Stop_epoc 
 | head 1001 
 |  map maxsearches=1001 search="| tstats count where index=mlc_live host=UBS-RC_QCST_MASTER sourcetype=MX_TIMING2 earliest=$Start_epoc$ latest=$Stop_epoc$   by _indextime host 
 | stats sum(count) as No_Of_MXTIMING_lines  by host | eval ID=\"$ID$\" | eval Start_epoch=$Start_epoc$ | eval Stop_epoc=$Stop_epoc$"
| sort 0 - by ID 
 |  table ID, host,  Start_epoc , Stop_epoc No_Of_MXTIMING_lines

[robertlynch2020]

Super super thanks so much 🙂

Also by changing the second search to tstats with out stats it goes much quicker

| map maxsearches=20 search="| tstats summariesonly=true count(MXTIMING.Elapsed) as No_Of_MXTIMING_lines FROM datamodel=MXTIMING_V7 WHERE
host=QCST_RSAT_40 earliest=$Start_epoc$ latest=$Stop_epoc$

[logloganathan]

I have modified the query as per your comment

Could you please try this query

| inputlookup Saved_Tests.csv
| where Host="UBS-RC_QCST_MASTER"
| where 1=1
| search Dev_Optimization=""
| search Functional_Optimization=""
| eval Start_epoc=Start
| eval Stop_epoc=Stop
| convert ctime(Start)
| convert ctime(Stop)
| table ID, host, Start_epoc , Stop_epoc
| head 1001
| sort 0 - by ID | join Start_epoc Stop_epoc [search | tstats count where index="mlc_live" host=UBS-RC_QCST_MASTER sourcetype="MX_TIMING2" earliest=Start_epoc latest=Stop_epoc by _indextime host
| stats sum(count) as No_Of_MXTIMING_lines by host
| table No_Of_MXTIMING_lines ]

[robertlynch2020]

hi

Sorry. No look there as

Invalid value "$Start_epoc$" for time term 'earliest'

I think as both searches run together i have to pass the earlist into the second search for each row.
THere migh be 100 rows, so i need the second search to run each time with different earliest=$Start_epoc$ latest=$Stop_epoc$ from the first search.

[logloganathan]

I have edited the answer as per your comment. Could you please try that query