Need Suggestion for creation of reports using vertica.logs

Hi, 

The errors that you see in the vertica.log you can see it in table error_messages, do you need more?

Eugenia

I use the system tables below to extract data into my own set of monitoring tables rather than monitor the Vertica log.

System tables that address some of your questions:

-- History of node states:

v_monitor.node_states

-- History of load events

v_internal.dc_load_events

-- Load fails (for example, joined with query_requests)

(see attachment - I copied a system view to do this)

-- Also take a look at:

dc_load_events

-- good refs for other ways of catpuring load errors:

https://my.vertica.com/docs/7.1.x/HTML/Content/Authoring/AdministratorsGuide/BulkLoadCOPY/CapturingLoadExceptionsAndRejections.htm

-- Monitor the amount of free disk space:

select min(pct_free) from
(select time::date as date
, current_database() as database
, node_name
, path
, device
, filesystem
, (used_bytes_end_value/(1024^3))::int as used_gb
, (free_bytes_end_value/(1024^3))::int as free_gb
, ((free_bytes_end_value + used_bytes_end_value) /(1024^3))::int as total
, (ROUND(free_bytes_end_value / (free_bytes_end_value + used_bytes_end_value) * 100)::int)::int as pct_free
from dc_storage_info_by_day
-- ***** Next line, replace my device & path values with your own *****
where (device = '/dev/md128' and path = '/vertica/data') -- md127 is the EC2 device.
and time::date = (select max(time::date) from dc_storage_info_by_day)
order by time desc, node_name
)X
where pct_free < 40 ;

-- My favorite Vertica ref - lots of good system tables here: 

http://vertica.tips/2014/06/02/under-the-hood-system-tables-and-views/

These solutions apply to  v7.1.2-1

Make sure you consult the docs for your own version.

Hope this Helps!

Let me know if you have questions.

 - Victor

Thanks Victor for your suggestion , i am working on this thing will come with more question when i will face problems

-- Monitor the amount of free disk space:

select min(pct_free) from
(select time::date as date
, current_database() as database
, node_name
, path
, device
, filesystem
, (used_bytes_end_value/(1024^3))::int as used_gb
, (free_bytes_end_value/(1024^3))::int as free_gb
, ((free_bytes_end_value + used_bytes_end_value) /(1024^3))::int as total
, (ROUND(free_bytes_end_value / (free_bytes_end_value + used_bytes_end_value) * 100)::int)::int as pct_free
from dc_storage_info_by_day
-- ***** Next line, replace my device & path values with your own *****
where (device = '/dev/md128' and path = '/vertica/data') -- md127 is the EC2 device.
and time::date = (select max(time::date) from dc_storage_info_by_day)
order by time desc, node_name
)X
where pct_free < 40 ;
Friv2
Friv7
Friv8
Friv9