Easyvista : Différence entre versions
Sauter à la navigation
Sauter à la recherche
| Ligne 34 : | Ligne 34 : | ||
15 Détruit | 15 Détruit | ||
17 En construction | 17 En construction | ||
| + | |||
| + | == critical level == | ||
| + | {| class="wikitable" | ||
| + | |- | ||
| + | ! Critical_LEVEL !! CRITICAL_LEVEL_FR | ||
| + | |- | ||
| + | | 2 || 2 - Moyenne | ||
| + | |- | ||
| + | | 1 || 1 - Basse | ||
| + | |- | ||
| + | | 0 || NULL | ||
| + | |- | ||
| + | | 3 || 3 - Haute | ||
| + | |} | ||
==Am_reference table des types de lines== | ==Am_reference table des types de lines== | ||
Version du 29 août 2016 à 08:30
Sommaire
- 1 Requête pour Easyvista
- 1.1 Afficher les requêtes
- 1.2 Clear cache
- 1.3 Info de status EZV
- 1.4 critical level
- 1.5 Am_reference table des types de lines
- 1.6 Changement status en supprimé
- 1.7 Changement nature
- 1.8 Changement nature et arg1
- 1.9 Changement ARG (e_supervision_X champ argEZV)
- 1.10 Changement état en téléexploite
- 1.11 Ajout Commentaire par CI
- 1.12 Changement status
- 1.13 Changement Status avec Commentaire
- 1.14 Copier le champ CI dans Nom HW si commence par S mais pas par SW
- 1.15 Changement type colonne:
- 1.16 Select des hard et host
- 1.17 Select Hardware par position et status
- 1.18 Select Application Installé sur quel Host
- 1.19 Dif crititité
- 1.20 Serveur env horaire sup
- 1.21 Appli sur host
- 1.22 Nouveau numero de CI
- 1.23 Changement responsable CI
- 1.24 Liste application/serveur
Requête pour Easyvista
Afficher les requêtes
&showstatement=yes
Clear cache
https://servicedesk/clear_cache.php
Info de status EZV
Table AM.status
Status ID Status FR ____________________________________________________________________ 31 Retour Atelier 32 En quarantaine 39 Dépollué par 3E 30 En maintenance 33 Manquant 34 Restitué au fournisseur 38 Post Migration 29 En commande 28 En activité - Nagios Préprod 22 En activité - Non Supervisé 26 Inactif 1 En panne 2 En réparation 3 Prêté 4 En stock 5 Mis à jour 6 Retourné 7 Supprimé 8 En activité 9 Volé 10 En cours de transfert 11 A vérifier 12 A restituer 13 Doublon 15 Détruit 17 En construction
critical level
| Critical_LEVEL | CRITICAL_LEVEL_FR |
|---|---|
| 2 | 2 - Moyenne |
| 1 | 1 - Basse |
| 0 | NULL |
| 3 | 3 - Haute |
Am_reference table des types de lines
select * from AM_REFERENCE where REFERENCE_FR like '%Hébergé sur%'
| REFERENCE_ID | REFERENCE_FR |
|---|---|
| 244 | Appartient à -> |
| 245 | Hébergé sur < |
| 246 | Installé sur <- |
| 247 | Interface -> |
| 248 | Intervient -> |
| 249 | Produit <- |
| 257 | Est sauvegardé par -> |
Changement status en supprimé
Update am_asset set status_id = 7 where asset_tag='N°CI';
Changement nature
Update am_asset set e_nature_obj_prod='NATURE', last_update=sysdate where asset_tag='N°CI'
Changement nature et arg1
Update am_asset set e_nature_obj_prod='NATURE', e_supervision_1='VALEUR' where asset_tag='N°CI';
Changement ARG (e_supervision_X champ argEZV)
Update am_asset set e_supervision_2='VALEUR', e_supervision_3='VALEUR', last_update=sysdate where asset_tag='N°CI';
Changement état en téléexploite
Update am_asset set e_teleexploit='1', last_update=sysdate where asset_tag='N°CI';
Ajout Commentaire par CI
update am_asset set COMMENT_ASSET='centreon', last_update=sysdate where asset_tag='N°CI'
Changement status
Update am_asset set status_id = 'Status ID', last_update=sysdate where asset_tag='N°CI';
Changement Status avec Commentaire
Update am_asset set status_id = 26, COMMENT_ASSET='Commantaire', last_update=sysdate where asset_tag='N°CI';
Copier le champ CI dans Nom HW si commence par S mais pas par SW
update am_asset set network_identifier=asset_tag where asset_tag like 'S%' and asset_tag not like 'SW%';
Changement type colonne:
alter table am_asset modify (ban_1 varchar2(20), ban_2 varchar2(20), ban_3 varchar2(20), ban_4 varchar2(20), ban_5 varchar2(20), ban_6 varchar2(20), ban_7 varchar2(20), ban_8 varchar2(20));
Select des hard et host
select hard.asset_tag HARDWARE, hard.status_id STATUS_HARD, toto.child_ci_id , serveur.network_identifier SERVEUR, serveur.status_id STATUS_SERVER from am_asset hard left outer join configuration_item_link toto on hard.asset_id=toto.parent_ci_id left outer join am_asset serveur on toto.child_ci_id=serveur.asset_id where hard.status_id not like '7' and hard.asset_tag like 'HW%' order by 1 desc ;
Ou
select hard.asset_tag HARDWARE, serveur.network_identifier SERVEUR from am_asset hard left outer join configuration_item_link toto on hard.asset_id=toto.parent_ci_id left outer join am_asset serveur on toto.child_ci_id=serveur.asset_id where hard.asset_tag like 'HW%' or hard.asset_tag like 'AN%' order by 1 desc ;
Select Hardware par position et status
select A.ASSET_TAG, A.E_NUM_BAIE, A.E_NUM_BAIE_U, S.STATUS_FR from am_asset A left outer join AM_STATUS S on A.status_ID=S.STATUS_ID where A.asset_tag like 'HW%' order by A.E_NUM_BAIE desc;
Select Application Installé sur quel Host
select A.asset_tag CI, A.network_identifier APPLICATION_INSTALLE, serveur.network_identifier SERVEUR from am_asset A left outer join configuration_item_link toto on A.asset_id=toto.CHILD_CI_ID left outer join am_asset serveur on toto.PARENT_CI_ID=serveur.asset_id where A.network_identifier like 'JBOSS%' and A.ASSET_TAG like 'LG10%' and serveur.ASSET_TAG like 'LG50%' and serveur.STATUS_ID not like'7';
Dif crititité
SELECT concat
(server.network_identifier , '_' , server.asset_tag , ' : ' , ISNULL(server.CRITICAL_LEVEL_ID, '10') , case server.CRITICAL_LEVEL_ID when 6 then 'Haute'
when 5 then 'NULL'
when 2 then 'Basse'
when 1 then 'Moyenne'
else 'Erreur'
end , appli.network_identifier , '_' + appli.asset_tag , ' : ' , ISNULL(appli.CRITICAL_LEVEL_ID, '10') , case appli.CRITICAL_LEVEL_ID when 6 then 'Haute'
when 5 then 'NULL'
when 2 then 'Basse'
when 1 then 'Moyenne'
else 'Erreur'
end , am_asset.network_identifier , '_' + am_asset.asset_tag , ' : ' ,ISNULL(am_asset.CRITICAL_LEVEL_ID, '10') , case am_asset.CRITICAL_LEVEL_ID when 6 then 'Haute'
when 5 then 'NULL'
when 2 then 'Basse'
when 1 then 'Moyenne'
else 'Erreur'
end )
as c1
FROM am_asset am_asset INNER JOIN am_catalog
ON am_asset.catalog_id = am_catalog.catalog_id
INNER JOINam_un_classification
ONam_catalog.un_classification_id =
am_un_classification.un_classification_id
INNER JOIN configuration_item_link
ONam_asset.asset_id = configuration_item_link.parent_ci_id
INNER JOIN am_assetappli
ONconfiguration_item_link.child_ci_id = appli.asset_id
INNER JOINconfiguration_item_link cit2
ON appli.asset_id =cit2.child_ci_id
INNER JOIN am_assetserver ON cit2.parent_ci_id = server.asset_id
inner joinam_status status_appli on status_appli.status_id=appli.status_id
inner joinam_status status_server on status_server.status_id=server.status_id
inner joinam_status oi_status on oi_status.status_id=am_asset.status_id
left outer join[EVO_DATA50004].[50004].tmp_nature_corres on c1=am_asset.e_nature_obj_prod
WHERE (am_asset.is_ci = 1)
AND (am_un_classification.un_classification_id= 165)
AND (am_asset.e_supervision = 0 ORam_asset.e_supervision IS NULL)
ANDconfiguration_item_link.relation_type_id = 244
AND cit2.relation_type_id = 246
and am_asset.status_id not in (7,17)
AND appli.status_id not in (7,17) -- Appliinstalee -en activite-
AND server.status_id not in (7,17)
and am_asset.asset_tag like 'LG32%'
and(am_asset.CRITICAL_LEVEL_ID != server.CRITICAL_LEVEL_ID
or appli.CRITICAL_LEVEL_ID !=server.CRITICAL_LEVEL_ID
or am_asset.CRITICAL_LEVEL_ID !=appli.CRITICAL_LEVEL_ID);
Serveur env horaire sup
select sr.asset_tag, sr.network_identifier, env.CI_STATUS_FR, sr.E_HORAIRE_SUP from AM_ASSET sr left outer join CMDB_CI_STATUS env on sr.CI_STATUS_ID = env.CI_STATUS_ID where sr.ASSET_TAG like 'LG50%';
Appli sur host
select AP.asset_tag, AP.network_identifier, AP.STATUS_ID, SR.ASSET_TAG, SR.NETWORK_IDENTIFIER, SR.status_ID,
case SR.CRITICAL_LEVEL_ID when 6 then 'Haute'
when 5 then 'NULL' when 2 then 'Basse'
when 1 then 'Moyenne' end Critical, ST.CI_STATUS_FR from configuration_item_link LK
left outer join am_asset AP
ON LK.child_ci_id = AP.asset_id
left outer join am_asset SR
ON LK.PARENT_CI_ID = SR.ASSET_ID
left outer join CMDB_CI_STATUS ST
ON SR.CI_STATUS_ID = ST.CI_STATUS_ID
where LK.RELATION_TYPE_ID in ('245','246')
and AP.ASSET_TAG like 'LG10%'
and AP.NETWORK_IDENTIFIER like '%automa%'
and SR.STATUS_ID !='7';
Nouveau numero de CI
SELECT MAX(LEFT(ASSET_TAG, 4)) + LTRIM(FORMAT(MAX(SUBSTRING(ASSET_TAG, 5, 5) + 1), '00000')) AS max_last_asset_tag, MAX(LEFT(ASSET_TAG, 4)) + LTRIM(FORMAT(MAX(SUBSTRING(ASSET_TAG, 5, 5) + 1), '00000')) AS max_last_asset_tag2 FROM AM_ASSET WHERE (ASSET_TAG LIKE 'LG50%')
Changement responsable CI
Depuis un CI
select EMPLOYEE_ID from am_asset where asset_tag = 'LGXXXXXX';
Retrouver le nom rattaché a un EMPLOYEE_ID
select LAST_NAME from AM_EMPLOYEE where EMPLOYEE_ID ='XXXXX';
Retrouver le EMPLOYEE_ID a partir du nom
select EMPLOYEE_ID, LAST_NAME from AM_EMPLOYEE where LAST_NAME like '%NOM%';
Retrouver les CI d'un EMPLOYEE_ID
select am.asset_tag, EMP.LAST_NAME from AM_ASSET AM join AM_EMPLOYEE EMP on am.EMPLOYEE_ID=EMP.EMPLOYEE_ID where AM.EMPLOYEE_ID='XXXX' and AM.ASSET_TAG like 'LG%';
Modifier EMPLOYEE_ID pour de CI d'un EMPLOYEE_ID
update AM set AM.EMPLOYEE_ID='NEW ID' from AM_ASSET AS AM inner join AM_EMPLOYEE EMP on am.EMPLOYEE_ID=EMP.EMPLOYEE_ID where AM.EMPLOYEE_ID='OLD ID' and AM.ASSET_TAG like 'LG%';
Liste application/serveur
select Asset.ASSET_TAG, asset.NETWORK_IDENTIFIER, APASSET.ASSET_TAG, APASSET.NETWORK_IDENTIFIER
from AM_ASSET ASSET
INNER JOIN am_catalog cat ON ASSET.catalog_id = cat.catalog_id
INNER JOIN configuration_item_link link ON ASSET.asset_id = link.PARENT_CI_ID
INNER JOIN AM_ASSET APASSET ON APASSET.ASSET_id = link.CHILD_CI_ID
where link.RELATION_TYPE_ID = 246
and ASSET.ASSET_TAG like 'LG50%'
and asset.STATUS_ID not in ('7','17')
and APASSET.STATUS_ID not in ('7','17');