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!

The Splunk Success Framework: Your Guide to Successful Splunk Implementations

Splunk Lantern is a customer success center that provides advice from Splunk experts on valuable data ...

Splunk Training for All: Meet Aspiring Cybersecurity Analyst, Marc Alicea

Splunk Education believes in the value of training and certification in today’s rapidly-changing data-driven ...

Investigate Security and Threat Detection with VirusTotal and Splunk Integration

As security threats and their complexities surge, security analysts deal with increased challenges and ...