Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

an alternative query #8

Closed
r2evans opened this issue Jan 22, 2024 · 5 comments
Closed

an alternative query #8

r2evans opened this issue Jan 22, 2024 · 5 comments
Assignees
@WAdama
Labels
enhancement New feature or request

Comments

@r2evans
Copy link

r2evans commented Jan 22, 2024

This doesn't produce XML, but I was inspired by your repo to form my own single-query for much of the same information. When connected to the config.db file, we can attach the activity.db (assuming current-directory here) to get to the other tables in the same command.

This saves repeated calls to sqlite3, and while repeated calls is likely performant enough, I thought it might be good (and/or useful) to unify it into a single select statement.

.mode table
attach database 'activity.db' as actdb;
with cte as (
  select dt.login_user, dt.host_name, -- rt.backup_type, rt.task_config,
    -- rt.time_start, rt.time_end,
    datetime(rt.time_start, 'unixepoch', 'localtime') as time_start,
    datetime(rt.time_end, 'unixepoch', 'localtime') as time_end,
    (case when rt.time_end is null then ''
          else format('%02.1fh', (rt.time_end - rt.time_start) / 3600.0)
          end) as hours,
    (case when drt.transfered_bytes > 1e12 then (round(drt.transfered_bytes / 1e12, 2) || ' TB')
          when drt.transfered_bytes > 1e9 then (round(drt.transfered_bytes / 1e9, 2) || ' GB')
          when drt.transfered_bytes > 1e6 then (round(drt.transfered_bytes / 1e6, 2) || ' MB')
          when drt.transfered_bytes > 1e3 then (round(drt.transfered_bytes / 1e3, 2) || ' KB')
     end) as transferred,
    json_extract(rdt.other_params, '$.speed') as speed,
    -- rt.result_id,
    (case when lt.log_type=1101 then 'started'
          when lt.log_type=1102 then 'completed'
          when lt.log_type=1115 then 'trigger screenlock'
          when lt.log_type=1125 then 'timeout'
          when lt.log_type=1137 then 'being processed'
          when lt.log_type=1146 then 'missed'
          when lt.log_type=1325 then 'deletion succeeded'
          when lt.log_type=1327 then 'starting to delete'
          else 'unk' end) as result,
    dt.device_id, rt.result_id,
    rank() over (partition by dt.device_id order by rt.time_end desc) as rn
  from device_table dt
    left join backup_task_device btd on dt.device_id = btd.device_id
    left join task_table tt on tt.task_id = btd.task_id
    left join result_table rt on rt.task_id = btd.task_id
      and rt.task_config like '%\"device_id\":' || dt.device_id || ',%'
    left join device_result_table drt on rt.result_id = drt.result_id
    left join result_detail_table rdt on rt.result_id = rdt.result_id
    left join log_table lt on rt.result_id = lt.result_id
  where (rt.job_action is null or rt.job_action = 1)
    and (rdt.log_type is null or rdt.log_type = '1111')
)
select * from cte where rn <= 3
order by device_id, rn

Output (de-PII-ized):

