Authored by Edwin Hernandez
We came across a scenario where a customer needs to run a Performance Test of an Application but due to security policies, customer is not able to provide Server-Level access to the Servers where the Application is hosted. Without the access, a Visual Studio Load Test will not be able to collect performance metrics from the target servers. Usually, in such scenario, Customer gets someone who does have access to run a Perform Data Collection while the Performance test is running and then send us the .BLG file(s) for analysis.
The analysis can be done directly on PERFMON by loading the .blg file, dragging the various counters to the chart and doing actions like comparisons, looking for threshold violations, etc. However, if what you need is to create nice-looking charts, overlay counters, pie charts, etc. for a report, then you probably need something better that PERFMON charts.
In the following steps, We will show you how to load the PERFMON .blg files into a SQL Database and then pull data from the SQL Database into an Excel pivot chart. We have based this information on a 2012 blog-post by Prashant Kumar here , although We have updated it by using PowerShell to provide better maintainability and Pivot charts on Excel 2016:
##########################################
# #
# COMBINE BLG FILES INTO SINGLE BLG #
# #
##########################################
$blgFile1 = "C:LogsDataCollection1.blg"
$blgFile2 = "C:LogsDataCollection1.blg"
$combinedFile = "C:LogsDataCollection_Combined.blg"
$AllArgs = @($blgFile1,$blgFile2, '-f', 'bin', '-o', $combinedFile)
& 'relog.exe' $AllArgs
##########################################
# #
# IMPORT BLG FILES INTO SQL DATABASE #
# #
##########################################
$sourceBlg = "C:LogsDataCollection_Combined.blg"
$sqlDSNconection = "SQL:Perfmon_DSN!logfile"
$AllArgs = @($sourceBlg, '-f', 'SQL', '-o', $sqlDSNconection)
& 'relog.exe' $AllArgs
SELECT counterdetails.machinename,
counterdetails.objectname,
counterdetails.countername,
counterdetails.countertype,
counterdetails.instancename,
Datediff(minute, st.starttime, Cast(LEFT(counterdata.counterdatetime, Len(counterdata.counterdatetime) - 5)
AS DATETIME)) AS interval,
Avg(counterdata.countervalue)
AS counterValues
FROM counterdata
INNER JOIN counterdetails
ON counterdata.counterid = counterdetails.counterid,
(SELECT counterdetails.machinename,
Min(Cast(LEFT(counterdata.counterdatetime, Len(
counterdata.counterdatetime) - 5)
AS
DATETIME))AS startTime
FROM counterdata
INNER JOIN counterdetails
ON counterdata.counterid = counterdetails.counterid
GROUP BY machinename) AS st
WHERE st.machinename = counterdetails.machinename
GROUP BY counterdetails.machinename,
counterdetails.objectname,
counterdetails.countername,
counterdetails.countertype,
counterdetails.instancename,
Datediff(minute, st.starttime, Cast(LEFT(counterdata.counterdatetime, Len(counterdata.counterdatetime) - 5)
AS DATETIME)),
counterdata.countervalue
Now you should have all the information you need for the Charts, however it may have too many objects, counters, or Machines for the chart to make sense. In the table use the Column Labels filters to chose only the objects that you need. Please note that you can change the field on top of the picture below in the Select field dropdown:
A much more rewarding way to do this is by adding Slicers. To do this, in excel go to PivotTable Tools > Analyze > Filter > Insert Slicer, then select the counter(s) that you need for easier filtering. It should look something like this:
Select
CounterDetails.MachineName,
CounterDetails.ObjectName,
CounterDetails.CounterName,
CounterDetails.CounterType,
CounterDetails.InstanceName,
CounterData.CounterDateTime,
CounterData.CounterValue,
DisplayToID.GUID,
DisplayToID.DisplayString,
DisplayToID.LogStartTime
from
CounterData JOIN
CounterDetails ON CounterData.CounterID = CounterDetails.CounterID
Join DisplayToID on CounterData.GUID = DisplayToID.GUID
where ObjectName in ('PhysicalDisk')
-- ObjectName in ('process')
--And CounterName = 'Available MBytes'
and DisplayString like '%_R2%'
-- and (InstanceName = ('sqlservr')
-- or InstanceName Like 'EXCEL%');
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.