Steps for create user insight dashboard
Step 1: Enable and Collect User Access Logs
Option A:
Grafana’s Internal Logging (if using an external data source)
• If you’re using an authentication service like LDAP, OAuth, or Auth Proxy, you could capture the user access logs by setting up logging in your web server (like Nginx, Apache) that proxies Grafana.
• For example, you can enable access logs for your Grafana web server. These logs will contain information about user logins and actions performed (which dashboards were accessed).
• For Grafana Enterprise, you might also have audit logs available, which you can query for user activity.
Option B:
Grafana Analytics (if using Grafana’s internal database)
• Grafana stores some information about user activity in the internal database. You can query these logs using the Grafana API or directly from the Grafana database (usually MySQL, PostgreSQL, or SQLite depending on your setup).
• For instance, you can track user activity by querying the internal dashboard and user related tables.
Step 2: Log User Access Data into a Data Source
• If you’re using external logs (like Nginx or Apache), you could store this information into an SQL database (e.g., PostgreSQL or MySQL) or a log aggregation system (e.g., Elasticsearch).
• For Grafana Enterprise users, you can directly access the audit logs from the internal database.
- Make sure the log contains key information like:
- User ID
- Dashboard ID or Name
- Access Timestamp
Example: SQL Table Structure:
CREATE TABLE user_access_logs (
id SERIAL PRIMARY KEY,
user_id VARCHAR(255),
dashboard_id VARCHAR(255),
access_timestamp TIMESTAMP
);
Step 3: Insert Logs into the Data Source
• For Nginx/Apache Logs:
Parse the logs using a tool like Logstash or Fluentd to extract user data and dashboard access details.
- Insert this data into your SQL database or Elasticsearch.
- For Grafana Enterprise:
- Use Grafana’s Audit Logs if you’re on an Enterprise version.
- You can query the audit logs directly or store them in an external database.
Step 4: Create a Data Source in Grafana
- In Grafana, add the SQL database (where you’re storing the access logs) as a data source:
- Go to Configuration > Data Sources > Add Data Source.
- Choose PostgreSQL, MySQL, or another relevant database.
- Provide the necessary connection details (host, user, password, etc.).
Step 5: Write the Query to Retrieve User Access Data
Write a query to retrieve the user access data for the last 30 days. For example, using SQL:
SELECT user_id, dashboard_id, COUNT(*) AS access_count
FROM user_access_logs
WHERE access_timestamp >= NOW() - INTERVAL 30 DAY
GROUP BY user_id, dashboard_id
ORDER BY access_count DESC;
This query returns the user_id, dashboard_id, and the number of times a user accessed a specific dashboard in the last 30 days.
Adjust the table and field names based on your setup.
Step 6: Create a Panel to Visualize the Data
- Go to your Grafana Dashboard and create a new Panel to visualize the access data.
- Select your SQL data source (e.g., PostgreSQL or MySQL).
- Paste the query you created in Step 5 into the Query Editor.
- Choose the appropriate visualization type, such as:
- Table: To list all users and their dashboard accesses.
- Bar Chart: To show the number of accesses per user or per dashboard.
Step 7: Set Up Time Filter
- Ensure the Time Range for the panel is set to the last 30 days.
- Use Grafana’s built-in time filters to adjust the time range dynamically, allowing users to view recent access data for any time period.
Step 8: Customize the Dashboard
- Customize the dashboard’s appearance by:
- Adding labels like “Users who accessed the dashboard in the last 30 days”.
- Configuring Tooltips, Legends, and Annotations for better clarity.
- Save the dashboard for future access.
Final Example Query for the Panel:
If your table is named user_access_logs, and you are using PostgreSQL, here’s an example of how you might set up the query:
SELECT
user_id,
COUNT(dashboard_id) AS dashboard_access_count,
MAX(access_timestamp) AS last_access_time
FROM user_access_logs
WHERE access_timestamp >= NOW() - INTERVAL ‘30 days’
GROUP BY user_id
ORDER BY dashboard_access_count DESC;
This query provides:
• user_id: The ID of the user accessing the dashboard.
• dashboard_access_count: How many times the user has accessed a dashboard.
• last_access_time: The most recent access time for the user.