| surveilr_osquery_ms_node_detail |
available_space |
|
/console/info-schema/view.sql?name=surveilr_osquery_ms_node_detail&stats=yes |
[Content](console/info-schema/view.sql?name=surveilr_osquery_ms_node_detail&stats=yes) |
[surveilr_osquery_ms_node_detail (view) Schema](console/info-schema/view.sql?name=surveilr_osquery_ms_node_detail&stats=yes) |
/console/content/view/surveilr_osquery_ms_node_detail.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/surveilr_osquery_ms_node_detail.sql?stats=yes) |
[surveilr_osquery_ms_node_detail (view) Content](console/content/view/surveilr_osquery_ms_node_detail.sql?stats=yes) |
CREATE VIEW surveilr_osquery_ms_node_detail AS
SELECT
n.surveilr_osquery_ms_node_id,
n.node_key,
n.host_identifier,
n.osquery_version,
n.last_seen,
n.created_at,
i.updated_at,
i.address AS ip_address,
i.mac,
b.boundary,
CASE
WHEN (strftime('%s', 'now') - strftime('%s', n.created_at)) < 60 THEN
(strftime('%s', 'now') - strftime('%s', n.created_at)) || ' seconds ago'
WHEN (strftime('%s', 'now') - strftime('%s', n.created_at)) < 3600 THEN
((strftime('%s', 'now') - strftime('%s', n.created_at)) / 60) || ' minutes ago'
WHEN (strftime('%s', 'now') - strftime('%s', n.created_at)) < 86400 THEN
((strftime('%s', 'now') - strftime('%s', n.created_at)) / 3600) || ' hours ago'
ELSE
((strftime('%s', 'now') - strftime('%s', n.created_at)) / 86400) || ' days ago'
END AS added_to_surveilr_osquery_ms,
o.name AS operating_system,
round(a.available_space, 2) || ' GB' AS available_space,
CASE
WHEN (strftime('%s', 'now') - strftime('%s', last_seen)) < 60 THEN 'Online'
ELSE 'Offline'
END AS node_status,
CASE
WHEN (strftime('%s', 'now') - strftime('%s', n.last_seen)) < 60 THEN
(strftime('%s', 'now') - strftime('%s', n.last_seen)) || ' seconds ago'
WHEN (strftime('%s', 'now') - strftime('%s', n.last_seen)) < 3600 THEN
((strftime('%s', 'now') - strftime('%s', n.last_seen)) / 60) || ' minutes ago'
WHEN (strftime('%s', 'now') - strftime('%s', n.last_seen)) < 86400 THEN
((strftime('%s', 'now') - strftime('%s', n.last_seen)) / 3600) || ' hours ago'
ELSE
((strftime('%s', 'now') - strftime('%s', n.last_seen)) / 86400) || ' days ago'
END AS last_fetched,
CASE
WHEN CAST(u.days AS INTEGER) > 0 THEN
'about ' || u.days || ' day' || (CASE WHEN CAST(u.days AS INTEGER) = 1 THEN '' ELSE 's' END) || ' ago'
WHEN CAST(u.hours AS INTEGER) > 0 THEN
'about ' || u.hours || ' hour' || (CASE WHEN CAST(u.hours AS INTEGER) = 1 THEN '' ELSE 's' END) || ' ago'
WHEN CAST(u.minutes AS INTEGER) > 0 THEN
'about ' || u.minutes || ' minute' || (CASE WHEN CAST(u.minutes AS INTEGER) = 1 THEN '' ELSE 's' END) || ' ago'
ELSE
'about ' || u.seconds || ' second' || (CASE WHEN CAST(u.seconds AS INTEGER) = 1 THEN '' ELSE 's' END) || ' ago'
END AS last_restarted,
COALESCE(failed_policies.failed_count, 0) AS issues
FROM surveilr_osquery_ms_node n
LEFT JOIN surveilr_osquery_ms_node_available_space a ON n.node_key = a.node_key
LEFT JOIN surveilr_osquery_ms_node_os_version o ON n.node_key = o.node_key
LEFT JOIN surveilr_osquery_ms_node_uptime u ON n.node_key = u.node_key
LEFT JOIN surveilr_osquery_ms_node_interface_address i ON n.node_key = i.node_key
LEFT JOIN surveilr_osquery_ms_node_boundary b ON n.node_key = b.node_key
LEFT JOIN (
SELECT node_key, COUNT(*) AS failed_count
FROM surveilr_osquery_ms_node_executed_policy
WHERE policy_result = 'Fail'
GROUP BY node_key
) AS failed_policies ON n.node_key = failed_policies.node_key |
| surveilr_osquery_ms_node_detail |
node_status |
|
/console/info-schema/view.sql?name=surveilr_osquery_ms_node_detail&stats=yes |
[Content](console/info-schema/view.sql?name=surveilr_osquery_ms_node_detail&stats=yes) |
[surveilr_osquery_ms_node_detail (view) Schema](console/info-schema/view.sql?name=surveilr_osquery_ms_node_detail&stats=yes) |
/console/content/view/surveilr_osquery_ms_node_detail.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/surveilr_osquery_ms_node_detail.sql?stats=yes) |
[surveilr_osquery_ms_node_detail (view) Content](console/content/view/surveilr_osquery_ms_node_detail.sql?stats=yes) |
CREATE VIEW surveilr_osquery_ms_node_detail AS
SELECT
n.surveilr_osquery_ms_node_id,
n.node_key,
n.host_identifier,
n.osquery_version,
n.last_seen,
n.created_at,
i.updated_at,
i.address AS ip_address,
i.mac,
b.boundary,
CASE
WHEN (strftime('%s', 'now') - strftime('%s', n.created_at)) < 60 THEN
(strftime('%s', 'now') - strftime('%s', n.created_at)) || ' seconds ago'
WHEN (strftime('%s', 'now') - strftime('%s', n.created_at)) < 3600 THEN
((strftime('%s', 'now') - strftime('%s', n.created_at)) / 60) || ' minutes ago'
WHEN (strftime('%s', 'now') - strftime('%s', n.created_at)) < 86400 THEN
((strftime('%s', 'now') - strftime('%s', n.created_at)) / 3600) || ' hours ago'
ELSE
((strftime('%s', 'now') - strftime('%s', n.created_at)) / 86400) || ' days ago'
END AS added_to_surveilr_osquery_ms,
o.name AS operating_system,
round(a.available_space, 2) || ' GB' AS available_space,
CASE
WHEN (strftime('%s', 'now') - strftime('%s', last_seen)) < 60 THEN 'Online'
ELSE 'Offline'
END AS node_status,
CASE
WHEN (strftime('%s', 'now') - strftime('%s', n.last_seen)) < 60 THEN
(strftime('%s', 'now') - strftime('%s', n.last_seen)) || ' seconds ago'
WHEN (strftime('%s', 'now') - strftime('%s', n.last_seen)) < 3600 THEN
((strftime('%s', 'now') - strftime('%s', n.last_seen)) / 60) || ' minutes ago'
WHEN (strftime('%s', 'now') - strftime('%s', n.last_seen)) < 86400 THEN
((strftime('%s', 'now') - strftime('%s', n.last_seen)) / 3600) || ' hours ago'
ELSE
((strftime('%s', 'now') - strftime('%s', n.last_seen)) / 86400) || ' days ago'
END AS last_fetched,
CASE
WHEN CAST(u.days AS INTEGER) > 0 THEN
'about ' || u.days || ' day' || (CASE WHEN CAST(u.days AS INTEGER) = 1 THEN '' ELSE 's' END) || ' ago'
WHEN CAST(u.hours AS INTEGER) > 0 THEN
'about ' || u.hours || ' hour' || (CASE WHEN CAST(u.hours AS INTEGER) = 1 THEN '' ELSE 's' END) || ' ago'
WHEN CAST(u.minutes AS INTEGER) > 0 THEN
'about ' || u.minutes || ' minute' || (CASE WHEN CAST(u.minutes AS INTEGER) = 1 THEN '' ELSE 's' END) || ' ago'
ELSE
'about ' || u.seconds || ' second' || (CASE WHEN CAST(u.seconds AS INTEGER) = 1 THEN '' ELSE 's' END) || ' ago'
END AS last_restarted,
COALESCE(failed_policies.failed_count, 0) AS issues
FROM surveilr_osquery_ms_node n
LEFT JOIN surveilr_osquery_ms_node_available_space a ON n.node_key = a.node_key
LEFT JOIN surveilr_osquery_ms_node_os_version o ON n.node_key = o.node_key
LEFT JOIN surveilr_osquery_ms_node_uptime u ON n.node_key = u.node_key
LEFT JOIN surveilr_osquery_ms_node_interface_address i ON n.node_key = i.node_key
LEFT JOIN surveilr_osquery_ms_node_boundary b ON n.node_key = b.node_key
LEFT JOIN (
SELECT node_key, COUNT(*) AS failed_count
FROM surveilr_osquery_ms_node_executed_policy
WHERE policy_result = 'Fail'
GROUP BY node_key
) AS failed_policies ON n.node_key = failed_policies.node_key |
| surveilr_osquery_ms_node_detail |
last_fetched |
|
/console/info-schema/view.sql?name=surveilr_osquery_ms_node_detail&stats=yes |
[Content](console/info-schema/view.sql?name=surveilr_osquery_ms_node_detail&stats=yes) |
[surveilr_osquery_ms_node_detail (view) Schema](console/info-schema/view.sql?name=surveilr_osquery_ms_node_detail&stats=yes) |
/console/content/view/surveilr_osquery_ms_node_detail.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/surveilr_osquery_ms_node_detail.sql?stats=yes) |
[surveilr_osquery_ms_node_detail (view) Content](console/content/view/surveilr_osquery_ms_node_detail.sql?stats=yes) |
CREATE VIEW surveilr_osquery_ms_node_detail AS
SELECT
n.surveilr_osquery_ms_node_id,
n.node_key,
n.host_identifier,
n.osquery_version,
n.last_seen,
n.created_at,
i.updated_at,
i.address AS ip_address,
i.mac,
b.boundary,
CASE
WHEN (strftime('%s', 'now') - strftime('%s', n.created_at)) < 60 THEN
(strftime('%s', 'now') - strftime('%s', n.created_at)) || ' seconds ago'
WHEN (strftime('%s', 'now') - strftime('%s', n.created_at)) < 3600 THEN
((strftime('%s', 'now') - strftime('%s', n.created_at)) / 60) || ' minutes ago'
WHEN (strftime('%s', 'now') - strftime('%s', n.created_at)) < 86400 THEN
((strftime('%s', 'now') - strftime('%s', n.created_at)) / 3600) || ' hours ago'
ELSE
((strftime('%s', 'now') - strftime('%s', n.created_at)) / 86400) || ' days ago'
END AS added_to_surveilr_osquery_ms,
o.name AS operating_system,
round(a.available_space, 2) || ' GB' AS available_space,
CASE
WHEN (strftime('%s', 'now') - strftime('%s', last_seen)) < 60 THEN 'Online'
ELSE 'Offline'
END AS node_status,
CASE
WHEN (strftime('%s', 'now') - strftime('%s', n.last_seen)) < 60 THEN
(strftime('%s', 'now') - strftime('%s', n.last_seen)) || ' seconds ago'
WHEN (strftime('%s', 'now') - strftime('%s', n.last_seen)) < 3600 THEN
((strftime('%s', 'now') - strftime('%s', n.last_seen)) / 60) || ' minutes ago'
WHEN (strftime('%s', 'now') - strftime('%s', n.last_seen)) < 86400 THEN
((strftime('%s', 'now') - strftime('%s', n.last_seen)) / 3600) || ' hours ago'
ELSE
((strftime('%s', 'now') - strftime('%s', n.last_seen)) / 86400) || ' days ago'
END AS last_fetched,
CASE
WHEN CAST(u.days AS INTEGER) > 0 THEN
'about ' || u.days || ' day' || (CASE WHEN CAST(u.days AS INTEGER) = 1 THEN '' ELSE 's' END) || ' ago'
WHEN CAST(u.hours AS INTEGER) > 0 THEN
'about ' || u.hours || ' hour' || (CASE WHEN CAST(u.hours AS INTEGER) = 1 THEN '' ELSE 's' END) || ' ago'
WHEN CAST(u.minutes AS INTEGER) > 0 THEN
'about ' || u.minutes || ' minute' || (CASE WHEN CAST(u.minutes AS INTEGER) = 1 THEN '' ELSE 's' END) || ' ago'
ELSE
'about ' || u.seconds || ' second' || (CASE WHEN CAST(u.seconds AS INTEGER) = 1 THEN '' ELSE 's' END) || ' ago'
END AS last_restarted,
COALESCE(failed_policies.failed_count, 0) AS issues
FROM surveilr_osquery_ms_node n
LEFT JOIN surveilr_osquery_ms_node_available_space a ON n.node_key = a.node_key
LEFT JOIN surveilr_osquery_ms_node_os_version o ON n.node_key = o.node_key
LEFT JOIN surveilr_osquery_ms_node_uptime u ON n.node_key = u.node_key
LEFT JOIN surveilr_osquery_ms_node_interface_address i ON n.node_key = i.node_key
LEFT JOIN surveilr_osquery_ms_node_boundary b ON n.node_key = b.node_key
LEFT JOIN (
SELECT node_key, COUNT(*) AS failed_count
FROM surveilr_osquery_ms_node_executed_policy
WHERE policy_result = 'Fail'
GROUP BY node_key
) AS failed_policies ON n.node_key = failed_policies.node_key |
| surveilr_osquery_ms_node_detail |
last_restarted |
|
/console/info-schema/view.sql?name=surveilr_osquery_ms_node_detail&stats=yes |
[Content](console/info-schema/view.sql?name=surveilr_osquery_ms_node_detail&stats=yes) |
[surveilr_osquery_ms_node_detail (view) Schema](console/info-schema/view.sql?name=surveilr_osquery_ms_node_detail&stats=yes) |
/console/content/view/surveilr_osquery_ms_node_detail.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/surveilr_osquery_ms_node_detail.sql?stats=yes) |
[surveilr_osquery_ms_node_detail (view) Content](console/content/view/surveilr_osquery_ms_node_detail.sql?stats=yes) |
CREATE VIEW surveilr_osquery_ms_node_detail AS
SELECT
n.surveilr_osquery_ms_node_id,
n.node_key,
n.host_identifier,
n.osquery_version,
n.last_seen,
n.created_at,
i.updated_at,
i.address AS ip_address,
i.mac,
b.boundary,
CASE
WHEN (strftime('%s', 'now') - strftime('%s', n.created_at)) < 60 THEN
(strftime('%s', 'now') - strftime('%s', n.created_at)) || ' seconds ago'
WHEN (strftime('%s', 'now') - strftime('%s', n.created_at)) < 3600 THEN
((strftime('%s', 'now') - strftime('%s', n.created_at)) / 60) || ' minutes ago'
WHEN (strftime('%s', 'now') - strftime('%s', n.created_at)) < 86400 THEN
((strftime('%s', 'now') - strftime('%s', n.created_at)) / 3600) || ' hours ago'
ELSE
((strftime('%s', 'now') - strftime('%s', n.created_at)) / 86400) || ' days ago'
END AS added_to_surveilr_osquery_ms,
o.name AS operating_system,
round(a.available_space, 2) || ' GB' AS available_space,
CASE
WHEN (strftime('%s', 'now') - strftime('%s', last_seen)) < 60 THEN 'Online'
ELSE 'Offline'
END AS node_status,
CASE
WHEN (strftime('%s', 'now') - strftime('%s', n.last_seen)) < 60 THEN
(strftime('%s', 'now') - strftime('%s', n.last_seen)) || ' seconds ago'
WHEN (strftime('%s', 'now') - strftime('%s', n.last_seen)) < 3600 THEN
((strftime('%s', 'now') - strftime('%s', n.last_seen)) / 60) || ' minutes ago'
WHEN (strftime('%s', 'now') - strftime('%s', n.last_seen)) < 86400 THEN
((strftime('%s', 'now') - strftime('%s', n.last_seen)) / 3600) || ' hours ago'
ELSE
((strftime('%s', 'now') - strftime('%s', n.last_seen)) / 86400) || ' days ago'
END AS last_fetched,
CASE
WHEN CAST(u.days AS INTEGER) > 0 THEN
'about ' || u.days || ' day' || (CASE WHEN CAST(u.days AS INTEGER) = 1 THEN '' ELSE 's' END) || ' ago'
WHEN CAST(u.hours AS INTEGER) > 0 THEN
'about ' || u.hours || ' hour' || (CASE WHEN CAST(u.hours AS INTEGER) = 1 THEN '' ELSE 's' END) || ' ago'
WHEN CAST(u.minutes AS INTEGER) > 0 THEN
'about ' || u.minutes || ' minute' || (CASE WHEN CAST(u.minutes AS INTEGER) = 1 THEN '' ELSE 's' END) || ' ago'
ELSE
'about ' || u.seconds || ' second' || (CASE WHEN CAST(u.seconds AS INTEGER) = 1 THEN '' ELSE 's' END) || ' ago'
END AS last_restarted,
COALESCE(failed_policies.failed_count, 0) AS issues
FROM surveilr_osquery_ms_node n
LEFT JOIN surveilr_osquery_ms_node_available_space a ON n.node_key = a.node_key
LEFT JOIN surveilr_osquery_ms_node_os_version o ON n.node_key = o.node_key
LEFT JOIN surveilr_osquery_ms_node_uptime u ON n.node_key = u.node_key
LEFT JOIN surveilr_osquery_ms_node_interface_address i ON n.node_key = i.node_key
LEFT JOIN surveilr_osquery_ms_node_boundary b ON n.node_key = b.node_key
LEFT JOIN (
SELECT node_key, COUNT(*) AS failed_count
FROM surveilr_osquery_ms_node_executed_policy
WHERE policy_result = 'Fail'
GROUP BY node_key
) AS failed_policies ON n.node_key = failed_policies.node_key |
| surveilr_osquery_ms_node_detail |
issues |
|
/console/info-schema/view.sql?name=surveilr_osquery_ms_node_detail&stats=yes |
[Content](console/info-schema/view.sql?name=surveilr_osquery_ms_node_detail&stats=yes) |
[surveilr_osquery_ms_node_detail (view) Schema](console/info-schema/view.sql?name=surveilr_osquery_ms_node_detail&stats=yes) |
/console/content/view/surveilr_osquery_ms_node_detail.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/surveilr_osquery_ms_node_detail.sql?stats=yes) |
[surveilr_osquery_ms_node_detail (view) Content](console/content/view/surveilr_osquery_ms_node_detail.sql?stats=yes) |
CREATE VIEW surveilr_osquery_ms_node_detail AS
SELECT
n.surveilr_osquery_ms_node_id,
n.node_key,
n.host_identifier,
n.osquery_version,
n.last_seen,
n.created_at,
i.updated_at,
i.address AS ip_address,
i.mac,
b.boundary,
CASE
WHEN (strftime('%s', 'now') - strftime('%s', n.created_at)) < 60 THEN
(strftime('%s', 'now') - strftime('%s', n.created_at)) || ' seconds ago'
WHEN (strftime('%s', 'now') - strftime('%s', n.created_at)) < 3600 THEN
((strftime('%s', 'now') - strftime('%s', n.created_at)) / 60) || ' minutes ago'
WHEN (strftime('%s', 'now') - strftime('%s', n.created_at)) < 86400 THEN
((strftime('%s', 'now') - strftime('%s', n.created_at)) / 3600) || ' hours ago'
ELSE
((strftime('%s', 'now') - strftime('%s', n.created_at)) / 86400) || ' days ago'
END AS added_to_surveilr_osquery_ms,
o.name AS operating_system,
round(a.available_space, 2) || ' GB' AS available_space,
CASE
WHEN (strftime('%s', 'now') - strftime('%s', last_seen)) < 60 THEN 'Online'
ELSE 'Offline'
END AS node_status,
CASE
WHEN (strftime('%s', 'now') - strftime('%s', n.last_seen)) < 60 THEN
(strftime('%s', 'now') - strftime('%s', n.last_seen)) || ' seconds ago'
WHEN (strftime('%s', 'now') - strftime('%s', n.last_seen)) < 3600 THEN
((strftime('%s', 'now') - strftime('%s', n.last_seen)) / 60) || ' minutes ago'
WHEN (strftime('%s', 'now') - strftime('%s', n.last_seen)) < 86400 THEN
((strftime('%s', 'now') - strftime('%s', n.last_seen)) / 3600) || ' hours ago'
ELSE
((strftime('%s', 'now') - strftime('%s', n.last_seen)) / 86400) || ' days ago'
END AS last_fetched,
CASE
WHEN CAST(u.days AS INTEGER) > 0 THEN
'about ' || u.days || ' day' || (CASE WHEN CAST(u.days AS INTEGER) = 1 THEN '' ELSE 's' END) || ' ago'
WHEN CAST(u.hours AS INTEGER) > 0 THEN
'about ' || u.hours || ' hour' || (CASE WHEN CAST(u.hours AS INTEGER) = 1 THEN '' ELSE 's' END) || ' ago'
WHEN CAST(u.minutes AS INTEGER) > 0 THEN
'about ' || u.minutes || ' minute' || (CASE WHEN CAST(u.minutes AS INTEGER) = 1 THEN '' ELSE 's' END) || ' ago'
ELSE
'about ' || u.seconds || ' second' || (CASE WHEN CAST(u.seconds AS INTEGER) = 1 THEN '' ELSE 's' END) || ' ago'
END AS last_restarted,
COALESCE(failed_policies.failed_count, 0) AS issues
FROM surveilr_osquery_ms_node n
LEFT JOIN surveilr_osquery_ms_node_available_space a ON n.node_key = a.node_key
LEFT JOIN surveilr_osquery_ms_node_os_version o ON n.node_key = o.node_key
LEFT JOIN surveilr_osquery_ms_node_uptime u ON n.node_key = u.node_key
LEFT JOIN surveilr_osquery_ms_node_interface_address i ON n.node_key = i.node_key
LEFT JOIN surveilr_osquery_ms_node_boundary b ON n.node_key = b.node_key
LEFT JOIN (
SELECT node_key, COUNT(*) AS failed_count
FROM surveilr_osquery_ms_node_executed_policy
WHERE policy_result = 'Fail'
GROUP BY node_key
) AS failed_policies ON n.node_key = failed_policies.node_key |
| uniform_resource_file |
uniform_resource_id |
VARCHAR |
/console/info-schema/view.sql?name=uniform_resource_file&stats=yes |
[Content](console/info-schema/view.sql?name=uniform_resource_file&stats=yes) |
[uniform_resource_file (view) Schema](console/info-schema/view.sql?name=uniform_resource_file&stats=yes) |
/console/content/view/uniform_resource_file.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/uniform_resource_file.sql?stats=yes) |
[uniform_resource_file (view) Content](console/content/view/uniform_resource_file.sql?stats=yes) |
CREATE VIEW uniform_resource_file AS
SELECT ur.uniform_resource_id,
ur.nature,
p.root_path AS source_path,
pe.file_path_rel,
ur.size_bytes
FROM uniform_resource ur
LEFT JOIN uniform_resource_edge ure ON ur.uniform_resource_id = ure.uniform_resource_id AND ure.nature = 'ingest_fs_path'
LEFT JOIN ur_ingest_session_fs_path p ON ure.node_id = p.ur_ingest_session_fs_path_id
LEFT JOIN ur_ingest_session_fs_path_entry pe ON ur.uniform_resource_id = pe.uniform_resource_id |
| uniform_resource_file |
nature |
TEXT |
/console/info-schema/view.sql?name=uniform_resource_file&stats=yes |
[Content](console/info-schema/view.sql?name=uniform_resource_file&stats=yes) |
[uniform_resource_file (view) Schema](console/info-schema/view.sql?name=uniform_resource_file&stats=yes) |
/console/content/view/uniform_resource_file.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/uniform_resource_file.sql?stats=yes) |
[uniform_resource_file (view) Content](console/content/view/uniform_resource_file.sql?stats=yes) |
CREATE VIEW uniform_resource_file AS
SELECT ur.uniform_resource_id,
ur.nature,
p.root_path AS source_path,
pe.file_path_rel,
ur.size_bytes
FROM uniform_resource ur
LEFT JOIN uniform_resource_edge ure ON ur.uniform_resource_id = ure.uniform_resource_id AND ure.nature = 'ingest_fs_path'
LEFT JOIN ur_ingest_session_fs_path p ON ure.node_id = p.ur_ingest_session_fs_path_id
LEFT JOIN ur_ingest_session_fs_path_entry pe ON ur.uniform_resource_id = pe.uniform_resource_id |
| uniform_resource_file |
source_path |
TEXT |
/console/info-schema/view.sql?name=uniform_resource_file&stats=yes |
[Content](console/info-schema/view.sql?name=uniform_resource_file&stats=yes) |
[uniform_resource_file (view) Schema](console/info-schema/view.sql?name=uniform_resource_file&stats=yes) |
/console/content/view/uniform_resource_file.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/uniform_resource_file.sql?stats=yes) |
[uniform_resource_file (view) Content](console/content/view/uniform_resource_file.sql?stats=yes) |
CREATE VIEW uniform_resource_file AS
SELECT ur.uniform_resource_id,
ur.nature,
p.root_path AS source_path,
pe.file_path_rel,
ur.size_bytes
FROM uniform_resource ur
LEFT JOIN uniform_resource_edge ure ON ur.uniform_resource_id = ure.uniform_resource_id AND ure.nature = 'ingest_fs_path'
LEFT JOIN ur_ingest_session_fs_path p ON ure.node_id = p.ur_ingest_session_fs_path_id
LEFT JOIN ur_ingest_session_fs_path_entry pe ON ur.uniform_resource_id = pe.uniform_resource_id |
| uniform_resource_file |
file_path_rel |
TEXT |
/console/info-schema/view.sql?name=uniform_resource_file&stats=yes |
[Content](console/info-schema/view.sql?name=uniform_resource_file&stats=yes) |
[uniform_resource_file (view) Schema](console/info-schema/view.sql?name=uniform_resource_file&stats=yes) |
/console/content/view/uniform_resource_file.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/uniform_resource_file.sql?stats=yes) |
[uniform_resource_file (view) Content](console/content/view/uniform_resource_file.sql?stats=yes) |
CREATE VIEW uniform_resource_file AS
SELECT ur.uniform_resource_id,
ur.nature,
p.root_path AS source_path,
pe.file_path_rel,
ur.size_bytes
FROM uniform_resource ur
LEFT JOIN uniform_resource_edge ure ON ur.uniform_resource_id = ure.uniform_resource_id AND ure.nature = 'ingest_fs_path'
LEFT JOIN ur_ingest_session_fs_path p ON ure.node_id = p.ur_ingest_session_fs_path_id
LEFT JOIN ur_ingest_session_fs_path_entry pe ON ur.uniform_resource_id = pe.uniform_resource_id |
| uniform_resource_file |
size_bytes |
INTEGER |
/console/info-schema/view.sql?name=uniform_resource_file&stats=yes |
[Content](console/info-schema/view.sql?name=uniform_resource_file&stats=yes) |
[uniform_resource_file (view) Schema](console/info-schema/view.sql?name=uniform_resource_file&stats=yes) |
/console/content/view/uniform_resource_file.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/uniform_resource_file.sql?stats=yes) |
[uniform_resource_file (view) Content](console/content/view/uniform_resource_file.sql?stats=yes) |
CREATE VIEW uniform_resource_file AS
SELECT ur.uniform_resource_id,
ur.nature,
p.root_path AS source_path,
pe.file_path_rel,
ur.size_bytes
FROM uniform_resource ur
LEFT JOIN uniform_resource_edge ure ON ur.uniform_resource_id = ure.uniform_resource_id AND ure.nature = 'ingest_fs_path'
LEFT JOIN ur_ingest_session_fs_path p ON ure.node_id = p.ur_ingest_session_fs_path_id
LEFT JOIN ur_ingest_session_fs_path_entry pe ON ur.uniform_resource_id = pe.uniform_resource_id |
| uniform_resource_imap |
uniform_resource_id |
VARCHAR |
/console/info-schema/view.sql?name=uniform_resource_imap&stats=yes |
[Content](console/info-schema/view.sql?name=uniform_resource_imap&stats=yes) |
[uniform_resource_imap (view) Schema](console/info-schema/view.sql?name=uniform_resource_imap&stats=yes) |
/console/content/view/uniform_resource_imap.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/uniform_resource_imap.sql?stats=yes) |
[uniform_resource_imap (view) Content](console/content/view/uniform_resource_imap.sql?stats=yes) |
CREATE VIEW uniform_resource_imap AS
SELECT
ur.uniform_resource_id,
graph.name,
iac.ur_ingest_session_imap_account_id,
iac.email,
iac.host,
iacm.subject,
iacm."from",
iacm.message,
iacm.date,
iaf.ur_ingest_session_imap_acct_folder_id,
iaf.ingest_account_id,
iaf.folder_name,
ur.size_bytes,
ur.nature,
ur.content
FROM uniform_resource ur
INNER JOIN uniform_resource_edge edge ON edge.uniform_resource_id=ur.uniform_resource_id
INNER JOIN uniform_resource_graph graph ON graph.name=edge.graph_name
INNER JOIN ur_ingest_session_imap_acct_folder_message iacm ON iacm.ur_ingest_session_imap_acct_folder_message_id = edge.node_id
INNER JOIN ur_ingest_session_imap_acct_folder iaf ON iacm.ingest_imap_acct_folder_id = iaf.ur_ingest_session_imap_acct_folder_id
LEFT JOIN ur_ingest_session_imap_account iac ON iac.ur_ingest_session_imap_account_id = iaf.ingest_account_id
WHERE ur.nature = 'text' AND graph.name='imap' AND ur.ingest_session_imap_acct_folder_message IS NOT NULL |
| uniform_resource_imap |
name |
VARCHAR |
/console/info-schema/view.sql?name=uniform_resource_imap&stats=yes |
[Content](console/info-schema/view.sql?name=uniform_resource_imap&stats=yes) |
[uniform_resource_imap (view) Schema](console/info-schema/view.sql?name=uniform_resource_imap&stats=yes) |
/console/content/view/uniform_resource_imap.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/uniform_resource_imap.sql?stats=yes) |
[uniform_resource_imap (view) Content](console/content/view/uniform_resource_imap.sql?stats=yes) |
CREATE VIEW uniform_resource_imap AS
SELECT
ur.uniform_resource_id,
graph.name,
iac.ur_ingest_session_imap_account_id,
iac.email,
iac.host,
iacm.subject,
iacm."from",
iacm.message,
iacm.date,
iaf.ur_ingest_session_imap_acct_folder_id,
iaf.ingest_account_id,
iaf.folder_name,
ur.size_bytes,
ur.nature,
ur.content
FROM uniform_resource ur
INNER JOIN uniform_resource_edge edge ON edge.uniform_resource_id=ur.uniform_resource_id
INNER JOIN uniform_resource_graph graph ON graph.name=edge.graph_name
INNER JOIN ur_ingest_session_imap_acct_folder_message iacm ON iacm.ur_ingest_session_imap_acct_folder_message_id = edge.node_id
INNER JOIN ur_ingest_session_imap_acct_folder iaf ON iacm.ingest_imap_acct_folder_id = iaf.ur_ingest_session_imap_acct_folder_id
LEFT JOIN ur_ingest_session_imap_account iac ON iac.ur_ingest_session_imap_account_id = iaf.ingest_account_id
WHERE ur.nature = 'text' AND graph.name='imap' AND ur.ingest_session_imap_acct_folder_message IS NOT NULL |
| uniform_resource_imap |
ur_ingest_session_imap_account_id |
VARCHAR |
/console/info-schema/view.sql?name=uniform_resource_imap&stats=yes |
[Content](console/info-schema/view.sql?name=uniform_resource_imap&stats=yes) |
[uniform_resource_imap (view) Schema](console/info-schema/view.sql?name=uniform_resource_imap&stats=yes) |
/console/content/view/uniform_resource_imap.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/uniform_resource_imap.sql?stats=yes) |
[uniform_resource_imap (view) Content](console/content/view/uniform_resource_imap.sql?stats=yes) |
CREATE VIEW uniform_resource_imap AS
SELECT
ur.uniform_resource_id,
graph.name,
iac.ur_ingest_session_imap_account_id,
iac.email,
iac.host,
iacm.subject,
iacm."from",
iacm.message,
iacm.date,
iaf.ur_ingest_session_imap_acct_folder_id,
iaf.ingest_account_id,
iaf.folder_name,
ur.size_bytes,
ur.nature,
ur.content
FROM uniform_resource ur
INNER JOIN uniform_resource_edge edge ON edge.uniform_resource_id=ur.uniform_resource_id
INNER JOIN uniform_resource_graph graph ON graph.name=edge.graph_name
INNER JOIN ur_ingest_session_imap_acct_folder_message iacm ON iacm.ur_ingest_session_imap_acct_folder_message_id = edge.node_id
INNER JOIN ur_ingest_session_imap_acct_folder iaf ON iacm.ingest_imap_acct_folder_id = iaf.ur_ingest_session_imap_acct_folder_id
LEFT JOIN ur_ingest_session_imap_account iac ON iac.ur_ingest_session_imap_account_id = iaf.ingest_account_id
WHERE ur.nature = 'text' AND graph.name='imap' AND ur.ingest_session_imap_acct_folder_message IS NOT NULL |
| uniform_resource_imap |
email |
TEXT |
/console/info-schema/view.sql?name=uniform_resource_imap&stats=yes |
[Content](console/info-schema/view.sql?name=uniform_resource_imap&stats=yes) |
[uniform_resource_imap (view) Schema](console/info-schema/view.sql?name=uniform_resource_imap&stats=yes) |
/console/content/view/uniform_resource_imap.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/uniform_resource_imap.sql?stats=yes) |
[uniform_resource_imap (view) Content](console/content/view/uniform_resource_imap.sql?stats=yes) |
CREATE VIEW uniform_resource_imap AS
SELECT
ur.uniform_resource_id,
graph.name,
iac.ur_ingest_session_imap_account_id,
iac.email,
iac.host,
iacm.subject,
iacm."from",
iacm.message,
iacm.date,
iaf.ur_ingest_session_imap_acct_folder_id,
iaf.ingest_account_id,
iaf.folder_name,
ur.size_bytes,
ur.nature,
ur.content
FROM uniform_resource ur
INNER JOIN uniform_resource_edge edge ON edge.uniform_resource_id=ur.uniform_resource_id
INNER JOIN uniform_resource_graph graph ON graph.name=edge.graph_name
INNER JOIN ur_ingest_session_imap_acct_folder_message iacm ON iacm.ur_ingest_session_imap_acct_folder_message_id = edge.node_id
INNER JOIN ur_ingest_session_imap_acct_folder iaf ON iacm.ingest_imap_acct_folder_id = iaf.ur_ingest_session_imap_acct_folder_id
LEFT JOIN ur_ingest_session_imap_account iac ON iac.ur_ingest_session_imap_account_id = iaf.ingest_account_id
WHERE ur.nature = 'text' AND graph.name='imap' AND ur.ingest_session_imap_acct_folder_message IS NOT NULL |
| uniform_resource_imap |
host |
TEXT |
/console/info-schema/view.sql?name=uniform_resource_imap&stats=yes |
[Content](console/info-schema/view.sql?name=uniform_resource_imap&stats=yes) |
[uniform_resource_imap (view) Schema](console/info-schema/view.sql?name=uniform_resource_imap&stats=yes) |
/console/content/view/uniform_resource_imap.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/uniform_resource_imap.sql?stats=yes) |
[uniform_resource_imap (view) Content](console/content/view/uniform_resource_imap.sql?stats=yes) |
CREATE VIEW uniform_resource_imap AS
SELECT
ur.uniform_resource_id,
graph.name,
iac.ur_ingest_session_imap_account_id,
iac.email,
iac.host,
iacm.subject,
iacm."from",
iacm.message,
iacm.date,
iaf.ur_ingest_session_imap_acct_folder_id,
iaf.ingest_account_id,
iaf.folder_name,
ur.size_bytes,
ur.nature,
ur.content
FROM uniform_resource ur
INNER JOIN uniform_resource_edge edge ON edge.uniform_resource_id=ur.uniform_resource_id
INNER JOIN uniform_resource_graph graph ON graph.name=edge.graph_name
INNER JOIN ur_ingest_session_imap_acct_folder_message iacm ON iacm.ur_ingest_session_imap_acct_folder_message_id = edge.node_id
INNER JOIN ur_ingest_session_imap_acct_folder iaf ON iacm.ingest_imap_acct_folder_id = iaf.ur_ingest_session_imap_acct_folder_id
LEFT JOIN ur_ingest_session_imap_account iac ON iac.ur_ingest_session_imap_account_id = iaf.ingest_account_id
WHERE ur.nature = 'text' AND graph.name='imap' AND ur.ingest_session_imap_acct_folder_message IS NOT NULL |
| uniform_resource_imap |
subject |
TEXT |
/console/info-schema/view.sql?name=uniform_resource_imap&stats=yes |
[Content](console/info-schema/view.sql?name=uniform_resource_imap&stats=yes) |
[uniform_resource_imap (view) Schema](console/info-schema/view.sql?name=uniform_resource_imap&stats=yes) |
/console/content/view/uniform_resource_imap.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/uniform_resource_imap.sql?stats=yes) |
[uniform_resource_imap (view) Content](console/content/view/uniform_resource_imap.sql?stats=yes) |
CREATE VIEW uniform_resource_imap AS
SELECT
ur.uniform_resource_id,
graph.name,
iac.ur_ingest_session_imap_account_id,
iac.email,
iac.host,
iacm.subject,
iacm."from",
iacm.message,
iacm.date,
iaf.ur_ingest_session_imap_acct_folder_id,
iaf.ingest_account_id,
iaf.folder_name,
ur.size_bytes,
ur.nature,
ur.content
FROM uniform_resource ur
INNER JOIN uniform_resource_edge edge ON edge.uniform_resource_id=ur.uniform_resource_id
INNER JOIN uniform_resource_graph graph ON graph.name=edge.graph_name
INNER JOIN ur_ingest_session_imap_acct_folder_message iacm ON iacm.ur_ingest_session_imap_acct_folder_message_id = edge.node_id
INNER JOIN ur_ingest_session_imap_acct_folder iaf ON iacm.ingest_imap_acct_folder_id = iaf.ur_ingest_session_imap_acct_folder_id
LEFT JOIN ur_ingest_session_imap_account iac ON iac.ur_ingest_session_imap_account_id = iaf.ingest_account_id
WHERE ur.nature = 'text' AND graph.name='imap' AND ur.ingest_session_imap_acct_folder_message IS NOT NULL |
| uniform_resource_imap |
from |
TEXT |
/console/info-schema/view.sql?name=uniform_resource_imap&stats=yes |
[Content](console/info-schema/view.sql?name=uniform_resource_imap&stats=yes) |
[uniform_resource_imap (view) Schema](console/info-schema/view.sql?name=uniform_resource_imap&stats=yes) |
/console/content/view/uniform_resource_imap.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/uniform_resource_imap.sql?stats=yes) |
[uniform_resource_imap (view) Content](console/content/view/uniform_resource_imap.sql?stats=yes) |
CREATE VIEW uniform_resource_imap AS
SELECT
ur.uniform_resource_id,
graph.name,
iac.ur_ingest_session_imap_account_id,
iac.email,
iac.host,
iacm.subject,
iacm."from",
iacm.message,
iacm.date,
iaf.ur_ingest_session_imap_acct_folder_id,
iaf.ingest_account_id,
iaf.folder_name,
ur.size_bytes,
ur.nature,
ur.content
FROM uniform_resource ur
INNER JOIN uniform_resource_edge edge ON edge.uniform_resource_id=ur.uniform_resource_id
INNER JOIN uniform_resource_graph graph ON graph.name=edge.graph_name
INNER JOIN ur_ingest_session_imap_acct_folder_message iacm ON iacm.ur_ingest_session_imap_acct_folder_message_id = edge.node_id
INNER JOIN ur_ingest_session_imap_acct_folder iaf ON iacm.ingest_imap_acct_folder_id = iaf.ur_ingest_session_imap_acct_folder_id
LEFT JOIN ur_ingest_session_imap_account iac ON iac.ur_ingest_session_imap_account_id = iaf.ingest_account_id
WHERE ur.nature = 'text' AND graph.name='imap' AND ur.ingest_session_imap_acct_folder_message IS NOT NULL |
| uniform_resource_imap |
message |
TEXT |
/console/info-schema/view.sql?name=uniform_resource_imap&stats=yes |
[Content](console/info-schema/view.sql?name=uniform_resource_imap&stats=yes) |
[uniform_resource_imap (view) Schema](console/info-schema/view.sql?name=uniform_resource_imap&stats=yes) |
/console/content/view/uniform_resource_imap.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/uniform_resource_imap.sql?stats=yes) |
[uniform_resource_imap (view) Content](console/content/view/uniform_resource_imap.sql?stats=yes) |
CREATE VIEW uniform_resource_imap AS
SELECT
ur.uniform_resource_id,
graph.name,
iac.ur_ingest_session_imap_account_id,
iac.email,
iac.host,
iacm.subject,
iacm."from",
iacm.message,
iacm.date,
iaf.ur_ingest_session_imap_acct_folder_id,
iaf.ingest_account_id,
iaf.folder_name,
ur.size_bytes,
ur.nature,
ur.content
FROM uniform_resource ur
INNER JOIN uniform_resource_edge edge ON edge.uniform_resource_id=ur.uniform_resource_id
INNER JOIN uniform_resource_graph graph ON graph.name=edge.graph_name
INNER JOIN ur_ingest_session_imap_acct_folder_message iacm ON iacm.ur_ingest_session_imap_acct_folder_message_id = edge.node_id
INNER JOIN ur_ingest_session_imap_acct_folder iaf ON iacm.ingest_imap_acct_folder_id = iaf.ur_ingest_session_imap_acct_folder_id
LEFT JOIN ur_ingest_session_imap_account iac ON iac.ur_ingest_session_imap_account_id = iaf.ingest_account_id
WHERE ur.nature = 'text' AND graph.name='imap' AND ur.ingest_session_imap_acct_folder_message IS NOT NULL |
| uniform_resource_imap |
date |
DATE |
/console/info-schema/view.sql?name=uniform_resource_imap&stats=yes |
[Content](console/info-schema/view.sql?name=uniform_resource_imap&stats=yes) |
[uniform_resource_imap (view) Schema](console/info-schema/view.sql?name=uniform_resource_imap&stats=yes) |
/console/content/view/uniform_resource_imap.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/uniform_resource_imap.sql?stats=yes) |
[uniform_resource_imap (view) Content](console/content/view/uniform_resource_imap.sql?stats=yes) |
CREATE VIEW uniform_resource_imap AS
SELECT
ur.uniform_resource_id,
graph.name,
iac.ur_ingest_session_imap_account_id,
iac.email,
iac.host,
iacm.subject,
iacm."from",
iacm.message,
iacm.date,
iaf.ur_ingest_session_imap_acct_folder_id,
iaf.ingest_account_id,
iaf.folder_name,
ur.size_bytes,
ur.nature,
ur.content
FROM uniform_resource ur
INNER JOIN uniform_resource_edge edge ON edge.uniform_resource_id=ur.uniform_resource_id
INNER JOIN uniform_resource_graph graph ON graph.name=edge.graph_name
INNER JOIN ur_ingest_session_imap_acct_folder_message iacm ON iacm.ur_ingest_session_imap_acct_folder_message_id = edge.node_id
INNER JOIN ur_ingest_session_imap_acct_folder iaf ON iacm.ingest_imap_acct_folder_id = iaf.ur_ingest_session_imap_acct_folder_id
LEFT JOIN ur_ingest_session_imap_account iac ON iac.ur_ingest_session_imap_account_id = iaf.ingest_account_id
WHERE ur.nature = 'text' AND graph.name='imap' AND ur.ingest_session_imap_acct_folder_message IS NOT NULL |
| uniform_resource_imap |
ur_ingest_session_imap_acct_folder_id |
VARCHAR |
/console/info-schema/view.sql?name=uniform_resource_imap&stats=yes |
[Content](console/info-schema/view.sql?name=uniform_resource_imap&stats=yes) |
[uniform_resource_imap (view) Schema](console/info-schema/view.sql?name=uniform_resource_imap&stats=yes) |
/console/content/view/uniform_resource_imap.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/uniform_resource_imap.sql?stats=yes) |
[uniform_resource_imap (view) Content](console/content/view/uniform_resource_imap.sql?stats=yes) |
CREATE VIEW uniform_resource_imap AS
SELECT
ur.uniform_resource_id,
graph.name,
iac.ur_ingest_session_imap_account_id,
iac.email,
iac.host,
iacm.subject,
iacm."from",
iacm.message,
iacm.date,
iaf.ur_ingest_session_imap_acct_folder_id,
iaf.ingest_account_id,
iaf.folder_name,
ur.size_bytes,
ur.nature,
ur.content
FROM uniform_resource ur
INNER JOIN uniform_resource_edge edge ON edge.uniform_resource_id=ur.uniform_resource_id
INNER JOIN uniform_resource_graph graph ON graph.name=edge.graph_name
INNER JOIN ur_ingest_session_imap_acct_folder_message iacm ON iacm.ur_ingest_session_imap_acct_folder_message_id = edge.node_id
INNER JOIN ur_ingest_session_imap_acct_folder iaf ON iacm.ingest_imap_acct_folder_id = iaf.ur_ingest_session_imap_acct_folder_id
LEFT JOIN ur_ingest_session_imap_account iac ON iac.ur_ingest_session_imap_account_id = iaf.ingest_account_id
WHERE ur.nature = 'text' AND graph.name='imap' AND ur.ingest_session_imap_acct_folder_message IS NOT NULL |
| uniform_resource_imap |
ingest_account_id |
VARCHAR |
/console/info-schema/view.sql?name=uniform_resource_imap&stats=yes |
[Content](console/info-schema/view.sql?name=uniform_resource_imap&stats=yes) |
[uniform_resource_imap (view) Schema](console/info-schema/view.sql?name=uniform_resource_imap&stats=yes) |
/console/content/view/uniform_resource_imap.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/uniform_resource_imap.sql?stats=yes) |
[uniform_resource_imap (view) Content](console/content/view/uniform_resource_imap.sql?stats=yes) |
CREATE VIEW uniform_resource_imap AS
SELECT
ur.uniform_resource_id,
graph.name,
iac.ur_ingest_session_imap_account_id,
iac.email,
iac.host,
iacm.subject,
iacm."from",
iacm.message,
iacm.date,
iaf.ur_ingest_session_imap_acct_folder_id,
iaf.ingest_account_id,
iaf.folder_name,
ur.size_bytes,
ur.nature,
ur.content
FROM uniform_resource ur
INNER JOIN uniform_resource_edge edge ON edge.uniform_resource_id=ur.uniform_resource_id
INNER JOIN uniform_resource_graph graph ON graph.name=edge.graph_name
INNER JOIN ur_ingest_session_imap_acct_folder_message iacm ON iacm.ur_ingest_session_imap_acct_folder_message_id = edge.node_id
INNER JOIN ur_ingest_session_imap_acct_folder iaf ON iacm.ingest_imap_acct_folder_id = iaf.ur_ingest_session_imap_acct_folder_id
LEFT JOIN ur_ingest_session_imap_account iac ON iac.ur_ingest_session_imap_account_id = iaf.ingest_account_id
WHERE ur.nature = 'text' AND graph.name='imap' AND ur.ingest_session_imap_acct_folder_message IS NOT NULL |
| uniform_resource_imap |
folder_name |
TEXT |
/console/info-schema/view.sql?name=uniform_resource_imap&stats=yes |
[Content](console/info-schema/view.sql?name=uniform_resource_imap&stats=yes) |
[uniform_resource_imap (view) Schema](console/info-schema/view.sql?name=uniform_resource_imap&stats=yes) |
/console/content/view/uniform_resource_imap.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/uniform_resource_imap.sql?stats=yes) |
[uniform_resource_imap (view) Content](console/content/view/uniform_resource_imap.sql?stats=yes) |
CREATE VIEW uniform_resource_imap AS
SELECT
ur.uniform_resource_id,
graph.name,
iac.ur_ingest_session_imap_account_id,
iac.email,
iac.host,
iacm.subject,
iacm."from",
iacm.message,
iacm.date,
iaf.ur_ingest_session_imap_acct_folder_id,
iaf.ingest_account_id,
iaf.folder_name,
ur.size_bytes,
ur.nature,
ur.content
FROM uniform_resource ur
INNER JOIN uniform_resource_edge edge ON edge.uniform_resource_id=ur.uniform_resource_id
INNER JOIN uniform_resource_graph graph ON graph.name=edge.graph_name
INNER JOIN ur_ingest_session_imap_acct_folder_message iacm ON iacm.ur_ingest_session_imap_acct_folder_message_id = edge.node_id
INNER JOIN ur_ingest_session_imap_acct_folder iaf ON iacm.ingest_imap_acct_folder_id = iaf.ur_ingest_session_imap_acct_folder_id
LEFT JOIN ur_ingest_session_imap_account iac ON iac.ur_ingest_session_imap_account_id = iaf.ingest_account_id
WHERE ur.nature = 'text' AND graph.name='imap' AND ur.ingest_session_imap_acct_folder_message IS NOT NULL |
| uniform_resource_imap |
size_bytes |
INTEGER |
/console/info-schema/view.sql?name=uniform_resource_imap&stats=yes |
[Content](console/info-schema/view.sql?name=uniform_resource_imap&stats=yes) |
[uniform_resource_imap (view) Schema](console/info-schema/view.sql?name=uniform_resource_imap&stats=yes) |
/console/content/view/uniform_resource_imap.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/uniform_resource_imap.sql?stats=yes) |
[uniform_resource_imap (view) Content](console/content/view/uniform_resource_imap.sql?stats=yes) |
CREATE VIEW uniform_resource_imap AS
SELECT
ur.uniform_resource_id,
graph.name,
iac.ur_ingest_session_imap_account_id,
iac.email,
iac.host,
iacm.subject,
iacm."from",
iacm.message,
iacm.date,
iaf.ur_ingest_session_imap_acct_folder_id,
iaf.ingest_account_id,
iaf.folder_name,
ur.size_bytes,
ur.nature,
ur.content
FROM uniform_resource ur
INNER JOIN uniform_resource_edge edge ON edge.uniform_resource_id=ur.uniform_resource_id
INNER JOIN uniform_resource_graph graph ON graph.name=edge.graph_name
INNER JOIN ur_ingest_session_imap_acct_folder_message iacm ON iacm.ur_ingest_session_imap_acct_folder_message_id = edge.node_id
INNER JOIN ur_ingest_session_imap_acct_folder iaf ON iacm.ingest_imap_acct_folder_id = iaf.ur_ingest_session_imap_acct_folder_id
LEFT JOIN ur_ingest_session_imap_account iac ON iac.ur_ingest_session_imap_account_id = iaf.ingest_account_id
WHERE ur.nature = 'text' AND graph.name='imap' AND ur.ingest_session_imap_acct_folder_message IS NOT NULL |
| uniform_resource_imap |
nature |
TEXT |
/console/info-schema/view.sql?name=uniform_resource_imap&stats=yes |
[Content](console/info-schema/view.sql?name=uniform_resource_imap&stats=yes) |
[uniform_resource_imap (view) Schema](console/info-schema/view.sql?name=uniform_resource_imap&stats=yes) |
/console/content/view/uniform_resource_imap.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/uniform_resource_imap.sql?stats=yes) |
[uniform_resource_imap (view) Content](console/content/view/uniform_resource_imap.sql?stats=yes) |
CREATE VIEW uniform_resource_imap AS
SELECT
ur.uniform_resource_id,
graph.name,
iac.ur_ingest_session_imap_account_id,
iac.email,
iac.host,
iacm.subject,
iacm."from",
iacm.message,
iacm.date,
iaf.ur_ingest_session_imap_acct_folder_id,
iaf.ingest_account_id,
iaf.folder_name,
ur.size_bytes,
ur.nature,
ur.content
FROM uniform_resource ur
INNER JOIN uniform_resource_edge edge ON edge.uniform_resource_id=ur.uniform_resource_id
INNER JOIN uniform_resource_graph graph ON graph.name=edge.graph_name
INNER JOIN ur_ingest_session_imap_acct_folder_message iacm ON iacm.ur_ingest_session_imap_acct_folder_message_id = edge.node_id
INNER JOIN ur_ingest_session_imap_acct_folder iaf ON iacm.ingest_imap_acct_folder_id = iaf.ur_ingest_session_imap_acct_folder_id
LEFT JOIN ur_ingest_session_imap_account iac ON iac.ur_ingest_session_imap_account_id = iaf.ingest_account_id
WHERE ur.nature = 'text' AND graph.name='imap' AND ur.ingest_session_imap_acct_folder_message IS NOT NULL |
| uniform_resource_imap |
content |
BLOB |
/console/info-schema/view.sql?name=uniform_resource_imap&stats=yes |
[Content](console/info-schema/view.sql?name=uniform_resource_imap&stats=yes) |
[uniform_resource_imap (view) Schema](console/info-schema/view.sql?name=uniform_resource_imap&stats=yes) |
/console/content/view/uniform_resource_imap.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/uniform_resource_imap.sql?stats=yes) |
[uniform_resource_imap (view) Content](console/content/view/uniform_resource_imap.sql?stats=yes) |
CREATE VIEW uniform_resource_imap AS
SELECT
ur.uniform_resource_id,
graph.name,
iac.ur_ingest_session_imap_account_id,
iac.email,
iac.host,
iacm.subject,
iacm."from",
iacm.message,
iacm.date,
iaf.ur_ingest_session_imap_acct_folder_id,
iaf.ingest_account_id,
iaf.folder_name,
ur.size_bytes,
ur.nature,
ur.content
FROM uniform_resource ur
INNER JOIN uniform_resource_edge edge ON edge.uniform_resource_id=ur.uniform_resource_id
INNER JOIN uniform_resource_graph graph ON graph.name=edge.graph_name
INNER JOIN ur_ingest_session_imap_acct_folder_message iacm ON iacm.ur_ingest_session_imap_acct_folder_message_id = edge.node_id
INNER JOIN ur_ingest_session_imap_acct_folder iaf ON iacm.ingest_imap_acct_folder_id = iaf.ur_ingest_session_imap_acct_folder_id
LEFT JOIN ur_ingest_session_imap_account iac ON iac.ur_ingest_session_imap_account_id = iaf.ingest_account_id
WHERE ur.nature = 'text' AND graph.name='imap' AND ur.ingest_session_imap_acct_folder_message IS NOT NULL |
| uniform_resource_imap_content |
uniform_resource_id |
VARCHAR |
/console/info-schema/view.sql?name=uniform_resource_imap_content&stats=yes |
[Content](console/info-schema/view.sql?name=uniform_resource_imap_content&stats=yes) |
[uniform_resource_imap_content (view) Schema](console/info-schema/view.sql?name=uniform_resource_imap_content&stats=yes) |
/console/content/view/uniform_resource_imap_content.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/uniform_resource_imap_content.sql?stats=yes) |
[uniform_resource_imap_content (view) Content](console/content/view/uniform_resource_imap_content.sql?stats=yes) |
CREATE VIEW uniform_resource_imap_content AS
SELECT
uri.uniform_resource_id,
base_ur.uniform_resource_id baseID,
ext_ur.uniform_resource_id extID,
base_ur.uri as base_uri,
ext_ur.uri as ext_uri,
base_ur.nature as base_nature,
ext_ur.nature as ext_nature,
json_extract(part.value, '$.body.Html') AS html_content
FROM
uniform_resource_imap uri
INNER JOIN uniform_resource base_ur ON base_ur.uniform_resource_id=uri.uniform_resource_id
INNER JOIN uniform_resource ext_ur ON ext_ur.uri = base_ur.uri ||'/json' AND ext_ur.nature = 'json',
json_each(ext_ur.content, '$.parts') AS part
WHERE ext_ur.nature = 'json' AND html_content NOT NULL |
| uniform_resource_imap_content |
baseID |
VARCHAR |
/console/info-schema/view.sql?name=uniform_resource_imap_content&stats=yes |
[Content](console/info-schema/view.sql?name=uniform_resource_imap_content&stats=yes) |
[uniform_resource_imap_content (view) Schema](console/info-schema/view.sql?name=uniform_resource_imap_content&stats=yes) |
/console/content/view/uniform_resource_imap_content.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/uniform_resource_imap_content.sql?stats=yes) |
[uniform_resource_imap_content (view) Content](console/content/view/uniform_resource_imap_content.sql?stats=yes) |
CREATE VIEW uniform_resource_imap_content AS
SELECT
uri.uniform_resource_id,
base_ur.uniform_resource_id baseID,
ext_ur.uniform_resource_id extID,
base_ur.uri as base_uri,
ext_ur.uri as ext_uri,
base_ur.nature as base_nature,
ext_ur.nature as ext_nature,
json_extract(part.value, '$.body.Html') AS html_content
FROM
uniform_resource_imap uri
INNER JOIN uniform_resource base_ur ON base_ur.uniform_resource_id=uri.uniform_resource_id
INNER JOIN uniform_resource ext_ur ON ext_ur.uri = base_ur.uri ||'/json' AND ext_ur.nature = 'json',
json_each(ext_ur.content, '$.parts') AS part
WHERE ext_ur.nature = 'json' AND html_content NOT NULL |
| uniform_resource_imap_content |
extID |
VARCHAR |
/console/info-schema/view.sql?name=uniform_resource_imap_content&stats=yes |
[Content](console/info-schema/view.sql?name=uniform_resource_imap_content&stats=yes) |
[uniform_resource_imap_content (view) Schema](console/info-schema/view.sql?name=uniform_resource_imap_content&stats=yes) |
/console/content/view/uniform_resource_imap_content.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/uniform_resource_imap_content.sql?stats=yes) |
[uniform_resource_imap_content (view) Content](console/content/view/uniform_resource_imap_content.sql?stats=yes) |
CREATE VIEW uniform_resource_imap_content AS
SELECT
uri.uniform_resource_id,
base_ur.uniform_resource_id baseID,
ext_ur.uniform_resource_id extID,
base_ur.uri as base_uri,
ext_ur.uri as ext_uri,
base_ur.nature as base_nature,
ext_ur.nature as ext_nature,
json_extract(part.value, '$.body.Html') AS html_content
FROM
uniform_resource_imap uri
INNER JOIN uniform_resource base_ur ON base_ur.uniform_resource_id=uri.uniform_resource_id
INNER JOIN uniform_resource ext_ur ON ext_ur.uri = base_ur.uri ||'/json' AND ext_ur.nature = 'json',
json_each(ext_ur.content, '$.parts') AS part
WHERE ext_ur.nature = 'json' AND html_content NOT NULL |
| uniform_resource_imap_content |
base_uri |
TEXT |
/console/info-schema/view.sql?name=uniform_resource_imap_content&stats=yes |
[Content](console/info-schema/view.sql?name=uniform_resource_imap_content&stats=yes) |
[uniform_resource_imap_content (view) Schema](console/info-schema/view.sql?name=uniform_resource_imap_content&stats=yes) |
/console/content/view/uniform_resource_imap_content.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/uniform_resource_imap_content.sql?stats=yes) |
[uniform_resource_imap_content (view) Content](console/content/view/uniform_resource_imap_content.sql?stats=yes) |
CREATE VIEW uniform_resource_imap_content AS
SELECT
uri.uniform_resource_id,
base_ur.uniform_resource_id baseID,
ext_ur.uniform_resource_id extID,
base_ur.uri as base_uri,
ext_ur.uri as ext_uri,
base_ur.nature as base_nature,
ext_ur.nature as ext_nature,
json_extract(part.value, '$.body.Html') AS html_content
FROM
uniform_resource_imap uri
INNER JOIN uniform_resource base_ur ON base_ur.uniform_resource_id=uri.uniform_resource_id
INNER JOIN uniform_resource ext_ur ON ext_ur.uri = base_ur.uri ||'/json' AND ext_ur.nature = 'json',
json_each(ext_ur.content, '$.parts') AS part
WHERE ext_ur.nature = 'json' AND html_content NOT NULL |
| uniform_resource_imap_content |
ext_uri |
TEXT |
/console/info-schema/view.sql?name=uniform_resource_imap_content&stats=yes |
[Content](console/info-schema/view.sql?name=uniform_resource_imap_content&stats=yes) |
[uniform_resource_imap_content (view) Schema](console/info-schema/view.sql?name=uniform_resource_imap_content&stats=yes) |
/console/content/view/uniform_resource_imap_content.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/uniform_resource_imap_content.sql?stats=yes) |
[uniform_resource_imap_content (view) Content](console/content/view/uniform_resource_imap_content.sql?stats=yes) |
CREATE VIEW uniform_resource_imap_content AS
SELECT
uri.uniform_resource_id,
base_ur.uniform_resource_id baseID,
ext_ur.uniform_resource_id extID,
base_ur.uri as base_uri,
ext_ur.uri as ext_uri,
base_ur.nature as base_nature,
ext_ur.nature as ext_nature,
json_extract(part.value, '$.body.Html') AS html_content
FROM
uniform_resource_imap uri
INNER JOIN uniform_resource base_ur ON base_ur.uniform_resource_id=uri.uniform_resource_id
INNER JOIN uniform_resource ext_ur ON ext_ur.uri = base_ur.uri ||'/json' AND ext_ur.nature = 'json',
json_each(ext_ur.content, '$.parts') AS part
WHERE ext_ur.nature = 'json' AND html_content NOT NULL |
| uniform_resource_imap_content |
base_nature |
TEXT |
/console/info-schema/view.sql?name=uniform_resource_imap_content&stats=yes |
[Content](console/info-schema/view.sql?name=uniform_resource_imap_content&stats=yes) |
[uniform_resource_imap_content (view) Schema](console/info-schema/view.sql?name=uniform_resource_imap_content&stats=yes) |
/console/content/view/uniform_resource_imap_content.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/uniform_resource_imap_content.sql?stats=yes) |
[uniform_resource_imap_content (view) Content](console/content/view/uniform_resource_imap_content.sql?stats=yes) |
CREATE VIEW uniform_resource_imap_content AS
SELECT
uri.uniform_resource_id,
base_ur.uniform_resource_id baseID,
ext_ur.uniform_resource_id extID,
base_ur.uri as base_uri,
ext_ur.uri as ext_uri,
base_ur.nature as base_nature,
ext_ur.nature as ext_nature,
json_extract(part.value, '$.body.Html') AS html_content
FROM
uniform_resource_imap uri
INNER JOIN uniform_resource base_ur ON base_ur.uniform_resource_id=uri.uniform_resource_id
INNER JOIN uniform_resource ext_ur ON ext_ur.uri = base_ur.uri ||'/json' AND ext_ur.nature = 'json',
json_each(ext_ur.content, '$.parts') AS part
WHERE ext_ur.nature = 'json' AND html_content NOT NULL |
| uniform_resource_imap_content |
ext_nature |
TEXT |
/console/info-schema/view.sql?name=uniform_resource_imap_content&stats=yes |
[Content](console/info-schema/view.sql?name=uniform_resource_imap_content&stats=yes) |
[uniform_resource_imap_content (view) Schema](console/info-schema/view.sql?name=uniform_resource_imap_content&stats=yes) |
/console/content/view/uniform_resource_imap_content.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/uniform_resource_imap_content.sql?stats=yes) |
[uniform_resource_imap_content (view) Content](console/content/view/uniform_resource_imap_content.sql?stats=yes) |
CREATE VIEW uniform_resource_imap_content AS
SELECT
uri.uniform_resource_id,
base_ur.uniform_resource_id baseID,
ext_ur.uniform_resource_id extID,
base_ur.uri as base_uri,
ext_ur.uri as ext_uri,
base_ur.nature as base_nature,
ext_ur.nature as ext_nature,
json_extract(part.value, '$.body.Html') AS html_content
FROM
uniform_resource_imap uri
INNER JOIN uniform_resource base_ur ON base_ur.uniform_resource_id=uri.uniform_resource_id
INNER JOIN uniform_resource ext_ur ON ext_ur.uri = base_ur.uri ||'/json' AND ext_ur.nature = 'json',
json_each(ext_ur.content, '$.parts') AS part
WHERE ext_ur.nature = 'json' AND html_content NOT NULL |
| uniform_resource_imap_content |
html_content |
|
/console/info-schema/view.sql?name=uniform_resource_imap_content&stats=yes |
[Content](console/info-schema/view.sql?name=uniform_resource_imap_content&stats=yes) |
[uniform_resource_imap_content (view) Schema](console/info-schema/view.sql?name=uniform_resource_imap_content&stats=yes) |
/console/content/view/uniform_resource_imap_content.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/uniform_resource_imap_content.sql?stats=yes) |
[uniform_resource_imap_content (view) Content](console/content/view/uniform_resource_imap_content.sql?stats=yes) |
CREATE VIEW uniform_resource_imap_content AS
SELECT
uri.uniform_resource_id,
base_ur.uniform_resource_id baseID,
ext_ur.uniform_resource_id extID,
base_ur.uri as base_uri,
ext_ur.uri as ext_uri,
base_ur.nature as base_nature,
ext_ur.nature as ext_nature,
json_extract(part.value, '$.body.Html') AS html_content
FROM
uniform_resource_imap uri
INNER JOIN uniform_resource base_ur ON base_ur.uniform_resource_id=uri.uniform_resource_id
INNER JOIN uniform_resource ext_ur ON ext_ur.uri = base_ur.uri ||'/json' AND ext_ur.nature = 'json',
json_each(ext_ur.content, '$.parts') AS part
WHERE ext_ur.nature = 'json' AND html_content NOT NULL |
| ur_ingest_session_files_stats |
device_id |
VARCHAR |
/console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes |
[Content](console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes) |
[ur_ingest_session_files_stats (view) Schema](console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes) |
/console/content/view/ur_ingest_session_files_stats.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/ur_ingest_session_files_stats.sql?stats=yes) |
[ur_ingest_session_files_stats (view) Content](console/content/view/ur_ingest_session_files_stats.sql?stats=yes) |
CREATE VIEW "ur_ingest_session_files_stats" AS
WITH Summary AS (
SELECT
device.device_id AS device_id,
ur_ingest_session.ur_ingest_session_id AS ingest_session_id,
ur_ingest_session.ingest_started_at AS ingest_session_started_at,
ur_ingest_session.ingest_finished_at AS ingest_session_finished_at,
COALESCE(ur_ingest_session_fs_path_entry.file_extn, '') AS file_extension,
ur_ingest_session_fs_path.ur_ingest_session_fs_path_id as ingest_session_fs_path_id,
ur_ingest_session_fs_path.root_path AS ingest_session_root_fs_path,
COUNT(ur_ingest_session_fs_path_entry.uniform_resource_id) AS total_file_count,
SUM(CASE WHEN uniform_resource.content IS NOT NULL THEN 1 ELSE 0 END) AS file_count_with_content,
SUM(CASE WHEN uniform_resource.frontmatter IS NOT NULL THEN 1 ELSE 0 END) AS file_count_with_frontmatter,
MIN(uniform_resource.size_bytes) AS min_file_size_bytes,
AVG(uniform_resource.size_bytes) AS average_file_size_bytes,
MAX(uniform_resource.size_bytes) AS max_file_size_bytes,
MIN(uniform_resource.last_modified_at) AS oldest_file_last_modified_datetime,
MAX(uniform_resource.last_modified_at) AS youngest_file_last_modified_datetime
FROM
ur_ingest_session
JOIN
device ON ur_ingest_session.device_id = device.device_id
LEFT JOIN
ur_ingest_session_fs_path ON ur_ingest_session.ur_ingest_session_id = ur_ingest_session_fs_path.ingest_session_id
LEFT JOIN
ur_ingest_session_fs_path_entry ON ur_ingest_session_fs_path.ur_ingest_session_fs_path_id = ur_ingest_session_fs_path_entry.ingest_fs_path_id
LEFT JOIN
uniform_resource ON ur_ingest_session_fs_path_entry.uniform_resource_id = uniform_resource.uniform_resource_id
GROUP BY
device.device_id,
ur_ingest_session.ur_ingest_session_id,
ur_ingest_session.ingest_started_at,
ur_ingest_session.ingest_finished_at,
ur_ingest_session_fs_path_entry.file_extn,
ur_ingest_session_fs_path.root_path
)
SELECT
device_id,
ingest_session_id,
ingest_session_started_at,
ingest_session_finished_at,
file_extension,
ingest_session_fs_path_id,
ingest_session_root_fs_path,
total_file_count,
file_count_with_content,
file_count_with_frontmatter,
min_file_size_bytes,
CAST(ROUND(average_file_size_bytes) AS INTEGER) AS average_file_size_bytes,
max_file_size_bytes,
oldest_file_last_modified_datetime,
youngest_file_last_modified_datetime
FROM
Summary
ORDER BY
device_id,
ingest_session_finished_at,
file_extension |
| ur_ingest_session_files_stats |
ingest_session_id |
VARCHAR |
/console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes |
[Content](console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes) |
[ur_ingest_session_files_stats (view) Schema](console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes) |
/console/content/view/ur_ingest_session_files_stats.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/ur_ingest_session_files_stats.sql?stats=yes) |
[ur_ingest_session_files_stats (view) Content](console/content/view/ur_ingest_session_files_stats.sql?stats=yes) |
CREATE VIEW "ur_ingest_session_files_stats" AS
WITH Summary AS (
SELECT
device.device_id AS device_id,
ur_ingest_session.ur_ingest_session_id AS ingest_session_id,
ur_ingest_session.ingest_started_at AS ingest_session_started_at,
ur_ingest_session.ingest_finished_at AS ingest_session_finished_at,
COALESCE(ur_ingest_session_fs_path_entry.file_extn, '') AS file_extension,
ur_ingest_session_fs_path.ur_ingest_session_fs_path_id as ingest_session_fs_path_id,
ur_ingest_session_fs_path.root_path AS ingest_session_root_fs_path,
COUNT(ur_ingest_session_fs_path_entry.uniform_resource_id) AS total_file_count,
SUM(CASE WHEN uniform_resource.content IS NOT NULL THEN 1 ELSE 0 END) AS file_count_with_content,
SUM(CASE WHEN uniform_resource.frontmatter IS NOT NULL THEN 1 ELSE 0 END) AS file_count_with_frontmatter,
MIN(uniform_resource.size_bytes) AS min_file_size_bytes,
AVG(uniform_resource.size_bytes) AS average_file_size_bytes,
MAX(uniform_resource.size_bytes) AS max_file_size_bytes,
MIN(uniform_resource.last_modified_at) AS oldest_file_last_modified_datetime,
MAX(uniform_resource.last_modified_at) AS youngest_file_last_modified_datetime
FROM
ur_ingest_session
JOIN
device ON ur_ingest_session.device_id = device.device_id
LEFT JOIN
ur_ingest_session_fs_path ON ur_ingest_session.ur_ingest_session_id = ur_ingest_session_fs_path.ingest_session_id
LEFT JOIN
ur_ingest_session_fs_path_entry ON ur_ingest_session_fs_path.ur_ingest_session_fs_path_id = ur_ingest_session_fs_path_entry.ingest_fs_path_id
LEFT JOIN
uniform_resource ON ur_ingest_session_fs_path_entry.uniform_resource_id = uniform_resource.uniform_resource_id
GROUP BY
device.device_id,
ur_ingest_session.ur_ingest_session_id,
ur_ingest_session.ingest_started_at,
ur_ingest_session.ingest_finished_at,
ur_ingest_session_fs_path_entry.file_extn,
ur_ingest_session_fs_path.root_path
)
SELECT
device_id,
ingest_session_id,
ingest_session_started_at,
ingest_session_finished_at,
file_extension,
ingest_session_fs_path_id,
ingest_session_root_fs_path,
total_file_count,
file_count_with_content,
file_count_with_frontmatter,
min_file_size_bytes,
CAST(ROUND(average_file_size_bytes) AS INTEGER) AS average_file_size_bytes,
max_file_size_bytes,
oldest_file_last_modified_datetime,
youngest_file_last_modified_datetime
FROM
Summary
ORDER BY
device_id,
ingest_session_finished_at,
file_extension |
| ur_ingest_session_files_stats |
ingest_session_started_at |
TIMESTAMPTZ |
/console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes |
[Content](console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes) |
[ur_ingest_session_files_stats (view) Schema](console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes) |
/console/content/view/ur_ingest_session_files_stats.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/ur_ingest_session_files_stats.sql?stats=yes) |
[ur_ingest_session_files_stats (view) Content](console/content/view/ur_ingest_session_files_stats.sql?stats=yes) |
CREATE VIEW "ur_ingest_session_files_stats" AS
WITH Summary AS (
SELECT
device.device_id AS device_id,
ur_ingest_session.ur_ingest_session_id AS ingest_session_id,
ur_ingest_session.ingest_started_at AS ingest_session_started_at,
ur_ingest_session.ingest_finished_at AS ingest_session_finished_at,
COALESCE(ur_ingest_session_fs_path_entry.file_extn, '') AS file_extension,
ur_ingest_session_fs_path.ur_ingest_session_fs_path_id as ingest_session_fs_path_id,
ur_ingest_session_fs_path.root_path AS ingest_session_root_fs_path,
COUNT(ur_ingest_session_fs_path_entry.uniform_resource_id) AS total_file_count,
SUM(CASE WHEN uniform_resource.content IS NOT NULL THEN 1 ELSE 0 END) AS file_count_with_content,
SUM(CASE WHEN uniform_resource.frontmatter IS NOT NULL THEN 1 ELSE 0 END) AS file_count_with_frontmatter,
MIN(uniform_resource.size_bytes) AS min_file_size_bytes,
AVG(uniform_resource.size_bytes) AS average_file_size_bytes,
MAX(uniform_resource.size_bytes) AS max_file_size_bytes,
MIN(uniform_resource.last_modified_at) AS oldest_file_last_modified_datetime,
MAX(uniform_resource.last_modified_at) AS youngest_file_last_modified_datetime
FROM
ur_ingest_session
JOIN
device ON ur_ingest_session.device_id = device.device_id
LEFT JOIN
ur_ingest_session_fs_path ON ur_ingest_session.ur_ingest_session_id = ur_ingest_session_fs_path.ingest_session_id
LEFT JOIN
ur_ingest_session_fs_path_entry ON ur_ingest_session_fs_path.ur_ingest_session_fs_path_id = ur_ingest_session_fs_path_entry.ingest_fs_path_id
LEFT JOIN
uniform_resource ON ur_ingest_session_fs_path_entry.uniform_resource_id = uniform_resource.uniform_resource_id
GROUP BY
device.device_id,
ur_ingest_session.ur_ingest_session_id,
ur_ingest_session.ingest_started_at,
ur_ingest_session.ingest_finished_at,
ur_ingest_session_fs_path_entry.file_extn,
ur_ingest_session_fs_path.root_path
)
SELECT
device_id,
ingest_session_id,
ingest_session_started_at,
ingest_session_finished_at,
file_extension,
ingest_session_fs_path_id,
ingest_session_root_fs_path,
total_file_count,
file_count_with_content,
file_count_with_frontmatter,
min_file_size_bytes,
CAST(ROUND(average_file_size_bytes) AS INTEGER) AS average_file_size_bytes,
max_file_size_bytes,
oldest_file_last_modified_datetime,
youngest_file_last_modified_datetime
FROM
Summary
ORDER BY
device_id,
ingest_session_finished_at,
file_extension |
| ur_ingest_session_files_stats |
ingest_session_finished_at |
TIMESTAMPTZ |
/console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes |
[Content](console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes) |
[ur_ingest_session_files_stats (view) Schema](console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes) |
/console/content/view/ur_ingest_session_files_stats.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/ur_ingest_session_files_stats.sql?stats=yes) |
[ur_ingest_session_files_stats (view) Content](console/content/view/ur_ingest_session_files_stats.sql?stats=yes) |
CREATE VIEW "ur_ingest_session_files_stats" AS
WITH Summary AS (
SELECT
device.device_id AS device_id,
ur_ingest_session.ur_ingest_session_id AS ingest_session_id,
ur_ingest_session.ingest_started_at AS ingest_session_started_at,
ur_ingest_session.ingest_finished_at AS ingest_session_finished_at,
COALESCE(ur_ingest_session_fs_path_entry.file_extn, '') AS file_extension,
ur_ingest_session_fs_path.ur_ingest_session_fs_path_id as ingest_session_fs_path_id,
ur_ingest_session_fs_path.root_path AS ingest_session_root_fs_path,
COUNT(ur_ingest_session_fs_path_entry.uniform_resource_id) AS total_file_count,
SUM(CASE WHEN uniform_resource.content IS NOT NULL THEN 1 ELSE 0 END) AS file_count_with_content,
SUM(CASE WHEN uniform_resource.frontmatter IS NOT NULL THEN 1 ELSE 0 END) AS file_count_with_frontmatter,
MIN(uniform_resource.size_bytes) AS min_file_size_bytes,
AVG(uniform_resource.size_bytes) AS average_file_size_bytes,
MAX(uniform_resource.size_bytes) AS max_file_size_bytes,
MIN(uniform_resource.last_modified_at) AS oldest_file_last_modified_datetime,
MAX(uniform_resource.last_modified_at) AS youngest_file_last_modified_datetime
FROM
ur_ingest_session
JOIN
device ON ur_ingest_session.device_id = device.device_id
LEFT JOIN
ur_ingest_session_fs_path ON ur_ingest_session.ur_ingest_session_id = ur_ingest_session_fs_path.ingest_session_id
LEFT JOIN
ur_ingest_session_fs_path_entry ON ur_ingest_session_fs_path.ur_ingest_session_fs_path_id = ur_ingest_session_fs_path_entry.ingest_fs_path_id
LEFT JOIN
uniform_resource ON ur_ingest_session_fs_path_entry.uniform_resource_id = uniform_resource.uniform_resource_id
GROUP BY
device.device_id,
ur_ingest_session.ur_ingest_session_id,
ur_ingest_session.ingest_started_at,
ur_ingest_session.ingest_finished_at,
ur_ingest_session_fs_path_entry.file_extn,
ur_ingest_session_fs_path.root_path
)
SELECT
device_id,
ingest_session_id,
ingest_session_started_at,
ingest_session_finished_at,
file_extension,
ingest_session_fs_path_id,
ingest_session_root_fs_path,
total_file_count,
file_count_with_content,
file_count_with_frontmatter,
min_file_size_bytes,
CAST(ROUND(average_file_size_bytes) AS INTEGER) AS average_file_size_bytes,
max_file_size_bytes,
oldest_file_last_modified_datetime,
youngest_file_last_modified_datetime
FROM
Summary
ORDER BY
device_id,
ingest_session_finished_at,
file_extension |
| ur_ingest_session_files_stats |
file_extension |
|
/console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes |
[Content](console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes) |
[ur_ingest_session_files_stats (view) Schema](console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes) |
/console/content/view/ur_ingest_session_files_stats.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/ur_ingest_session_files_stats.sql?stats=yes) |
[ur_ingest_session_files_stats (view) Content](console/content/view/ur_ingest_session_files_stats.sql?stats=yes) |
CREATE VIEW "ur_ingest_session_files_stats" AS
WITH Summary AS (
SELECT
device.device_id AS device_id,
ur_ingest_session.ur_ingest_session_id AS ingest_session_id,
ur_ingest_session.ingest_started_at AS ingest_session_started_at,
ur_ingest_session.ingest_finished_at AS ingest_session_finished_at,
COALESCE(ur_ingest_session_fs_path_entry.file_extn, '') AS file_extension,
ur_ingest_session_fs_path.ur_ingest_session_fs_path_id as ingest_session_fs_path_id,
ur_ingest_session_fs_path.root_path AS ingest_session_root_fs_path,
COUNT(ur_ingest_session_fs_path_entry.uniform_resource_id) AS total_file_count,
SUM(CASE WHEN uniform_resource.content IS NOT NULL THEN 1 ELSE 0 END) AS file_count_with_content,
SUM(CASE WHEN uniform_resource.frontmatter IS NOT NULL THEN 1 ELSE 0 END) AS file_count_with_frontmatter,
MIN(uniform_resource.size_bytes) AS min_file_size_bytes,
AVG(uniform_resource.size_bytes) AS average_file_size_bytes,
MAX(uniform_resource.size_bytes) AS max_file_size_bytes,
MIN(uniform_resource.last_modified_at) AS oldest_file_last_modified_datetime,
MAX(uniform_resource.last_modified_at) AS youngest_file_last_modified_datetime
FROM
ur_ingest_session
JOIN
device ON ur_ingest_session.device_id = device.device_id
LEFT JOIN
ur_ingest_session_fs_path ON ur_ingest_session.ur_ingest_session_id = ur_ingest_session_fs_path.ingest_session_id
LEFT JOIN
ur_ingest_session_fs_path_entry ON ur_ingest_session_fs_path.ur_ingest_session_fs_path_id = ur_ingest_session_fs_path_entry.ingest_fs_path_id
LEFT JOIN
uniform_resource ON ur_ingest_session_fs_path_entry.uniform_resource_id = uniform_resource.uniform_resource_id
GROUP BY
device.device_id,
ur_ingest_session.ur_ingest_session_id,
ur_ingest_session.ingest_started_at,
ur_ingest_session.ingest_finished_at,
ur_ingest_session_fs_path_entry.file_extn,
ur_ingest_session_fs_path.root_path
)
SELECT
device_id,
ingest_session_id,
ingest_session_started_at,
ingest_session_finished_at,
file_extension,
ingest_session_fs_path_id,
ingest_session_root_fs_path,
total_file_count,
file_count_with_content,
file_count_with_frontmatter,
min_file_size_bytes,
CAST(ROUND(average_file_size_bytes) AS INTEGER) AS average_file_size_bytes,
max_file_size_bytes,
oldest_file_last_modified_datetime,
youngest_file_last_modified_datetime
FROM
Summary
ORDER BY
device_id,
ingest_session_finished_at,
file_extension |
| ur_ingest_session_files_stats |
ingest_session_fs_path_id |
VARCHAR |
/console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes |
[Content](console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes) |
[ur_ingest_session_files_stats (view) Schema](console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes) |
/console/content/view/ur_ingest_session_files_stats.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/ur_ingest_session_files_stats.sql?stats=yes) |
[ur_ingest_session_files_stats (view) Content](console/content/view/ur_ingest_session_files_stats.sql?stats=yes) |
CREATE VIEW "ur_ingest_session_files_stats" AS
WITH Summary AS (
SELECT
device.device_id AS device_id,
ur_ingest_session.ur_ingest_session_id AS ingest_session_id,
ur_ingest_session.ingest_started_at AS ingest_session_started_at,
ur_ingest_session.ingest_finished_at AS ingest_session_finished_at,
COALESCE(ur_ingest_session_fs_path_entry.file_extn, '') AS file_extension,
ur_ingest_session_fs_path.ur_ingest_session_fs_path_id as ingest_session_fs_path_id,
ur_ingest_session_fs_path.root_path AS ingest_session_root_fs_path,
COUNT(ur_ingest_session_fs_path_entry.uniform_resource_id) AS total_file_count,
SUM(CASE WHEN uniform_resource.content IS NOT NULL THEN 1 ELSE 0 END) AS file_count_with_content,
SUM(CASE WHEN uniform_resource.frontmatter IS NOT NULL THEN 1 ELSE 0 END) AS file_count_with_frontmatter,
MIN(uniform_resource.size_bytes) AS min_file_size_bytes,
AVG(uniform_resource.size_bytes) AS average_file_size_bytes,
MAX(uniform_resource.size_bytes) AS max_file_size_bytes,
MIN(uniform_resource.last_modified_at) AS oldest_file_last_modified_datetime,
MAX(uniform_resource.last_modified_at) AS youngest_file_last_modified_datetime
FROM
ur_ingest_session
JOIN
device ON ur_ingest_session.device_id = device.device_id
LEFT JOIN
ur_ingest_session_fs_path ON ur_ingest_session.ur_ingest_session_id = ur_ingest_session_fs_path.ingest_session_id
LEFT JOIN
ur_ingest_session_fs_path_entry ON ur_ingest_session_fs_path.ur_ingest_session_fs_path_id = ur_ingest_session_fs_path_entry.ingest_fs_path_id
LEFT JOIN
uniform_resource ON ur_ingest_session_fs_path_entry.uniform_resource_id = uniform_resource.uniform_resource_id
GROUP BY
device.device_id,
ur_ingest_session.ur_ingest_session_id,
ur_ingest_session.ingest_started_at,
ur_ingest_session.ingest_finished_at,
ur_ingest_session_fs_path_entry.file_extn,
ur_ingest_session_fs_path.root_path
)
SELECT
device_id,
ingest_session_id,
ingest_session_started_at,
ingest_session_finished_at,
file_extension,
ingest_session_fs_path_id,
ingest_session_root_fs_path,
total_file_count,
file_count_with_content,
file_count_with_frontmatter,
min_file_size_bytes,
CAST(ROUND(average_file_size_bytes) AS INTEGER) AS average_file_size_bytes,
max_file_size_bytes,
oldest_file_last_modified_datetime,
youngest_file_last_modified_datetime
FROM
Summary
ORDER BY
device_id,
ingest_session_finished_at,
file_extension |
| ur_ingest_session_files_stats |
ingest_session_root_fs_path |
TEXT |
/console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes |
[Content](console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes) |
[ur_ingest_session_files_stats (view) Schema](console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes) |
/console/content/view/ur_ingest_session_files_stats.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/ur_ingest_session_files_stats.sql?stats=yes) |
[ur_ingest_session_files_stats (view) Content](console/content/view/ur_ingest_session_files_stats.sql?stats=yes) |
CREATE VIEW "ur_ingest_session_files_stats" AS
WITH Summary AS (
SELECT
device.device_id AS device_id,
ur_ingest_session.ur_ingest_session_id AS ingest_session_id,
ur_ingest_session.ingest_started_at AS ingest_session_started_at,
ur_ingest_session.ingest_finished_at AS ingest_session_finished_at,
COALESCE(ur_ingest_session_fs_path_entry.file_extn, '') AS file_extension,
ur_ingest_session_fs_path.ur_ingest_session_fs_path_id as ingest_session_fs_path_id,
ur_ingest_session_fs_path.root_path AS ingest_session_root_fs_path,
COUNT(ur_ingest_session_fs_path_entry.uniform_resource_id) AS total_file_count,
SUM(CASE WHEN uniform_resource.content IS NOT NULL THEN 1 ELSE 0 END) AS file_count_with_content,
SUM(CASE WHEN uniform_resource.frontmatter IS NOT NULL THEN 1 ELSE 0 END) AS file_count_with_frontmatter,
MIN(uniform_resource.size_bytes) AS min_file_size_bytes,
AVG(uniform_resource.size_bytes) AS average_file_size_bytes,
MAX(uniform_resource.size_bytes) AS max_file_size_bytes,
MIN(uniform_resource.last_modified_at) AS oldest_file_last_modified_datetime,
MAX(uniform_resource.last_modified_at) AS youngest_file_last_modified_datetime
FROM
ur_ingest_session
JOIN
device ON ur_ingest_session.device_id = device.device_id
LEFT JOIN
ur_ingest_session_fs_path ON ur_ingest_session.ur_ingest_session_id = ur_ingest_session_fs_path.ingest_session_id
LEFT JOIN
ur_ingest_session_fs_path_entry ON ur_ingest_session_fs_path.ur_ingest_session_fs_path_id = ur_ingest_session_fs_path_entry.ingest_fs_path_id
LEFT JOIN
uniform_resource ON ur_ingest_session_fs_path_entry.uniform_resource_id = uniform_resource.uniform_resource_id
GROUP BY
device.device_id,
ur_ingest_session.ur_ingest_session_id,
ur_ingest_session.ingest_started_at,
ur_ingest_session.ingest_finished_at,
ur_ingest_session_fs_path_entry.file_extn,
ur_ingest_session_fs_path.root_path
)
SELECT
device_id,
ingest_session_id,
ingest_session_started_at,
ingest_session_finished_at,
file_extension,
ingest_session_fs_path_id,
ingest_session_root_fs_path,
total_file_count,
file_count_with_content,
file_count_with_frontmatter,
min_file_size_bytes,
CAST(ROUND(average_file_size_bytes) AS INTEGER) AS average_file_size_bytes,
max_file_size_bytes,
oldest_file_last_modified_datetime,
youngest_file_last_modified_datetime
FROM
Summary
ORDER BY
device_id,
ingest_session_finished_at,
file_extension |
| ur_ingest_session_files_stats |
total_file_count |
|
/console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes |
[Content](console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes) |
[ur_ingest_session_files_stats (view) Schema](console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes) |
/console/content/view/ur_ingest_session_files_stats.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/ur_ingest_session_files_stats.sql?stats=yes) |
[ur_ingest_session_files_stats (view) Content](console/content/view/ur_ingest_session_files_stats.sql?stats=yes) |
CREATE VIEW "ur_ingest_session_files_stats" AS
WITH Summary AS (
SELECT
device.device_id AS device_id,
ur_ingest_session.ur_ingest_session_id AS ingest_session_id,
ur_ingest_session.ingest_started_at AS ingest_session_started_at,
ur_ingest_session.ingest_finished_at AS ingest_session_finished_at,
COALESCE(ur_ingest_session_fs_path_entry.file_extn, '') AS file_extension,
ur_ingest_session_fs_path.ur_ingest_session_fs_path_id as ingest_session_fs_path_id,
ur_ingest_session_fs_path.root_path AS ingest_session_root_fs_path,
COUNT(ur_ingest_session_fs_path_entry.uniform_resource_id) AS total_file_count,
SUM(CASE WHEN uniform_resource.content IS NOT NULL THEN 1 ELSE 0 END) AS file_count_with_content,
SUM(CASE WHEN uniform_resource.frontmatter IS NOT NULL THEN 1 ELSE 0 END) AS file_count_with_frontmatter,
MIN(uniform_resource.size_bytes) AS min_file_size_bytes,
AVG(uniform_resource.size_bytes) AS average_file_size_bytes,
MAX(uniform_resource.size_bytes) AS max_file_size_bytes,
MIN(uniform_resource.last_modified_at) AS oldest_file_last_modified_datetime,
MAX(uniform_resource.last_modified_at) AS youngest_file_last_modified_datetime
FROM
ur_ingest_session
JOIN
device ON ur_ingest_session.device_id = device.device_id
LEFT JOIN
ur_ingest_session_fs_path ON ur_ingest_session.ur_ingest_session_id = ur_ingest_session_fs_path.ingest_session_id
LEFT JOIN
ur_ingest_session_fs_path_entry ON ur_ingest_session_fs_path.ur_ingest_session_fs_path_id = ur_ingest_session_fs_path_entry.ingest_fs_path_id
LEFT JOIN
uniform_resource ON ur_ingest_session_fs_path_entry.uniform_resource_id = uniform_resource.uniform_resource_id
GROUP BY
device.device_id,
ur_ingest_session.ur_ingest_session_id,
ur_ingest_session.ingest_started_at,
ur_ingest_session.ingest_finished_at,
ur_ingest_session_fs_path_entry.file_extn,
ur_ingest_session_fs_path.root_path
)
SELECT
device_id,
ingest_session_id,
ingest_session_started_at,
ingest_session_finished_at,
file_extension,
ingest_session_fs_path_id,
ingest_session_root_fs_path,
total_file_count,
file_count_with_content,
file_count_with_frontmatter,
min_file_size_bytes,
CAST(ROUND(average_file_size_bytes) AS INTEGER) AS average_file_size_bytes,
max_file_size_bytes,
oldest_file_last_modified_datetime,
youngest_file_last_modified_datetime
FROM
Summary
ORDER BY
device_id,
ingest_session_finished_at,
file_extension |
| ur_ingest_session_files_stats |
file_count_with_content |
|
/console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes |
[Content](console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes) |
[ur_ingest_session_files_stats (view) Schema](console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes) |
/console/content/view/ur_ingest_session_files_stats.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/ur_ingest_session_files_stats.sql?stats=yes) |
[ur_ingest_session_files_stats (view) Content](console/content/view/ur_ingest_session_files_stats.sql?stats=yes) |
CREATE VIEW "ur_ingest_session_files_stats" AS
WITH Summary AS (
SELECT
device.device_id AS device_id,
ur_ingest_session.ur_ingest_session_id AS ingest_session_id,
ur_ingest_session.ingest_started_at AS ingest_session_started_at,
ur_ingest_session.ingest_finished_at AS ingest_session_finished_at,
COALESCE(ur_ingest_session_fs_path_entry.file_extn, '') AS file_extension,
ur_ingest_session_fs_path.ur_ingest_session_fs_path_id as ingest_session_fs_path_id,
ur_ingest_session_fs_path.root_path AS ingest_session_root_fs_path,
COUNT(ur_ingest_session_fs_path_entry.uniform_resource_id) AS total_file_count,
SUM(CASE WHEN uniform_resource.content IS NOT NULL THEN 1 ELSE 0 END) AS file_count_with_content,
SUM(CASE WHEN uniform_resource.frontmatter IS NOT NULL THEN 1 ELSE 0 END) AS file_count_with_frontmatter,
MIN(uniform_resource.size_bytes) AS min_file_size_bytes,
AVG(uniform_resource.size_bytes) AS average_file_size_bytes,
MAX(uniform_resource.size_bytes) AS max_file_size_bytes,
MIN(uniform_resource.last_modified_at) AS oldest_file_last_modified_datetime,
MAX(uniform_resource.last_modified_at) AS youngest_file_last_modified_datetime
FROM
ur_ingest_session
JOIN
device ON ur_ingest_session.device_id = device.device_id
LEFT JOIN
ur_ingest_session_fs_path ON ur_ingest_session.ur_ingest_session_id = ur_ingest_session_fs_path.ingest_session_id
LEFT JOIN
ur_ingest_session_fs_path_entry ON ur_ingest_session_fs_path.ur_ingest_session_fs_path_id = ur_ingest_session_fs_path_entry.ingest_fs_path_id
LEFT JOIN
uniform_resource ON ur_ingest_session_fs_path_entry.uniform_resource_id = uniform_resource.uniform_resource_id
GROUP BY
device.device_id,
ur_ingest_session.ur_ingest_session_id,
ur_ingest_session.ingest_started_at,
ur_ingest_session.ingest_finished_at,
ur_ingest_session_fs_path_entry.file_extn,
ur_ingest_session_fs_path.root_path
)
SELECT
device_id,
ingest_session_id,
ingest_session_started_at,
ingest_session_finished_at,
file_extension,
ingest_session_fs_path_id,
ingest_session_root_fs_path,
total_file_count,
file_count_with_content,
file_count_with_frontmatter,
min_file_size_bytes,
CAST(ROUND(average_file_size_bytes) AS INTEGER) AS average_file_size_bytes,
max_file_size_bytes,
oldest_file_last_modified_datetime,
youngest_file_last_modified_datetime
FROM
Summary
ORDER BY
device_id,
ingest_session_finished_at,
file_extension |
| ur_ingest_session_files_stats |
file_count_with_frontmatter |
|
/console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes |
[Content](console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes) |
[ur_ingest_session_files_stats (view) Schema](console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes) |
/console/content/view/ur_ingest_session_files_stats.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/ur_ingest_session_files_stats.sql?stats=yes) |
[ur_ingest_session_files_stats (view) Content](console/content/view/ur_ingest_session_files_stats.sql?stats=yes) |
CREATE VIEW "ur_ingest_session_files_stats" AS
WITH Summary AS (
SELECT
device.device_id AS device_id,
ur_ingest_session.ur_ingest_session_id AS ingest_session_id,
ur_ingest_session.ingest_started_at AS ingest_session_started_at,
ur_ingest_session.ingest_finished_at AS ingest_session_finished_at,
COALESCE(ur_ingest_session_fs_path_entry.file_extn, '') AS file_extension,
ur_ingest_session_fs_path.ur_ingest_session_fs_path_id as ingest_session_fs_path_id,
ur_ingest_session_fs_path.root_path AS ingest_session_root_fs_path,
COUNT(ur_ingest_session_fs_path_entry.uniform_resource_id) AS total_file_count,
SUM(CASE WHEN uniform_resource.content IS NOT NULL THEN 1 ELSE 0 END) AS file_count_with_content,
SUM(CASE WHEN uniform_resource.frontmatter IS NOT NULL THEN 1 ELSE 0 END) AS file_count_with_frontmatter,
MIN(uniform_resource.size_bytes) AS min_file_size_bytes,
AVG(uniform_resource.size_bytes) AS average_file_size_bytes,
MAX(uniform_resource.size_bytes) AS max_file_size_bytes,
MIN(uniform_resource.last_modified_at) AS oldest_file_last_modified_datetime,
MAX(uniform_resource.last_modified_at) AS youngest_file_last_modified_datetime
FROM
ur_ingest_session
JOIN
device ON ur_ingest_session.device_id = device.device_id
LEFT JOIN
ur_ingest_session_fs_path ON ur_ingest_session.ur_ingest_session_id = ur_ingest_session_fs_path.ingest_session_id
LEFT JOIN
ur_ingest_session_fs_path_entry ON ur_ingest_session_fs_path.ur_ingest_session_fs_path_id = ur_ingest_session_fs_path_entry.ingest_fs_path_id
LEFT JOIN
uniform_resource ON ur_ingest_session_fs_path_entry.uniform_resource_id = uniform_resource.uniform_resource_id
GROUP BY
device.device_id,
ur_ingest_session.ur_ingest_session_id,
ur_ingest_session.ingest_started_at,
ur_ingest_session.ingest_finished_at,
ur_ingest_session_fs_path_entry.file_extn,
ur_ingest_session_fs_path.root_path
)
SELECT
device_id,
ingest_session_id,
ingest_session_started_at,
ingest_session_finished_at,
file_extension,
ingest_session_fs_path_id,
ingest_session_root_fs_path,
total_file_count,
file_count_with_content,
file_count_with_frontmatter,
min_file_size_bytes,
CAST(ROUND(average_file_size_bytes) AS INTEGER) AS average_file_size_bytes,
max_file_size_bytes,
oldest_file_last_modified_datetime,
youngest_file_last_modified_datetime
FROM
Summary
ORDER BY
device_id,
ingest_session_finished_at,
file_extension |
| ur_ingest_session_files_stats |
min_file_size_bytes |
|
/console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes |
[Content](console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes) |
[ur_ingest_session_files_stats (view) Schema](console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes) |
/console/content/view/ur_ingest_session_files_stats.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/ur_ingest_session_files_stats.sql?stats=yes) |
[ur_ingest_session_files_stats (view) Content](console/content/view/ur_ingest_session_files_stats.sql?stats=yes) |
CREATE VIEW "ur_ingest_session_files_stats" AS
WITH Summary AS (
SELECT
device.device_id AS device_id,
ur_ingest_session.ur_ingest_session_id AS ingest_session_id,
ur_ingest_session.ingest_started_at AS ingest_session_started_at,
ur_ingest_session.ingest_finished_at AS ingest_session_finished_at,
COALESCE(ur_ingest_session_fs_path_entry.file_extn, '') AS file_extension,
ur_ingest_session_fs_path.ur_ingest_session_fs_path_id as ingest_session_fs_path_id,
ur_ingest_session_fs_path.root_path AS ingest_session_root_fs_path,
COUNT(ur_ingest_session_fs_path_entry.uniform_resource_id) AS total_file_count,
SUM(CASE WHEN uniform_resource.content IS NOT NULL THEN 1 ELSE 0 END) AS file_count_with_content,
SUM(CASE WHEN uniform_resource.frontmatter IS NOT NULL THEN 1 ELSE 0 END) AS file_count_with_frontmatter,
MIN(uniform_resource.size_bytes) AS min_file_size_bytes,
AVG(uniform_resource.size_bytes) AS average_file_size_bytes,
MAX(uniform_resource.size_bytes) AS max_file_size_bytes,
MIN(uniform_resource.last_modified_at) AS oldest_file_last_modified_datetime,
MAX(uniform_resource.last_modified_at) AS youngest_file_last_modified_datetime
FROM
ur_ingest_session
JOIN
device ON ur_ingest_session.device_id = device.device_id
LEFT JOIN
ur_ingest_session_fs_path ON ur_ingest_session.ur_ingest_session_id = ur_ingest_session_fs_path.ingest_session_id
LEFT JOIN
ur_ingest_session_fs_path_entry ON ur_ingest_session_fs_path.ur_ingest_session_fs_path_id = ur_ingest_session_fs_path_entry.ingest_fs_path_id
LEFT JOIN
uniform_resource ON ur_ingest_session_fs_path_entry.uniform_resource_id = uniform_resource.uniform_resource_id
GROUP BY
device.device_id,
ur_ingest_session.ur_ingest_session_id,
ur_ingest_session.ingest_started_at,
ur_ingest_session.ingest_finished_at,
ur_ingest_session_fs_path_entry.file_extn,
ur_ingest_session_fs_path.root_path
)
SELECT
device_id,
ingest_session_id,
ingest_session_started_at,
ingest_session_finished_at,
file_extension,
ingest_session_fs_path_id,
ingest_session_root_fs_path,
total_file_count,
file_count_with_content,
file_count_with_frontmatter,
min_file_size_bytes,
CAST(ROUND(average_file_size_bytes) AS INTEGER) AS average_file_size_bytes,
max_file_size_bytes,
oldest_file_last_modified_datetime,
youngest_file_last_modified_datetime
FROM
Summary
ORDER BY
device_id,
ingest_session_finished_at,
file_extension |
| ur_ingest_session_files_stats |
average_file_size_bytes |
INT |
/console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes |
[Content](console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes) |
[ur_ingest_session_files_stats (view) Schema](console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes) |
/console/content/view/ur_ingest_session_files_stats.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/ur_ingest_session_files_stats.sql?stats=yes) |
[ur_ingest_session_files_stats (view) Content](console/content/view/ur_ingest_session_files_stats.sql?stats=yes) |
CREATE VIEW "ur_ingest_session_files_stats" AS
WITH Summary AS (
SELECT
device.device_id AS device_id,
ur_ingest_session.ur_ingest_session_id AS ingest_session_id,
ur_ingest_session.ingest_started_at AS ingest_session_started_at,
ur_ingest_session.ingest_finished_at AS ingest_session_finished_at,
COALESCE(ur_ingest_session_fs_path_entry.file_extn, '') AS file_extension,
ur_ingest_session_fs_path.ur_ingest_session_fs_path_id as ingest_session_fs_path_id,
ur_ingest_session_fs_path.root_path AS ingest_session_root_fs_path,
COUNT(ur_ingest_session_fs_path_entry.uniform_resource_id) AS total_file_count,
SUM(CASE WHEN uniform_resource.content IS NOT NULL THEN 1 ELSE 0 END) AS file_count_with_content,
SUM(CASE WHEN uniform_resource.frontmatter IS NOT NULL THEN 1 ELSE 0 END) AS file_count_with_frontmatter,
MIN(uniform_resource.size_bytes) AS min_file_size_bytes,
AVG(uniform_resource.size_bytes) AS average_file_size_bytes,
MAX(uniform_resource.size_bytes) AS max_file_size_bytes,
MIN(uniform_resource.last_modified_at) AS oldest_file_last_modified_datetime,
MAX(uniform_resource.last_modified_at) AS youngest_file_last_modified_datetime
FROM
ur_ingest_session
JOIN
device ON ur_ingest_session.device_id = device.device_id
LEFT JOIN
ur_ingest_session_fs_path ON ur_ingest_session.ur_ingest_session_id = ur_ingest_session_fs_path.ingest_session_id
LEFT JOIN
ur_ingest_session_fs_path_entry ON ur_ingest_session_fs_path.ur_ingest_session_fs_path_id = ur_ingest_session_fs_path_entry.ingest_fs_path_id
LEFT JOIN
uniform_resource ON ur_ingest_session_fs_path_entry.uniform_resource_id = uniform_resource.uniform_resource_id
GROUP BY
device.device_id,
ur_ingest_session.ur_ingest_session_id,
ur_ingest_session.ingest_started_at,
ur_ingest_session.ingest_finished_at,
ur_ingest_session_fs_path_entry.file_extn,
ur_ingest_session_fs_path.root_path
)
SELECT
device_id,
ingest_session_id,
ingest_session_started_at,
ingest_session_finished_at,
file_extension,
ingest_session_fs_path_id,
ingest_session_root_fs_path,
total_file_count,
file_count_with_content,
file_count_with_frontmatter,
min_file_size_bytes,
CAST(ROUND(average_file_size_bytes) AS INTEGER) AS average_file_size_bytes,
max_file_size_bytes,
oldest_file_last_modified_datetime,
youngest_file_last_modified_datetime
FROM
Summary
ORDER BY
device_id,
ingest_session_finished_at,
file_extension |
| ur_ingest_session_files_stats |
max_file_size_bytes |
|
/console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes |
[Content](console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes) |
[ur_ingest_session_files_stats (view) Schema](console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes) |
/console/content/view/ur_ingest_session_files_stats.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/ur_ingest_session_files_stats.sql?stats=yes) |
[ur_ingest_session_files_stats (view) Content](console/content/view/ur_ingest_session_files_stats.sql?stats=yes) |
CREATE VIEW "ur_ingest_session_files_stats" AS
WITH Summary AS (
SELECT
device.device_id AS device_id,
ur_ingest_session.ur_ingest_session_id AS ingest_session_id,
ur_ingest_session.ingest_started_at AS ingest_session_started_at,
ur_ingest_session.ingest_finished_at AS ingest_session_finished_at,
COALESCE(ur_ingest_session_fs_path_entry.file_extn, '') AS file_extension,
ur_ingest_session_fs_path.ur_ingest_session_fs_path_id as ingest_session_fs_path_id,
ur_ingest_session_fs_path.root_path AS ingest_session_root_fs_path,
COUNT(ur_ingest_session_fs_path_entry.uniform_resource_id) AS total_file_count,
SUM(CASE WHEN uniform_resource.content IS NOT NULL THEN 1 ELSE 0 END) AS file_count_with_content,
SUM(CASE WHEN uniform_resource.frontmatter IS NOT NULL THEN 1 ELSE 0 END) AS file_count_with_frontmatter,
MIN(uniform_resource.size_bytes) AS min_file_size_bytes,
AVG(uniform_resource.size_bytes) AS average_file_size_bytes,
MAX(uniform_resource.size_bytes) AS max_file_size_bytes,
MIN(uniform_resource.last_modified_at) AS oldest_file_last_modified_datetime,
MAX(uniform_resource.last_modified_at) AS youngest_file_last_modified_datetime
FROM
ur_ingest_session
JOIN
device ON ur_ingest_session.device_id = device.device_id
LEFT JOIN
ur_ingest_session_fs_path ON ur_ingest_session.ur_ingest_session_id = ur_ingest_session_fs_path.ingest_session_id
LEFT JOIN
ur_ingest_session_fs_path_entry ON ur_ingest_session_fs_path.ur_ingest_session_fs_path_id = ur_ingest_session_fs_path_entry.ingest_fs_path_id
LEFT JOIN
uniform_resource ON ur_ingest_session_fs_path_entry.uniform_resource_id = uniform_resource.uniform_resource_id
GROUP BY
device.device_id,
ur_ingest_session.ur_ingest_session_id,
ur_ingest_session.ingest_started_at,
ur_ingest_session.ingest_finished_at,
ur_ingest_session_fs_path_entry.file_extn,
ur_ingest_session_fs_path.root_path
)
SELECT
device_id,
ingest_session_id,
ingest_session_started_at,
ingest_session_finished_at,
file_extension,
ingest_session_fs_path_id,
ingest_session_root_fs_path,
total_file_count,
file_count_with_content,
file_count_with_frontmatter,
min_file_size_bytes,
CAST(ROUND(average_file_size_bytes) AS INTEGER) AS average_file_size_bytes,
max_file_size_bytes,
oldest_file_last_modified_datetime,
youngest_file_last_modified_datetime
FROM
Summary
ORDER BY
device_id,
ingest_session_finished_at,
file_extension |
| ur_ingest_session_files_stats |
oldest_file_last_modified_datetime |
|
/console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes |
[Content](console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes) |
[ur_ingest_session_files_stats (view) Schema](console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes) |
/console/content/view/ur_ingest_session_files_stats.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/ur_ingest_session_files_stats.sql?stats=yes) |
[ur_ingest_session_files_stats (view) Content](console/content/view/ur_ingest_session_files_stats.sql?stats=yes) |
CREATE VIEW "ur_ingest_session_files_stats" AS
WITH Summary AS (
SELECT
device.device_id AS device_id,
ur_ingest_session.ur_ingest_session_id AS ingest_session_id,
ur_ingest_session.ingest_started_at AS ingest_session_started_at,
ur_ingest_session.ingest_finished_at AS ingest_session_finished_at,
COALESCE(ur_ingest_session_fs_path_entry.file_extn, '') AS file_extension,
ur_ingest_session_fs_path.ur_ingest_session_fs_path_id as ingest_session_fs_path_id,
ur_ingest_session_fs_path.root_path AS ingest_session_root_fs_path,
COUNT(ur_ingest_session_fs_path_entry.uniform_resource_id) AS total_file_count,
SUM(CASE WHEN uniform_resource.content IS NOT NULL THEN 1 ELSE 0 END) AS file_count_with_content,
SUM(CASE WHEN uniform_resource.frontmatter IS NOT NULL THEN 1 ELSE 0 END) AS file_count_with_frontmatter,
MIN(uniform_resource.size_bytes) AS min_file_size_bytes,
AVG(uniform_resource.size_bytes) AS average_file_size_bytes,
MAX(uniform_resource.size_bytes) AS max_file_size_bytes,
MIN(uniform_resource.last_modified_at) AS oldest_file_last_modified_datetime,
MAX(uniform_resource.last_modified_at) AS youngest_file_last_modified_datetime
FROM
ur_ingest_session
JOIN
device ON ur_ingest_session.device_id = device.device_id
LEFT JOIN
ur_ingest_session_fs_path ON ur_ingest_session.ur_ingest_session_id = ur_ingest_session_fs_path.ingest_session_id
LEFT JOIN
ur_ingest_session_fs_path_entry ON ur_ingest_session_fs_path.ur_ingest_session_fs_path_id = ur_ingest_session_fs_path_entry.ingest_fs_path_id
LEFT JOIN
uniform_resource ON ur_ingest_session_fs_path_entry.uniform_resource_id = uniform_resource.uniform_resource_id
GROUP BY
device.device_id,
ur_ingest_session.ur_ingest_session_id,
ur_ingest_session.ingest_started_at,
ur_ingest_session.ingest_finished_at,
ur_ingest_session_fs_path_entry.file_extn,
ur_ingest_session_fs_path.root_path
)
SELECT
device_id,
ingest_session_id,
ingest_session_started_at,
ingest_session_finished_at,
file_extension,
ingest_session_fs_path_id,
ingest_session_root_fs_path,
total_file_count,
file_count_with_content,
file_count_with_frontmatter,
min_file_size_bytes,
CAST(ROUND(average_file_size_bytes) AS INTEGER) AS average_file_size_bytes,
max_file_size_bytes,
oldest_file_last_modified_datetime,
youngest_file_last_modified_datetime
FROM
Summary
ORDER BY
device_id,
ingest_session_finished_at,
file_extension |
| ur_ingest_session_files_stats |
youngest_file_last_modified_datetime |
|
/console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes |
[Content](console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes) |
[ur_ingest_session_files_stats (view) Schema](console/info-schema/view.sql?name=ur_ingest_session_files_stats&stats=yes) |
/console/content/view/ur_ingest_session_files_stats.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/ur_ingest_session_files_stats.sql?stats=yes) |
[ur_ingest_session_files_stats (view) Content](console/content/view/ur_ingest_session_files_stats.sql?stats=yes) |
CREATE VIEW "ur_ingest_session_files_stats" AS
WITH Summary AS (
SELECT
device.device_id AS device_id,
ur_ingest_session.ur_ingest_session_id AS ingest_session_id,
ur_ingest_session.ingest_started_at AS ingest_session_started_at,
ur_ingest_session.ingest_finished_at AS ingest_session_finished_at,
COALESCE(ur_ingest_session_fs_path_entry.file_extn, '') AS file_extension,
ur_ingest_session_fs_path.ur_ingest_session_fs_path_id as ingest_session_fs_path_id,
ur_ingest_session_fs_path.root_path AS ingest_session_root_fs_path,
COUNT(ur_ingest_session_fs_path_entry.uniform_resource_id) AS total_file_count,
SUM(CASE WHEN uniform_resource.content IS NOT NULL THEN 1 ELSE 0 END) AS file_count_with_content,
SUM(CASE WHEN uniform_resource.frontmatter IS NOT NULL THEN 1 ELSE 0 END) AS file_count_with_frontmatter,
MIN(uniform_resource.size_bytes) AS min_file_size_bytes,
AVG(uniform_resource.size_bytes) AS average_file_size_bytes,
MAX(uniform_resource.size_bytes) AS max_file_size_bytes,
MIN(uniform_resource.last_modified_at) AS oldest_file_last_modified_datetime,
MAX(uniform_resource.last_modified_at) AS youngest_file_last_modified_datetime
FROM
ur_ingest_session
JOIN
device ON ur_ingest_session.device_id = device.device_id
LEFT JOIN
ur_ingest_session_fs_path ON ur_ingest_session.ur_ingest_session_id = ur_ingest_session_fs_path.ingest_session_id
LEFT JOIN
ur_ingest_session_fs_path_entry ON ur_ingest_session_fs_path.ur_ingest_session_fs_path_id = ur_ingest_session_fs_path_entry.ingest_fs_path_id
LEFT JOIN
uniform_resource ON ur_ingest_session_fs_path_entry.uniform_resource_id = uniform_resource.uniform_resource_id
GROUP BY
device.device_id,
ur_ingest_session.ur_ingest_session_id,
ur_ingest_session.ingest_started_at,
ur_ingest_session.ingest_finished_at,
ur_ingest_session_fs_path_entry.file_extn,
ur_ingest_session_fs_path.root_path
)
SELECT
device_id,
ingest_session_id,
ingest_session_started_at,
ingest_session_finished_at,
file_extension,
ingest_session_fs_path_id,
ingest_session_root_fs_path,
total_file_count,
file_count_with_content,
file_count_with_frontmatter,
min_file_size_bytes,
CAST(ROUND(average_file_size_bytes) AS INTEGER) AS average_file_size_bytes,
max_file_size_bytes,
oldest_file_last_modified_datetime,
youngest_file_last_modified_datetime
FROM
Summary
ORDER BY
device_id,
ingest_session_finished_at,
file_extension |
| ur_ingest_session_files_stats_latest |
device_id |
VARCHAR |
/console/info-schema/view.sql?name=ur_ingest_session_files_stats_latest&stats=yes |
[Content](console/info-schema/view.sql?name=ur_ingest_session_files_stats_latest&stats=yes) |
[ur_ingest_session_files_stats_latest (view) Schema](console/info-schema/view.sql?name=ur_ingest_session_files_stats_latest&stats=yes) |
/console/content/view/ur_ingest_session_files_stats_latest.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/ur_ingest_session_files_stats_latest.sql?stats=yes) |
[ur_ingest_session_files_stats_latest (view) Content](console/content/view/ur_ingest_session_files_stats_latest.sql?stats=yes) |
CREATE VIEW "ur_ingest_session_files_stats_latest" AS
SELECT iss.*
FROM ur_ingest_session_files_stats AS iss
JOIN ( SELECT ur_ingest_session.ur_ingest_session_id AS latest_session_id
FROM ur_ingest_session
ORDER BY ur_ingest_session.ingest_finished_at DESC
LIMIT 1) AS latest
ON iss.ingest_session_id = latest.latest_session_id |
| ur_ingest_session_files_stats_latest |
ingest_session_id |
VARCHAR |
/console/info-schema/view.sql?name=ur_ingest_session_files_stats_latest&stats=yes |
[Content](console/info-schema/view.sql?name=ur_ingest_session_files_stats_latest&stats=yes) |
[ur_ingest_session_files_stats_latest (view) Schema](console/info-schema/view.sql?name=ur_ingest_session_files_stats_latest&stats=yes) |
/console/content/view/ur_ingest_session_files_stats_latest.sql?stats=yes |
[Content]($SITE_PREFIX_URL/console/content/view/ur_ingest_session_files_stats_latest.sql?stats=yes) |
[ur_ingest_session_files_stats_latest (view) Content](console/content/view/ur_ingest_session_files_stats_latest.sql?stats=yes) |
CREATE VIEW "ur_ingest_session_files_stats_latest" AS
SELECT iss.*
FROM ur_ingest_session_files_stats AS iss
JOIN ( SELECT ur_ingest_session.ur_ingest_session_id AS latest_session_id
FROM ur_ingest_session
ORDER BY ur_ingest_session.ingest_finished_at DESC
LIMIT 1) AS latest
ON iss.ingest_session_id = latest.latest_session_id |