Getting Data In

Splunk 7.0.0 - Metrics Index - Filter on hour and weekday

RasmusToelhoej
Explorer

Hi
We are trying out the new Metrics Index in Splunk 7.0 and ran into issues when filtering on the data.
We want to only report the values inside business hours (Mon-Fri 7-18) for monthly reporting.

Our old search in the Events type Index looked like this:
index="response" (date_hour>=7 AND date_hour<=18 AND NOT date_wday=sunday AND NOT date_wday=saturday) | stats count, avg(time), perc95(time) by app

We can query the data but can't filter on hour or weekday, is there any possibilities to do this in the Metrics Index with mstats?
Or can you only see all data in a selected time period?
| mstats avg(_value), perc95(_value) as p95, count WHERE index=mcg_apm_response_metrics AND metric_name=apm.response by app

One work-around could be to add a dimension that was named Inside/Outside business hours, but then it would be impossible to change afterwards.

1 Solution

rjthibod
Champion

Per usual, everything @guilxmx is on point. Still wanted to offer something else I figured out.

Basically, you don't have things like date_hour and date_wday in the default Metrics Indexes because you are likely using the default Metrics sourcetypes (stats or collectd) or you have a custom sourcetype that has ADD_EXTRA_TIME_FIELDS = false in props.conf.

If you create a new sourcetype (a custom one) where you define the metrics fields and you set ADD_EXTRA_TIME_FIELDS = true in props.conf, you will see the additional time fields.

From a licensing standpoint, this shouldn't matter because Metrics Indexes only charge by the event count, not the size of the event. Still, there may be some performance hit as @guilmxm suggested (i.e., you are increasing the cardinality of the Metrics Index), so its worth experimenting with or getting feedback from Splunk before moving forward.

View solution in original post

0 Karma

rjthibod
Champion

Per usual, everything @guilxmx is on point. Still wanted to offer something else I figured out.

Basically, you don't have things like date_hour and date_wday in the default Metrics Indexes because you are likely using the default Metrics sourcetypes (stats or collectd) or you have a custom sourcetype that has ADD_EXTRA_TIME_FIELDS = false in props.conf.

If you create a new sourcetype (a custom one) where you define the metrics fields and you set ADD_EXTRA_TIME_FIELDS = true in props.conf, you will see the additional time fields.

From a licensing standpoint, this shouldn't matter because Metrics Indexes only charge by the event count, not the size of the event. Still, there may be some performance hit as @guilmxm suggested (i.e., you are increasing the cardinality of the Metrics Index), so its worth experimenting with or getting feedback from Splunk before moving forward.

0 Karma

RasmusToelhoej
Explorer

@rjthibod that would be really cool if it's possible to do.

Have tried to create my own custom sourcetype, but how can I search in the metrics index for date_hour and date_wday, all my searches returns empty datasets when including hour?

For example: | mstats avg(_value), perc95(_value), count WHERE index=mcg_apm_response_metrics AND metric_name=apm.* AND date_hour>2 by app

| mstats avg(_value), perc95(_value), count WHERE index=mcg_apm_response_metrics AND metric_name=apm.* by app, date_hour

My sourcetype:
[mcg_apm_metrics]
ADD_EXTRA_TIME_FIELDS = true
DATETIME_CONFIG =
NO_BINARY_CHECK = true
category = Metrics
description = Metrics Index Data with Time Fields
pulldown_type = 1

0 Karma

RasmusToelhoej
Explorer

Got it to work, it only works when you index CSV data, not when you use the HTTP Event collector. Will create other question about that.

For those interested, using ADD_EXTRA_TIME_FIELDS add the fields as dimensions in the Metrics Index, so it's properly not an god idea, as it will give overhead to number of dimensions/cardinality, have to test it with a large number of results with different values to see the performance/memory overhead.

CSV file:
metric_timestamp,metric_name,_value
1508756758.000,Test,0.50
1508756758.000,Test2,1.50

Props:
[mcg_apm_metrics_csv]
ADD_EXTRA_TIME_FIELDS = True
DATETIME_CONFIG =
INDEXED_EXTRACTIONS = csv
KV_MODE = none
NO_BINARY_CHECK = true
SHOULD_LINEMERGE = false
TIMESTAMP_FIELDS = metric_timestamp
TIME_FORMAT = %s.%Q
category = Metrics
description = Comma-separated value format for metrics. Must have metric_timestamp, metric_name, and _value fields.
disabled = false
pulldown_type = 1

Used this command to see what was stored in the Splunk Index: c:\Program Files\Splunk\bin>splunk cmd walklex "C:\Program Files\Splunk\var\lib\splunk\mcg_apm_response_metrics_hour\db\hot_v1_1\1507908525-1507598431-3077548847437235074.tsidx" ""

