IIS log files , convert queries to PowerBI

[BoerelzZ]

Hi,

I'm currently trying to create a small dashboard giving me an overview of details about IIS log files.

• Views per application
• top 20 urls
• top 25 slowest urls
• etc.

I know the SQL queries, but i don't know how i can convert them to Power BI.

For example the TOP 20 urls should be

SELECT TOP 20 	csuristem, 
		COUNT(*) AS Total, 
		MAX(timetaken) AS MaxTime, 
		AVG(timetaken) AS AvgTime 
	/* bytes-->	AVG(sc-bytes) AS AvgBytes */
FROM XXXX 
GROUP BY csuristem
ORDER BY Total DESC

and the top 25 slowest urls should be

SELECT TOP 25  
    csuristem as URL,  
    MAX(timetaken) As Max,  
    MIN(timetaken) As Min,  
    Avg(timetaken) As Average  
    FROM XXXX
GROUP BY URL  
ORDER By Average DESC 

Can anyone help me and push me in the right way on how to convert the queries to Power BI?

Thank you all in advance!

Kristof

[Phil_Seamark · Accepted solution]

Hi @BoerelzZ

This is reasonably easy.  Just start dragging your fieds to a matrix visual.

For all but your cs-uri-stem field, you should chose what happens to the field using the small down arrow at the end of the field in the Values area.  You can select, Average, Count, Max, Min etc.

Then for the Top/Bottom use the Visual Level Filters on your cs-uri-stem field to set the Top N or Bottom N (advanced settings)

[BoerelzZ]

Hi,

I'm currently trying to create a small dashboard giving me an overview of details about IIS log files.

• Views per application
• top 20 urls
• top 25 slowest urls
• etc.

I know the SQL queries, but i don't know how i can convert them to Power BI.

For example the TOP 20 urls should be

SELECT TOP 20 	csuristem, 
		COUNT(*) AS Total, 
		MAX(timetaken) AS MaxTime, 
		AVG(timetaken) AS AvgTime 
	/* bytes-->	AVG(sc-bytes) AS AvgBytes */
FROM XXXX 
GROUP BY csuristem
ORDER BY Total DESC

and the top 25 slowest urls should be

SELECT TOP 25  
    csuristem as URL,  
    MAX(timetaken) As Max,  
    MIN(timetaken) As Min,  
    Avg(timetaken) As Average  
    FROM XXXX
GROUP BY URL  
ORDER By Average DESC 

Can anyone help me and push me in the right way on how to convert the queries to Power BI?

Thank you all in advance!

Kristof

[Phil_Seamark]

I think this is a duplicate post and you should be able to merge/delete so we only have one topic

Here is the other one

https://community.powerbi.com/t5/Desktop/IIS-log-files-convert-queries-to-PowerBI/m-p/166160#U166160