Looking to add AppInsight IIS info to report

I have seen some similar posts but I can't seem to find an answer. On my AppInsight monitor page, I have this widget:

I would like to create a report that shows this for all of my IIS installations. I cannot for the life of me find it in the reporting. I can't find a stock report for it and I cannot find anything resembling "Page Requests by Avg Sever Execution Time" in the column list for a custom table either. The information has to be somewhere. Does anyone know where I can find it?

Find more posts tagged with

appinsight for iis

Reports

report creation

iis

appinsight

Accepted answers

Seashore

Can this be what you are after?

SELECT TOP 100 
    R.URLStem
    ,R.AverageElapsedTime
    ,R.RecentRequestDate
    ,R.Site.Name
    ,R.DetailsUrl
FROM Orion.APM.IIS.Request AS R
WHERE 
    R.RecentRequestDate>GETUTCDATE()-1
ORDER BY R.AverageElapsedTime desc

Seashore

Hi
As Orion alredy know how many things are joined there is no need here to add a manual join. You can but that will just be extra work.

So we can jump from table [Request] to [site] to [application] to [node] and there get caption as servername. Like this:

SELECT TOP 100 
    R.URLStem
    ,R.AverageElapsedTime
    ,R.RecentRequestDate
    ,R.Site.Name
    ,R.DetailsUrl
    ,R.site.Application.Node.Caption AS  [Servername]
FROM Orion.APM.IIS.Request AS R
WHERE 
    R.RecentRequestDate>GETUTCDATE()-1
ORDER BY R.AverageElapsedTime desc

You see that I just added one line from previous query

All comments

Seashore

Can this be what you are after?

SELECT TOP 100 
    R.URLStem
    ,R.AverageElapsedTime
    ,R.RecentRequestDate
    ,R.Site.Name
    ,R.DetailsUrl
FROM Orion.APM.IIS.Request AS R
WHERE 
    R.RecentRequestDate>GETUTCDATE()-1
ORDER BY R.AverageElapsedTime desc

kpourmand

Yes it appears it is. Than you!

kpourmand

Any idea how to add the name of the node the IIS site is from? I figured out how to use an inner join to get more data, but I dont see anything in the two sides that I can use to match together.

Seashore

Hi
As Orion alredy know how many things are joined there is no need here to add a manual join. You can but that will just be extra work.

So we can jump from table [Request] to [site] to [application] to [node] and there get caption as servername. Like this:

SELECT TOP 100 
    R.URLStem
    ,R.AverageElapsedTime
    ,R.RecentRequestDate
    ,R.Site.Name
    ,R.DetailsUrl
    ,R.site.Application.Node.Caption AS  [Servername]
FROM Orion.APM.IIS.Request AS R
WHERE 
    R.RecentRequestDate>GETUTCDATE()-1
ORDER BY R.AverageElapsedTime desc

You see that I just added one line from previous query

kpourmand

Thank you! I knew there had to be an easier way to do it.