my needle:
0 2 host::rt-laptop
1 1 metric_name::Test
2 1 metric_name::Test2
3 2 source::metrics_data.txt
4 2 sourcetype::mcg_apm_metrics_csv
5 1 _catalog::Test2|date_hour|date_mday|date_minute|date_month|date_second|date_wday|date_year|date_zone
6 1 _catalog::Test|date_hour|date_mday|date_minute|date_month|date_second|date_wday|date_year|date_zone
7 2 _dims::date_hour
8 2 _dims::date_mday
9 2 _dims::date_minute
10 2 _dims::date_month
11 2 _dims::date_second
12 2 _dims::date_wday
13 2 _dims::date_year
14 2 _dims::date_zone
15 2 _subsecond::.000
16 2 date_hour::11
17 2 date_mday::23
18 2 date_minute::5
19 2 date_month::october
20 2 date_second::58
21 2 date_wday::monday
22 2 date_year::2017
23 2 date_zone::0
24 2 host::rt-laptop
25 1 metric_name::test
26 1 metric_name::test2
27 2 source::metrics_data.txt
28 2 sourcetype::mcg_apm_metrics_csv

0 Karma

DATEVeG
Path Finder

For me this doesn't work.
I'm using a Summary-Search with | mcollect

I even tried to explicitly specify the fields:

index=_internal source=*license_usage.log type="Usage" 
| eval h=if(len(h)=0 OR isnull(h),"(SQUASHED)",h) 
| eval s=if(len(s)=0 OR isnull(s),"(SQUASHED)",s) 
| eval idx=if(len(idx)=0 OR isnull(idx),"(UNKNOWN)",idx) 
| bin _time span=1h 
| stats sum(b) as _value by _time, pool, s, st, h, idx, date_wday, date_hour 
| eval metric_name="license_usage.used_bytes" 
| mcollect index=summary_license_metric

Any ideas?

0 Karma

rjthibod
Champion

I had added them in while I was doing something else (that's how I knew they would show up), but I have not tried using them. Let me go spin that up.

0 Karma

rjthibod
Champion

It works fine for me. Did you re-index your data correctly once you set up the new props.conf settings?

Here is my props.conf

[metrics_app_ux_layer8insight]
MAX_TIMESTAMP_LOOKAHEAD = 20
TIME_FORMAT             = %s%3N
TZ                      = UTC
ADD_EXTRA_TIME_FIELDS   = true
SHOULD_LINEMERGE        = false
NO_BINARY_CHECK         = true
ANNOTATE_PUNCT          = false
pulldown_type           = 1
category                = Metrics
TRANSFORMS-app-ux-metrics      = app-ux-metrics
TRANSFORMS-app-ux-metrics-host = app-ux-metrics-host

The data is pipe-delimited with the timestamp at the front. The transforms (not shown) just pull out the fields for each segment between the pipes.

I re-indexed some data and then ran the following search and everything worked fine

| mstats sum(_value) WHERE metric_name=layer8insight.app_ux.activity AND date_wday="friday" AND date_hour > 20 by dest date_wday date_hour

The data came out like this.

dest date_wday date_hour sum(_value)
XXX-xa-1 friday 22 568.780000
XXX-xa-4 friday 23 906.570000
XXX-xa-3 friday 22 488.120000
XXX-xa-2 friday 23 5757.760000

0 Karma

guilmxm
SplunkTrust
SplunkTrust

Thanks @rjthibod, you are the man 😉

0 Karma

guilmxm
SplunkTrust
SplunkTrust

Hello,

I actually had to check that as well for the release under development of my apps.

What about:

| mstats avg(_value) as value WHERE index=mcg_apm_response_metrics AND metric_name=apm.response by app span=1s
| eval date_hour=strftime(_time, "%H"), date_wday=lower(strftime(_time, "%A"))
| where (date_hour>=7 AND date_hour<=18 AND NOT date_wday=sunday AND NOT date_wday=saturday)
| stats avg(value) as value, perc95(value) as p95, count as count by app

Or with some variance. (not sure for the count but it should work)

Basically it does not change much but you first retrieve the metrics on the lowest time scale and then you rely on the SPL power to achieve filtering out the periods you don't want, and finally do the calculation.
Thanks to the metric store performance, this should perform well.

I doubt that ingesting the date_hour and date_wday as dimensions would be the best way to to go (specially for storage, licensing costs, reliability and maintainability), but the performance comparison of the 2 would be an interesting test.

Cheers,

Guilhem

RasmusToelhoej
Explorer

Will have to test it on a larger dataset, I wouldn't add date_hour and date_wday as dimensions.
I would add an dimension called "in_business_hours" and set it to 0 or 1, and then do my hour/day check before I index the data, the big limitation is that I can't change it dynamically afterwards.

One big issue with your query is that we are doing avg and percentiles on averages (already aggregated data), it's properly ok when span is 1s, by if we increased it to span=1h for performance reasons, the data would be incorrect. (sistats fixes this on the Events Index when doing rollups)

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...