How to achieve to _time filter with transpose?

[jip31]

hi

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

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?

[jip31]

Is anybody can help please?

[rnowitzki]

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

[jip31]

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

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?

[rnowitzki]

Ah ok, I understand now.

You could throw away the irrelevant hours:

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

[jip31]

any results with this

[rnowitzki · Accepted solution]

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

[jip31]

perfect thanks

[rnowitzki]

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

[jip31]

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