Skip to content
English - United States
Contact us
  • There are no suggestions because the search field is empty.

Report & Query Guide (Redash / SQL Reference)

A reference of Redash queries and SQL scripts for zone hours, portfolio, and subcontractor reports

Table of Contents:

Zone Hours (Replacement for Zone Detection)

Instead of displaying a row for every detection, this report calculates the total time spent in a zone until the worker is detected in a different zone. This approach significantly reduces the number of rows. In the previous Zone Detection report, a worker could have `dozens or even hundreds` of entries if they remained in the same zone throughout the day (e.g., 9:00, 9:06, 9:12, 9:18, etc.). With the new `Zone Hours` report, only a `single entry` is recorded for continuous time in a zone. For example: Previous format: - Zone_Name, 9:00 - Zone_Name, 9:06 - Zone_Name, 9:12 - Zone_Name, 9:18 New format: - Zone_Name, 9:00 – 9:18 This update makes the report `more concise and easier` to analyze while preserving the accuracy of time spent in each zone. This report utilizes Campus `RECURSIVE CTEs`, which include the parent site along with all child sites. If you need to run the report for a `single site without its child sites`, you will need to modify the `RECURSIVE CTEs` accordingly. (Templates for these modifications will be added later.)

Updated Jan 2025 Zone Report - Company Totals (Date Range)

Updated Jan 2025 Zone Report - Company Totals (One Date)

Updated March 2025 Zone Report - Worker Hours (Date Range)

