Splunk Search

appendcols to take values from my first search for each row

robertlynch2020
Influencer

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 ]
1 Solution

somesoni2
Revered Legend

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

View solution in original post

somesoni2
Revered Legend

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
Influencer

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$

0 Karma

logloganathan
Motivator

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 ]

0 Karma

robertlynch2020
Influencer

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.

0 Karma

logloganathan
Motivator

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

0 Karma
Get Updates on the Splunk Community!

Say goodbye to manually analyzing phishing and malware threats with Splunk Attack ...

In today’s evolving threat landscape, we understand you’re constantly bombarded with phishing and malware ...

AppDynamics is now part of Splunk Ideas

Hello Splunkers, We have exciting news for you! AppDynamics has been added to the Splunk Ideas Portal. Which ...

Advanced Splunk Data Management Strategies

Join us on Wednesday, May 14, 2025, at 11 AM PDT / 2 PM EDT for an exclusive Tech Talk that delves into ...