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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...