+------------+-----------------+---------------------+---------------------+-------+-------------+-------------+-----------+-----------+-----------+----+
| login_user |    host_name    |     time_start      |      time_end       | hours | transferred |    speed    |  result   | device_id | result_id | rn |
+------------+-----------------+---------------------+---------------------+-------+-------------+-------------+-----------+-----------+-----------+----+
| sarah      | host1           |                     |                     |       |             |             | unk       | 3         |           | 1  |
| sarah      | otherhost       | 2024-01-18 08:28:33 | 2024-01-18 08:34:33 | 0.1h  | 8.24 GB     | 21.00 MB/s  | completed | 4         | 1391      | 1  |
| sarah      | otherhost       | 2024-01-09 07:51:21 | 2024-01-09 07:55:04 | 0.1h  | 4.76 GB     | 20.00 MB/s  | completed | 4         | 1372      | 2  |
| sarah      | otherhost       | 2023-12-28 15:23:10 | 2023-12-28 15:26:35 | 0.1h  | 3.83 GB     | 17.00 MB/s  | completed | 4         | 1325      | 3  |
| jack       | jacks_desktop   | 2024-01-21 04:00:02 | 2024-01-21 06:41:36 | 2.7h  | 108.96 GB   | 10.00 MB/s  | completed | 5         | 1404      | 1  |
| jack       | jacks_desktop   | 2024-01-20 04:00:03 | 2024-01-20 04:06:44 | 0.1h  | 4.99 GB     | 11.00 MB/s  | completed | 5         | 1400      | 2  |
| jack       | jacks_desktop   | 2024-01-19 04:00:02 | 2024-01-19 04:02:30 | 0.0h  | 2.09 GB     | 13.00 MB/s  | completed | 5         | 1398      | 3  |
| sarah      | host2           | 2024-01-13 15:24:34 | 2024-01-13 15:35:42 | 0.2h  | 10.09 GB    | 14.00 MB/s  | completed | 6         | 1382      | 1  |
| sarah      | host2           | 2023-12-31 10:53:09 | 2023-12-31 11:06:04 | 0.2h  | 14.82 GB    | 18.00 MB/s  | completed | 6         | 1337      | 2  |
| bob        | old_laptop      |                     |                     |       |             |             | unk       | 7         |           | 1  |
| bob        | current_laptop  | 2024-01-22 03:29:42 | 2024-01-22 08:59:56 | 5.5h  | 4.0 GB      | 197.00 KB/s | completed | 8         | 1406      | 1  |
| bob        | current_laptop  | 2024-01-20 16:43:22 | 2024-01-20 18:39:34 | 1.9h  | 78.86 GB    | 10.00 MB/s  | completed | 8         | 1401      | 2  |
| bob        | current_laptop  | 2024-01-10 21:16:17 | 2024-01-13 21:44:27 | 72.5h | 3.3 GB      | 12.00 KB/s  | timeout   | 8         | 1376      | 3  |
| sarah      | host3           |                     |                     |       |             |             | unk       | 9         |           | 1  |
+------------+-----------------+---------------------+---------------------+-------+-------------+-------------+-----------+-----------+-----------+----+

While my use is for a simple ssh console output, the method could easily be adapted to produce XML instead of the above tabular format.

  • This adds details like "timeout" or other failures backup-attempt failures. I did not look deeply enough to see how the rdt.log_type='1111' relates to the lt.log_type formats I added here; most of that case when on lt.log_type may never be used, but I keep it as built-in documentation. (I found the meanings by comparing the timestamps with my Syno ABB log output. Imperfect.)
  • I do the json parsing inline, no need for external utilities (though I do like jq).
  • I made the rt.task_config conditional a bit more specific, guarding against false-joins on pattern matching.
  • The rn is simply to return the last few rows for each device_id, I wanted a simple history/trend.
  • The use of left join versus inner join gives us the empty rows where ABB is showing "nothing recent". (ABB Portal may still offer restoration options for those.)

I started mapping out the databases so that I could better understand the joins.

image

