SQL Server timeline reports

An example of a timeline report is as follows.

Example


select * from (
	select
		'Data File - ' || p1.LOGICAL_file name as "Description",
		p1.PHYSICAL_file name as "File_Name",
		p1.PHYSICAL_file name as "Full_Path",
		p1.FILE_SIZE as "File_Size",
		TO_CHAR(p1.COLLECTION_TIME, 'YYYY-MM-DD"T"HH24:MI:SS') COLLECTION_TIME,
		p2.FILE_SIZE as "File_Size_1",
		p1.CHANGE_SINCE_PREVIOUS
		from
			mssql_db_file_deltas p1
			left join mssql_db_file p2
				on p1.PREVIOUS_COLLECTION_ID = p2.COLLECTION_ID
		where
			p1.clarity_object_id = __ENTITY_ID__
			and	p1.clarity_manager_id = '__MGR_ID__'
			and
			p1.file_type = 'ROWS'
			and
			p1.collection_time between
				to_date('__COLLECTION_START_TIME__', 'YYYY-MM-DD HH24:MI:SS')
				and
				to_date('__COLLECTION_END_TIME__', 'YYYY-MM-DD HH24:MI:SS')
	union all
	select
		'Log File - ' || p1.LOGICAL_file name as "Description",
		p1.PHYSICAL_file name as "File_Name",
		p1.PHYSICAL_file name as "Full_Path",
		p1.FILE_SIZE as "File_Size",
		TO_CHAR(p1.COLLECTION_TIME, 'YYYY-MM-DD"T"HH24:MI:SS') COLLECTION_TIME,
		p2.FILE_SIZE as "File_Size_1",
		p1.CHANGE_SINCE_PREVIOUS
		from
			mssql_db_file_deltas p1
			left join mssql_db_file p2
				on p1.PREVIOUS_COLLECTION_ID = p2.COLLECTION_ID
		where
			p1.clarity_object_id = __ENTITY_ID__
			and	p1.clarity_manager_id = '__MGR_ID__'
			and
			p1.file_type = 'LOG'
			and
			p1.collection_time between
				to_date('__COLLECTION_START_TIME__', 'YYYY-MM-DD HH24:MI:SS')
				and
				to_date('__COLLECTION_END_TIME__', 'YYYY-MM-DD HH24:MI:SS')
)
order by
	COLLECTION_TIME asc
Was this page helpful? Yes No Submitting... Thank you

Comments