Forum Discussion
Combine different time generated data
The charts in the Analytics portal does not support aggregating on two fields and being visible at the same time. With this you will have to do some workarounds. Also if you want to place the results on top of each other you will have to alter time time line. Here is example below:
let result1 = search not(ObjectName == "Advisor Metrics" or ObjectName == "ManagedSpace") and CounterName == "% Processor Time" and Computer startswith_cs "NT1" | summarize Avg_ProcessorTime = avg(CounterValue) by Computer, bin(TimeGenerated, 1m) | sort by TimeGenerated desc | where TimeGenerated > datetime("DATEVALUE") and TimeGenerated < datetime("DATEVALUE") | extend CommonTime = TimeGenerated + 12h | extend Result = strcat(Computer, '_result1'); let result2 = search not(ObjectName == "Advisor Metrics" or ObjectName == "ManagedSpace") and CounterName == "% Processor Time" and Computer startswith_cs "NT1" | summarize Avg_ProcessorTime = avg(CounterValue) by Computer, bin(TimeGenerated, 1m) | sort by TimeGenerated desc | where TimeGenerated > datetime("DATEVALUE") and TimeGenerated < datetime("DATEVALUE") | extend CommonTime = TimeGenerated | extend Result = strcat(Computer, '_result2'); result1 | union result2 | summarize avg() by Result, bin(CommonTime, 1m)Now first let me say that it is easier for me to think if the aggregation is on Computer field rather counter field so I've replaced that. Also as you do not want to join results but rather have the same results into single table union function should be used. Next notice that I am extending result1 with CommonTime column which will have the time of the records + 12 hours. In your case you will have to replace 12 hours with value on your own in a way that the results from result 1 will be in the same timeline as result2. For example if results1 are from 17th of February between 02:00 and 10:00 and the results2 are from 18th of February between 02:00 and 10:00 you will have to add + 24h. I hope you understand the example. Next also I am creating another column so when the computer name is the same we can differentiate it from which results it is coming. That way we can later summarize on that column.
I hope this helps and matches what you want to achieve.
The problem lies around selecting the series from the list. The table has all the required data but when it comes to chart rendering I can select only one of the timeline at time. Even if I have an option to select two them, they will not overlap in the chart as they have different time data.
My requirement is to render different time data in the same chart and compare them,
For example,
result01 = Avg Process Time between 9:00 AM to 10:00 AM
result02 = Avg Process Time between 2:00 PM to 3:00 PM
Get these results to render in the same chart with series grouped based on the results on the same timeline.
Is it something possible?
The charts in the Analytics portal does not support aggregating on two fields and being visible at the same time. With this you will have to do some workarounds. Also if you want to place the results on top of each other you will have to alter time time line. Here is example below:
let result1 = search not(ObjectName == "Advisor Metrics" or ObjectName == "ManagedSpace")
and CounterName == "% Processor Time"
and Computer startswith_cs "NT1"
| summarize Avg_ProcessorTime = avg(CounterValue) by Computer, bin(TimeGenerated, 1m)
| sort by TimeGenerated desc
| where TimeGenerated > datetime("DATEVALUE") and TimeGenerated < datetime("DATEVALUE")
| extend CommonTime = TimeGenerated + 12h
| extend Result = strcat(Computer, '_result1');
let result2 = search not(ObjectName == "Advisor Metrics" or ObjectName == "ManagedSpace")
and CounterName == "% Processor Time"
and Computer startswith_cs "NT1"
| summarize Avg_ProcessorTime = avg(CounterValue) by Computer, bin(TimeGenerated, 1m)
| sort by TimeGenerated desc
| where TimeGenerated > datetime("DATEVALUE") and TimeGenerated < datetime("DATEVALUE")
| extend CommonTime = TimeGenerated
| extend Result = strcat(Computer, '_result2');
result1 | union result2 | summarize avg() by Result, bin(CommonTime, 1m)
Now first let me say that it is easier for me to think if the aggregation is on Computer field rather counter field so I've replaced that. Also as you do not want to join results but rather have the same results into single table union function should be used. Next notice that I am extending result1 with CommonTime column which will have the time of the records + 12 hours. In your case you will have to replace 12 hours with value on your own in a way that the results from result 1 will be in the same timeline as result2. For example if results1 are from 17th of February between 02:00 and 10:00 and the results2 are from 18th of February between 02:00 and 10:00 you will have to add + 24h. I hope you understand the example. Next also I am creating another column so when the computer name is the same we can differentiate it from which results it is coming. That way we can later summarize on that column.
I hope this helps and matches what you want to achieve.