topic Re: Looking for multiple results in query in Splunk Search

Looking for multiple results in query

pshangguan — Tue, 29 Sep 2020 21:41:15 GMT

I have the following query I use to get the latest status and time(_time).

index=jenkins |spath job_name | search job_name="job/utl-dataflow-check-TST6/" | sort -_time | stats latest(job_result) as status, latest(_time) as tst6t

Now, I want to get the time(_time) of the job that run successfully -> job_result=SUCCESS. I wanyt to seew if I can get those two times from the same query and display them inside the same dashboard panel. I tried different ways and could not get it working.

Thanks!

Re: Looking for multiple results in query

Vijeta — Tue, 29 Sep 2020 21:41:18 GMT

Try this :-

ME TOO
I have the following query I use to get the latest status and time(_time).

‘index=jenkins |spath job_name | search job_name="job/utl-dataflow-check-TST6/" | sort -_time | stats latest(job_result) as status, latest(eval(job_result=“SUCCESS”)) ,latest(_time) as tst6t`

Re: Looking for multiple results in query

pshangguan — Tue, 29 Sep 2020 21:41:21 GMT

I changed it to:

stats latest(job_result) as dev2status, latest(_time=if(eval(job_result=“SUCCESS”))) as dev2st, latest(_time) as dev2t

It did not pickup the _time for the latest successful job run...

Re: Looking for multiple results in query

Vijeta — Mon, 15 Oct 2018 19:18:08 GMT

Can you do this and see if it suffices your requirement-

stats  latest(_time) as tst6t` by job_result

Re: Looking for multiple results in query

pshangguan — Tue, 29 Sep 2020 21:41:24 GMT

I used "latest(_time) as dev2st by job_result" in the stats command and the it did not get anything. I am getting "no results found" in the panel.

Re: Looking for multiple results in query

Vijeta — Mon, 15 Oct 2018 21:42:39 GMT

Are you using this query?

    index=jenkins |spath job_name | search job_name="job/utl-dataflow-check-TST6/" | stats latest(_time) as tst6t by job_result

Re: Looking for multiple results in query

pshangguan — Tue, 29 Sep 2020 21:41:26 GMT

index=jenkins |spath job_name | search job_name="job/utl-dataflow-check-DEV2/" | sort -_time | stats latest(job_result) as dev2status, latest(_time) as dev2st by job_result, latest(_time) as dev2t

Re: Looking for multiple results in query

Vijeta — Mon, 15 Oct 2018 22:07:37 GMT

Please try with the above query in my comments

Re: Looking for multiple results in query

pshangguan — Mon, 15 Oct 2018 22:25:26 GMT

How can I get two time values? one for the latest run, and one for the success run in your query?

Re: Looking for multiple results in query

Vijeta — Mon, 15 Oct 2018 22:35:03 GMT

you will get the latest time for each unique value in job_result. Once you get that you need to sort - dev2t. This will give you the latest job result name and also the row with value SUCCESS will give you latest time for success.

Can you paste your results here with the above query

Re: Looking for multiple results in query

pshangguan — Tue, 16 Oct 2018 16:44:58 GMT

I got two results:

job_result . tst6t
FAILURE . 1539707765.083
SUCCESS . 1539704175.318

Re: Looking for multiple results in query

Vijeta — Tue, 16 Oct 2018 16:52:50 GMT

This gives you the latest time of Failure and Success and if you sort - tst6t, it will give you the latest event among the 2 events.
Also you can convert the time in yyyy/mm/dd format using below command at end of yiur query

| eval tst6t = strftime(tst6t,"%Y/%m/%d %H:%M:%S")

Re: Looking for multiple results in query

pshangguan — Tue, 16 Oct 2018 17:47:31 GMT

tst6t only have the two times, how do i know which one is for "SUCCESS" and whicch one is for "FAILURE"?

Re: Looking for multiple results in query

Vijeta — Tue, 16 Oct 2018 18:11:42 GMT

You have the job_result column in your output against the time .

Re: Looking for multiple results in query

pshangguan — Thu, 18 Oct 2018 16:45:34 GMT

sorry i am not sure how to retrieve them individually as i am new to splunk and xml 🙂

Re: Looking for multiple results in query

Vijeta — Thu, 18 Oct 2018 16:51:53 GMT

I am not sure what your end goal is , the output you have is which gives you latest time of each job_result. Please specify what you need to do with this data

job_result . tst6t
FAILURE . 1539707765.083
SUCCESS . 1539704175.318

Re: Looking for multiple results in query

pshangguan — Thu, 18 Oct 2018 16:56:49 GMT

I want to display the last run time in the panel title field, and the last success run time in the single value title field.

Re: Looking for multiple results in query

pshangguan — Tue, 29 Sep 2020 21:42:53 GMT

In another word, I want to do something like:

tst6t_success_time and tst6t_failure_time are from tst6t. No idea how to pick them up from tst6t...