Splunk Search

timechart not using correct _time timeframe

weidertc
Communicator

I have a dashboard with 2 columns of panels, each containing the same 5 panels, 5 on the left and 5 on the right. the difference between the columns is the timeframe the user chose. the right is the current, and the left is 2 weeks ago. I want to use base search for both so I only need one cpu thread to power the dashboard.

here is my query

index=asdf this=that earliest=-60m@m latest=+1m@m
| eval timeframe=1
| append
    [ search index=asdf this=that earliest=-2w@m-60m latest=-2w@m+1m
    | eval timeframe=2 ]
| where timeframe=2
| timechart count

The problem is the | where timeframe=2.

If i put | where timeframe=1, then the _time column in the timechart displays 60 minutes' worth, so 60 rows, of the timeframe from the outer search. this is good.

_time                       count
8/23/2019 00:00             32
[...]                 
8/23/2019 01:00             45

If i put | where timeframe=2, then the _time column in the timechart displays 120 minutes' worth, so 120 rows, of both timeframe 1 and 2. The 2nd half of the timeframe is empty, but the _time still shows it.

_time                       count
8/09/2019 00:00             16
[...]                 
8/09/2019 01:00             22
8/23/2019 00:00
[...]                 
8/23/2019 01:00

I want to avoid having to run 5 inline searches plus a base search for the other 5. i want 1 base search for both sides.

How do i get the _time of "only" timeframe=2 in my chart?

Tags (2)
0 Karma
1 Solution

weidertc
Communicator

Adding "fixedrange=false" accomplishes what cont=false does, but allows for the x-axis labels to remain intact. This restricts the _time to just what the where clause dictates, but keeps the date/time and approximate hour at beginning, end, and a few in between present.

index=asdf this=that earliest=-60m@m latest=+1m@m
| eval timeframe=1
| append
    [ search index=asdf this=that earliest=-2w@m-60m latest=-2w@m+1m
    | eval timeframe=2 ]
| where timeframe=2
| timechart fixedrange=false count

View solution in original post

weidertc
Communicator

Adding "fixedrange=false" accomplishes what cont=false does, but allows for the x-axis labels to remain intact. This restricts the _time to just what the where clause dictates, but keeps the date/time and approximate hour at beginning, end, and a few in between present.

index=asdf this=that earliest=-60m@m latest=+1m@m
| eval timeframe=1
| append
    [ search index=asdf this=that earliest=-2w@m-60m latest=-2w@m+1m
    | eval timeframe=2 ]
| where timeframe=2
| timechart fixedrange=false count

Sukisen1981
Champion

hi @weidertc
I don't think I deserve any credit for the solution at all, it was you who solved your issue yourself 🙂 so kudos for that,hence no answers (wrong answers 🙂 ???) from my side.
You should convert your comment o an answer and accept it , I will up vote it.
I am sorry for any confusion caused, I tried to the best of my knowledge and ability

0 Karma

weidertc
Communicator

thanks, i converted this to an answer. no confusion caused.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

By default, timechart fills in missing data points, which is how you end up with rows for today as well those for 2 weeks ago. Use the cont=false option to disable that.

---
If this reply helps you, Karma would be appreciated.

weidertc
Communicator

Interesting. So the where clause doesn't remove the records, just the data in them. That is really unfortunate, and borderline ridiculous, since the where clause removes the whole records in every other query I write, not just the data in them.

I tried cont=false, and while it does remove those records from showing, it also removes the x-axis major label, (the date/year and approximate time of day), so I can no longer tell what hour is at the beginning, middle, and end.

Is there a way to keep the labels?

0 Karma

Sukisen1981
Champion

hi @weidertc
Sorry, I did not explain clearly
Let us look at your query-
index=asdf this=that earliest=-60m@m latest=+1m@m
| eval timeframe=1
| append
[ search index=asdf this=that earliest=-2w@m-60m latest=-2w@m+1m
| eval timeframe=2 ]
| where timeframe=2
| timechart count
Now, how does this query get executed?
1- the sub search, that is the append gets executed first and sets timeframe=2
2- Now, the main search gets executed. Here irrespective of what you set the value timeframe as, in the append sub search, you over write this by setting timeframe as 1
3- When i ran this query on the default audit index, i received exactly what i expected - no results found because timeframe is ALWAYS =1 , irrespective of what you set in the append sub search. Try this query as is , since _audit is a delivered index it should run as it is. something like this - index=_audit earliest=-60m@m latest=+1m@m
| eval timeframe=1
| append
[ search index=audit earliest=-2w@m-60m latest=-2w@m+1m
| eval timeframe=2 ]
| where timeframe=2
| timechart count

Execute this first with |where timeframe=2 and then with |where timeframe=1, see the difference? Also, let timeframe remain as 2 in the sub search but set eval timeframe=2 in the main search, you will get results...

What I am not getting is how you get an output when timeframe=2 , it should not ideally return any results @niketn can you please advise? I must confess that I am not able to understand the this=that part of the query. As per me the query should not return any results and will ALWAYS return results based on the value of timeframe as set in the main query

0 Karma

weidertc
Communicator

I tried this search, but access is whitelisted and I only have a few indexes I can use. This returned no results, (even after fixing what may be a typo with the underscore).

the "this=that" is a replacement for my actual filters, which wouldn't make sense to anyone outside of our organization. I simplified it considerably to exaggerate the framework of the query and isolate the part that may need to change.

I would also like to know why i get any results at all if the outer query value of timeframe overwrites the inner one. I didn't think they would since they are only set for the contents of the records which that search returns, of which none overlap due to the time difference.

Maybe "append" just isn't the right command here. I just need 2 (or more) sets of results from different weeks.

0 Karma

Sukisen1981
Champion

to second @richgalloway 's comment
@weidertc have you checked that individually both the timecharts give outputs (counts), i ran your query and tinkered with the time modifiers, it is highly probable that your second time modifiers , inside the append is not returning any results. In this case you do a cont=f or fillnull value=0
the issue is not with the timechart but with the modifiers and the respective counts generated by the timechart using those modifiers

0 Karma

weidertc
Communicator

Thanks for the insight.

I checked both for values present. In this case, there are records in both. The where clause successfully hides the records in the other week. The cont=f does actually work, but not without suppressing the x-axis major label (the date/year and approximate time of day), which is not really going to work that well.

What do you mean by modifiers? do you mean the where clause?

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 ...