rafmio / log-tracker

Log tracking applications
0 stars 0 forks source link

Write a handler for total statistics #30

Open rafmio opened 2 months ago

rafmio commented 2 months ago

image

rafmio commented 1 week ago

Set this indicators: // map of stat indicator names tsp.statIndicatorsNames["total_records"] = "Total Number of Records" tsp.statIndicatorsNames["unique_ip_count"] = "Unique IP Count" tsp.statIndicatorsNames["records_per_day"] = "Records Per Day" tsp.statIndicatorsNames["top_10_ips"] = "Top 10 Most Frequent IP Addresses"

// set SQL queries for statistics
tsp.statIndicatorsQueries["total_records"] = `SELECT COUNT(*) FROM lg_tab`
tsp.statIndicatorsQueries["unique_ip_count"] = `SELECT COUNT(DISTINCT srcip) FROM lg_tab`

// returned value: average records per day
tsp.statIndicatorsQueries["records_per_day"] = `
   SELECT AVG(daily_count) AS average_records_per_day
    FROM (
        SELECT COUNT(*) AS daily_count
        FROM lg_tab
        GROUP BY DATE(tmstmp)
        ) AS subquery;
`

// returned value: ip:count
tsp.statIndicatorsQueries["top_10_ips"] = `
    SELECT srcip, COUNT(*) AS ip_count
    FROM lg_tab
    GROUP BY srcip
    ORDER BY ip_count DESC
    LIMIT 10
`

// returned value: destination_port:count
tsp.statIndicatorsQueries["top_10_dpt"] = `
SELECT dpt, COUNT(*) AS count
FROM lg_tab
GROUP BY dpt
ORDER BY count DESC
LIMIT 10;
`