Timechart for a field that occurs only once in 24 hour. The field has multiple values

[sara91]

Hi All,

I want to create timechart which shows CPU utilization for each clienthost. There is only one event that occurs every 24 hour.
That event has 12 CPU utilization values. Each value is the cpu utilization for each 2 hour in the last 24 hour i.e.

if this is the event for that got indexed at 18:00 hour today 10/2/2020 , cpuoverall: 1.51,9.47,1.70,1.45,1.51,1.47,1.46,1.46,1.48,1.48,1.50,1.50
then;

cpu1=1.59 is the cpu utilization for the 2 hours between, 18:00 and 20:00 of 09/2/2020.
cpu2=9.47 is the cpu utilization for the next 2 hours between, 20:00 and 22:00 of 09/2/2020.
cpu3=1.70 is the cpu utilization for the next hours between, 22:00 and 00:00 of 09/2/2020.
cpu4=1.45 is the cpu utilization for the next 2 hours between, 00:00 and 02:00 of 10/2/2020
and so on.

How can I create a timechart (as a line graph) for these values, in which each cpu value is marked on that particular hour of the day on the chart ?

In the end the chart should be a line graph where each line represent different client host, and the cpu values should be spread across the time.

Please let me know if it is possible. I have tired it by mapping time and cpu values but I am not able to create a graph

[maciep · Accepted solution]

Not sure if this overkill...maybe missing something easier, but it's what came to mind. The first 2 lines are just trying to get an event that looks close enough to what you have...spl starts with the delta eval

Essentially, I'm creating a mv field from the cpu data, then creating a static mv field to represent the offset of those cpu data points compared to the _time field of the event. Once they're joined together with mvzip, I mvexpand that field, so now they're each their own event and should still include the original timestamp and host. Then I use the delta in that combined field to re-eval _time in the event and the cpu to re-eval the cpu...at which piont, we can timechart with a span of 2h.

| stats count 
| eval _time = relative_time(now(),"@d+18h"), cpu="1.51,9.47,1.70,1.45,1.51,1.47,1.46,1.46,1.48,1.48,1.50,1.50",host="ServerA"
| eval delta="-24h,-22h,-20h,-18h,-16h,-14h,-12h,-10h,-8h,-6h,-4h,-2h,-0h"
| eval cpu_with_delta = mvzip(split(delta,","),split(cpu,","),":")
| mvexpand cpu_with_delta
| eval delta = mvindex(split(cpu_with_delta,":"),0), cpu = mvindex(split(cpu_with_delta,":"),1)
| eval _time = relative_time(_time,delta)
| timechart span=2h max(cpu) by host

[sara91]

thankyou. This is working

[sara91]

This needed few more modifications so that we can show all the values of last 7 days. Major change is the way relative time is calculated. Instead of calculating relative time wrt now(), we have calculated with _time itself. Thsi helped us in calculating the time wrt the indexed time value.

The modified search query

index="myindex" sourcetype="mysourcetype" "cpuoverall"
| stats values(cpuoverall) as cpu by _time, host
| eval time2 = relative_time(_time,"@h+1h")
| eval delta = "-22h,-20h,-18h,-16h,-14h,-12h,-10h,-8h,-6h,-4h,-2h,-0h"
| eval cpu_with_delta = mvzip(split(delta,","), split(cpu,","),":")
| mvexpand cpu_with_delta
| eval delta2 = mvindex(split(cpu_with_delta,":"),0), cpu2 = mvindex(split(cpu_with_delta,":"),1)
| eval _time = relative_time(time2,delta2)
| timechart span=1h values(cpu2) as "CPU Usage" by host

[maciep]

thanks for following up...these types of improvement notes can often help others when they land here with a similar problem.

[to4kawa]

I see the situation.
what's your logs?

your search
| table _time cpuoverall

Viz >> Line Chart
Is this enough?