topic Re: How to write subquery to run the sub query for timings different from dashboard timings in Splunk Search

How to write subquery to run the sub query for timings different from dashboard timings

SG — Tue, 18 May 2021 12:06:38 GMT

Hi,

We need help in drawing the trend for multiple timings in the splunk.

Below is my query -

index=nextgen sourcetype=lighthouse_json datasource=webpagetest step="Homepage"

| timechart span=1h list(speedindex) as "speedindex_latest"

| fieldformat _time=strftime(_time,"%D:%I:%M %p")

| table _time speedindex_latest

| appendcols

[search index=nextgen sourcetype=lighthouse_json datasource=webpagetest step="Homepage"

| timechart span=1h list(speedindex) as "speedindex_notlatest"

| fieldformat _time=strftime(_time,"%D:%I:%M %p")

| table _time speedindex_notlatest]

In the dashboard if i give timing as 24hrs speedindex_latest should give me the data for last 24hrs, i am able to see that data now. And speedindex_notlatest column should give me the data for previous 24hrs data.

For example -

speedindex_latest - Last 24Hrs data

speedindex_notlatest -

24*2 = 48hrs

from 48hrs i need to subtract dashboard timing data and give me the data for 24hrs to 48hrs data.

In this way i will be able to overlap the details and check how the performance is.

Can someone please help me how i can resolve this?

Re: How to write subquery to run the sub query for timings different from dashboard timings

gcusello — Tue, 18 May 2021 12:15:08 GMT

Hi @SG,

try a different approach classifying events using eval, something like this:

index=nextgen sourcetype=lighthouse_json datasource=webpagetest step="Homepage" earliest=-24h | eval my_span=if(_time>now()-86400,"speedindex_latest","speedindex_notlatest") | timechart span=1h values(speedindex) AS speedindex BY my_span

Ciao.

Giuseppe

Re: How to write subquery to run the sub query for timings different from dashboard timings

SG — Tue, 18 May 2021 12:21:06 GMT

HI, Thanks for your response. In the query you posted, i could see that "earliest=-24h" time is fixed. I have a dashboard in that dashboard i have many panels. All the panels in the dashbord will get time details globally what is mentioned in the top of the dashboard. In the same way this query also i wanted to work on dashboard timings. For example if i run dashboard for last 15min, i should get latest data for 15 min and that latest data should overlap with last 30min to 15min.

Re: How to write subquery to run the sub query for timings different from dashboard timings

gcusello — Tue, 18 May 2021 12:31:24 GMT

Hi @SG,

in this case, explore the timewrap command that should solve your need https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Timewrap

Ciao.

Giuseppe

Re: How to write subquery to run the sub query for timings different from dashboard timings

SG — Thu, 20 May 2021 11:30:22 GMT

I edited my query to get the dashboard timings and eval the previous hrs timings and run the subsearch as per the evaluated timings. Below is the query, but i am getting error while running the query and it is not giving any results

index=nextgen sourcetype=lighthouse_json datasource=webpagetest step="Homepage" | eval DiffTime=latest-earliest | eval BeginTime=earliest-DiffTime-604800 | eval CompleteTime=latest-604800 | timechart span=1h list(speedindex) as "speedindex_latest" | fieldformat _time=strftime(_time,"%I:%M %p") | table _time speedindex_latest | appendcols [search index=nextgen sourcetype=lighthouse_json datasource=webpagetest step="Homepage" earliest=-$BeginTime$ latest=-$CompleteTime$ | timechart span=1h list(speedindex) as "speedindex_previous" | fieldformat _time=strftime(_time,"%I:%M %p") | table _time speedindex_previous]

Error details -

Invalid value "-$BeginTime$" for time term 'earliest' The search job has failed due to an error. You may be able view the job in the Job Inspector.

Re: How to write subquery to run the sub query for timings different from dashboard timings

gcusello — Thu, 20 May 2021 15:01:47 GMT

Hi @SG,

earliest and latest probably are in format "-24h" and to run a a difference in eval command you need two epochtimes.

Try to add addinfo:

index=nextgen sourcetype=lighthouse_json datasource=webpagetest step="Homepage" | addinfo | eval DiffTime=info_max_time-info_min_time | eval BeginTime=info_min_time-DiffTime-604800 | eval CompleteTime=info_max_time-604800 | timechart span=1h list(speedindex) as "speedindex_latest" | fieldformat _time=strftime(_time,"%I:%M %p") | table _time speedindex_latest | appendcols [search index=nextgen sourcetype=lighthouse_json datasource=webpagetest step="Homepage" earliest=-$BeginTime$ latest=-$CompleteTime$ | timechart span=1h list(speedindex) as "speedindex_previous" | fieldformat _time=strftime(_time,"%I:%M %p") | table _time speedindex_previous]

