topic Re: appendcols to take values from my first search for each row in Splunk Search

appendcols to take values from my first search for each row

robertlynch2020 — Tue, 29 Sep 2020 18:47:50 GMT

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 ]

Re: appendcols to take values from my first search for each row

logloganathan — Tue, 29 Sep 2020 18:47:57 GMT

I have modified the query as per your comment

Could you please try this query

Re: appendcols to take values from my first search for each row

robertlynch2020 — Tue, 29 Sep 2020 18:48:29 GMT

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.

Re: appendcols to take values from my first search for each row

logloganathan — Thu, 29 Mar 2018 08:54:57 GMT

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

Re: appendcols to take values from my first search for each row

somesoni2 — Thu, 29 Mar 2018 09:31:53 GMT

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

Re: appendcols to take values from my first search for each row

robertlynch2020 — Tue, 29 Sep 2020 18:53:04 GMT

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$