Dave’s Notes: There are now two separate reports: 1. Total Hours in Zone This report shows the total amount of time a person spent in each zone over the course of a day. For example, if someone moves from Zone A to Zone B and then back to Zone A, the report will add up all the time spent in Zone A during the day. 2. Zone Movements This report lists each individual movement between zones. You’ll see entries showing someone moving from Zone A to Zone B and then back to Zone A, along with the amount of time spent in each zone between movements. Issue Fixed: Sessions Longer Than 24 Hours Previously, there was a bug with stationary badges that caused total hours to exceed 24 in a day. For example, a session might appear as starting at 7:14 AM on 3/14 and ending at 8:20 AM — but it was actually spanning from 7:14 AM on 3/14 to 8:20 AM on 3/15. This led to an inflated total for 3/14. Now, these cross-day sessions are automatically split at midnight. The session would end at 11:59 PM on 3/14 and a new session would begin at 12:00 AM on 3/15, preventing overcounting on a single day.

  • [https://redash.sitemetric.com/queries/5851/source?p_company_id=["All"]&p_date_range=d_last_7_days&p_hours_between_sessions=6&p_site_id=VA63854302&p_zone=["All"]](https://redash.sitemetric.com/queries/5851/source?p_company_id=%5B%22All%22%5D&p_date_range=d_last_7_days&p_hours_between_sessions=6&p_site_id=VA63854302&p_zone=%5B%22All%22%5D)
  • [https://redash.sitemetric.com/queries/5852/source?p_company_id=["All"]&p_date_range=d_last_7_days&p_hours_between_sessions=6&p_site_id=VA63854302&p_zone=["All"]](https://redash.sitemetric.com/queries/5852/source?p_company_id=%5B%22All%22%5D&p_date_range=d_last_7_days&p_hours_between_sessions=6&p_site_id=VA63854302&p_zone=%5B%22All%22%5D)

Updated Jan 2025 Zone Report - Worker Hours (One Date)

Portfolio Reports

Will explain later

Owners Portfolio Project Summary - CompanyUses Owner company_id

Owners Portfolio Project Summary-Cumulative Daily Headcount Chart

Owners Portfolio Project Summary-Sites Daily Headcount Chart

Dashboard Examples

Max Trade Headcount (Weekly & Monthly) In-App Dashboard

Max Trade Headcount (Weekly & Monthly) In-App Dashboard

Max Trades - Monthly Headcount Report Emailed Report

Max Trades - Weekly Headcount Report Emailed Report

Monthly Headcount

[https://redash.sitemetric.com/dashboards/21822-amazon-aws-sites---monthly-headcount?p_project=["All"]](https://redash.sitemetric.com/dashboards/21822-amazon-aws-sites---monthly-headcount?p_project=%5B%22All%22%5D)

Report Naming Convention

Standardized format for naming reports

Dashboards

  • Daily Report — CONFIRMED NAME: ✅
  • Headcount and Hours — ALTERNATIVE IDEAS: Headcount and Hours (Site-Wide)
  • Project Summary — CONFIRMED NAME: ✅
  • Site Access List — CONFIRMED NAME: ✅
  • Real-Time Location Report
  • Zone Headcount and Hours — ALTERNATIVE IDEAS: Headcount and Hours (Zone-Specific)
  • Zone Detection — ALTERNATIVE IDEAS: Zone Trace, Zone Movements, Worker Location Trace, Worker Zone Trace, Worker Zone Movements,
  • Zone Summary — ALTERNATIVE IDEAS: Zone Summary (Hourly), Zone Summary (By Hour)

Queries

Daily Worker Hours - List of individual worker's daily hours

  • Daily Worker Hours — DESCRIPTION: List of individual worker's daily hours; CONFIRMED NAME: ✅

Equipment and Zone Counts

Used as a case study for a marketing paper.

Count of Zone and Equipment at a site - Count at Campus(Parent + Child)

Monthly Chart - Monthly Deployment numbers

Active Sites with Address and Zip

https://redash.sitemetric.com/queries/5731/source

Subcontractor Daily Report

This version of the report has 3 tabs: 1. Daily Company Totals – Displays the daily headcount and total hours for each company, along with a count of badges left onsite overnight. *Overnight badges are excluded from the headcount and total hours.* 2. Daily Worker Hours – Lists the total hours worked by each worker. *Stationary workers are not included.* 3. Stationary/Overnight Badges – Provides a list of all badges that remained onsite overnight. `The queries do not include the site settings or tiered subs`

Daily Company Totals

  • [https://redash.sitemetric.com/queries/5738/source?p_Date=d_yesterday&p_company_id=["8XE6AN0VZ0"]&p_dates_to_exclude=["2024-12-25"]&p_day_numbers=["0"%2C"1"%2C"2"%2C"3"%2C"4"%2C"5"%2C"6"]&p_site_id=GA77596503&p_sitemetric_id=["All"]](https://redash.sitemetric.com/queries/5738/source?p_Date=d_yesterday&p_company_id=%5B%228XE6AN0VZ0%22%5D&p_dates_to_exclude=%5B%222024-12-25%22%5D&p_day_numbers=%5B%220%22%2C%221%22%2C%222%22%2C%223%22%2C%224%22%2C%225%22%2C%226%22%5D&p_site_id=GA77596503&p_sitemetric_id=%5B%22All%22%5D)

Daily Worker Hours

  • [https://redash.sitemetric.com/queries/5739/source?p_Date=d_yesterday&p_company_id=["8XE6AN0VZ0"]&p_dates_to_exclude=["2024-12-25"]&p_day_numbers=["0"%2C"1"%2C"2"%2C"3"%2C"4"%2C"5"%2C"6"]&p_site_id=GA77596503&p_sitemetric_id=["All"]](https://redash.sitemetric.com/queries/5739/source?p_Date=d_yesterday&p_company_id=%5B%228XE6AN0VZ0%22%5D&p_dates_to_exclude=%5B%222024-12-25%22%5D&p_day_numbers=%5B%220%22%2C%221%22%2C%222%22%2C%223%22%2C%224%22%2C%225%22%2C%226%22%5D&p_site_id=GA77596503&p_sitemetric_id=%5B%22All%22%5D)

Stationary/Overnight Badges

  • [https://redash.sitemetric.com/queries/5740/source?p_Date=d_yesterday&p_company_id=["8XE6AN0VZ0"]&p_dates_to_exclude=["2024-12-25"]&p_day_numbers=["0"%2C"1"%2C"2"%2C"3"%2C"4"%2C"5"%2C"6"]&p_site_id=GA77596503&p_sitemetric_id=["All"]](https://redash.sitemetric.com/queries/5740/source?p_Date=d_yesterday&p_company_id=%5B%228XE6AN0VZ0%22%5D&p_dates_to_exclude=%5B%222024-12-25%22%5D&p_day_numbers=%5B%220%22%2C%221%22%2C%222%22%2C%223%22%2C%224%22%2C%225%22%2C%226%22%5D&p_site_id=GA77596503&p_sitemetric_id=%5B%22All%22%5D)

Cloning Dashboards

Clone

INSERT INTO and UPDATE site_companies

### INSERT new company_id(s) INTO the site_companies table - This script inserts a predefined list of company_id(s) into the site_companies table for a given site_id. - If a site requires a dropdown of companies for its registration form but has no workers registered yet, some companies might not exist in site_companies. This script ensures those companies are added and skips companies that already exist. - Step 1: 1. Creates a temporary dataset (co) with new company_ids. 2. Filters out companies that already exist in site_companies. 3. Inserts only the new company_ids. - This prevents duplicate entries and ensures necessary companies are available for site registration. - Step 2: Will add this later - Step 3: Will add this later

Given a site_id and a list of company_id(s), you can run this script:

site_id = AZ4PNYHGGT

company_id = 28CC66592D, 0SFS41FZ7X, 704CEAC675, 2320011E80

-- EXAMPLE:
/*
====================
1. This will INSERT companies that are not already in site_companies
====================
*/
INSERT INTO site_companies (site_id, company_id, parent_id, primary_id, created_at, updated_at, deleted_at)
SELECT * FROM (
    SELECT 'TX36633349' AS site_id, '15C2W18TY2' AS company_id, 'XM0YDVUBW4' AS parent_id, 'XM0YDVUBW4' AS primary_id, NOW() AS created_at, NOW() AS updated_at, NULL AS deleted_at UNION ALL
    SELECT 'TX36633349', '5879271F9D', '9V4UEDEKCV', '9V4UEDEKCV', NOW(), NOW(), NULL UNION ALL
    SELECT 'TX36633349', 'JKYN26D452', '9V4UEDEKCV', '9V4UEDEKCV', NOW(), NOW(), NULL UNION ALL
    SELECT 'TX36633349', '848E26231B', '9V4UEDEKCV', '9V4UEDEKCV', NOW(), NOW(), NULL UNION ALL
) co
WHERE NOT EXISTS (
    SELECT 1 FROM site_companies sc
    WHERE sc.site_id = co.site_id
    AND sc.company_id = co.company_id
    AND sc.deleted_at IS NULL
);

-- Alternatively, you can do this:

INSERT INTO site_companies (site_id, company_id, parent_id, primary_id, created_at, updated_at, deleted_at)
SELECT co.site_id, co.company_id, co.parent_id, co.primary_id, co.created_at, co.updated_at, co.deleted_at
FROM (
    SELECT 'AZ4PNYHGGT' AS site_id, '28CC66592D' AS company_id, '28CC66592D' AS parent_id, '28CC66592D' AS primary_id, NOW() AS created_at, NOW() AS updated_at, NULL AS deleted_at UNION ALL
    SELECT 'AZ4PNYHGGT', '0SFS41FZ7X', '0SFS41FZ7X', '0SFS41FZ7X', NOW(), NOW(), NULL UNION ALL
    SELECT 'AZ4PNYHGGT', '704CEAC675', '704CEAC675', '704CEAC675', NOW(), NOW(), NULL UNION ALL
    SELECT 'AZ4PNYHGGT', '2320011E80', '2320011E80', '2320011E80', NOW(), NOW(), NULL
) co
WHERE NOT EXISTS (
    SELECT 1 FROM site_companies sc
    WHERE sc.site_id = co.site_id
    AND sc.company_id = co.company_id
    AND sc.deleted_at IS NULL
);

/*
====================
2. OPTIONAL: Use this if you want to check to see which companies were skipped
because they were already in site_companies, but did not have different
parent companies.

- You can copy the SELECT lines from step 1 — be sure to remove
the NOW(), NOW(), NULL
====================
*/
SELECT sc.site_id, sc.company_id, sc.parent_id, sc.primary_id
FROM site_companies sc
WHERE EXISTS (
    SELECT 1 FROM (
    SELECT 'TX36633349' AS site_id, '15C2W18TY2' AS company_id, 'XM0YDVUBW4' AS parent_id, 'XM0YDVUBW4' AS primary_id UNION ALL
    SELECT 'TX36633349', '5879271F9D', '9V4UEDEKCV', '9V4UEDEKCV' UNION ALL
    SELECT 'TX36633349', 'JKYN26D452', '9V4UEDEKCV', '9V4UEDEKCV' UNION ALL
    SELECT 'TX36633349', 'PUWPDNTQVW', 'Z6RVQP0SDF', 'Z6RVQP0SDF'
    ) excluded
    WHERE excluded.site_id = sc.site_id
    AND excluded.company_id = sc.company_id
    AND (excluded.parent_id <> sc.parent_id
    OR excluded.primary_id <> sc.primary_id)
);

-- Or to see if a company_id is already in site_companies as a first Tier sub
SELECT site_id, company_id, parent_id, primary_id
FROM site_companies sc
WHERE company_id IN (
'aaaaa',
'aaaaa',
'aaaaa',
'aaaaa'
)
AND site_id = 'ADD_SITE_ID'
AND company_id <> primary_id;

/*
====================
3. Use this to UPDATE multiple companies that are already in site_companies
but do not have different parent_id or primary_id

- You can copy the SELECT lines from STEP 1
Be sure to change NOW(), NOW(), NULL
to NOW() since you are only updating updated_at.
====================
*/
UPDATE site_companies sc
JOIN (
    SELECT 'TX36633349' AS site_id, 'A84EBA03E6' AS company_id, '9V4UEDEKCV' AS parent_id, '9V4UEDEKCV' AS primary_id, NOW() AS updated_at UNION ALL
    SELECT 'TX36633349', 'VT1G3Q0HCZ', '9V4UEDEKCV', '9V4UEDEKCV', NOW() UNION ALL
    SELECT 'TX36633349', 'AC4C6297A8', '9V4UEDEKCV', '9V4UEDEKCV', NOW() UNION ALL
    SELECT 'TX36633349', 'AE1CB3005A', 'D7CF129FBF', 'D7CF129FBF', NOW()
) updates
ON sc.site_id = updates.site_id
AND sc.company_id = updates.company_id
AND sc.primary_id = updates.company_id
SET
    sc.parent_id = updates.parent_id,
    sc.primary_id = updates.primary_id,
    sc.updated_at = updates.updated_at;

/*
========================
3.1 Or use this to UPDATE only one company that is already
in site_companies but does not have different parent_id or primary_id
========================
*/
UPDATE site_companies sc
JOIN (
    SELECT 'TX36633349' AS site_id, '848E26231B' AS company_id, '9V4UEDEKCV' AS parent_id, '9V4UEDEKCV' AS primary_id, NOW() AS updated_at
) updates
ON sc.site_id = updates.site_id
AND sc.company_id = updates.company_id
SET
    sc.parent_id = updates.parent_id,
    sc.primary_id = updates.primary_id,
    sc.updated_at = updates.updated_at;

Daily Report Dashboard

This is the link to the new, updated Campus Daily Report — let me know if you see any issues with it. You should be able to run with it if you want, but don’t modify this one. - Campus-enabled (if you don’t want that, you can just force it to return a single site) - Date exclusions - Day of week exclusions - Company exclusions - Allowed resource types (defaulting to LABOR only) - Company hierarchies (site_companies) - Site Settings (ie. hours before stationary, whether to adjust stationary hours, whether to exclude stationary hours, etc.)

https://redash.sitemetric.com/dashboards/15760-campus-daily-report?p_date=d_yesterday

Badges that have not been onsite in X number of days

Not a common report. - worker_badges table determines where the badge is billed to

[https://redash.sitemetric.com/queries/5756/source?p_SITE ID=["OH90537338"%2C"OH18359303"]&p_date=d_now&p_days=60](https://redash.sitemetric.com/queries/5756/source?p_SITE%20ID=%5B%22OH90537338%22%2C%22OH18359303%22%5D&p_date=d_now&p_days=60)

Morning Scan Report

List of times each worker scanned today Made into a “Generic Report”

https://redash.sitemetric.com/queries/5798/source?p_Date=d_now&p_site_id=GA27364899#10244

Badge-In Report

Lists the first scan time for each worker onsite.

https://redash.sitemetric.com/queries/5803/source?p_Date=d_now&p_site_id=WAGHU7FAV5

Certification Report

All certs and expiring certs. form_id is usually from the self-reg form Need to check why the attachments are not working

https://redash.sitemetric.com/dashboards/16377-certification-report?p_Expiring in (Days)%3A=30&p_w70089_Expiring in (Days)%3A=30

Report Exclusions

List of all the report exclusions added to a site

https://redash.sitemetric.com/dashboards/16438-report-exclusions?p_site_id=GA6NAA84NK

Max Company Headcounts for the week

List the max daily headcount for each company for the week Tie breaker is the day with the higher hours

[https://redash.sitemetric.com/queries/5813/source?p_Date=d_yesterday&p_company_id=["All"]&p_date_range=d_last_week&p_dates_to_exclude=["2024-12-25"]&p_day_numbers=["0"%2C"1"%2C"2"%2C"3"%2C"4"%2C"5"%2C"6"]&p_site_id=AZ38711074&p_sitemetric_id=["All"]](https://redash.sitemetric.com/queries/5813/source?p_Date=d_yesterday&p_company_id=%5B%22All%22%5D&p_date_range=d_last_week&p_dates_to_exclude=%5B%222024-12-25%22%5D&p_day_numbers=%5B%220%22%2C%221%22%2C%222%22%2C%223%22%2C%224%22%2C%225%22%2C%226%22%5D&p_site_id=AZ38711074&p_sitemetric_id=%5B%22All%22%5D)

Form Dropdown (Hardcoded companies)

List the max daily headcount for each compa

SELECT
  name AS text,
  name AS value
FROM
  companies co
WHERE
  co.company_id IN (
    '0782FEDC3B',
    'U1CFVM85PJ',
    'F7D2D029DE',
    '5879271F9D'
  )
ORDER BY
  name

Send this to Nate or whoever put in the request:

CUSTOMER PROJECT SITE_ID https://us.sitemetric.com/api/v3/redash/query/QUERY_ID_GOES_HERE/rows?api_key=API_KEY_GOES_HERE

Report “Inclusions”

When a customer provides a list of companies they want included in their reports only: 1. Interpret the list as an inclusion list, they only want to see data from these specific companies. 2. To implement this, identify all company IDs *with site access* that are not on the inclusion list. 3. Add those company IDs to the Report Exclusions – essentially, you're taking the reverse of the provided list. 4. Use the script below to generate the list of companies to exclude.

SELECT sc.company_id
FROM site_companies sc
WHERE sc.site_id = 'ADD_SITE_ID'
  AND sc.company_id NOT IN ('ADD_COMPANY_ID','ADD_COMPANY_ID','ADD_COMPANY_ID','ADD_COMPANY_ID')
  AND sc.company_id NOT IN (
    SELECT company_id
    FROM site_companies
    WHERE site_id = 'ADD_SITE_ID'
      AND (parent_id IN ('ADD_COMPANY_ID','ADD_COMPANY_ID','ADD_COMPANY_ID','ADD_COMPANY_ID')
           OR primary_id IN ('ADD_COMPANY_ID','ADD_COMPANY_ID','ADD_COMPANY_ID','ADD_COMPANY_ID'))
  )

If given an excel file with company_id, you can use this

def format_for_sql(input_text):
    ids = [line.strip() for line in input_text.strip().splitlines() if line.strip()]
    return ', '.join(f"'{id}'" for id in ids) # Wrap each id in single quotes and join with commas and space

# Example company_ids: paste your all your ids here
raw_list = """
66B4293B2B
KPDKHK022W
90337267DC
1H776XTZ4D
7CB48110F6
5E47D054EE
"""

formatted_list = format_for_sql(raw_list)
print(formatted_list)

Insert one Report Exclusion

INSERT INTO site_report_exclusions (site_id, rule_type, created_at, updated_at, deleted_at, value)
SELECT 'GA77596503', 'company_id', NOW(), NOW(), NULL, val
FROM (
    SELECT 'E1E8BDC8F7' AS val
) AS exclusions
WHERE NOT EXISTS (
    SELECT 1
    FROM site_report_exclusions sre
    WHERE sre.site_id = 'GA77596503'
      AND sre.value = exclusions.val
);

Fixing a worker’s company

For example, if the worker was put into the system under the wrong company for a site, and we need to fix it for the future and historically. First, someone would remove his access and re-grant access with the correct company in the app. Then, as an example:

UPDATE data.dailytech
SET
    company_id = 'C72291C959'
WHERE
    sitemetric_id = 'NXBP0TNMSS'
    AND site_id = 'WA35950425'
    AND company_id = '4WTZ7YSQYC';

UPDATE data.dailyhours
SET
    company_id = 'C72291C959'
WHERE
    sitemetric_id = 'NXBP0TNMSS'
    AND site_id = 'WA35950425'
    AND company_id = '4WTZ7YSQYC';

UPDATE data.sessions
SET
    company_id = 'C72291C959'
WHERE
    sitemetric_id = 'NXBP0TNMSS'
    AND site_id = 'WA35950425'
    AND company_id = '4WTZ7YSQYC';

Sitemetric Worker Report

To run a report on a sitemetric worker on all sites.

[https://redash.sitemetric.com/dashboards/17197-sitemetric-worker-report?p_date_range=2025-03-16--2025-03-22&p_sitemetric_id=["AFZ8149N3B"%2C"AC9EJKIC0J"%2C"17QH7618XG"%2C"2JH64U0YIG"]&p_w73316_date_range=2025-03-16--2025-03-22&p_w73316_sitemetric_id=["AFZ8149N3B"%2C"AC9EJKIC0J"%2C"17QH7618XG"%2C"2JH64U0YIG"]&p_w73317_date_range=2025-03-16--2025-03-22&p_w73317_sitemetric_id=["AFZ8149N3B"%2C"AC9EJKIC0J"%2C"17QH7618XG"%2C"2JH64U0YIG"]](https://redash.sitemetric.com/dashboards/17197-sitemetric-worker-report?p_date_range=2025-03-16--2025-03-22&p_sitemetric_id=%5B%22AFZ8149N3B%22%2C%22AC9EJKIC0J%22%2C%2217QH7618XG%22%2C%222JH64U0YIG%22%5D&p_w73316_date_range=2025-03-16--2025-03-22&p_w73316_sitemetric_id=%5B%22AFZ8149N3B%22%2C%22AC9EJKIC0J%22%2C%2217QH7618XG%22%2C%222JH64U0YIG%22%5D&p_w73317_date_range=2025-03-16--2025-03-22&p_w73317_sitemetric_id=%5B%22AFZ8149N3B%22%2C%22AC9EJKIC0J%22%2C%2217QH7618XG%22%2C%222JH64U0YIG%22%5D)

On-demand: https://us.sitemetric.com/sites/US81228450/reports/on-demand-reports/new/redash-dashboard/17197

Headcount and Hours Report (Worker Hours) (Excel Version)

`Worker Daily Hours`: https://redash.sitemetric.com/queries/5892/source?p_date_range=2025-03-26--2025-03-27&p_site_id=TXP4D6KZV5

`Companies Totals`: https://redash.sitemetric.com/queries/5894/source?p_date_range=d_last_month&p_site_id=TXP4D6KZV5

`Companies Daily`: https://redash.sitemetric.com/queries/5895/source?p_date_range=d_last_month&p_site_id=TXP4D6KZV5

Tiered Subs using Daily Hours (Date)

`Worker Daily Hours`: https://redash.sitemetric.com/queries/5924?p_date=d_now&p_site_id=PAAMV78AG8

`Companies Totals`: https://redash.sitemetric.com/queries/5923?p_date=d_now&p_site_id=PAAMV78AG8

Overnight shifts per Calendar day

(Sub-Tier Company)(Date)

Overnight shifts are split and counted based on the calendar day. For example, on 4/1, if a worker is onsite from 12:00 AM–2:00 AM and again from 10:00 PM–11:59 PM, the Worker Hours tab will show two separate rows — one for 12–2 AM and another for 10–11:59 PM. In the Company Totals tab, the Headcount column counts each worker only once per day, even if they appear on multiple shifts that day.

April 2025

`Company Totals`: https://redash.sitemetric.com/queries/5928/source?p_date=d_now&p_site_id=VA21712216 `Worker Hours`: https://redash.sitemetric.com/queries/5927/source?p_date=d_now&p_site_id=VA21712216

May 13, 2025

`Company Totals`: https://redash.sitemetric.com/queries/6153/source?p_date=d_yesterday&p_site_id=DC87587423 `Worker Hours`: https://redash.sitemetric.com/queries/6149/source?p_date=d_yesterday&p_site_id=DC87587423

`Stationary`: https://redash.sitemetric.com/queries/6152/source?p_date=d_yesterday&p_site_id=DC87587423 `All Detection`: https://redash.sitemetric.com/queries/6150/source?p_date=d_yesterday&p_site_id=DC87587423

May 21, 2025

This version excludes any workers whose first detection was on the day of the emailed report, but includes overnight workers who started the previous day.

`Worker Hours`: https://redash.sitemetric.com/queries/6193/source?p_date=d_yesterday&p_site_id=DC87587423

June 18, 2025

`Worker Hours with Date Range`: [https://redash.sitemetric.com/queries/6332/source?p_company_id=["14F802741E"]&p_date=d_yesterday&p_date_range=2025-06-02--2025-06-16&p_gap_hours=540&p_site_id=GA6NAA84NK](https://redash.sitemetric.com/queries/6332/source?p_company_id=%5B%2214F802741E%22%5D&p_date=d_yesterday&p_date_range=2025-06-02--2025-06-16&p_gap_hours=540&p_site_id=GA6NAA84NK)

Low Voltage Badges

https://us.sitemetric.com/sites/VA28019978/reports/report-types/view/314

Site Report Exclusions - Where Are They Being Used

INSERT INTO site_report_exclusions

Setting up a site_id exclusion by manually inserting a record into the site_report_exclusions table. The following reports support it: - Daily Report + - Headcount & Hours - Project Summary - Stationary Badges - Zone Detection Site Pulse does not show campus sites so the child site_id will not

insert into site_report_exclusions (site_id, rule_type, value, created_at, updated_at)
values (
	'IN89963840',
    'site_id',
    'AB12345678', -- replace this with the new site_id
    now(),
    now()
)

Subcontractor Weekly Report

This can also be cloned but would still need to make the parameters static (sitemetric_id, days, etc)

Daily Company Totals

  • [https://redash.sitemetric.com/dashboards/18510-campus---subcontractor-report---date-range---2025-04-24?p_company_id=["BQ7PCMP5GR"]&p_date_range=d_last_week&p_dates_to_exclude=["2024-12-25"]&p_day_numbers=["0"%2C"1"%2C"2"%2C"3"%2C"4"%2C"5"%2C"6"]&p_site_id=WA64849104&p_sitemetric_id=["All"]](https://redash.sitemetric.com/dashboards/18510-campus---subcontractor-report---date-range---2025-04-24?p_company_id=%5B%22BQ7PCMP5GR%22%5D&p_date_range=d_last_week&p_dates_to_exclude=%5B%222024-12-25%22%5D&p_day_numbers=%5B%220%22%2C%221%22%2C%222%22%2C%223%22%2C%224%22%2C%225%22%2C%226%22%5D&p_site_id=WA64849104&p_sitemetric_id=%5B%22All%22%5D)
-- Subcontractor Weekly Report
-- You'll need to change the company_id and all the other parameters (except the site_id)
SELECT clone_dashboard(id, 'PAAMV78AG8') FROM dashboards_by_tags('template,310531187')

Text Message Alert Documentation

This is the text messaging alert documentation we have. Neither is very clear atm.

https://www.notion.so/go-sitemetric/Text-Email-Alerts-Notifications-193fc5f52f208041a979c04f8daf05b4?pvs=4

https://www.notion.so/go-sitemetric/Text-Messaging-Scan-Notification-System-11cfc5f52f208091a9fae9555ae132c8?pvs=4

Equipment & Zone Site Deployments

For getting an excel file of all zones and equipment at a site. Good for giving to field techs when they have report requests that require many different zones or sensors. Ie, easier to copy and paste the ids from an spreadsheet than a PDF.

https://us.sitemetric.com/sites/US81228450/reports/19821?url=https%3A%2F%2Fredash.sitemetric.com%2Fpublic%2Fdashboards%2Fr15JJGiLHPFveSAzFRM39BCtrwHeKCQdKDzI0MAX&name=Equipment %26 Zone Site Deployments

Scheduled Roll Call Report using zones

-- Roll Call Report
SELECT clone_dashboard(id, 'TN88426031') FROM dashboards_by_tags('template,760282893')

[https://redash.sitemetric.com/dashboards/21196-roll-call-report?p_date=d_now&p_detection_zone_id=["YXHFGUBYF6"]&p_end_hr=07&p_end_min=00&p_site_id=GA27364899&p_start_hr=06&p_start_min=30&p_w88829_end_hr=07&p_w88829_end_min=00&p_w88829_start_hr=06&p_w88829_start_min=30&p_w88830_end_hr=07&p_w88830_end_min=00&p_w88830_start_hr=06&p_w88830_start_min=30&p_w88831_end_hr=00&p_w88831_end_min=00&p_w88831_start_hr=00&p_w88831_start_min=00](https://redash.sitemetric.com/dashboards/21196-roll-call-report?p_date=d_now&p_detection_zone_id=%5B%22YXHFGUBYF6%22%5D&p_end_hr=07&p_end_min=00&p_site_id=GA27364899&p_start_hr=06&p_start_min=30&p_w88829_end_hr=07&p_w88829_end_min=00&p_w88829_start_hr=06&p_w88829_start_min=30&p_w88830_end_hr=07&p_w88830_end_min=00&p_w88830_start_hr=06&p_w88830_start_min=30&p_w88831_end_hr=00&p_w88831_end_min=00&p_w88831_start_hr=00&p_w88831_start_min=00)

Weekly Total Zone Hours

- - Shows where the most time was spent -- Moved scanners to a separate report because the hours spent in one entrance or exit scanner zone aren't relevant to zones. And Entrance to Exit hours plus individual zone hours will inflate the number Tabs: Total Zone Hours Daily Zone Hours Daily Worker Zone Hours

https://redash.sitemetric.com/dashboards/20956-weekly-zone-hours?p_date_range=d_last_week&p_site_id=VA23501290

Unflagged Report

Some sites require the customer to “unflag” from workforce before approving site access. These reports join with the audit table to track who unflags a worker registration

Date Range

https://redash.sitemetric.com/queries/5673/source?p_date_range=d_last_7_days&p_site_id=VA14563963

Date

https://redash.sitemetric.com/queries/6310/source?p_date=d_now&p_date_range=d_last_7_days&p_site_id=VA14563963

JSON Form Reports

Use this query to get a list of keys used

SELECT DISTINCT jt.key_name
FROM form_responses,
JSON_TABLE(
  JSON_KEYS(data),
  '$[*]' COLUMNS(key_name VARCHAR(255) PATH '$')
) AS jt
WHERE form_id = '1RB4C6HT4W';

keys:
time
notes
submitter_name
systems_gallery_number
All doors are closed and locked

To check one form field

SELECT JSON_EXTRACT(data, '$."safety_sticker"') AS safety_sticker FROM form_responses WHERE form_id = 'AWAMV4VK2A'
-- Sample Report

WITH site_timezones AS (
    SELECT
    --   sitemetric_id AS site_id,
      timezone
    FROM
      sites
    WHERE
      sitemetric_id = 'VA1CDKE44Z'
)

SELECT
  REPLACE(JSON_EXTRACT(fr.data, '$."time"'), '"', '') AS TIME,
  UPPER(REPLACE(JSON_EXTRACT(fr.data, '$."All doors are closed and locked"'), '"', '')) AS "ALL DOORS CLOSED & LOCKED",
  REPLACE(JSON_EXTRACT(fr.data, '$."notes"'), '"', '') AS NOTES,
  REPLACE(JSON_EXTRACT(fr.data, '$."submitter_name"'), '"', '') AS NAME,

  CONCAT(CONCAT(DATE_FORMAT(CONVERT_TZ(created_at, '+00:00', st.timezone), '%Y-%m-%d'), ' @ '), DATE_FORMAT(CONVERT_TZ(created_at, '+00:00', st.timezone), '%h:%i %p')) AS "FORM SUBMITTED",

GROUP_CONCAT(
  CONCAT(
    'Download'
  ) SEPARATOR '
'
) AS ATTACHMENTS

FROM
  form_responses fr
INNER JOIN attachments a ON a.resource_id = fr.id
CROSS JOIN site_timezones st
WHERE
  form_id = 'HZ23Z828U0'
GROUP BY
  fr.id, fr.data, fr.created_at, st.timezone
ORDER BY
  "SUBMITTED DATE" DESC,
  "SUBMITTED TIME" DESC;

Key Fob Report

https://redash.sitemetric.com/dashboards/21277-key-fob-report

Mustering Report

https://redash.sitemetric.com/queries/6336

https://redash.sitemetric.com/queries/6336/source

Zone Headcount and Hours with PII remove

Google Trade Worker Zone Hours

https://redash.sitemetric.com/queries/6369/source?p_date=2025-06-24&p_site_id=IN98737034

API Key: km3fzElAnnwehpSQvSNwRm6IomcW0x5PfKa0JHH4

Google Zone Headcount and Hours Roll-up

https://redash.sitemetric.com/queries/6368

API Key: rJV8k6YjS0C2Ic0XD1xcIyBZ97WpNXlrfumN3Elm

Microsoft Trade Worker Zone Hours

https://redash.sitemetric.com/queries/6370/

API Key: B4nn5aOiGfBS5qUQMSXIhIlkixtgqXOEreze1Chp

Using API Endpoints to Retrieve Redash Query Results

Trade Report Dashboard

https://redash.sitemetric.com/dashboards/21597-trade-report

Fatigue Report (Ryan Companies request)

Want report/notifications sent of all worker onsite today that have crossed any of the 3 thresholds:

  1. Onsite > 10 consecutive days
  2. Total hours for the past 7 days >= 50 hours
  3. Total hours for the past 30 days >= 200 hours

Challenges that arise from not enough sensors: 1. 6 hour gaps separates worker hours who are onsite for 8 straight hours but only detected at the gates 2. 10 hour gaps fixes challenge #1 3. But then a 10-hour gap combines shifts where a worker scans out at 11:30 PM but then comes back to work at 5 AM. 1. These examples inflate hours that are either slightly higher or are so high they fall into the stationary category and are excluded entirely.

https://redash.sitemetric.com/dashboards/21828-fatigue-report?p_date_range=d_last_30_days

Generic Reports

Moved. See

Generic Reports

Signatures

CONCAT('data:image/png;base64,', REPLACE(JSON_EXTRACT(fr.data, '$."visitorSignature"'), '"', '')) AS SIGNATURE

Example: https://redash.sitemetric.com/queries/6455/source

Customer Sites List

https://redash.sitemetric.com/queries/6454/source

Zone Equipment List

For adding a list of zone equipment to a daily report

https://redash.sitemetric.com/queries/6513/source?p_site_id=CAB3AV2KDY

Muster/Rollcall/Stand-down Reports

Has parameters for date, start/end time, zone_id

https://redash.sitemetric.com/dashboards/23730-roll-call-report?p_date=2025-07-17&p_detection_zone_id=%5B%22X2CCDNQ91Z%22%2C%22E3X12X5NJF%22%5D&p_end_hour=12&p_site_id=NV17166429&p_start_hour=10

-- Roll Call Report with zone, date, time
SELECT clone_dashboard(id, 'TX12859380') FROM dashboards_by_tags('template,1394963170')

Worker Registrations

https://redash.sitemetric.com/queries/6566?p_date=d_yesterday&p_site_id=GA89382519

Fatigue and Safety Reports

`Fatigue Assessment Report` List of flagged workers who meet any of the following: - Onsite for 13 or more consecutive days - Onsite for 59+ hours in the past 7 days - Onsite for 259+ hours in the past 30 days `Safety Compliance Report` Covers any company whose average daily headcount (Mon–Fri) exceeds 25. Includes: - Whether they meet the required 25:1 Worker-to-Safety Officer ratio - Count of onsite Safety Officers (must have Safety Officer trade and OSHA 30+) - Safety Coverage Score: % of time a required Safety Officer was onsite when workers were present

Fatigue Assessments Report:

https://redash.sitemetric.com/dashboards/21828-fatigue-assessments-report?p_date_range=d_last_30_days&p_site_id=GA37493173

Safety Compliance Report:

https://redash.sitemetric.com/dashboards/23317-safety-compliance-report?p_date_range=d_last_7_days&p_site_id=GA37493173

INSERT INTO site_companies – TRADES

-- EXAMPLE:
/*
====================
1. This will INSERT companies that are not already in site_companies
====================
*/

INSERT INTO site_companies (site_id, company_id, parent_id, primary_id, trade, created_at, updated_at, deleted_at)
SELECT co.site_id, co.company_id, co.parent_id, co.primary_id, trade, co.created_at, co.updated_at, co.deleted_at
FROM (
    SELECT 'VA27826262' AS site_id, '274D8AEC8F' AS company_id, '274D8AEC8F' AS parent_id, '274D8AEC8F' AS primary_id, trade AS 'Electrical', NOW() AS created_at, NOW() AS updated_at, NULL AS deleted_at UNION ALL
    SELECT 'VA27826262', 'B50452324B', 'Sitework & Utilities', NOW(), NOW(), NULL UNION ALL
    SELECT 'VA27826262', 'A93B4B20F1', 'Doors, Frames & Hardware', NOW(), NOW(), NULL UNION ALL
    SELECT 'VA27826262', 'A7ABDFBEB5', 'Elevator', NOW(), NOW(), NULL
) co
WHERE NOT EXISTS (
    SELECT 1 FROM site_companies sc
    WHERE sc.site_id = co.site_id
    AND sc.company_id = co.company_id
    AND sc.deleted_at IS NULL
);

/*
====================
2. Use this to UPDATE multiple company trades that are already in site_companies
but do not have a trade assigned
====================
*/
UPDATE site_companies sc
JOIN (
    SELECT 'VA27826262' AS site_id, '274D8AEC8F' AS company_id, 'Electrical' AS trade, NOW() AS updated_at UNION ALL
    SELECT 'VA27826262', 'B50452324B', 'Sitework & Utilities', NOW() UNION ALL
    SELECT 'VA27826262', 'A93B4B20F1', 'Doors, Frames & Hardware', NOW() UNION ALL
    SELECT 'VA27826262', 'A7ABDFBEB5', 'Elevator', NOW()
) updates
ON sc.site_id = updates.site_id
AND sc.company_id = updates.company_id
SET
    sc.trade = updates.trade,
    sc.updated_at = updates.updated_at;

Trade Reports (+ API Documentation)

Weekly Subcontractor Headcount & Hours

Generic report with company_id & sitemetric_id parameters

[https://redash.sitemetric.com/dashboards/23915-weekly-subcontractor-headcount-hours?p_company_id=["All"]&p_date_range=d_last_week&p_site_id=IN89963840&p_sitemetric_id=["All"]](https://redash.sitemetric.com/dashboards/23915-weekly-subcontractor-headcount-hours?p_company_id=%5B%22All%22%5D&p_date_range=d_last_week&p_site_id=IN89963840&p_sitemetric_id=%5B%22All%22%5D)

https://us.sitemetric.com/sites/WAH62J7775/reports/report-types/view/397

Weekly Worker Report

Generic report with company_id & sitemetric_id parameters

https://redash.sitemetric.com/dashboards/23916-weekly-worker-report?p_company_id=%5B%22All%22%5D&p_date_range=d_last_week&p_site_id=VA23501290&p_sitemetric_id=%5B%22MCR8U1FXWG%22%5D

https://us.sitemetric.com/sites/VA23501290/reports/report-types/view/398

Submitted Form

https://us.sitemetric.com/forms/{{ @ }}?r={{ id }}

List of a Parent's Children Sites

https://redash.sitemetric.com/queries/5022?p_parent%20site_id=IA44937699

Updated Recursive site_ids for Campus

WITH RECURSIVE -- Only keep recursive if there's recursion later on in the script
site_ids as (
             SELECT site_id
             FROM (
                   SELECT DISTINCT(site_id) AS site_id
                   FROM data.site_hierarchy_view
                   WHERE site_id = '{{site_id}}'

                   UNION

                   SELECT child_id AS site_id
                   FROM data.site_hierarchy_view
                   WHERE site_id = '{{site_id}}'
                  ) AS combined)

Creating a link to a completed form (closest thing to a PDF version of a completed form)

https://redash.sitemetric.com/queries/6889?p_form_id=WWHMABH8HF&p_site_id=CA15159060

Bulk update redash dashboard tags

[](https://redash.sitemetric.com/queries/6902/source?p_dashboard_id=25900)

Update redash dashboard tags - SITE_ID hardcoded into query https://redash.sitemetric.com/queries/7025/source?p_dashboard_id=26501&p_new_tag=VA08061853&p_new_tags=%5B%22VA62056789%22%2C%22VA63370387%22%2C%22VA87140862%22%2C%22SC00356893%22%2C%22TX43259035%22%5D

Update redash dashboard tags - SITE_ID with Dropdown

https://redash.sitemetric.com/queries/7024/source?p_dashboard_id=26503&p_new_tag=VA08061853&p_new_tags=%5B%22VA62056789%22%2C%22VA63370387%22%2C%22VA87140862%22%2C%22SC00356893%22%2C%22TX43259035%22%5D

Update redash dashboard tags - One SITE_ID

https://redash.sitemetric.com/queries/7023/source?p_dashboard_id=26503&p_new_tag=VA08061853

Counting Buildings per Campus

http://redash.sitemetric.com/queries/6951/source

Site Billing Dashboard for accounting

https://us.sitemetric.com/sites/US81228450/reports/27499?url=https%3A%2F%2Fus.sitemetric.com%2Freports%2Fdashboard%2F27499%3Fembed%3Dinternal%26p_site_id%3DUS81228450%26p_date%3Dd_now%26p_headcount_threshold%3D10%26p_headcount_date_range%3D30&name=Billing+Start+Date+with+Headcounts

Company Dropdown format

Turner: MDN06 NC57958796 https://us.sitemetric.com/api/v3/redash/query/5825/rows?api_key=pA851BaWpySfynQlVqk1RxkYWDsozVDMal5FWsbA

custom_data and custom_fields tables

https://sitemetric.slack.com/archives/C08V7S85K88/p1760450608276649

From Jackson:

First, create the row in the "custom_fields" table that will define this field. Values for that below: - created_at, updated_at, deleted_at (null), id - Autogenerated - customer_id - Customer id of Microsoft - custom_id - Custom id that uniquely identifies this field. Can be generated with the generate_id() function - name - Something like "worker_site_title" - description - Descriptor of what the field is representing. I'd also describe how this data would get joined to the "worker_site_access" table in here - resource_type - "worker_site_access_view" Then, we populate the "custom_data" table: - created_at, updated_at, deleted_at (null) - Autogenerated - custom_id - The custom id that was created in the custom_fields table - resource_id - This'll get a bit wonky, but it'll work. Make this the concat of the worker_site_access_view (edited) sitemetric_id and worker_id. So if you have a worker with sitemetric_id of 'L86LP6LJVX' in worker_site_access_view with a site_id of 'MD30743307', then the field would be 'L86LP6LJVXMD30743307'. Unfortunately we can't do just the unique id from worker_site_access_view because that can get superseded by later worker_site_accesses for a specific worker. - value - Worker title on that site, e.g. 'Plumber' So then when you need to retrieve worker titles for a specific site, you can run something like this:

SELECT
    wsa.sitemetric_id,
    wsa.site_id,
    cd.value AS worker_site_title
FROM worker_site_access wsa
INNER JOIN custom_data cd
    ON cd.resource_id = CONCAT(wsa.sitemetric_id, wsa.site_id)
WHERE
    cd.deleted_at IS NULL
    AND wsa.deleted_at IS NULL;

Returns:

Compare Static site_id with tags

To make sure there are no dashboards with static site_ids that do not match the site_id tag. There are a few remaining but they are demo dashboards.

https://redash.sitemetric.com/queries/7225/source

These are reports that can be used for any site. They include a site_id tag. When viewed in dashview, the site_id dropdown will not be visible, and the system will automatically use the current site_id from the app.

Internal reports will display the site_id dropdown only if the user has access to reports from the internal reports site.

Generic Dashboards

  • Just need to add site_id tag to show in-app.

Generic Dashboards

TODO: We need to clean this up and organize it a little better. Not sure how yet.

*Do these go in the list above? I guess they should? I don’t know why they weren’t there already.*

`Monthly Headcount and Hours`

https://us.sitemetric.com/sites/TXX5WM16KZ/reports/report-types/view/368

dash.us.sitemetric.com

`Weekly Headcount and Hours`

https://us.sitemetric.com/sites/TXX5WM16KZ/reports/report-types/view/369 With site_id parameter

dash.us.sitemetric.com With site_id parameter

https://us.sitemetric.com/sites/WAH62J7775/reports/report-types/view/397 With company_id & sitemetric_id parameters

dash.us.sitemetric.com With company_id & sitemetric_id parameters

`Daily Scan Report` (with Daily Zone Hours from sensors)

https://us.sitemetric.com/sites/MN10139625/reports/report-types/view/353

dash.us.sitemetric.com

Trade Reports

These Trade Reports (created for Roqeeb’s google pilots sites) are all generic now. We need to make campus-enabled ones too. (Not hard, just haven’t done it yet.)

Dashboards needing to be cloned and parameters manually set to static

⁉️ But… if you have to clone it, then it’s not *generic* right?

/*
Portfolio Dashboards
*/

-- Portfolio Sites Monthly Headcount << Set owner_id to static >>
SELECT clone_dashboard(id, 'KS56346263') FROM dashboards_by_tags('template,513197122')

-- Portfolio Sites Monthly Hours << Set owner_id to static >>
SELECT clone_dashboard(id, 'KS56346263') FROM dashboards_by_tags('template,767241542')

-- Portfolio Compannies Monthly Headcount & Hours << Set owner_id to static >>
SELECT clone_dashboard(id, 'KS56346263') FROM dashboards_by_tags('template,333117014')

-- Portfolio Compannies Directory << Set owner_id to static >>
SELECT clone_dashboard(id, 'KS56346263') FROM dashboards_by_tags('template,881714844')

-- Portfolio Sites Weekly Headcount << Set owner_id to static >>
SELECT clone_dashboard(id, 'KS56346263') FROM dashboards_by_tags('template,108393844')

-- Portfolio Sites Weekly Hours << Set owner_id to static >>
SELECT clone_dashboard(id, 'KS56346263') FROM dashboards_by_tags('template,904025682')

/*
Other Dashboards
*/

-- Single Scan
SELECT clone_dashboard(id, 'PAAMV78AG8') FROM dashboards_by_tags('template,58423891')

-- Perimeter Check Dashboard <
  
   > -- One form and QR code with checkpoint dropdown SELECT clone_dashboard(id, 'CA15159060') FROM dashboards_by_tags('template,745305981') -- Link to dropdown query: https://dash.us.sitemetric.com/queries/5026 -- Security Log Patrol Action Report <
   
    > -- One form and multiple QR codes that populate the checkpoint selection SELECT clone_dashboard(id, 'CA15159060') FROM dashboards_by_tags('template,403243097') -- Link to dropdown query: https://dash.us.sitemetric.com/queries/5026 -- Manage Worker Labels << Set form_id to static >> SELECT clone_dashboard(id, 'KS56346263') FROM dashboards_by_tags('template,594430044') -- Link to dropdown query: https://dash.us.sitemetric.com/queries/5026 -- Daily Report that includes a visitor form << Set the form_id to static >> SELECT clone_dashboard(id, 'UT61663758') FROM dashboards_by_tags('template,320675079')

https://dash.us.sitemetric.com/dashboards/25581-company-directory---hierarchies?p_site_id=PAAMV78AG8

Generic Report Types (Scheduled email reports)

We now have a series of trade reports that can be accessed via an API endpoint, however I want to phase that out and have them use a different data delivery mechanism if possible.

Now we have the ability to dump the data into S3 daily, with Azure as an upcoming option.

There are two ways to access this data:

  1. Trade Reports API (I’m trying to deprecate this; hardly anyone uses it anyway, and possibly no one uses it)
  2. Trade Reports Data Delivery via S3/Azure Blob Storage

Trade Reports - BigQuery Data Transfer Setup

Report Descriptions

Trade Worker Hours

Includes every individual worker’s hours during the specified time frame, by day.

Trade Worker Times

Includes

Trade Summary

Shows the total hours that trades spent onsite during the specified time range.

Trade Zone Summary

Shows the total hours that trades spent in each zone during the specified time range.

Trade Reports API

*I’m trying to deprecate this in favor of the Data Delivery method via S3/Azure Blob Storage.*

They all have the exact same parameters. And every request requires authentication. You can learn about our authentication here: https://us.sitemetric.com/docs.html#auth

In short: POST to /auth/token with your client_id and client_secret, and you’ll get back your bearer token, which you will then use to make further authenticated requests.

⚠️ The queries that power these reports are quite hefty. If you get timeouts, you may need to get smaller chunks. For example, you may have to get the data one day at a time.

Required Parameters

site_id

Replace with the Site ID of the project site; this is replaced in the URL itself; it is not a query string parameter.

Optional Query String Parameters

companyIds

?companyIds=AB12345678,XY12345678

Default: No filters

Filters the data by the specified Company IDs. A comma separated list.

endDate

?endDate=2025-08-04

Default: Yesterday’s date

Specifies an end date for the date range. See startDate.

excludePII

?excludePII=true

Default: false

Excludes name and sitemetric_id from the data.

hashPII

?hashPII=true

Default: false

Creates a unique AnonWorkerId for every worker, so that you can still group detections by an individual worker without knowing who they are.

"anonWorkerId": "12342dda4ab1a67f",

page

?page=1

Default: 1

Which page of data you’d like to get. We use Link and X-Pagination headers to give you additional information about the paged results. Also see perPage.

Link:

  
   ; per_page="25"; rel="first prev", 
   
    ; per_page="25"; rel="next", 
    
     ; per_page="25"; rel="last" X-Pagination: {"currentPage":1,"perPage":25,"total":1797,"lastPage":72}

perPage

?perPage=100

Default: 25

The number of items to return per page. Also see page.

startDate

?startDate=2025-08-01

Default: Yesterday

Specifies a start date for the date range. See endDate.

titles

?titles=Electrician,Plumber

Default: No filters

Filters the data by the specified titles. A comma separated list.

trades

?trades=Electrician,Plumber

Default: No filters

Filters the data by the specified trades. A comma separated list.

zoneIds

?zoneIds=AB12345678,XY12345678

Default: No filters

Filters the data by the specified Zone Ids. A comma separated list.

Parameter Combinations

You can combine them all together as needed, like so:

?page=1&perPage=1000&companyIds=AB12345678&excludePII=true&hashPII=true

Trade Worker Hours

https://us.sitemetric.com/api/v3/reports/sites/{{site_id}}/trade-reports/worker-hours

Includes every individual worker’s hours during the specified time frame, by day.

[
    {
        "date": "2025-08-03",
        "zone": "Exterior Field Office",
        "zoneId": "XYF0G1FXZT",
        "company": "ABC Technologies",
        "companyId": "1BKPA6SWYS",
        "trade": "Deliveries",
        "title": "Delivery Driver",
        "sitemetricId": "1AB2C3D4E5",
        "name": "John Doe",
        "totalHours": 7.95
    }
]

Trade Worker Times

https://us.sitemetric.com/api/v3/reports/sites/{{site_id}}/trade-reports/worker-times

Includes

[
    {
        "date": "2025-08-03",
        "zone": "Exterior Field Office",
        "zoneId": "XYF0G1FXZT",
        "company": "ABC Technologies",
        "companyId": "1BKPA6SWYS",
        "trade": "Deliveries",
        "title": "Delivery Driver",
        "sitemetricId": "1AB2C3D4E5",
        "name": "John Doe",
        "inTime": "2025-08-03T06:00:54.000Z",
        "outTime": "2025-08-03T13:55:25.000Z"
    }
]

Trade Summary

https://us.sitemetric.com/api/v3/reports/sites/{{site_id}}/trade-reports/trade-summary

Shows the total hours that trades spent onsite during the specified time range.

[
    {
        "trade": "Electrical",
        "totalHours": 48.1,
        "headcount": 2
    }
]

Trade Zone Summary

https://us.sitemetric.com/api/v3/reports/sites/{{site_id}}/trade-reports/trade-zone-summary

Shows the total hours that trades spent in each zone during the specified time range.

[
    {
        "trade": "Electrical",
        "zone": "Break Tent",
        "zoneId": "W5REJ8EZDP",
        "totalHours": 6.13,
        "headcount": 2
    },
    {
        "trade": "Electrical",
        "zone": "Exterior Bathroom Building",
        "zoneId": "X3DC8147HJ",
        "totalHours": 3.65,
        "headcount": 1
    }
]

We can access redash data by using special endpoints.

TODO: Add documentation for the other ways we can do this, or how to get these encrypted query IDs (use the Encrypt endpoint — see Postman).

Here are some examples.

Google Sites - Zone Headcount Hours

https://redash.sitemetric.com/queries/6368/source

api_key: rJV8k6YjS0C2Ic0XD1xcIyBZ97WpNXlrfumN3Elm query_id: 6368 encrypted query_id: b8wBN6OyQYgLJOzGcebjpw==.ocnvL9ZKDtzyxC2us7joyg==

curl --location 'https://us.sitemetric.com/api/v3/dash/queries/b8wBN6OyQYgLJOzGcebjpw==.ocnvL9ZKDtzyxC2us7joyg==/results?api_key=rJV8k6YjS0C2Ic0XD1xcIyBZ97WpNXlrfumN3Elm' \
--header 'Content-Type: application/json' \
--header 'Authorization: Bearer ...' \
--data '{
    "parameters": {}
}'

Google Sites - Trade Worker Zone Hours

https://redash.sitemetric.com/queries/6369/source api_key: km3fzElAnnwehpSQvSNwRm6IomcW0x5PfKa0JHH4 query_id: 6369 encrypted query_id: J_f9MJ-Fpw8Gnb9nlxHAUA==.u-EYrzcomuvlri2GtPE_Sg==

curl --location 'https://us.sitemetric.com/api/v3/dash/queries/J_f9MJ-Fpw8Gnb9nlxHAUA==.u-EYrzcomuvlri2GtPE_Sg==/results?api_key=km3fzElAnnwehpSQvSNwRm6IomcW0x5PfKa0JHH4' \
--header 'Content-Type: application/json' \
--header 'Authorization: Bearer ...' \
--data '{
    "parameters": {}
}'

Microsoft Sites - Trade Worker Zone Hours

https://redash.sitemetric.com/queries/6370/source api_key: B4nn5aOiGfBS5qUQMSXIhIlkixtgqXOEreze1Chp query_id: 6370 encrypted query_id: QJ03gO6IuC-mZRQ2wrfXKA==.rm9D9UrKURLYjiipmgbJuw==

curl --location 'https://us.sitemetric.com/api/v3/dash/queries/QJ03gO6IuC-mZRQ2wrfXKA==.rm9D9UrKURLYjiipmgbJuw==/results?api_key=B4nn5aOiGfBS5qUQMSXIhIlkixtgqXOEreze1Chp' \
--header 'Content-Type: application/json' \
--header 'Authorization: Bearer ...' \
--data '{
    "parameters": {}
}'

*Accurate as of 4/21/2025*

This table shows the report and which exclusions are in place:

  • DailyHours Table (Not retroactive) — Companies: No; Zones: Yes; Sites: No; Date: No; Day of Week: No; Supports site_companies: No; Campus Enabled: No
  • Sessions Table (Not retroactive) — Companies: No; Zones: Yes; Sites: No; Date: No; Day of Week: No; Supports site_companies: No; Campus Enabled: No
  • Daily Report+ Dashboard: 15760 PDF query: 6007 — Companies: Yes; Zones: Yes; Sites: Yes; Date: No; Day of Week: No; Supports site_companies: Yes; Campus Enabled: Yes
  • Headcount & Hours — Companies: Yes; Zones: Yes; Sites: Yes; Date: Yes; Day of Week: Yes; Supports site_companies: No; Campus Enabled: Yes
  • Project Summary — Companies: Yes; Zones: Sort of**; Sites: Yes; Date: Yes; Day of Week: Yes; Supports site_companies: No; Campus Enabled: Yes
  • Real-Time Location — Companies: Yes; Zones: No; Sites: No; Date: No; Day of Week: No; Supports site_companies: No; Campus Enabled: Yes
  • Site Access — Companies: Yes; Zones: N/A; Sites: No; Date: N/A; Day of Week: N/A; Supports site_companies: No; Campus Enabled: No
  • Site Pulse — Companies: Yes; Zones: Yes; Sites: No; Date: No; Day of Week: No; Supports site_companies: No; Campus Enabled: Yes
  • Zone Headcount & Hours — Companies: Yes; Zones: No; Sites: No; Date: No; Day of Week: No; Supports site_companies: No; Campus Enabled: Yes

⚠️ If custom queries are used, you’d have to check those individually.

** = Sort of; Project Summary uses DailyHours which would start filtering them out from the time that exclusion was added, but it won’t be retroactive.