Easyvista

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

Requête pour Easyvista[modifier]

User EZV RO[modifier]

user centreon ==> b9%r23$sPPb 

Afficher les requêtes[modifier]

&ShowStatement=yes

Clear cache[modifier]

https://servicedesk/clear_cache.php

Info de status EZV[modifier]

Table AM_status

Status_id	STATUS_FR
58	        A Générer
12	        A restituer
11	        A vérifier
39	        Dépollué par 3E
15	        Détruit
13	        Doublon
8	        En activité
28	        En activité - Nagios Préprod
22	        En activité - Non Supervisé
29	        En commande
17	        En construction
10	        En cours de transfert
30	        En maintenance
1	        En panne
32	        En quarantaine
2	        En réparation
4	        En stock
40	        Inactif
33	        Manquant
5	        Mis à jour 
38	        Post Migration
3	        Prêté
26	        Replacement
34	        Restitué au fournisseur
31	        Retour Atelier
6	        Retourné
7	        Supprimé
9	        Volé

critical level[modifier]

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[modifier]

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é[modifier]

Update am_asset set status_id = 7 where asset_tag='N°CI';

Changement nature[modifier]

Update am_asset set e_nature_obj_prod='NATURE', last_update=sysdate where asset_tag='N°CI'

Changement nature et arg1[modifier]

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)[modifier]

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[modifier]

Update am_asset set e_teleexploit='1', last_update=sysdate where asset_tag='N°CI';

Ajout Commentaire par CI[modifier]

update am_asset set COMMENT_ASSET='centreon', last_update=sysdate where asset_tag='N°CI'


Changement status[modifier]

Update am_asset set status_id = 'Status ID', last_update=sysdate where asset_tag='N°CI';


Changement Status avec Commentaire[modifier]

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[modifier]

update am_asset set network_identifier=asset_tag where asset_tag like 'S%' and  asset_tag not like 'SW%';

Changement type colonne:[modifier]

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[modifier]

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[modifier]

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[modifier]

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é[modifier]

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[modifier]

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[modifier]

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[modifier]

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[modifier]

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[modifier]

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[modifier]

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[modifier]

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)[modifier]

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[modifier]

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[modifier]

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

Recherche OI>AI>HOST>HARD[modifier]

---OI>AI----

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 like 'LG320%' and OI.E_NATURE_OBJ_PROD like '%IDRAC%';

---AI > HOST----

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 link2 ON ASSET.asset_id = link2.PARENT_CI_ID 
INNER JOIN AM_ASSET APASSET ON APASSET.ASSET_id = link2.CHILD_CI_ID 
where link2.RELATION_TYPE_ID = 246 
and ASSET.ASSET_TAG like 'LG50%' 
and APASSET.NETWORK_IDENTIFIER like '%Hard%' 
and asset.STATUS_ID not in ('7','17') 
and APASSET.STATUS_ID not in ('7','17');

---Host>HARD----

select HASSET.ASSET_TAG HOSTCI,HASSET.NETWORK_IDENTIFIER HOSTNAME, asset.NETWORK_IDENTIFIER HARDCI
from AM_ASSET ASSET  
INNER JOIN configuration_item_link link ON ASSET.asset_id =  link.PARENT_CI_ID
INNER JOIN AM_ASSET HASSET ON HASSET.ASSET_id = link.CHILD_CI_ID
where link.RELATION_TYPE_ID = 245 
and HASSET.ASSET_TAG like 'LG50%'
and HASSET.status_id != 7
and asset.STATUS_ID != 7;

Requête de Nicolas[modifier]

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;

afficher historique d'un serveur[modifier]

SELECT AM_ASSET.NETWORK_IDENTIFIER as "hostname", AM_ASSET.ASSET_TAG as "CI", AM_HISTORY.CHANGE_DATE AS "HISTORY_CHANGE_DATE" ,
AM_EMPLOYEE.LAST_NAME AS "EMPLOYEE_NAME",
AM_HISTORY.NEW_VALUE_FR AS "HISTORY_NEW_VALUE"
FROM AM_ASSET INNER JOIN AM_HISTORY ON AM_HISTORY.[ID] = AM_ASSET.ASSET_ID 
INNER JOIN AM_HISTORY_PARAM ON AM_HISTORY. HISTORY_PARAM_ID = AM_HISTORY_PARAM.HISTORY_PARAM_ID 
LEFT OUTER JOIN AM_EMPLOYEE ON AM_HISTORY.CHANGED_BY_EMPLOYEE_ID = AM_EMPLOYEE.EMPLOYEE_ID 
WHERE (AM_HISTORY_PARAM.TABLE_NAME = 'AM_ASSET') 
AND (AM_ASSET.ASSET_TAG like 'LG50%') 
and (AM_HISTORY.NEW_VALUE_FR = 'En activité' ) 
and (AM_ASSET.STATUS_ID != 7) ORDER BY 1 DESC  

Modif Share/LUN[modifier]

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'

Liste windows Actif[modifier]

