Hi,
I have a dashboard where I have a drop down which returns me a string. The xml of the drop down is below:
<input type="dropdown" token="jobID" searchWhenChanged="true">
<label>JOB ID II</label>
<fieldForLabel>JOB-ID-WITH-TIME</fieldForLabel>
<fieldForValue>JOB-ID-WITH-TIME</fieldForValue>
<search>
<query>index=test sourcetype="testabc"
| rename sre_job_id as JOB_ID
| stats earliest(_time) AS Earliest by JOB_ID
| eval FirstEvent=strftime(Earliest,"%b %d %Y, %H:%M:%S")
| eval JOB_ID_STR=tostring(JOB_ID)
| eval JOB-ID-WITH-TIME=JOB_ID + "-" + FirstEvent
| table JOB-ID-WITH-TIME
| dedup JOB-ID-WITH-TIME
| sort JOB-ID-WITH-TIME</query>
<earliest>$timeToken.earliest$</earliest>
<latest>$timeToken.latest$</latest>
</search>
</input>
This drop down returns me data something like: 6802-Jul 20 2020, 10:41:14 (an ID with a date separated by a dash).
Now, I have a tabular report where I want to use just the ID (6802) in the search for the report to show data. The xml for the tabular report is below:
<table>
<title>TOTAL TIME for JOB $jobID$</title>
<search>
<query>index=test sourcetype="testabc" sre_job_id=$jobID$ Perf_Type=Perf*
| stats sum(Time_Taken) as NetTime_secs by Perf_Type
| eval NetTime_mins=(NetTime_secs/60)
| table Perf_Type, NetTime_mins
| sort -Perf_Type
<earliest>0</earliest>
<latest></latest>
The problem with the above query is sre_job_id=$jobID$ gets the whole value 6802-Jul 20 2020, 10:41:14 and hence do not shown any results, whereas it expects only 6802 to show some data.
I tried to eval the token and split the token data but not able to use the split data into my search. This is what I have tried:
index=test sourcetype="testabc" Perf_Type=Perf*
[| makeresults | eval test="6802-Jul 20 2020, 10:41:14" | eval results=split(test,"-") | eval job_id=mvindex(results,0)] sre_job_id=job_id.
For now tried hard coding the value, but no luck.
Could someone please help on this. I hope the question is clear.
Thanks in advance for your time.
Hi @subhrangshu
The subsearch part of the query - the bit between the square brackets [ ... ] - is run first and the results simply inserted into the base search as if it was part of the initial base search.
You can test the subsearch in a separate search to see what I mean.
| makeresults | eval sre_job_id=replace("6720-Aug 04 2020, 16:05:04", "-.*", "") | return sre_job_id
Result:
sre_job_id="6720"
So drop the extra sre_job_id=sre_job_id from your base search as it is not needed and means no results are being returned from your search query.
index=test sourcetype="testabc" Perf_Type=Perf* [ | makeresults | eval sre_job_id=replace("6720-Aug 04 2020, 16:05:04", "-.*", "") | return sre_job_id ]
Splunk will translates this into a base search that looks like this:
index=test sourcetype="testabc" Perf_Type=Perf* sre_job_id="6720" | ...
Hope that makes some sense.
Hi @subhrangshu
Looks like you were getting pretty close. Try this...
index=test sourcetype="testabc" Perf_Type=Perf* [ | makeresults | eval sre_job_id=replace("$jobID$", "-.*", "") | return sre_job_id ] | ...
Hope that helps
Thanks a lot for your reply @yeahnah
So, as per your suggestion my search query looks like this:
index=test sourcetype="testabc" [ | makeresults | eval sre_job_id=replace("6720-Aug 04 2020, 16:05:04", "-.*", "") | return sre_job_id ] sre_job_id=sre_job_id Perf_Type=Perf*
But this query returns me no result. But if I use this below search instead, I get data back:
index=test sourcetype="testabc" sre_job_id=6720 Perf_Type=Perf*
6720-Aug 04 2020, 16:05:04: is the value that I got from drop down and was passed upon to this tabular report query from the token.
Am I missing something here or doing anything wrong. Thanks again.
Hi @subhrangshu
The subsearch part of the query - the bit between the square brackets [ ... ] - is run first and the results simply inserted into the base search as if it was part of the initial base search.
You can test the subsearch in a separate search to see what I mean.
| makeresults | eval sre_job_id=replace("6720-Aug 04 2020, 16:05:04", "-.*", "") | return sre_job_id
Result:
sre_job_id="6720"
So drop the extra sre_job_id=sre_job_id from your base search as it is not needed and means no results are being returned from your search query.
index=test sourcetype="testabc" Perf_Type=Perf* [ | makeresults | eval sre_job_id=replace("6720-Aug 04 2020, 16:05:04", "-.*", "") | return sre_job_id ]
Splunk will translates this into a base search that looks like this:
index=test sourcetype="testabc" Perf_Type=Perf* sre_job_id="6720" | ...
Hope that makes some sense.
@yeahnah Awesome explanation. Thanks for the help 😀