Splunk Search

Filtering a timechart on time to remove unwanted data

Shahindoh
Explorer

Hello,

I'm building some dashboard statistics from telecom data.

I have a data source as follows  :

_time

OfferedTime 

PickedUpTime

Offered="0/1"

Handled="0/1"

_time is populated with OfferedTime

 

User can use a Time picker that is generating a token.

I'm manipulating this token by going 5 days in the past for earliest and 5 days in the future for latest in dashboard to get a wider data set than the one selected by the user.

And then using variables in the search to restore time boundaries to initial selection that I use for some specific calculation (not shown in the code sample).

I'm trying to Timechart some metrics and to remove all data that is out of the time range initially selected by the user :

 

[MYSEARCH]

| addinfo
| eval end=if(info_max_time=="+Infinity",now(),info_max_time)-432000
| eval beginning=if(info_min_time=="0.000",1604260193,info_min_time)+432000

| eval DateBegin = beginning
| eval DateEnd = end
| eval FormatTime = _time

| timechart

count(eval(if(strptime(OfferedTime,"%Y-%m-%d %H:%M:%S.%Q") > beginning and strptime(OfferedTime,"%Y-%m-%d %H:%M:%S.%Q") < end,Offered,null()))) as OfferedCalls

count(eval(if(Handled="1" AND strptime(PickedUpTime,"%Y-%m-%d %H:%M:%S.%Q") > beginning and strptime(PickedUpTime,"%Y-%m-%d %H:%M:%S.%Q") < end AND BindType_ID!=4 AND BindType_ID!=5,Handled,null()))) as HandledCalls

| where _time > beginning and _time < end

 

I added DateBegin / DateEnd / FormatTime as I wanted to make sure in events tab that my dates had the correct format and could be compared.

_timeOfferedTimeDateBeginDateEndFormatTime
21/09/2021 18:24:54,0002021-09-21 18:24:54.01630926000.0001632223379.0001632241494.000


The result of this search is ... no results found.

If I go to events tab, copy the DateBegin and DateEnd and change my search to :

 

| where _time > 1630926000.000 and _time < 1632223379.000

 

It works fine and I get the expected result...

I don't understand why...

If I don't put where condition at the end I get this result :

_timeOfferedCallsHandledCalls
2021-09-0400
2021-09-0500
2021-09-06156115
2021-09-07215174
2021-09-08280217
2021-09-09227176
2021-09-10223184
2021-09-1100
2021-09-1200
2021-09-13336254
2021-09-14285220
2021-09-15228172
2021-09-16243177
2021-09-17273197
2021-09-1800


What I'm trying to get is :

 _timeOfferedCallsHandledCalls
2021-09-06156115
2021-09-07215174
2021-09-08280217
2021-09-09227176
2021-09-10223184
2021-09-1100
2021-09-1200
2021-09-13336254
2021-09-14285220
2021-09-15228172
2021-09-16243177
2021-09-17273197

 

Basically getting rid of the data before / after my date range (beginning / end) without losing the 0 values which are inside the time range.

I tried to play with various functions to replace 0 with NULL outside the range but couldn't manage to have this apply only outside my time range,.

If anybody has an idea on how to solve this issue that would be great.

Thanks in advance !

Labels (1)
0 Karma

ITWhisperer
Legend

timechart will use the earliest and latest time values from the search definition not the search results. The where command removes these extra results from the table generated by timechart.

0 Karma

Shahindoh
Explorer

Hi,

I'm not sure I agree with this, I think the Timechart is built out of the search result i.e. the table of results produced by the query.

The query itself will use the set of data covered by the earliest / latest set in the visualisation panel.

But controlling the content of this output table will control the drawing of the timechart.

I found the solution actually and this was about adding before my final WHERE condition the addinfo + the calculation of end / beginning again :

 

| addinfo
| eval end=if(info_max_time=="+Infinity",now(),info_max_time)-432000
| eval beginning=if(info_min_time=="0.000",1604260193,info_min_time)+432000

| where _time >= beginning and _time <= end
| fields _time Décrochés Reçus

 

0 Karma

ITWhisperer
Legend

Try this simple experiment

| gentimes start=-1 increment=1h 
| rename starttime as _time 
| streamstats count as row
| timechart values(row)

Now change the timerange on the search (which affects the earliest and latest values) and see how timechart generates different results depending on the earliest and latest, even though there are only 24 rows in the original query. The visualisation comes after the results are generated and should not be confused with the timechart command. 

0 Karma

Shahindoh
Explorer

Well,

I did your query with time range = beginning of the week and I get a lot of empty rows.

Then I modified your proposition with a WHEN condition on _time and this stripped all the unwanted values from the timechart (and I'm not talking about the visualisation) :

| gentimes start=-1 increment=1h 
| rename starttime as _time 
| streamstats count as row
| timechart values(row)
| where _time < strptime("2021-10-18 00:10:00","%Y-%m-%d %H:%M:%S.%Q")

So I'm not sure I get your point.

I know the timechart will be done on the whole time period, my question is exactly about removing data from a timechart result before visualising the results.

0 Karma

Richfez
SplunkTrust
SplunkTrust

Dumb question!

Is this right, or should the minus and plus be reversed?

| eval end=if(info_max_time=="+Infinity",now(),info_max_time)-432000
| eval beginning=if(info_min_time=="0.000",1604260193,info_min_time)+432000

Because a quick read of that seems to say you are taking the end and *subtracting* 432000 from it, and the beginning and *adding* 4320000 to it, then trying to find where _time was between those two.

I think it might need to be

| eval end=if(info_max_time=="+Infinity",now(),info_max_time)+432000
| eval beginning=if(info_min_time=="0.000",1604260193,info_min_time)-432000

Hopefully it's that simple of a facepalm, but let us know if it's not!

 

Also, you could try hardcoding some times in there for those to make sure it works right when you do that.  Might help narrow down where it might be going wrong.

0 Karma

Shahindoh
Explorer

Hello,

thanks for taking time to look at it.

It's actually a big dashboard where the users can select time range.

The goal of the stats then is to show number of calls arrived and also picked up within that range (among other metrics).

Issue is that _time used by the search is only based on time of arrival of the call.

So in the XML of the dashboard I'm doing+432000 to latest and -432000 to earliest stored in specific tokens that are being used by the search.

Then inside the search query I'm removing those offsets to reduce the time period to what the user initially selected for the calculation of the metrics.

 

So I'm volontarily taking a wider time period for the search so I can get for example calls that arrived before the time period selected but picked within the time period.

I actually changed a bit my query since then to do that in 2 steps with an appendcols in the middle, and having first query calculating offered calls doing a _time = OfferedTime and then a second one calculating picked up calls with a _time = PickedUpTime.

0 Karma
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!