Splunk Search

appendcols to take values from my first search for each row

robertlynch2020
Motivator

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
SplunkTrust
SplunkTrust

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
SplunkTrust
SplunkTrust

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
Motivator

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
Motivator

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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...