In the query below, for each host, I am searching for its performance data for each value for past 5 minutes.
The expected output is the following:
Open screenshot
I have solved this problem using 4 joins… But that made the source code large and ugly…
Is there any way I can optimize the size of the query below?
Can a define a custom macro inside the query and call it several times with different parameters instead of copy-pasting the code?
index=perfmon host=lena counter="% Processor Time" earliest=-1m
| fields host,counter,Value
| eval ValueR_1m_Ago = round(Value, 2)
| eval HostUpperCase = upper(host)
| convert ctime(_time) as Time_1m_Ago
| fields HostUpperCase, counter, ValueR_1m_Ago, Time_1m_Ago
| join HostUpperCase
[search index=perfmon host=lena counter="% Processor Time" earliest=-2m latest=-1m
| fields host,counter,Value
| eval ValueR_2m_Ago = round(Value, 2)
| eval HostUpperCase = upper(host)
| convert ctime(_time) as Time_2m_Ago
| fields HostUpperCase, ValueR_2m_Ago, Time_2m_Ago ]
| join HostUpperCase
[search index=perfmon host=lena counter="% Processor Time" earliest=-3m latest=-2m
| fields host,counter,Value
| eval ValueR_3m_Ago = round(Value, 2)
| eval HostUpperCase = upper(host)
| convert ctime(_time) as Time_3m_Ago
| fields HostUpperCase, ValueR_3m_Ago, Time_3m_Ago ]
| join HostUpperCase
[search index=perfmon host=lena counter="% Processor Time" earliest=-4m latest=-3m
| fields host,counter,Value
| eval ValueR_4m_Ago = round(Value, 2)
| eval HostUpperCase = upper(host)
| convert ctime(_time) as Time_4m_Ago
| fields HostUpperCase, ValueR_4m_Ago, Time_4m_Ago ]
| join HostUpperCase
[search index=perfmon host=lena counter="% Processor Time" earliest=-5m latest=-4m
| fields host,counter,Value
| eval ValueR_5m_Ago = round(Value, 2)
| eval HostUpperCase = upper(host)
| convert ctime(_time) as Time_5m_Ago
| fields HostUpperCase, ValueR_5m_Ago, Time_5m_Ago ]
| DEDUP HostUpperCase
| sort -ValueR_1m_Ago
| table HostUpperCase
,counter
,ValueR_1m_Ago
,Time_1m_Ago
,ValueR_2m_Ago
,ValueR_3m_Ago
,ValueR_4m_Ago
,ValueR_5m_Ago
... View more
