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.
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.
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.
@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
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
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?
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.
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
Thanks @rjthibod, you are the man 😉
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
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)