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

critical level

Table de ref AM_CRITICAL_LEVEL

Critical_LEVEL_ID CRITICAL_LEVEL_FR
1 2 - Moyenne
2 1 - Basse
5 NULL
6 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');

Recherche des Hosts hebergés sur HARD

select HASSET.ASSET_TAG HDWNAME, Hasset.SERIAL_NUMBER SerialN, HASSET.status_id HSTATUS, asset.NETWORK_IDENTIFIER HOSTN, asset.STATUS_ID
from AM_ASSET ASSET  
INNER JOIN configuration_item_link link ON ASSET.asset_id =  link.CHILD_CI_ID 
INNER JOIN AM_ASSET HASSET ON HASSET.ASSET_id = link.PARENT_CI_ID
where link.RELATION_TYPE_ID = 245 
and HASSET.ASSET_TAG like 'HW00%' and asset.Asset_tag not in ('LG5001772','LG15001113','LG5001940','LG5001729') and HASSET.NETWORK_IDENTIFIER not like '%_VM'
or HASSET.ASSET_TAG like 'HW70%' and asset.Asset_tag not in ('LG5001772','LG15001113','LG5001940','LG5001729') and HASSET.NETWORK_IDENTIFIER not like '%_VM'
or HASSET.ASSET_TAG like 'HW60%' and asset.Asset_tag not in ('LG5001772','LG15001113','LG5001940','LG5001729') and HASSET.NETWORK_IDENTIFIER not like '%_VM'
or HASSET.ASSET_TAG like 'HW50%' and asset.Asset_tag not in ('LG5001772','LG15001113','LG5001940','LG5001729') and HASSET.NETWORK_IDENTIFIER not like '%_VM';