root/sql/sprocs/stratcon.metric_name_summary_compile_fts_data.sql

Revision 3d20bc1379d600f43cfd1385da58f12c1f448d15, 2.0 kB (checked in by Theo Schlossnagle <jesus@omniti.com>, 4 years ago)

fix the fetch of the check to be the most recent

  • Property mode set to 100644
Line 
1 set search_path = noit,pg_catalog;
2
3 CREATE OR REPLACE FUNCTION stratcon.metric_name_summary_compile_fts_data
4 (in_sid integer, in_metric_name text, in_metric_type text)
5 RETURNS tsvector AS
6 $BODY$
7 DECLARE
8     ref_sid integer;
9     ref_module text;
10     ref_name text;
11     ref_target text;
12     ref_tags text;
13     ref_ctags text;
14     ref_hostname text;
15     ref_metric_name text;
16     ref_alias text;
17     v_fts_data tsvector;
18 BEGIN
19     SELECT sid, module, name, target FROM check_archive WHERE sid=in_sid
20         ORDER BY whence DESC LIMIT 1 INTO ref_sid,ref_module,ref_name,ref_target;
21     IF NOT FOUND THEN
22         RETURN NULL;
23     END IF;
24
25     SELECT COALESCE(array_to_string(tags_array, ' '), ' ') FROM metric_tag WHERE sid=in_sid and metric_name=in_metric_name INTO ref_tags;
26     IF NOT FOUND THEN
27         ref_tags := ' ';
28     END IF;
29
30     SELECT COALESCE(array_to_string(tags_array, ' '), ' ') FROM check_tag WHERE sid=in_sid INTO ref_ctags;
31     IF NOT FOUND THEN
32         ref_ctags := ' ';
33     END IF;
34
35     SELECT value FROM metric_text_currently mt JOIN check_archive s USING(sid)
36         WHERE module='dns' AND s.name='in-addr.arpa' AND target = ref_target INTO ref_hostname;
37      
38     SELECT mt.value FROM metric_text_currently mt JOIN check_archive s USING(sid)
39         WHERE s.module='snmp' AND mt.name='alias' AND s.sid=in_sid INTO ref_alias;
40
41     ref_hostname := coalesce(replace(ref_hostname, '.', ' '), ' ');
42     ref_metric_name := regexp_replace(in_metric_name, E'[_\`/.\\134]', ' ', 'g');
43     ref_alias := coalesce(regexp_replace(ref_alias, E'[_\`/.\\134]', ' ', 'g'), ' ');
44
45     v_fts_data := to_tsvector(ref_metric_name || ' ' ||
46                                 ref_module || ' ' ||
47                                 ref_name || ' ' ||
48                                 ref_target || ' ' ||
49                                 ref_hostname || ' ' ||
50                                 ref_alias || ' ' ||
51                                 ref_tags || ' ' ||
52                                 ref_ctags);
53     RETURN v_fts_data;
54 END
55 $BODY$
56 LANGUAGE 'plpgsql'
57 SECURITY DEFINER;
58  
Note: See TracBrowser for help on using the browser.