Splunk Search
Highlighted

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

Builder

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

Highlighted

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

SplunkTrust
SplunkTrust

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

0 Karma
Highlighted

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

Builder

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.

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

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

0 Karma
Highlighted

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

Builder

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

0 Karma
Highlighted

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

Super Champion

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
0 Karma
Highlighted

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

Builder

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.

0 Karma
Highlighted

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

Path Finder

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.

0 Karma
Highlighted

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

Builder

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 ?

0 Karma
Highlighted

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

Path Finder

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
0 Karma