Splunk Enterprise

How to achieve to _time filter with transpose?

jip31
Motivator

hi

I use a search  thats transpose events with span of 30 m

jip31_0-1668696954731.png

the end of the search is this one

 

| where _time <= now() AND _time >= now()-14400 
| eval time=strftime(_time,"%H:%M") 
| sort time 
| fields - _time _span _origtime _events 
| fillnull value=0 
| transpose 0 header_field=time column_name=KPI include_empty=true 
| sort + KPI

 

as you can see, I just display events which exist in a specific time range

 

| where _time <= now() AND _time >= now()-14400 

 

It works fine but just when the timepicker choice is "today"

I would like to do the same think on previous timepicker choice like "last 7 days" or "last 30 days"

Could you help please?

Labels (1)
Tags (1)
0 Karma
1 Solution

rnowitzki
Builder

You mean no results?

Maybe you used it "too late". As we use _time to retrieve the "hour", the _time field of course still has to be in the resultset.  

Try to use it in this location of the SPL:

 `index_mes` sourcetype="web_request"  
| timechart span=30m count as "6 - CP - Nombre de temps de réponse > 10 sec"
| eval time=strftime(_time,"%d-%m %H:%M") 
| eval hour=strftime(_time, "%H")
| where hour>=6 AND hour<=18
| sort time 
| fields - _time _span _origtime _events hour
| fillnull value=0 
| transpose 0 header_field=time column_name=KPI include_empty=true 
| sort + KPI
--
Karma and/or Solution tagging appreciated.

View solution in original post

0 Karma

jip31
Motivator

Is anybody can help please?

0 Karma

rnowitzki
Builder

Hi @jip31 ,

Can you please give more details about your use case?

I tested your SPL and it works in general. It gets into troubles when you set the time picker to several days. 
One limitation are the sort commands. (sort 0 time might help). 

But in general I don't see a reason why you'd select events of 7d and then limit in in the search to 4 hours.
Is there a reason to limit time later, instead of using the time picker?

Ralph

--
Karma and/or Solution tagging appreciated.
0 Karma

jip31
Motivator

Hi

Until now, I just was using this search for "Today" time range

Now I need to see the results on the period selected in the timepicker

Contrary to I said at the beginning, if I chose "Last 7days" for example, I can see all the results for this period

I have just replaced 

| eval time=strftime(_time,"%H:%M") 

by 

| eval time=strftime(_time,"%d-%m %H:%M") 

in order to see not only the hour but also the day concernend

So it gives me this

jip31_0-1669037065350.png

Now the last thing I want to do is to not display the events between 19:00 PM and 6 AM

It means I just need to display the events between 6:AM and 19:00 PM

Have you an idea please for doing this?

 

0 Karma

rnowitzki
Builder

Ah ok, I understand now.

You could throw away the irrelevant hours:

| eval hour=strftime(_time, "%H")
| where hour>=6 AND hour<=18



--
Karma and/or Solution tagging appreciated.
0 Karma

jip31
Motivator

any results with this

0 Karma

rnowitzki
Builder

You mean no results?

Maybe you used it "too late". As we use _time to retrieve the "hour", the _time field of course still has to be in the resultset.  

Try to use it in this location of the SPL:

 `index_mes` sourcetype="web_request"  
| timechart span=30m count as "6 - CP - Nombre de temps de réponse > 10 sec"
| eval time=strftime(_time,"%d-%m %H:%M") 
| eval hour=strftime(_time, "%H")
| where hour>=6 AND hour<=18
| sort time 
| fields - _time _span _origtime _events hour
| fillnull value=0 
| transpose 0 header_field=time column_name=KPI include_empty=true 
| sort + KPI
--
Karma and/or Solution tagging appreciated.
0 Karma

jip31
Motivator

perfect thanks

0 Karma

rnowitzki
Builder

Hi @jip31,

It seems to work in general for me, independent of the timepicker setting (well, if you select less than 4 hours it will only show you events from the selected range or course).

Can you show the first part of the search? Is there a timechart or something that groups by 30 min? 
Because when I use the given part of the search I get columns for each minute.

Is there a reason why you filter the time range in the SPL instead of selecting  e.g. "last 4 hours"?

Ralph

 

 

--
Karma and/or Solution tagging appreciated.
0 Karma

jip31
Motivator

Hi

Yes there is a timechart

 

 `index_mes` sourcetype="web_request"  
| timechart span=30m count as "6 - CP - Nombre de temps de réponse > 10 sec" 
  ] 
| where _time <= now() AND _time >= now()-14400 
| eval time=strftime(_time,"%H:%M") 
| sort time 
| fields - _time _span _origtime _events 
| fillnull value=0 
| transpose 0 header_field=time column_name=KPI include_empty=true 
| sort + KPI
0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...