Easyvista

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

Requête pour Easyvista

User EZV RO

user centreon ==> b9%r23$sPPb 

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 liens

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';

Recherche Ai d'un CI

select OI.asset_tag OI_asset_tag, OI.NETWORK_IDENTIFIER Oi_name, APASSET.ASSET_TAG AI_asset_tag, APASSET.NETWORK_IDENTIFIER AI_name
from am_asset OI
INNER JOIN configuration_item_link link ON OI.ASSET_ID = link.PARENT_CI_ID
inner join am_asset apasset on apasset.ASSET_ID = link.CHILD_CI_ID
where link.RELATION_TYPE_ID = 244
and OI.ASSET_TAG = 'LG320XXXX';

Recherche les OI d'une AI (Sans AG)

select AI.asset_tag AI_asset_tag, AI.NETWORK_IDENTIFIER Ai_name, APASSET.ASSET_TAG OI_asset_tag, APASSET.NETWORK_IDENTIFIER OI_name
from am_asset AI
INNER JOIN configuration_item_link link ON AI.ASSET_ID = link.CHILD_CI_ID
inner join am_asset apasset on apasset.ASSET_ID = link.PARENT_CI_ID
where link.RELATION_TYPE_ID = 244
and AI.ASSET_TAG in ('LG100XXXX','LG100XXXX','LG100XXXX') and APASSET.ASSET_TAG not like 'SW%';

Recherche des criticités des HOST < AI

select ST1.STATUS_FR status_Host, H.asset_tag HOST,H.NETWORK_IDENTIFIER HOST,CR1.CRITICAL_LEVEL_FR CR_HOST,ST2.STATUS_FR sttus_AI, AI.asset_tag AI, AI.NETWORK_IDENTIFIER NOM_AI, CR2.CRITICAL_LEVEL_FR CR_AI from AM_ASSET H 
inner join configuration_item_link link ON H.ASSET_ID = link.PARENT_CI_ID
inner join AM_ASSET AI on AI.ASSET_ID = link.CHILD_CI_ID
inner join AM_CRITICAL_LEVEL CR1 on CR1.CRITICAL_LEVEL_ID = H.CRITICAL_LEVEL_ID
inner join AM_CRITICAL_LEVEL CR2 on CR2.CRITICAL_LEVEL_ID = AI.CRITICAL_LEVEL_ID
inner join AM_STATUS ST1 on ST1.STATUS_ID = H.STATUS_ID
inner join AM_STATUS ST2 on ST2.STATUS_ID = AI.STATUS_ID
where link.RELATION_TYPE_ID = 246
and H.STATUS_ID != 7
and SUBSTRING(CR1.CRITICAL_LEVEL_FR,1,1) < SUBSTRING(CR2.CRITICAL_LEVEL_FR,1,1);


Recherche des criticités des OI > au AI

 select ST1.STATUS_FR status_OI, H.asset_tag CI_OI,H.NETWORK_IDENTIFIER OI,CR1.CRITICAL_LEVEL_FR CR_OI,ST2.STATUS_FR status_AI, AI.asset_tag AI, AI.NETWORK_IDENTIFIER NOM_AI,  
CR2.CRITICAL_LEVEL_FR CR_AI from AM_ASSET H 
inner join configuration_item_link link ON H.ASSET_ID = link.PARENT_CI_ID
inner join AM_ASSET AI on AI.ASSET_ID = link.CHILD_CI_ID
inner join AM_CRITICAL_LEVEL CR1 on CR1.CRITICAL_LEVEL_ID = H.CRITICAL_LEVEL_ID
inner join AM_CRITICAL_LEVEL CR2 on CR2.CRITICAL_LEVEL_ID = AI.CRITICAL_LEVEL_ID
inner join AM_STATUS ST1 on ST1.STATUS_ID = H.STATUS_ID
inner join AM_STATUS ST2 on ST2.STATUS_ID = AI.STATUS_ID
where link.RELATION_TYPE_ID = 244
and H.STATUS_ID != 7
and SUBSTRING(CR1.CRITICAL_LEVEL_FR,1,1) > SUBSTRING(CR2.CRITICAL_LEVEL_FR,1,1)
and H.ASSET_TAG not like 'SW10%'
and H.ASSET_TAG not like 'LG30%';

Requête de Nicolas

USE [EVO_DATA50004]
GO
select a.network_identifier,
	   a.asset_tag,
       a.asset_id,
	   a.ci_status_id, 
	   a.status_id
into #table_host
from  [50004].[AM_ASSET] a
where 
--	  LG50%=Host, LG10%=IA, LG32%=OI   
	  a.asset_tag like 'LG50%' and
--    1=Production,2=Développement,3=Test,6=Pré Production,28=Recette,35=Formation
	  a.CI_STATUS_ID=1 and