This is far from complete and would benefit from completing the links of foreign keys (and it does not attempt to differentiate between config.db-vs-activity.db, they appear to be "all the same db" after the attach database... above.

This can be "played with" using https://sql.toad.cz, clicking on "Save / Load", pasting the below text into the "Input/Output" text-input, then clicking "LOAD XML".

schema xml 
<?xml version="1.0" encoding="utf-8" ?>
<!-- SQL XML created by WWW SQL Designer, https://github.com/ondras/wwwsqldesigner/ -->
<!-- Active URL: https://sql.toad.cz/?keyword=online_library -->
<sql>
<datatypes db="mysql">
	<group label="Numeric" color="rgb(238,238,170)">
		<type label="Integer" length="0" sql="INTEGER" quote=""/>
	 	<type label="TINYINT" length="0" sql="TINYINT" quote=""/>
	 	<type label="SMALLINT" length="0" sql="SMALLINT" quote=""/>
	 	<type label="MEDIUMINT" length="0" sql="MEDIUMINT" quote=""/>
	 	<type label="INT" length="0" sql="INT" quote=""/>
		<type label="BIGINT" length="0" sql="BIGINT" quote=""/>
		<type label="Decimal" length="1" sql="DECIMAL" re="DEC" quote=""/>
		<type label="Single precision" length="0" sql="FLOAT" quote=""/>
		<type label="Double precision" length="0" sql="DOUBLE" re="DOUBLE" quote=""/>
	</group>

	<group label="Character" color="rgb(255,200,200)">
		<type label="Char" length="1" sql="CHAR" quote="'"/>
		<type label="Varchar" length="1" sql="VARCHAR" quote="'"/>
		<type label="Text" length="0" sql="MEDIUMTEXT" re="TEXT" quote="'"/>
		<type label="Binary" length="1" sql="BINARY" quote="'"/>
		<type label="Varbinary" length="1" sql="VARBINARY" quote="'"/>
		<type label="BLOB" length="0" sql="BLOB" re="BLOB" quote="'"/>
	</group>

	<group label="Date &amp; Time" color="rgb(200,255,200)">
		<type label="Date" length="0" sql="DATE" quote="'"/>
		<type label="Time" length="0" sql="TIME" quote="'"/>
		<type label="Datetime" length="0" sql="DATETIME" quote="'"/>
		<type label="Year" length="0" sql="YEAR" quote=""/>
		<type label="Timestamp" length="0" sql="TIMESTAMP" quote="'"/>
	</group>
	
	<group label="Miscellaneous" color="rgb(200,200,255)">
		<type label="ENUM" length="1" sql="ENUM" quote=""/>
		<type label="SET" length="1" sql="SET" quote=""/>
		<type label="Bit" length="0" sql="bit" quote=""/>
	</group>
</datatypes><table x="1209" y="582" name="info_table">
<row name="id" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="info" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
<row name="value" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
</table>
<table x="1150" y="855" name="general_setting_table">
<row name="id" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="name" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
<row name="value" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
</table>
<table x="213" y="109" name="task_table">
<row name="task_id" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="storage_id" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="task_name" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
<row name="backup_type" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="source_type" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="custom_volume" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
<row name="sched_id" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="sched_content" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
<row name="target_dir" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
<row name="dedup_path" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
<row name="unikey" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
<row name="retention_policy" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
<row name="enable_dedup" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="enable_notify" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="cbt_enable_mode" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="bandwidth" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="connection_timeout" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="max_concurrent_devices" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="enable_verification" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="verification_policy" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
<row name="enable_app_aware_bkp" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="enable_encrypt_transfer" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="enable_compress_transfer" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="agentless_backup_policy" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="agentless_enable_dedup" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="agentless_enable_windows_vss" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="agentless_enable_block_transfer" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="agentless_backup_path" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
<row name="enable_datastore_aware" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="datastore_reserved_percentage" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="backup_external" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="pre_post_script_setting" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
<row name="backup_cache_content" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
<row name="bandwidth_content" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
<row name="enable_shutdown_after_complete" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="enable_windows_working_state" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="enable_wake_up" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="sched_modify_time" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="dedup_api_restore" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="view_type" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
</table>
<table x="18" y="13" name="device_table">
<row name="device_id" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="device_uuid" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
<row name="create_time" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="backup_type" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="host_name" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
<row name="host_ip" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
<row name="host_port" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="os_name" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
<row name="login_user" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
<row name="login_user_id" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="login_password" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
<row name="login_time" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="agent_token" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
<row name="inventory_id" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="vm_moid_path" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
<row name="agentless_auth_policy" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="hypervisor_id" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
<row name="dsm_unique" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
<row name="dsm_model" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
<row name="auto_discovery" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
</table>
<table x="1288" y="918" name="storage_table">
<row name="storage_id" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="volume_path" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
<row name="share_name" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
<row name="repo_dir" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
<row name="fs_type" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="automount_location" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
<row name="automount_iv" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
</table>
<table x="1226" y="889" name="inventory_table">
<row name="inventory_id" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="inventory_uuid" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
<row name="host_type" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="host_name" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
<row name="host_addr" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
<row name="port_webapi" null="0" autoincrement="0">
<datatype>INT</datatype>
</row>
<row name="auth_user" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
<row name="auth_password" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
<row name="auth_thumbprint" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
<row name="nfc_host_port" null="0" autoincrement="0">
<datatype>INT</datatype>
</row>
<row name="status" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
<row name="nfc_nas_addr" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
<row name="nfc_nas_port" null="0" autoincrement="0">
<datatype>INT</datatype>
</row>
<row name="protocol" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
<row name="other_spec" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
</table>
<table x="1300" y="655" name="available_task_device">
<row name="task_id" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
<relation table="task_table" row="task_id" />
</row>
<row name="device_id" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
<relation table="device_table" row="device_id" />
</row>
</table>
<table x="205" y="21" name="backup_task_device">
<row name="task_id" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
<relation table="task_table" row="task_id" />
</row>
<row name="device_id" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
<relation table="device_table" row="device_id" />
</row>
<key type="PRIMARY" name="">
</key>
</table>
<table x="1348" y="703" name="backup_task_user">
<row name="task_id" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
<relation table="task_table" row="task_id" />
</row>
<row name="dsm_uid" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
</table>
<table x="1231" y="622" name="backup_task_group">
<row name="task_id" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="dsm_gid" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
</table>
<table x="1317" y="329" name="vm_task_script_table">
<row name="id" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="task_id" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
<relation table="task_table" row="task_id" />
</row>
<row name="device_id" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
<relation table="device_table" row="device_id" />
</row>
<row name="vm_moid_path" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
<row name="enabled" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="script_exec_mode" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="script_os_type" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="pre_script_path" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
<row name="post_script_path" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
</table>
<table x="1418" y="737" name="agent_version_table">
<row name="device_id" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
<relation table="device_table" row="device_id" />
</row>
<row name="major" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="minor" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="mini" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="build" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
</table>
<table x="1456" y="775" name="agent_token_dirty_device">
<row name="device_id" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
<relation table="device_table" row="device_id" />
</row>
</table>
<table x="1064" y="820" name="delegation_role_table">
<row name="dummy_key" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="id" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
<row name="id_type" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
<row name="auth_type" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
<row name="name" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
<row name="role_backup" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="role_restore" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="role_delete" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="role_admin" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
</table>
<table x="1275" y="281" name="vm_task_folder_table">
<row name="dummy_key" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="task_id" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
<relation table="task_table" row="task_id" />
</row>
<row name="inventory_id" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="folder_id" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
<row name="folder_type" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
<row name="exclusive_folder_id" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
<row name="exclusive_vm_id" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
<row name="view_type" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
</table>
<table x="786" y="1201" name="info_table">
<row name="id" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="info" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
<row name="value" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
</table>
<table x="837" y="681" name="log_table">
<row name="log_id" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="log_level" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="log_type" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="log_time" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="error_code" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="user_id" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="user_name" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
<row name="task_id" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
<relation table="task_table" row="task_id" />
</row>
<row name="task_name" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
<row name="backup_type" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="device_id" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
<relation table="device_table" row="device_id" />
</row>
<row name="device_name" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
<row name="other_params" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
<row name="result_id" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
<relation table="result_table" row="result_id" />
</row>
</table>
<table x="469" y="328" name="result_table">
<row name="result_id" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="status" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="task_id" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
<relation table="task_table" row="task_id" />
</row>
<row name="task_name" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
<row name="backup_type" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="time_start" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="time_end" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="detail_path" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
<row name="task_config" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
<row name="success_count" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="warning_count" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="error_count" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="none_count" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="job_action" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
</table>
<table x="640" y="561" name="device_result_table">
<row name="device_result_id" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="result_id" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
<relation table="result_table" row="result_id" />
</row>
<row name="config_device_id" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="status" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="device_name" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
<row name="time_start" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="time_end" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="transfered_bytes" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="processed_bytes" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
</table>
<table x="656" y="350" name="result_detail_table">
<row name="result_detail_id" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="log_level" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="log_type" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="log_time" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="other_params" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
<row name="error_code" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="result_id" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
<relation table="result_table" row="result_id" />
</row>
</table>
<table x="687" y="1168" name="storage_log_table">
<row name="storage_log_id" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="volume_name" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
<row name="share_name" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
<row name="log_time" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="usage_size" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="volume_free_space" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
</table>
<table x="667" y="1114" name="report_table">
<row name="report_id" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="report_time" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="report_interval" null="0" autoincrement="0">
<datatype>INTEGER</datatype>
</row>
<row name="uri_suffix" null="0" autoincrement="0">
<datatype>MEDIUMTEXT</datatype>
</row>
</table>
</sql>

Offered solely as a "hope it helps".
@WAdama WAdama added the enhancement New feature or request label Jan 22, 2024
@WAdama WAdama self-assigned this Jan 22, 2024
@WAdama
Copy link
Owner

WAdama commented Jan 22, 2024 •
edited
Loading

Hi @r2evans

This looks very interesting, of course this helps. Databases are not so much my expertise, so every input is appreciated, thanks for that.

I try to use it and to better my script.

Is this started in sqlite3 or part of script?

Regards
Ingo

@r2evans
Copy link
Author

r2evans commented Jan 24, 2024

I wanted to remind you that sqlite3 takes a .mode argument that changes its output format, making it much easier to use. For instance, while it doesn't support XML, it does support json, which is very easy to consume in most languages:

[{"login_user":"bob","host_name":"host1","time_start":null,"time_end":null,"hours":"","transferred":null,"speed":null,"result":"unk","device_id":3,"result_id":null,"rn":1},
{"login_user":"bob","host_name":"host2","time_start":"2024-01-23 09:40:18","time_end":"2024-01-23 09:46:44","hours":"0.1h","transferred":"12.42 GB","speed":"30.00 MB/s","result":"completed","device_id":4,"result_id":1412,"rn":1},
{"login_user":"jack","host_name":"somewhere","time_start":"2024-01-24 04:03:56","time_end":"2024-01-24 04:05:47","hours":"0.0h","transferred":"1.55 GB","speed":"13.00 MB/s","result":"completed","device_id":5,"result_id":1415,"rn":1},
{"login_user":"bob","host_name":"host3","time_start":"2024-01-13 15:24:34","time_end":"2024-01-13 15:35:42","hours":"0.2h","transferred":"10.09 GB","speed":"14.00 MB/s","result":"completed","device_id":6,"result_id":1382,"rn":1},
{"login_user":"sarah","host_name":"otherhost","time_start":null,"time_end":null,"hours":"","transferred":null,"speed":null,"result":"unk","device_id":7,"result_id":null,"rn":1},
{"login_user":"sarah","host_name":"somehost","time_start":"2024-01-23 08:43:39","time_end":"2024-01-23 08:55:05","hours":"0.2h","transferred":"4.95 GB","speed":"6.00 MB/s","result":"completed","device_id":8,"result_id":1411,"rn":1},
{"login_user":"bob","host_name":"host1","time_start":null,"time_end":null,"hours":"","transferred":null,"speed":null,"result":"unk","device_id":9,"result_id":null,"rn":1}]

It's true json (a complete list), not ndjson (newline-delimited json).

@WAdama
Copy link
Owner

WAdama commented Jan 29, 2024

Again, thanks for your input...

Already played with it. But it will be a little time for me to get it running, but I'm already working on it...

@WAdama
Copy link
Owner

WAdama commented Feb 27, 2024

In work

@WAdama WAdama closed this as completed Feb 27, 2024
@WAdama
Copy link
Owner

WAdama commented Mar 11, 2024

Hi,

thanks again for your help.

I've just published my new PRTG script. I could also used most of your query.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@WAdama WAdama

Labels
enhancement New feature or request
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@r2evans @WAdama