SOLVED

Combine different time generated data

Deleted
Not applicable

I am running some performance testing against Service Fabric application and capturing the counter values and rendering the report of utilization of different components from Log Analytics.

Till now I was able to generate for a single timeline, but now I need to compare different test I have conducted over different timelines.

For example,

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")
 
I have got another set of data which is fetched by changing the TimeGenerated values and these two are two different report.
What I need is to have a single report to show CPU utilization of different timelines.
I tried with OR in the where condition but they lay one after another as the time is different.
 
 
6 Replies

Hi

If you want to get the results from two timelines and join them you just need to use let and join functions. You get the first results in a let, the second results on a let and after that you perform join on them by a command field like Computer. If needed you can join on more than one field. Example based on your query:

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");
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");
Result1 | join kind=inner 
(
Result2
)
on Computer

Hope this helps.

 

 

Hi Stanislav,

 

Thanks for your reply, it helped partially and got some insights on joining results.

With the current query the problem is both the results will have a single line in chart, basically I am trying to plot them in chart showing the variance. With the join the data grouping is lost and they all fall within the same series.

Query below and attached a screenshot of the chart where it renders without the series grouping.

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 CounterName, bin(TimeGenerated, 1m) 
| sort by TimeGenerated desc
| where TimeGenerated > datetime("DATEVALUE") and TimeGenerated < datetime("DATEVALUE");
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 CounterName, bin(TimeGenerated, 1m) 
| sort by TimeGenerated desc
| where TimeGenerated > datetime("DATEVALUE") and TimeGenerated < datetime("DATEVALUE");
result1 | join kind= inner (
   result2 
) on CounterName

Is there anyway that I can plot the results in two different lines in the line chart?

Thank you,

 

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?

 

best response
Solution

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.

Regardless of the problem that Stanislav called out, you should not use "search" at the beginning of the query. Instead, you should use the table name:
Perf
| where ObjectName == "Advisor Metrics" or ObjectName == "ManagedSpace") and CounterName == "% Processor Time" and Computer startswith_cs "NT1"

It would make your query much more efficient and predictable.

Meir :>
Meir is correct. Almost never use search. I personally use it if I need to explore something but once I am starting to construct the query I am referencing the table/s directly.
1 best response

Accepted Solutions
best response
Solution

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.

View solution in original post