Easyvista : Différence entre versions
Sauter à la navigation
Sauter à la recherche
| Ligne 177 : | Ligne 177 : | ||
AS max_last_asset_tag, 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%') | 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%'; | ||
Version du 26 novembre 2015 à 11:27
Sommaire
- 1 Requête pour Easyvista
- 1.1 Afficher les requêtes
- 1.2 Clear cache
- 1.3 Info de status EZV
- 1.4 Changement status en supprimé
- 1.5 Changement nature
- 1.6 Changement nature et arg1
- 1.7 Changement ARG (e_supervision_X champ argEZV)
- 1.8 Changement état en téléexploite
- 1.9 Ajout Commentaire par CI
- 1.10 Changement status
- 1.11 Changement Status avec Commentaire
- 1.12 Copier le champ CI dans Nom HW si commence par S mais pas par SW
- 1.13 Changement type colonne:
- 1.14 Select des hard et host
- 1.15 Select Hardware par position et status
- 1.16 Select Application Installé sur quel Host
- 1.17 Dif crititité
- 1.18 Serveur env horaire sup
- 1.19 Appli sur host
- 1.20 Nouveau numero de CI
- 1.21 Changement responsable CI
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
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%';