0

I'm trying to query log analytics Perf table. This table has performance counters about Computers.

I want to get all performance counters for the machine in one row.

I have written this Kusto query but it brings every counter in his own row.

Perf  
| where Computer in ('aks-nodepool1-85388480-3', 'aks-agentpool-40719753-2') 
| summarize arg_max(TimeGenerated, *) by CounterName, Computer
| project   Computer, CounterName, TimeGenerated, CounterValue

I want a query that can bring the following result :

(Computer1, TimeGenerated, CounterName1, CounterName1Value, CounterName2, CounterName2Value, ... )

(Computer2, TimeGenerated, CounterName1, CounterName1Value, CounterName2, CounterName2Value, ... )

(Computer3, TimeGenerated, CounterName1, CounterName1Value, CounterName2, CounterName2Value, ... )

Any help or advices would be appreciated.

Zack ISSOIR
  • 964
  • 11
  • 24

1 Answers1

2

How about something like this? (its output schema is just slightly different than the output schema you originally mentioned in your question)

datatable(Computer:string, CounterName:string, CounterValue:double, TimeGenerated:datetime)
[
    "comp1", "counter1", 1.0, datetime(2019-02-07 16:31:15),
    "comp2", "counter1", 1.1, datetime(2019-02-07 16:31:15),
    "comp3", "counter1", 1.2, datetime(2019-02-07 16:31:15),
    "comp4", "counter1", 1.3, datetime(2019-02-07 16:31:16),
    "comp2", "counter2", 1.4, datetime(2019-02-07 16:31:16),
    "comp3", "counter3", 1.5, datetime(2019-02-07 16:31:16),
    "comp4", "counter2", 1.6, datetime(2019-02-07 16:31:14),
]
| summarize TimeGenerated = any(TimeGenerated), d = make_dictionary(pack(CounterName, CounterValue)) by Computer
| evaluate bag_unpack(d)

will output:

| Computer | TimeGenerated               | counter1 | counter2 | counter3 |
|----------|-----------------------------|----------|----------|----------|
| comp1    | 2019-02-07 16:31:15.0000000 | 1        |          |          |
| comp2    | 2019-02-07 16:31:15.0000000 | 1.1      | 1.4      |          |
| comp3    | 2019-02-07 16:31:15.0000000 | 1.2      |          | 1.5      |
| comp4    | 2019-02-07 16:31:16.0000000 | 1.3      | 1.6      |          |

and you could also do this:

datatable(Computer:string, CounterName:string, CounterValue:double, TimeGenerated:datetime)
[
    "comp1", "counter1", 1.0, datetime(2019-02-07 16:31:15),
    "comp2", "counter1", 1.1, datetime(2019-02-07 16:31:15),
    "comp3", "counter1", 1.2, datetime(2019-02-07 16:31:15),
    "comp4", "counter1", 1.3, datetime(2019-02-07 16:31:16),
    "comp2", "counter2", 1.4, datetime(2019-02-07 16:31:16),
    "comp3", "counter3", 1.5, datetime(2019-02-07 16:31:16),
    "comp4", "counter2", 1.6, datetime(2019-02-07 16:31:14),
]
| summarize arg_max(TimeGenerated, *) by Computer, CounterName
| summarize d = make_dictionary(pack(CounterName, CounterValue, "TimeGenerated", TimeGenerated)) by Computer
| evaluate bag_unpack(d)

which will output:

| Computer | TimeGenerated               | counter1 | counter2 | counter3 |
|----------|-----------------------------|----------|----------|----------|
| comp1    | 2019-02-07 16:31:15.0000000 | 1        |          |          |
| comp2    | 2019-02-07 16:31:15.0000000 | 1.1      | 1.4      |          |
| comp3    | 2019-02-07 16:31:15.0000000 | 1.2      |          | 1.5      |
| comp4    | 2019-02-07 16:31:16.0000000 | 1.3      | 1.6      |          |
Yoni L.
  • 22,627
  • 2
  • 29
  • 48
  • 1
    This exactly what I was looking for. But it only lacks one thing : I want the last I want the last inserted perfcounters only . Can replacing TimeGenerated = any(TimeGenerated) by arg_max(TimeGenerated, *) achieve this ? – Zack ISSOIR Feb 07 '19 at 16:48
  • 1
    I've updated my reply, you may want to check if it aligns your request. if it does not - you may want to provide a sample data set (using the `datatable` operator and the output you wish to get for it) – Yoni L. Feb 07 '19 at 18:15
  • This answers the question. I'll mark your this as an answer because it achieves the result I'm seeking. I'll add some checks later and I'll add updates if this anything new. Thanks for the very helpful answer and by the way your blog on kusto is excellent. – Zack ISSOIR Feb 07 '19 at 18:31
  • Suppose we need to rename the : "% Processor Time" after unbacking the dictionary , using a project-rename and that we want to reanme the column to CPUTime. How can we deal with this ? I searched in the project-rename doc and I did not find anything that shows how to rename Column names that has spaces. – Zack ISSOIR Feb 08 '19 at 11:48
  • i would suggest opening a new topic on this question [I'm currently unsure where you have the CounterName in the resultset of the example above] – Yoni L. Feb 08 '19 at 19:22
  • I posted a question that is related to this one, can you please see it. – Zack ISSOIR Apr 04 '19 at 22:49