Ciao.

Giuseppe

Re: How to write subquery to run the sub query for timings different from dashboard timings

SG — Fri, 21 May 2021 00:53:56 GMT

Hi @gcusello ,

I validated the query provided by you by adding "addinfo", still, I am getting the same error. Attached the error screenshot.

Re: How to write subquery to run the sub query for timings different from dashboard timings

gcusello — Fri, 21 May 2021 06:04:47 GMT

Hi @SG,

you cannot pass a token from he main search to the subsearch, you have to find a different approach: did you explored the timewrap command, as I hinted?

probably you can have the same result, something like this:

your_search | timechart span=1h list(speedindex) AS speedindex | timewrap 24h

anyway, if you want to continue with your approach, you can use your main search as subsearch (subsearches can pass tokens to main searches) or try something like this:

I don't like your approach because if your have more than 50,000 results in subsearch you could have errors and it's slower.

Then beware to use the "list" option: if you have many results, your dashbo0ard could be unreadable!

Maybe "values" could be better.

Ciao.

Giuseppe

Re: How to write subquery to run the sub query for timings different from dashboard timings

SG — Fri, 21 May 2021 11:43:04 GMT

Hi @gcusello ,

Thanks for your continuous suggestions and help in resolving my Splunk querying issues.

I cannot use "timewrap" option in my query as I don't want to wrap the results either with hrs/days/weeks/Months.

Based on the timings given by uses in the dashboard i wanted to give a comparison. For example, if user selects the last 4hrs I wanted to show data for the last 4hrs in "speedindex_latest" series and the last 8hrs to 4hrs data as "speedindex_previos" series.

The below query is giving me the expected data.

I understood that by using "makeresults" we are creating dummy timings like "difftime", "earliest", "latest" in the query. and by adding "addinfo " we are getting "info_max_time" and "info_min_time".

One thing I didn't understand is by giving "fields earliest latest " how come the derived timings are passed to the subquery? Can you please explain to me that?

Also, when i use this query for last 4hrs/24hrs it is giving me proper data. But when i run for last 15min it is not calculating the 30 to 15min timing and giving me the data, "speedindex_previous" column itself not coming in the results. Can i know the reason please?

index=nextgen sourcetype=lighthouse_json datasource=webpagetest step="Homepage" | timechart span=1h list(speedindex) AS speedindex_latest | appendcols [ search index=nextgen sourcetype=lighthouse_json datasource=webpagetest step="Homepage" [ | makeresults | addinfo | eval DiffTime=info_max_time-info_min_time | eval earliest=info_min_time-DiffTime | eval latest=info_min_time | fields earliest latest ] | timechart span=1h list(speedindex) AS speedindex_previous ]

Since I don't require week wise comparison I removed below from the query.

604800

Re: How to write subquery to run the sub query for timings different from dashboard timings

gcusello — Fri, 21 May 2021 11:55:20 GMT

Hi @SG,

to define the time limits of a search you can use the Time Picker (if you don't use any value) or you can declare a value for these tokens,

But the problem is that you cannot make an elaboration to modyfy the values in the main search that you have from the Time Picker.

A simple way to do this is the one I used:

I create a record with makesults,
I elaborate the values as your search,
and I pass the earliest and latest tokens to the other search.

Ciao.

Giuseppe

Re: How to write subquery to run the sub query for timings different from dashboard timings

SG — Mon, 24 May 2021 05:58:12 GMT

HI @gcusello , I am able to get the data for below query for last 60min and last 4hrs. When i select last 24hrs I am getting only "speedindex_latest" but not "speedindex_previous" series values.

Below is the screenshot for the same, can you please let me know what is the problem with the last 24hrs?

Re: How to write subquery to run the sub query for timings different from dashboard timings

gcusello — Mon, 24 May 2021 07:07:03 GMT

Hi @SG,

in this way, you take the events only in the last 24 hours so the solution with makersults is ineffient, you have to modify time modifiers in the subsearch to take the previous 24 hours (e.g. subtracting 86400 seconds to earliest and latest), something like this:

index=nextgen sourcetype=lighthouse_json datasource=webpagetest step="Homepage" | timechart span=1h list(speedindex) AS speedindex_latest | appendcols [ search index=nextgen sourcetype=lighthouse_json datasource=webpagetest step="Homepage" [ | makeresults | addinfo | eval DiffTime=info_max_time-info_min_time | eval earliest=info_min_time-DiffTime-86400 | eval latest=info_min_time-86400 | fields earliest latest ] | timechart span=1h list(speedindex) AS speedindex_previous ]

Ciao.

Giuseppe

Re: How to write subquery to run the sub query for timings different from dashboard timings

SG — Mon, 24 May 2021 08:39:46 GMT

Yes, this helps. Thanks