select asset.ASSET_TAG CI, asset.NETWORK_IDENTIFIER HOST, STS.STATUS_FR status, ENV.CI_STATUS_FR ENV, asset.e_type_os OS, 
asset.ASSET_LABEL GROUPEMANA, asset.E_HOSTGROUP_NAGIOS HOSTGROUPE, asset.E_NOM_ENV Projet
from AM_ASSET asset
inner join AM_STATUS STS on STS.STATUS_ID = asset.STATUS_ID 
inner join CMDB_CI_STATUS ENV on ENV.CI_STATUS_ID = asset.CI_STATUS_ID
where asset.ASSET_TAG like 'LG50%'
and asset.STATUS_ID != 7
and asset.E_TYPE_OS like 'W%'

Classification Asset[modifier]

select A.ASSET_TAG as TAG,Cla.UN_CLASSIFICATION_FR from AM_ASSET as A 
inner join AM_CATALOG as Cat on A.CATALOG_ID = Cat.CATALOG_ID
inner join AM_UN_CLASSIFICATION as Cla on Cat.UN_CLASSIFICATION_ID = Cla.UN_CLASSIFICATION_ID


Liste Windows hostgroupe Status Domaine Env GroupeESM[modifier]

select asset_tag as CI_Number, network_identifier as Hostname, 
E_HOSTGROUP_NAGIOS as HOSTGROUP, 
S.STATUS_FR, BAN_3 as Domaine, 
Env.CI_STATUS_FR as Environement, 
A.ASSET_LABEL as Groupe_ESM
from AM_ASSET as A
inner join AM_STATUS As S on A.STATUS_ID = S.STATUS_ID
inner join CMDB_CI_STATUS as Env on A.CI_STATUS_ID = Env.CI_STATUS_ID
where A.E_TYPE_OS like '%W2K12%' and A.ASSET_TAG like 'LG50%';

liste des TK en cours de plus de 31 jours[modifier]

Select  SD_REQUEST.RFC_NUMBER AS "Numéro de ticket",
SD_REQUEST.CREATION_DATE_UT AS "Date de création",
SD_STATUS.STATUS_FR AS "Statut du ticket",
SD_CATALOG.TITLE_FR AS "Type de demande",
AM_ASSET_CI.NETWORK_IDENTIFIER AS "Nom du CI",
AM_RECIPIENT.LAST_NAME AS "Demandeur",
AM_GROUP.GROUP_FR As "GroupInCharge",
AM_EMPLOYEE.LAST_NAME As "Attribué à",
AM_CRITICAL_LEVEL.CRITICAL_LEVEL_FR Criticité
 
From [EVO_DATA50004].[50004].AM_ACTION
 
LEFT OUTER JOIN [EVO_DATA50004].[50004].SD_REQUEST ON AM_ACTION.REQUEST_ID = SD_REQUEST.REQUEST_ID 
LEFT OUTER JOIN [EVO_DATA50004].[50004].SD_STATUS ON SD_REQUEST.STATUS_ID = SD_STATUS.STATUS_ID
LEFT OUTER JOIN [EVO_DATA50004].[50004].SD_CATALOG ON SD_CATALOG.SD_CATALOG_ID = SD_REQUEST.SD_CATALOG_ID
LEFT OUTER JOIN [EVO_DATA50004].[50004].AM_EMPLOYEE AM_RECIPIENT ON AM_RECIPIENT.EMPLOYEE_ID = SD_REQUEST.RECIPIENT_ID 
LEFT OUTER JOIN [EVO_DATA50004].[50004].AM_EMPLOYEE ON AM_EMPLOYEE.EMPLOYEE_ID = AM_ACTION.DONE_BY_ID
LEFT OUTER JOIN [EVO_DATA50004].[50004].AM_GROUP ON AM_GROUP.GROUP_ID = AM_ACTION.GROUP_ID
LEFT OUTER JOIN [EVO_DATA50004].[50004].AM_ASSET AM_ASSET_CI ON SD_REQUEST.CI_ID = AM_ASSET_CI.ASSET_ID 
LEFT OUTER JOIN [EVO_DATA50004].[50004].AM_CRITICAL_LEVEL ON AM_ASSET_CI.CRITICAL_LEVEL_ID =  AM_CRITICAL_LEVEL.CRITICAL_LEVEL_ID
 
where  SD_REQUEST.RFC_NUMBER LIKE 'I%'
and SD_REQUEST.SUBMIT_DATE_UT < (convert(datetime, concat(convert(varchar(10), DATEADD(day, -31, SYSDATETIME ( ))), ' 23:59')))
and SD_STATUS.STATUS_FR NOT LIKE 'Résolu'
and SD_STATUS.STATUS_FR NOT LIKE 'Clôturé'
and SD_STATUS.STATUS_FR NOT LIKE 'Refus%'
and SD_STATUS.STATUS_FR NOT LIKE 'Annulé'
and AM_ACTION.END_DATE_UT is null

ORDER BY AM_GROUP.GROUP_FR ASC