LoadRunner database (MS Access) data in grafana. not able to display data in grafana

muzammil · April 11, 2018, 1:06pm

i have migrated all my load runner results database value of MS Access database to MySQL using one utility.

and i have created a MySQL data source successfully in grafana.

now problem i am facing is that i am not able to display the raw data in the graphs(or something similar).

i also dont find any supported dashboard to do same.

can anyone help me here?

mefraimsson · April 11, 2018, 2:21pm

Hi,

Have you read the documentation for using MySQL as data source in Grafana?

I’m afraid you’ll have to give us more information than this to be able to help you. Present table schemas and the queries you’ve tried so far (that’s not working).

Marcus

muzammil · April 11, 2018, 2:29pm

thanks for the quick reply. i have followed the url given and created data source successfully.

now the question is how do i show the loadrunner report data (now in MySQL) on grafana dashbaord?

i haven’t written anything yet and need to know that what and how should i write? and where?

there are 95 tables totally.

below is structure of 1 table (t4811343t;).

mefraimsson · April 11, 2018, 6:52pm

In general if you have rows with timestamps (epoch time or datetime/timestamp data type) you should be able to use the graph panel and visualize time series. If not, tabular output using table panel is probably only option.

If Response_Time - Describe ID contains an epoch timestamp (epoch in seconds) you should be able to write a query like this:

SELECT
   $__time(`Response_Time - Describe ID`),
  Response_Time as value,
  'Response time' as metric
FROM
  <insert your table name here>
WHERE
  $__unixEpochFilter(`Response_Time - Describe ID`)
ORDER BY
  `Response_Time - Describe ID`

Once you understand this you should be able to read documentation that i linked earlier to get better understands/help or search here to find other questions regarding using mysql as data source in Grafana.

Marcus

muzammil · May 22, 2018, 11:10am

sorry for the late reply. i looked through the my all tables. there is some change in the requirement. please look at the attached table. i need to display data fromi

2 tables. and one column is in “number” format and other in “string” format. i want to display “event name” from table 1 on x -axis and “value” from table 2 on y-axis.

mefraimsson · May 22, 2018, 11:29am

That’s not supported using the builtin graph panel. The nearest you can come is to use the X-Axis mode=Series.

Marcus

muzammil · May 22, 2018, 11:59am

what would be the query if i want to display event id and value from table 2, what do i need to do to? sorry to ask the basic questions… i am new to grafana custom dashboard.

mefraimsson · May 22, 2018, 12:50pm

I’m not going to write you a query for you. But basically you use a join to combine data from two tables.

muzammil · May 22, 2018, 1:22pm

sorry to bother you again. if you can write query using only a single table , that would give me good start.
Table 2, Y axis -“Event ID” and x -axis - “value”

mefraimsson · May 22, 2018, 2:34pm

Just answered you earlier that having non-time on x-axis is almost not supported - only using X-axis mode=series.

But try and write a regular metric query and use X-axis mode=series

muzammil · August 13, 2018, 3:14pm

i had added data time column to my column and table description looks like below.

trying to display data with below queries but getting error and blank data on dashboard.

SQL 1:

SELECT
UNIX_TIMESTAMP(end_time) as time_sec,
end_time as value,
end_time as metric
FROM event_meter
WHERE $__timeFilter(end_time)
ORDER BY end_time ASC

SQL 2:
SELECT
UNIX_TIMESTAMP(end_time) as time_sec
FROM event_meter
WHERE $__timeFilter(end_time)
ORDER BY end_time ASC

mefraimsson · August 13, 2018, 3:38pm

What are you trying to visualize - which of the columns? Please refer to documentation. You must have a time column and at least a value column.

So for example the following should work given a series named Value plotted over time in a Graph panel:

SELECT
UNIX_TIMESTAMP(end_time) as time_sec,
Value,
FROM event_meter
WHERE $__timeFilter(end_time)
ORDER BY end_time ASC

muzammil · August 28, 2018, 2:18pm

i am able to display the data on dashboard by writing below query. however as the amount of data is 160k, the dashboard works very slow. is there any optimization technique i can apply to make it quick?

i tried creating single column and multi column index on table. but nothing works

SELECT
UNIX_TIMESTAMP(rttime) as time_sec,
rt as value,txn as metric
FROM dis
WHERE $__timeFilter(rttime)
ORDER BY rttime ASC

mefraimsson · August 28, 2018, 6:05pm

Use the $__timeGroup macro to group by minutes, hours, days etc and aggregate metric with sum for example to minimize the number of returned points(rows)

muzammil · August 29, 2018, 10:38am

i used below SQL but still it is taking time to load. this new SQL is skipping the data which i dont want to skip.

any other alternate we have, like creating indexes etc?

SELECT
__timeGroup(rttime,'5m') as time_sec , rt as value,txn as metric FROM dis WHERE __timeFilter(rttime)
ORDER BY rttime ASC

mefraimsson · August 29, 2018, 12:39pm

Please read documentation I referenced above. It should be clear that you’ll need to do a group by time_sec and txn and then use aggregate function for rt column, for example sum(rt)