This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Help with custom report for SAM template and components

Not a SQL person by any means so struggling here. I need to provide monthly metrics that capture the total number of Azure monitors we have in Orion. We are moving from on prem to Azure and leadership wants to see how our monitoring is changing. To do this I have added a new custom property to the apps that lets me specify Azure for easy identification. I can get a list of the Sam templates that have this property set but I would like to include the component count for each template. Example below

Application                                                     

Application TemplateLocationComponents
Windows 2016 Standard OS TemplateAzure24
SQL Application InsightAzure48

And so on.

So the new custom property that would provide the location info is "report" and I have allowed 4 drop down options. On Prem, Azure, AWS, and Other.

The application template field is just the application template used.

And obviously the components would be the total number of components in said template.

I imagine this is a very basic query but my SQL buddy went to another job and I am flapping in the wind without him. Any help would be much appreciated.

  • This little query should work for you:

    SELECT A.Name, A.Node.CustomProperties.Location, count(A.Components.ComponentID) as Components

    FROM Orion.APM.Application A

    group by A.Name, A.Node.CustomProperties.Location

    order by A.Name

    If you want to filter it to only the Azure nodes you can just add the Where clause after the FROM clause:

    SELECT A.Name, A.Node.CustomProperties.Location, count(A.Components.ComponentID) as Components

    FROM Orion.APM.Application A

    WHERE A.Node.CustomProperties.Location = 'Azure'

    group by A.Name, A.Node.CustomProperties.Location

    order by A.Name

    This will return an alphabetical listing, if you want to sort by count instead you can order by Components or "order by Components desc" if you want to see the largest grouping on top.

    This is a SWQL query, not a SQL query. The joins are easier in SWQL, but the same should be possible in SQL you would just need to do the joins yourself if you need SQL instead.

  • This is so close to exactly what I am looking for. Thank you very much for sharing the information!!!!

    So by using what you provided I was able to get this to display apps and the custom property that I use to flag Azure components. Note: the custom property is in the apps rather than nodes.

    SELECT App, Reporting FROM Orion.APM.ApplicationCustomProperties

    where reporting like 'Azure'

    Would you know what to add to get the componeth counts for each template in this view? Thats all I would need to call this done emoticons_happy.png

    Thanks again for the help. I am reading up trying to learn this but its a challenge for sure.

  • Ah, wasn't even thinking of anything else, instead of "A.Node.CustomProperties.Location" you should just be able to sub in "A.CustomProperties.Reporting" from what I provided before:

    SELECT A.Name, A.Node.CustomProperties.Location, count(A.Components.ComponentID) as Components

    FROM Orion.APM.Application A

    group by A.Name, A.Node.CustomProperties.Location

    order by A.Name

  • That did it! Just in time, My 1 on 1 is in 1 hour this morning and I can finally deliver this report.

    Thanks so much for the with this. I really appreciate your time and knowledge.