Ad Widget

**Kos** · 03-09-2020, 11:00

Originally posted by Vanchez

Куда копнуть что почитать?

Почитать про зависимые элементы данных и препроцессинг.
Ещё можно почитать про формат JSON (хотя бы на уровне Википедии) и подогнать процитированное Вами значение под корректный JSON - тогда извлекать из него значения с помощью препроцессинга будет проще (для этого нужно убрать пробелы в числовых значениях queryid и total_rows, а так же лишнюю закрывающую фигурную скобку в конце).

**Vanchez** · 04-09-2020, 09:45

Кое что поправил в самом запросе.

Code:

[root@db postgresql]$ cat pgsql.qslow.sql
WITH T AS
(SELECT userid, dbid, queryid, query, calls, round(total_time) as total_time, rows as total_rows, round(total_time/calls) as average_time
FROM pg_stat_statements
ORDER BY average_time
DESC LIMIT 5)
SELECT row_to_json(T)
FROM T

В заббикс теперь приходит уже интереснее и вроде бы понятнее

Code:

{"userid":"16384","dbid":"1848551","queryid":10570 54500,"query":"SELECT DISTINCT doc.*,\r\n\t\t substring(doc.\"MEDO_DETAIL\" FROM 'POSTALADDRESS=(.*?)\\|') as \"MEDO_ADDRESS\", \r\n\t\t medo_country.name as \"MEDO_COUNTRY\", \r\n\t\t doc_movement.\"PPID\" as \"DOCMOVEMENT_PPID\",\r\n\t\t country.name AS \"COUNTRY\" ,\r\n\t\t citizen.firstname AS \"FIRSTNAME\" ,\r\n\t\t citizen.midname AS \"MIDNAME\" ,\r\n\t\t citizen.lastname AS \"LASTNAME\" ,\r\n\t\t pp_address.\"address\" AS \"ADDRESS\" ,\r\n\t\t ship.name AS \"CITIZENSHIP_NAME\" ,\r\n\t\t files.\"filename\" AS \"ATTACH_COUNT\" ,\r\n\t\t correspondence.name AS \"CORRESPONDENCE_NAME\" ,\r\n\t\t ppdv.name AS \"NAME_DICTIONARY\" ,\r\n\t\t pap.\"PPID\" AS \"PPID_APPELANT\" \r\n FROM ( \r\n SELECT \"PPID\", \"PPID_question\", \"PPID_appelant\", \"MEDO_detail\" as \"MEDO_DETAIL\", \"regnum\" as \"REGNUM\" , \"docnum_proxy_correspondence\" as \"DOCNUM_PROXY_CORRESPONDENCE\" , \"PPID_recordbook\" as \"PPID_RECORDBOOK\" , \"citizen_fullname\" as \"CITIZEN_FULLNAME\", \"dt_doc\" as \"DT_DOC\", \"PPID_delivery\" \r\n FROM \"PA.Appeal\".\"DocAppeal\" \r\n WHERE 1=1 ) as doc \r\n LEFT JOIN \"PP\".\"PPGeo\" as medo_country ON medo_country.\"PPID\"::text = substring(doc.\"MEDO_DETAIL\" FROM 'PPID_COUNTRY=(.*?)\\|')\r\n LEFT JOIN \"PA.Appeal\".\"DocInstance\" AS doc_instance ON doc_instance.\"PPID_doc\" = doc.\"PPID\" \r\n\r\n LEFT JOIN \"PA.Appeal\".\"DocMovement\" AS doc_movement ON doc_movement.\"PPID_instance\" = doc_instance.\"PPID\" \r\n LEFT JOIN \"PA.Appeal\".\"Correspondence\" AS correspondence ON correspondence.\"PPID\" = doc_movement.\"PPID_correspondence\" \r\n LEFT JOIN \"PA.Appeal\".\"Citizen\" AS citizen ON citizen.\"PPID\" = doc.\"PPID_appelant\" \r\n LEFT JOIN \"PA.Appeal\".\"Citizenship\" AS ship ON ship.\"PPID\" = citizen.\"PPID_citizenship\" \r\n LEFT JOIN \"PP\".\"PPAddress\" AS pp_address ON pp_address.\"PPID\" = \r\n (SELECT link.\"PPID_dst\" FROM \"PP\".\"PPLink\" AS link \r\n\r\n WHERE \"PPID_src\" = doc.\"PPID_appelant\" AND link.type = 'ADDRESS' ORDER BY \"id\" DESC LIMIT 1 )\r\n LEFT JOIN \"PP\".\"PPGeo\" as country ON country.\"PPID\" = citizen.\"PPID_country\" \r\n LEFT JOIN \"PP\".\"PPFile\" AS files ON files.\"PPID\" = \r\n (SELECT \"PPID_dst\" FROM \"PP\".\"PPLink\" AS link WHERE link.\"PPID_src\" = doc.\"PPID\" AND type = 'DocAppeal_attach' ORDER BY link.id LIMIT 1 )\r\n LEFT JOIN \"PP\".\"PPDictionaryValue\" AS ppdv ON ppdv.\"PPID\" = doc.\"PPID_delivery\"\r\n LEFT JOIN \"PA.Appeal\".\"PersonalArea\" AS pap ON pap.\"PPID_appelant\" = citizen.\"PPID\"\r\n JOIN \"PA.Appeal\".\"DocMoveState\" AS m_state1 ON m_state1.\"PPID_move\" = doc_movement.\"PPID\" AND m_state1.\"PPID_state\" = 4918977 ORDER BY \"DT_DOC\" DESC, \"REGNUM\" DESC","calls":1,"total_time":302476,"total_rows":2 159,"average_time":302476}
{"userid":"16384","dbid":"15324138","queryid":6984 839778303454589,"query":"SELECT * FROM (SELECT embassy.country_name as embassy_name, embassy.\"PPID\" as \"embassy_PPID\", embassy.f_accredited, concat(wives.firstname, $1, wives.lastname) as fio, wives.gender as gender, posol_list.dt_accred as dt_accred, posol_list.dt_enter as dt_enter, posol_list.is_posol FROM \n ( SELECT person.\"PPID\", person.dt_accred, vise.dt_enter, substring(dic_value.properties from $2) as is_posol \n FROM \"PA.PFC\".\"PersonPosition\" as pp \n JOIN \"PA.PFC\".\"Person\" as person ON person.\"PPID\" = pp.\"PPID_person\" \n JOIN \"PA.PFC\".\"PersonVise\" as vise ON vise.\"PPID_person\" = person.\"PPID\" \n JOIN \"PP\".\"PPDictionaryValue\" as dic_value ON dic_value.\"PPID\" = pp.\"PPID_position\" \n AND (substring(dic_value.properties from $3) = $4 OR substring(dic_value.properties from $5) = $6 ) \n WHERE pp.\"PPID_embassy\" IN ($7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19, $20,$21,$22,$23,$24,$25,$26,$27,$28,$29,$30,$31,$3 2,$33,$34,$35,$36,$37,$38,$39,$40,$41,$42,$43,$44, $45,$46,$47,$48,$49,$50,$51,$52,$53,$54,$55,$56,$5 7,$58,$59,$60,$61,$62,$63,$64,$65,$66,$67,$68,$69, $70,$71,$72,$73,$74,$75,$76,$77,$78,$79,$80,$81,$8 2,$83,$84,$85,$86,$87,$88,$89,$90,$91,$92,$93,$94, $95,$96,$97,$98,$99,$100,$101,$102,$103,$104,$105, $106,$107,$108,$109,$110,$111,$112,$113,$114,$115, $116,$117,$118,$119,$120,$121,$122,$123,$124,$125, $126,$127,$128,$129,$130,$131,$132,$133,$134,$135, $136,$137,$138,$139,$140,$141,$142,$143,$144,$145, $146,$147,$148,$149,$150,$151,$152,$153,$154,$155, $156,$157,$158,$159,$160,$161,$162,$163,$164,$165, $166,$167,$168,$169,$170,$171,$172,$173,$174,$175, $176,$177,$178,$179,$180,$181,$182,$183,$184,$185, $186,$187,$188,$189,$190,$191,$192,$193,$194,$195, $196,$197,$198,$199,$200,$201,$202,$203,$204,$205, $206,$207,$208,$209,$210,$211,$212,$213,$214,$215, $216,$217,$218,$219,$220,$221,$222,$223,$224,$225, $226,$227,$228,$229,$230,$231,$232,$233,$234,$235, $236,$237,$238,$239,$240,$241,$242,$243,$244,$245, $246,$247,$248,$249,$250,$251) \n ) as posol_list \n JOIN \"PA.PFC\".\"Person\" as wives ON wives.\"PPID_node\" = posol_list.\"PPID\" \n AND wives.\"PPID\" IN ( \n SELECT wives_list.\"PPID\" FROM \"PA.PFC\".\"Person\" as wives_list JOIN \"PP\".\"PPObject\" as obj ON obj.\"PPID\" = wives_list.\"PPID\" AND obj.hidden = $252 WHERE wives_list.\"PPID_node\" = posol_list.\"PPID\" \n AND wives_list.\"PPID_kinship\" = $253 \n LIMIT $254) \n JOIN \"PA.PFC\".\"PersonPosition\" as wive_position ON wive_position.\"PPID_person\" = wives.\"PPID\" \n JOIN \"PA.PFC\".\"Embassy\" as embassy ON embassy.\"PPID\" = wive_position.\"PPID_embassy\" \n UNION \n SELECT embassy.country_name as embassy_name, embassy.\"PPID\" as \"embassy_PPID\", embassy.f_accredited, concat(person_female.firstname, $255, person_female.lastname) as fio, person_female.gender as gender, person_female.dt_accred as dt_accred, vise.dt_enter as dt_enter,substring(dic_value.properties from $256) as is_posol \n FROM \"PA.PFC\".\"Embassy\" as embassy \n JOIN \"PA.PFC\".\"PersonPosition\" as position ON position.\"PPID_embassy\" = embassy.\"PPID\" \n AND position.\"PPID_embassy\" IN ($257,$258,$259,$260,$261,$262,$263,$264,$265,$266 ,$267,$268,$269,$270,$271,$272,$273,$274,$275,$276 ,$277,$278,$279,$280,$281,$282,$283,$284,$285,$286 ,$287,$288,$289,$290,$291,$292,$293,$294,$295,$296 ,$297,$298,$299,$300,$301,$302,$303,$304,$305,$306 ,$307,$308,$309,$310,$311,$312,$313,$314,$315,$316 ,$317,$318,$319,$320,$321,$322,$323,$324,$325,$326 ,$327,$328,$329,$330,$331,$332,$333,$334,$335,$336 ,$337,$338,$339,$340,$341,$342,$343,$344,$345,$346 ,$347,$348,$349,$350,$351,$352,$353,$354,$355,$356 ,$357,$358,$359,$360,$361,$362,$363,$364,$365,$366 ,$367,$368,$369,$370,$371,$372,$373,$374,$375,$376 ,$377,$378,$379,$380,$381,$382,$383,$384,$385,$386 ,$387,$388,$389,$390,$391,$392,$393,$394,$395,$396 ,$397,$398,$399,$400,$401,$402,$403,$404,$405,$406 ,$407,$408,$409,$410,$411,$412,$413,$414,$415,$416 ,$417,$418,$419,$420,$421,$422,$423,$424,$425,$426 ,$427,$428,$429,$430,$431,$432,$433,$434,$435,$436 ,$437,$438,$439,$440,$441,$442,$443,$444,$445,$446 ,$447,$448,$449,$450,$451,$452,$453,$454,$455,$456 ,$457,$458,$459,$460,$461,$462,$463,$464,$465,$466 ,$467,$468,$469,$470,$471,$472,$473,$474,$475,$476 ,$477,$478,$479,$480,$481,$482,$483,$484,$485,$486 ,$487,$488,$489,$490,$491,$492,$493,$494,$495,$496 ,$497,$498,$499,$500,$501)\n JOIN \"PA.PFC\".\"Person\" as person_female ON person_female.\"PPID\" = position.\"PPID_person\" \n AND person_female.gender = $502 \n JOIN \"PP\".\"PPObject\" as obj ON obj.\"PPID\" = person_female.\"PPID\" \n AND obj.hidden = $503 \n JOIN \"PP\".\"PPDictionaryValue\" as dic_value \n ON dic_value.\"PPID\" = position.\"PPID_position\" \n AND (substring(dic_value.properties from $504) = $505 OR substring(dic_value.properties from $506) = $507 ) \n JOIN \"PA.PFC\".\"PersonVise\" as vise \n ON vise.\"PPID_person\" = person_female.\"PPID\" ) as list_person_female_and_wives \n ORDER BY \n (case when is_posol = $508 then $509 else $510 end), \n (case when f_accredited IS TRUE then $511 else $512 end), \n (case when dt_accred::date > $513::date then $514 else $515 end), dt_accred, \n (case when dt_enter::date > $516::date then $517 else $518 end), dt_enter, \n fio","calls":2,"total_time":294831,"total_rows":22 8,"average_time":147416}
{"userid":"16384","dbid":"15324138","queryid":-6274821168367978543,"query":"SELECT country.\"PPID\" as country_ppid,country.name as country_name, person.gender, \n concat(person.firstname, $1, person.lastname) AS fio, \n concat(wife.firstname, $2, wife.lastname) AS wife_fio FROM \"PA.PFC\".\"Embassy\" as embassy \n LEFT JOIN \"PA.PFC\".\"PersonPosition\" as pp on pp.\"PPID_embassy\" = embassy.\"PPID\" and pp.hidden <> $3 \n JOIN \"PA.PFC\".\"Person\" as person ON person.\"PPID\" = pp.\"PPID_person\" \n JOIN \"PP\".\"PPObject\" as obj ON obj.\"PPID\" = person.\"PPID\" AND obj.hidden = $4 \n JOIN \"PA.PFC\".\"PersonVise\" as vise ON vise.\"PPID_person\" = person.\"PPID\" \n LEFT JOIN \"PA.PFC\".\"Person\" as wife ON wife.\"PPID_node\" = person.\"PPID\" \n AND wife.\"PPID\" IN (\n SELECT wifes.\"PPID\" FROM \"PA.PFC\".\"Person\" as wifes \n JOIN \"PP\".\"PPObject\" as obj2 ON obj2.\"PPID\" = wifes.\"PPID\" AND obj2.hidden = $5 \n JOIN \"PP\".\"PPDictionaryValue\" as kin_dic ON kin_dic.\"PPID\" = wifes.\"PPID_kinship\" \n AND LOWER(kin_dic.name) IN ($6, $7) \n WHERE wifes.\"PPID_node\" = person.\"PPID\" \n LIMIT $8 \n )\n JOIN \"PP\".\"PPDictionaryValue\" as position_value ON position_value.\"PPID\" = pp.\"PPID_position\" \n AND ( substring(position_value.properties from $9) = $10 OR substring(position_value.properties from $11) = $12 ) \n JOIN \"PP\".\"PPGeo\" country ON embassy.\"PPID_country\" = country.\"PPID\" WHERE embassy.f_accredited is not false AND (person.firstname IS NOT NULL AND person.firstname != $13 OR person.lastname IS NOT NULL AND person.lastname != $14) ORDER BY (case when substring(position_value.properties from $15)::boolean = $16 then $17 else $18 end), \n (case when person.dt_accred::date > $19::date then $20 else $21 end), person.dt_accred, \n (case when vise.dt_enter::date > $22::date then $23 else $24 end), vise.dt_enter, \n fio","calls":3,"total_time":171075,"total_rows":45 6,"average_time":57025}
{"userid":"16384","dbid":"1848551","queryid":-214376894515124688,"query":"COPY \"PP\".\"PPObject\" (\"PPID\", \"GUID\", id_class, revision, state, hidden, dt_retro) TO stdout","calls":1,"total_time":16037,"total_rows": 8435628,"average_time":16037}
{"userid":"16384","dbid":"1848551","queryid":74793 71859744737650,"query":"COPY \"PP\".\"PPObjectLog\" (id, \"PPID\", ts_action, id_user, action, diff, is_clean, revision) TO stdout","calls":1,"total_time":12364,"total_rows": 232116,"average_time":12364}

Пытаюсь делать препроцессинг в таком виде

В итоге тест меня отвергает т.е. он не понимает обьект из JSON который понимает заббикс, что не так?

**Evgeniy** · 04-09-2020, 10:19

2 Поддержка JSONPath функционала

https://www.zabbix.com/documentation/4.4/ru/manual/appendix/preprocessing/jsonpath_functionality

**Vanchez** · 04-09-2020, 11:10

Пробую вытащит по другому, не вытаскиевает
cannot extract value from json by path "${"average_time"]": cannot parse as a valid JSON object: invalid character following JSON object at:

**Kos** · 04-09-2020, 12:49

Originally posted by Vanchez

Пробую вытащит по другому, не вытаскиевает
cannot extract value from json by path "${"average_time"]": cannot parse as a valid JSON object: invalid character following JSON object at:

Посмотрите, пожалуйста, повнимательнее на своё JSONPath-выражение. Я бы на месте парсера тоже не разобрал :-)

Ad Widget

Триггер по айтему.

Триггер по айтему.

Comment

Comment

Comment

Comment

Comment