Easyvista

De Wiki-noulot
Sauter à la navigation Sauter à la recherche

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%')