timechart sum of field value from different sources with changing span ?

[pgadhari]

I have 3 sources having a field called value, that collects power ratings. I have to timechart the sum of those values to show the final power ratings. When I keep the timerange as "last 60 minutes", that works, as the values are getting collected every 1 minute. So the span of 1m works fine. But when I change the timerange to "Last 4 hours" or "Last 24 hours" or more than that, problem is - it add the all the values of each source and shows that value which is not proper. How do I resolve this issue :

My query is :

index=dcim | timechart sum(value) by source | addtotals ....

If I do span=1m, which is fine. But If i change the time-range, span=1m does not seem to feasible option, as the search becomes very slow as it is returning lot of events and then doing addtotals and further using eval, will take more time.
In short, my output of events should be 1 min, add those values and do the timechart ? whatever time range I select. Please help resolve the issue ?

Thanks
PG

[soskaykakehi]

Hi @pgadhari

Can you try this. In your case , you shoud use bin command (to grouping events timestamp), and stats command .

index=dcim
| fields value, source
| bin _time span=1m
| stats sum(value) as values by _time,source
| xyseries _time source values
| addtotals

The timechart command with span=1m option is searching and calcurate events in every all 1min( really every 1 minuite) .
So ,if you change the timerange to more longer , this affects to search speed.

[pgadhari]

Yes, this query seems to be working, but it is taking long time to return the result. Appox it is taking 70 seconds for this query to execute if I select "last 30 days". How can I increase the performance of this query ?

[soskaykakehi]

Hmm. Sorry , I have no answer to increase more faster this query search performance easly.
This query has dispatch almost procsess at indexers. Add more indexer can increase this performance. but... yeah, this not an answer you want.

Other option is, how about to save your shortly span results to summary index using hourly or daily scheduled search. You can use fill_summary_index.py to fill your summary index with past timerange.
If you need many times to search a same query or more than long timerange, this is one of the answer i think.

Or, If this index is using index-time field extractions with structured data (ex: CSV , json ,etc), you can use tstats command to get more faster results. like this :

| tstats sum(value) where index=dcim by _time,source span=1m
| xyseries _time source value
| addtotals

[pgadhari]

So, you mean to say is - I should forward the output of that query in summary index and then use the tstats command in the search query for the panel right ?

can you tell me detailed steps on that, on how can I do that ? As per my understanding, I should schedule a saved search and put following query in that search, right ?

 index=dcim
 | fields value, source
 | bin _time span=1m
 | stats sum(value) as values by _time,source
 | xyseries _time source values
 | addtotals

and then forward to summary index. Once this is done, I should use tstats command in the query for the panel right ? Please guide ?

Thanks
PG

[soskaykakehi]

@pgadhari
You should forward your search results in summary index and then just search it without tstats.
tstats needs to extract fields during indexing time. I am not sure if this works with a summary index.

However, storing calculation results in a summary index can provide significant benefits. Also, you don't have to do a lot of processing over and over, and you can search on a longer time range. And Summary Index does not consume daily license.
I'm still looking for a way to use tstats at the summary index or add a field extraction configuration that can use tstats later, but I haven't yet found a good way.

Here is the step to use summary index without using tstats command.

*Reference : https://docs.splunk.com/Documentation/Splunk/latest/Knowledge/Usesummaryindexing *

Step 1) Create a new index to use as a summary index.
The method of creating a summary index is the same as creating a general index. The entity is just an index.

Note: When creating a summary index, your configuration may affect where you create the summary index. Only SearchHead can generate summary index data. And you can not access that summary index from other SearchHeads.

Option 1: Store summary index locally on SearchHead
If you are using standalone Splunk or single SearchHead, there is no problem creating an index locally.

Option 2: Save summary index in Indexer
If the amount of data in the summary index to create or the number of events is large, it is possible to hold the summary index in Indexer.
By doing so, you have the following benefits:

• Aggregates data in Indexer
• Summary index can be used from other SearchHead such as SearchHeadCluster

If you want to save the summary index in Indexer, you need to set up to transfer SearchHead data to Indexer, referring to the following page.

*Reference : Best practice: Forward search head data to the indexer layer
https://docs.splunk.com/Documentation/Splunk/latest/DistSearch/Forwardsearchheaddata *

Keep in mind the following points when making this setting:
- You will not be able to access the summary index already created in SearchHead (since all requests will be sent to Indexer)
- Before setting up, it is necessary to create a summary index used by SearchHead in Indexer. To create an index in Indexer, distribute indexes.conf from Cluster Master.
   
Step 2) Set the schedule search as the following settings.
Execution interval and time range specification are one example, please change according to your search frequency and requirements.
If you want to set the summary index from the schedule search setting screen on the WebUI, please delete the collect command line of the query because it is unnecessary.

Execution interval ： per 1h
Search Timerange ： Relative , earliest: -1h@h, latest: @h
Query1:
collect command line means , store search results in to summary index dcim_summary and add a strings tags=dcim_sum_v1 to record.
Please change the index name from dcim_summary to created in Step 1.
You should add a tag or label, if you use same summary index to other usage.

index=dcim
| fields value, source
| bin _time span=1m
| stats sum(value) as values by _time,source
| eval labels="source:"+source
| xyseries _time labels values
| addtotals
| collect index=dcim_summary marker="tags=dcim_sum_v1"    

Step 3) Search statistical data from summary index.
Query2:

index=dcim_summary tags=dcim_sum_v1
| bin _time span=1m
| stats sum(source:*) sum(Total) by _time   

Thanks for reading. I hope it helps you.

[pgadhari]

Sure @soskaykakehi , thanks. I will try that out and revert if there are any issues.

[koshyk]

If you don't use span, timechart should automatically adjust the span according to the time-range

You could also try to put a minspan to ensure it never goes below 1min

index=dcim | timechart minspan=1m sum(value)  by source

[pgadhari]

If I depend on timechart for automatic span, then the values are coming too high, as it adds all the values of that field for that source, which is wrong.

[DavidHourani]

Hi @pgadhari,

If you're using timechart for with a 1m span over a long period of time you will also hit :
This visualization is configured to display a maximum of 10000 results per series, and that limit has been reached
try something like this to first get the total over 1 min, then make the timechart with whatever span you need :

index=dcim| bucket _time span=1m  | stats sum(value) by source,_time | addtotals .... |timechart sum(value) by source

Let me know if that helps!

Cheers,
David

[pgadhari]

This query is not working, as the stats is not showing up the values properly and when I add addtotals, timechart not showing any values.

[DavidHourani]

ah yeah, by addtotals ... I meant you should add your logic there. What totals did you need there ? Try it like this :
index=dcim| bucket _time span=1m | stats sum(value) as value by source,_time | addtotals |timechart sum(value) by source

[pgadhari]

This is my query,

index=dcim 41025 |timechart sum(value) by source | addtotals |eval Power=round(Total/1000/3,1) | fields - snmp* Total

wherein I am getting timechart of the value of different sources in columns and then doing the addtotals, to find the final power value and further doing calculation on that using eval function. Now, using "bucket _time span=1m", I am getting the values every 1 min, even after changing the time-range, which is working fine now. The only problem is, when I change the time range, getting events every one minute is slowing down the performance of the query and taking long time to load the panel chart. I need to fix that issue. How can I fix it ?

Thanks
PG