Easyvista
(Redirigé depuis Esayvista)
Sauter à la navigation
Sauter à la recherche
Sommaire
- 1 Requête pour Easyvista
- 1.1 User EZV RO
- 1.2 Afficher les requêtes
- 1.3 Clear cache
- 1.4 Info de status EZV
- 1.5 critical level
- 1.6 Am_reference table des types de liens
- 1.7 Changement status en supprimé
- 1.8 Changement nature
- 1.9 Changement nature et arg1
- 1.10 Changement ARG (e_supervision_X champ argEZV)
- 1.11 Changement état en téléexploite
- 1.12 Ajout Commentaire par CI
- 1.13 Changement status
- 1.14 Changement Status avec Commentaire
- 1.15 Copier le champ CI dans Nom HW si commence par S mais pas par SW
- 1.16 Changement type colonne:
- 1.17 Select des hard et host
- 1.18 Select Hardware par position et status
- 1.19 Select Application Installé sur quel Host
- 1.20 Dif crititité
- 1.21 Serveur env horaire sup
- 1.22 Appli sur host
- 1.23 Nouveau numero de CI
- 1.24 Changement responsable CI
- 1.25 Liste application/serveur
- 1.26 Recherche des Hosts hebergés sur HARD
- 1.27 Recherche Ai d'un CI
- 1.28 Recherche les OI d'une AI (Sans AG)
- 1.29 Recherche des criticités des HOST < AI
- 1.30 Recherche des criticités des OI > au AI
- 1.31 Recherche OI>AI>HOST>HARD
- 1.32 Requête de Nicolas
- 1.33 afficher historique d'un serveur
- 1.34 Modif Share/LUN
- 1.35 Liste windows Actif
- 1.36 Classification Asset
- 1.37 Liste Windows hostgroupe Status Domaine Env GroupeESM
- 1.38 liste des TK en cours de plus de 31 jours
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
[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