--    8=En activité,7=Supprimé,17=En construction,22=En activité - Non Supervisé,26=Inactif,28=En activité - Nagios Préprod
	  a.status_id<>7;

select a.network_identifier, 
	   a.asset_tag,
       a.asset_id,
	   a.ci_status_id, 
	   a.status_id	   
into #table_IA
from  [50004].[AM_ASSET] a
where 
--	  LG50%=Host, LG10%=IA, LG32%=OI  
	  a.asset_tag like 'LG10%' and
--    1=Production,2=Développement,3=Test,6=Pré Production,28=Recette,35=Formation
	  a.CI_STATUS_ID=1 and
--    8=En activité,7=Supprimé,17=En construction,22=En activité - Non Supervisé,26=Inactif,28=En activité - Nagios Préprod
	  a.status_id<>7;

select a.network_identifier,
	   a.asset_tag,
       a.asset_id,
	   a.ci_status_id, 
	   a.status_id
into #table_OI
from  [50004].[AM_ASSET] a
where 
--	  LG50%=Host, LG10%=IA, LG32%=OI  
	  a.asset_tag like 'LG32%' and
--    1=Production,2=Développement,3=Test,6=Pré Production,28=Recette,35=Formation
	  a.CI_STATUS_ID=1 and
--    8=En activité,7=Supprimé,17=En construction,22=En activité - Non Supervisé,26=Inactif,28=En activité - Nagios Préprod
	  a.status_id<>7;

select conf.PARENT_CI_ID,
	   conf.CHILD_CI_ID
into #table_child_1 
from [50004].[configuration_item_link] conf, 
     #table_host host 
where conf.PARENT_CI_ID = host.asset_id;


select host.asset_tag asset_tag_host,
	   host.network_identifier network_identifier_host,
	   host.asset_id asset_id_host,
	   host.ci_status_id ci_status_id_host,
	   host.status_id status_id_host,
	   ia.asset_tag asset_tag_ia,	 
	   ia.NETWORK_IDENTIFIER network_identifier_ia, 
	   ia.asset_id asset_id_ia,
	   ia.ci_status_id ci_status_id_ia,
	   ia.status_id status_id_ia
into #table_cons_1
from #table_host host,
	 #table_IA ia,
	 #table_child_1 c1
where host.ASSET_id = c1.PARENT_CI_ID
and	c1.CHILD_CI_ID = ia.ASSET_ID; 

select conf.CHILD_CI_ID PARENT_CI_ID, conf.PARENT_CI_ID CHILD_CI_ID
into #table_child_2
from [50004].[configuration_item_link]  conf,
	 #table_child_1 c1
where conf.CHILD_CI_ID = c1.CHILD_CI_ID;

select ia.asset_tag asset_tag_ia,
       ia.network_identifier network_identifier_ia,
	   ia.ASSET_ID ASSET_ID_IA,
	   ia.ci_status_id ci_status_id_ia,
	   ia.status_id status_id_ia,
	   oi.asset_tag asset_tag_oi,
	   oi.NETWORK_IDENTIFIER network_identifier_oi, 
	   oi.asset_id asset_id_oi,
	   oi.ci_status_id ci_status_id_oi,
	   oi.status_id status_id_oi
into #table_cons_2
from #table_IA ia,
	 #table_OI oi,
	 #table_child_2 c2
where ia.ASSET_id = c2.PARENT_CI_ID
and	c2.CHILD_CI_ID = oi.ASSET_ID; 


select	cons1.asset_tag_host,
		cons1.network_identifier_host,
		cons1.ci_status_id_host,
		cons1.status_id_host,
		cons1.asset_tag_ia,		
		cons1.network_identifier_ia,
		cons1.ci_status_id_ia,
		cons1.status_id_ia,
		cons2.asset_tag_oi, 
		cons2.network_identifier_oi,
		cons2.ci_status_id_oi,
		cons2.status_id_oi
into #table_cons_final
from #table_cons_1 cons1,
	 #table_cons_2 cons2
where cons1.asset_id_ia = cons2.ASSET_ID_IA;


select * 
from #table_cons_final
where 
   network_identifier_host = 'EUCCESASQP02 '
-- asset_tag_host = 
-- network_identifier_ia =
-- asset_tag_ia = 
-- network_identifier_io =
-- asset_tag_oi =
order by 1 asc, 5 asc, 9 asc;

Modif Share/LUN

Modifier le FSTAB (Attention puppet)

Modifier les champs dans la base MSSQL

update EZV_ADMIN.A_PARAMETERS set PARAM_VALUE = '\\FRCCEISSNP02\evz_ressource' where PARAM_ID = 'INTEGR_UPLOAD_PATH' or PARAM_ID = 'RESOURCE_FOLDER'