Howtos:Inventory thin client (Wyse, Dell...)

From OCS Inventory NG
Jump to: navigation, search
' OCSINVENTORY-NG
' Web : http://www.ocsinventory-ng.org
'
' Ce script extrait tous les devices de la base SQL du Wyse Device Manager et crée un fichier .ocs pour chacun d'entre eux
'
' Auteur     : Sylvie Cozic (Grimonpont)
' Date       : 25/03/2016
' Version    : 1.6.0
'
' Historique : 1.0.0 - 04/05/2012 : Première version stable. Requête sur la vue View_ClientDetails.
'              1.1.0 - 31/05/2012 : Requête sur plusieurs tables -> Récuparation infos supplémentaires (CPU, réseau, vidéo, son...)
'              1.2.0 - 03/04/2014 : Calcul du sous-réseau, remontée de l'Asset-Tag, ajout des spécificités des nouveaux modèles Wyse utilisés
'              1.3.0 - 10/02/2015 : Remontée de la version de l'image dans HARDWARE.OSVersion et de l'agent WDM comme un logiciel dans SOFTWARES
'              1.4.0 - 01/09/2015 : Remontée du nom commercial du Wyse en fonction de l'OS (i.e. "CX0LE" et Wyse ThinOS --> "C10LE")
'              1.5.0 - 29/02/2016 : Calcul un device_id en fonction de la mac (simule un timestamp) -> si 2 Wyse ont le même nom, 2 fiches créées dans OCS
'              1.6.0 - 25/03/2016 : Trappe les erreurs à la création de fichier .ocs (pb de Wyse nommés avec des caractères non conventionnels"
'                                   + envoi mail si erreur
'
' This code is open source and may be copied and modified as long as the source
' code is always made freely available.
' Please refer to the General Public Licence http://www.gnu.org/ or Licence.txt

' Déclaration des variables et constantes
'-----------------------------------------
' Répertoire d'écriture des fichiers .ocs
Const strPath       = "C:\wyse_devices\"
' Serveur et mot de passe de la base SQL du Wyse Device Manager
Const strWyseServer = "nom_du_serveur_où_WDM_est_installé"
Const strWysePwd    = "mot_de_passe_de_la_base_SQl_du_WDM"
' Nombre de jours depuis le dernier CheckIn pour prise en compte dans OCS
Const intLastCheckin = 2

' Paramètres de l'envoi de mail
Dim strSMTPFrom : strSMTPFrom = "helpdesk@foo.com"
Dim strSMTPTo : strSMTPTo = "cmdb@foo.com;inventory@foo.com"
Dim strSMTPCc : strSMTPCc = ""
Dim strSMTPRelay : strSMTPRelay = "IP_du_relai_SMTP"
Dim strSubject : strSubject = "Erreur(s) lors de l'intégration des Wyses dans OCS"
Dim strTextBody
Dim strErr : strErr = ""

' Requête SQL
strSQL = "SELECT DISTINCT                                                                                                    " & _
         "    dbo.Client.Name,                                                                                               " & _
         "    dbo.ClientHardware.Serial,                                                                                     " & _
         "    RTRIM(CONVERT(char,dbo.Client.CheckIn,120)) as CheckIn,                                                        " & _
         "    REPLACE(dbo.Platform.Description,'X0', dbo.OSType.OSDispCode) AS Platform,                                     " & _
         "    dbo.ClientHardware.BIOS,                                                                                       " & _
         "    dbo.Vendor.Description AS Vendor,                                                                              " & _
         "    dbo.OSType.Description AS OS,                                                                                  " & _
         "    dbo.Client.Image AS OSVersion,                                                                                 " & _
         "    dbo.Client.AgentVersion AS WDMAgentVersion,                                                                    " & _
         "    dbo.ClientNetwork.MAC,                                                                                         " & _
         "    dbo.ClientNetwork.IP,                                                                                          " & _
         "    dbo.ClientNetwork.SubnetMask,                                                                                  " & _
         "    dbo.ClientNetwork.Gateway,                                                                                     " & _
         "    dbo.ClientNetwork.Domain,                                                                                      " & _
         "    dbo.ClientNetwork.DHCP,                                                                                        " & _
         "    dbo.ClientNetwork.DNS1,                                                                                        " & _
         "    dbo.ClientNetwork.DNS2,                                                                                        " & _
         "    dbo.ClientHardware.CPU,                                                                                        " & _
         "    dbo.ClientHardware.CPUSpeed,                                                                                   " & _
         "    dbo.ClientHardware.SoundCard,                                                                                  " & _
         "    dbo.ClientHardware.VideoCard,                                                                                  " & _
         "    CEILING(dbo.ClientHardware.RAM/512) as RAM,                                                                    " & _
         "    dbo.ClientStorage.TotalSpace as StorageTotalSpace,                                                             " & _
         "    dbo.Storage.Description as StorageDescription,                                                                 " & _
         "    dbo.ClientHardware.AssetTag                                                                                    " & _
         "FROM                                                                                                               " & _
         "                     dbo.Client                                                                                    " & _
         "    LEFT OUTER JOIN dbo.ClientNetwork  ON dbo.Client.ClientID = dbo.ClientNetwork.ClientID                         " & _
         "    LEFT OUTER JOIN dbo.OSType         ON dbo.Client.OSTypeID = dbo.OSType.OSTypeID                                " & _
         "    LEFT OUTER JOIN dbo.Vendor         ON dbo.Client.VendorID = dbo.Vendor.VendorID                                " & _
         "    LEFT OUTER JOIN dbo.ClientHardware ON dbo.Client.ClientID = dbo.ClientHardware.ClientID                        " & _
         "    LEFT OUTER JOIN dbo.Platform       ON dbo.ClientHardware.PlatformID = dbo.Platform.PlatformID                  " & _
         "    LEFT OUTER JOIN dbo.ClientStorage  ON dbo.Client.ClientID = dbo.ClientStorage.ClientID AND                     " & _
         "                                                               (dbo.ClientStorage.IsBootDisk = 1 OR                " & _
         "                                                                (SELECT     COUNT(*) AS Expr1                      " & _
         "                                                                  FROM          dbo.ClientStorage                  " & _
         "                                                                  WHERE      (ClientID = dbo.Client.ClientID)) = 1)" & _
         "    LEFT OUTER JOIN dbo.Storage        ON dbo.ClientStorage.StorageID = dbo.Storage.StorageID                      " & _
         "WHERE datediff(d,dbo.Client.CheckIn,getdate()) <= " & intLastCheckin & ";"

' Chaine de connexion à la base
Dim connectionString: connectionString = "Provider=SQLOLEDB;Data Source=" & strWyseServer & "\RapportDB;Initial Catalog=RapportDB;User ID=Rapport;Password=" & strWysePwd & ";"

' Process
'-----------

' Création de l'objet objFSO
Dim objFSO: Set objFSO = CreateObject("Scripting.FileSystemObject")

' Création de la connexion
Dim connection: Set connection = CreateObject("ADODB.Connection")
Dim rows: Set rows = CreateObject("ADODB.Recordset")

' Ouverture de la connexion
connection.Open connectionString

' Execution de la requête SQL
Set rows = connection.Execute(strSQL)

' Lecture des enregistrements retournés
While Not rows.EOF

    ' Trappe les erreurs
    On Error Resume Next

    ' Création / Modification du fichier OCS
    Set MyFile = objFSO.CreateTextFile(strPath & UCase(rows.Fields("name")) & MacToDate(rows.Fields("mac")) & ".ocs")
    If Err Then
        strErr = strErr & _
                 "Ligne " & Err.Line & " / " & Err.Column & " - Erreur " & Err.Number & " : " & Err.Description & vbCRLF & vbCRLF & _
                 "A la création du fichier " & strPath & UCase(rows.Fields("name")) & MacToDate(rows.Fields("mac")) & ".ocs" & vbCRLF & vbCRLF & _
                 "Nom / IP du Wyse : " & rows.Fields("name") & " / " & rows.Fields("ip") & vbCRLF & vbCRLF & _
        Err.Clear
    Else
        ' Ne trappe plus les erreurs
        On Error Goto 0

        ' Ecriture du fichier OCS
        MyFile.WriteLine "<?xml version=""1.0"" encoding=""UTF-8"" ?>"
        MyFile.WriteLine "<REQUEST>"
        MyFile.WriteLine "    <DEVICEID>" & UCase(rows.Fields("name")) & MacToDate(rows.Fields("mac")) & "</DEVICEID>"
        MyFile.WriteLine "    <CONTENT>"
        MyFile.WriteLine "        <DOWNLOAD>"
        MyFile.WriteLine "            <HISTORY />"
        MyFile.WriteLine "        </DOWNLOAD>"
        MyFile.WriteLine "        <HARDWARE>"
        MyFile.WriteLine "            <CHECKSUM>262143</CHECKSUM>"
        MyFile.WriteLine "            <NAME>" & rows.Fields("name") & "</NAME>"
        MyFile.WriteLine "            <OSNAME>" & rows.Fields("os") & "</OSNAME>"
        MyFile.WriteLine "            <OSVERSION>" & rows.Fields("OSVersion") & "</OSVERSION>"
        MyFile.WriteLine "            <WORKGROUP>" & rows.Fields("domain") & "</WORKGROUP>"
        MyFile.WriteLine "            <IPADDR>" & rows.Fields("ip") & "</IPADDR>"
        MyFile.WriteLine "            <MEMORY>" & rows.Fields("ram") & "</MEMORY>"
        MyFile.WriteLine "            <LASTDATE>" & rows.Fields("checkin") & "</LASTDATE>"
        MyFile.WriteLine "            <VMSYSTEM>Physical</VMSYSTEM>"
        MyFile.WriteLine "            <PROCESSORT>" & rows.Fields("CPU") & "</PROCESSORT>"
        MyFile.WriteLine "            <PROCESSORS>" & rows.Fields("CPUSpeed") & "</PROCESSORS>"
        MyFile.WriteLine "            <PROCESSORN>1</PROCESSORN>"
        MyFile.WriteLine "        </HARDWARE>"
        MyFile.WriteLine "        <BIOS>"
        MyFile.WriteLine "            <SMANUFACTURER>" & rows.Fields("vendor") & "</SMANUFACTURER>"
        Select Case rows.Fields("platform")
            Case "Franklin"
                MyFile.WriteLine "            <SMODEL>Wyse 3125SE</SMODEL>"
            Case Else
                MyFile.WriteLine "            <SMODEL>" & rows.Fields("platform") & "</SMODEL>"
        End Select
        MyFile.WriteLine "            <SSN>" & rows.Fields("serial") & "</SSN>"
        MyFile.WriteLine "            <BVERSION>" & rows.Fields("BIOS") & "</BVERSION>"
        MyFile.WriteLine "            <TYPE>Thin Client</TYPE>"
        If Len(rows.Fields("AssetTag")) > 0 Then
            MyFile.WriteLine "            <ASSETTAG>" & rows.Fields("AssetTag") & "</ASSETTAG>"
        End If
        MyFile.WriteLine "        </BIOS>"
        MyFile.WriteLine "        <SOFTWARES>"
        MyFile.WriteLine "            <PUBLISHER>Wyse</PUBLISHER>"
        MyFile.WriteLine "            <NAME>WDM Agent</NAME>"
        MyFile.WriteLine "            <VERSION>" & rows.Fields("WDMAgentVersion") & "</VERSION>"
        MyFile.WriteLine "        </SOFTWARES>"
        MyFile.WriteLine "        <NETWORKS>"
        MyFile.WriteLine "            <DESCRIPTION>Wyse</DESCRIPTION>"
        MyFile.WriteLine "            <TYPE>Other</TYPE>"
        MyFile.WriteLine "            <MACADDR>" & mac2string(rows.Fields("mac")) & "</MACADDR>"
        MyFile.WriteLine "            <STATUS>Up</STATUS>"
        MyFile.WriteLine "            <IPADDRESS>" & rows.Fields("ip") & "</IPADDRESS>"
        MyFile.WriteLine "            <IPMASK>" & rows.Fields("SubnetMask") & "</IPMASK>"
        MyFile.WriteLine "            <IPGATEWAY>" & rows.Fields("Gateway") & "</IPGATEWAY>"
        MyFile.WriteLine "            <IPSUBNET>" & CalcNetworkAddress(rows.Fields("ip"), rows.Fields("SubnetMask")) & "</IPSUBNET>"
        MyFile.WriteLine "        </NETWORKS>"
        Select Case rows.Fields("platform")
            Case "Franklin"
                MyFile.WriteLine "        <MEMORIES>"
                MyFile.WriteLine "            <CAPTION>System Memory</CAPTION>"
                MyFile.WriteLine "            <CAPACITY>64</CAPACITY>"
                MyFile.WriteLine "            <PURPOSE>System Memory</PURPOSE>"
                MyFile.WriteLine "            <TYPE>DDR2</TYPE>"
                MyFile.WriteLine "        </MEMORIES>"
                MyFile.WriteLine "        <MEMORIES>"
                MyFile.WriteLine "            <CAPTION>Flash Memory</CAPTION>"
                MyFile.WriteLine "            <DESCRIPTION>SYSTEM ROM (No ECC)</DESCRIPTION>"
                MyFile.WriteLine "            <CAPACITY>" & rows.Fields("StorageTotalSpace") & "</CAPACITY>"
                MyFile.WriteLine "            <PURPOSE>Flash Memory</PURPOSE>"
                MyFile.WriteLine "            <TYPE>Flash</TYPE>"
                MyFile.WriteLine "            <SERIALNUMBER>SYSTEM ROM</SERIALNUMBER>"
                MyFile.WriteLine "        </MEMORIES>"
                MyFile.WriteLine "        <PORTS>"
                MyFile.WriteLine "            <TYPE>Video Port</TYPE>"
                MyFile.WriteLine "            <NAME>VIDEO</NAME>"
                MyFile.WriteLine "            <CAPTION>VIDEO DB-15 pin female</CAPTION>"
                MyFile.WriteLine "            <DESCRIPTION>DB-15 pin female</DESCRIPTION>"
                MyFile.WriteLine "        </PORTS>"
                MyFile.WriteLine "        <PORTS>"
                MyFile.WriteLine "            <TYPE>USB</TYPE>"
                MyFile.WriteLine "            <NAME>USB 1</NAME>"
                MyFile.WriteLine "            <CAPTION>USB 1 Access Bus (USB)</CAPTION>"
                MyFile.WriteLine "            <DESCRIPTION>Access Bus (USB)</DESCRIPTION>"
                MyFile.WriteLine "        </PORTS>"
                MyFile.WriteLine "        <PORTS>"
                MyFile.WriteLine "            <TYPE>USB</TYPE>"
                MyFile.WriteLine "            <NAME>USB 2</NAME>"
                MyFile.WriteLine "            <CAPTION>USB 2 Access Bus (USB)</CAPTION>"
                MyFile.WriteLine "            <DESCRIPTION>Access Bus (USB)</DESCRIPTION>"
                MyFile.WriteLine "        </PORTS>"
                MyFile.WriteLine "        <PORTS>"
                MyFile.WriteLine "            <TYPE>USB</TYPE>"
                MyFile.WriteLine "            <NAME>USB 3</NAME>"
                MyFile.WriteLine "            <CAPTION>USB 3 Access Bus (USB)</CAPTION>"
                MyFile.WriteLine "            <DESCRIPTION>Access Bus (USB)</DESCRIPTION>"
                MyFile.WriteLine "        </PORTS>"
                MyFile.WriteLine "        <PORTS>"
                MyFile.WriteLine "            <TYPE>Keyboard Port</TYPE>"
                MyFile.WriteLine "            <NAME>KEYBOARD</NAME>"
                MyFile.WriteLine "            <CAPTION>KEYBOARD PS/2</CAPTION>"
                MyFile.WriteLine "            <DESCRIPTION>PS/2</DESCRIPTION>"
                MyFile.WriteLine "        </PORTS>"
                MyFile.WriteLine "        <PORTS>"
                MyFile.WriteLine "            <TYPE>Mouse Port</TYPE>"
                MyFile.WriteLine "            <NAME>MOUSE</NAME>"
                MyFile.WriteLine "            <CAPTION>MOUSE PS/2</CAPTION>"
                MyFile.WriteLine "            <DESCRIPTION>PS/2</DESCRIPTION>"
                MyFile.WriteLine "        </PORTS>"
                MyFile.WriteLine "        <PORTS>"
                MyFile.WriteLine "            <TYPE>Parallel Port ECP/EPP</TYPE>"
                MyFile.WriteLine "            <NAME>PARALLEL</NAME>"
                MyFile.WriteLine "            <CAPTION>PARALLEL DB-25 pin female</CAPTION>"
                MyFile.WriteLine "            <DESCRIPTION>DB-25 pin female</DESCRIPTION>"
                MyFile.WriteLine "        </PORTS>"
                MyFile.WriteLine "        <PORTS>"
                MyFile.WriteLine "            <TYPE>Serial Port 16550A Compatible</TYPE>"
                MyFile.WriteLine "            <NAME>SERIAL A</NAME>"
                MyFile.WriteLine "            <CAPTION>SERIAL A DB-9 pin male</CAPTION>"
                MyFile.WriteLine "            <DESCRIPTION>DB-9 pin male</DESCRIPTION>"
                MyFile.WriteLine "        </PORTS>"
                MyFile.WriteLine "        <PORTS>"
                MyFile.WriteLine "            <TYPE>Serial Port 16550A Compatible</TYPE>"
                MyFile.WriteLine "            <NAME>SERIAL B</NAME>"
                MyFile.WriteLine "            <CAPTION>SERIAL B DB-9 pin male</CAPTION>"
                MyFile.WriteLine "            <DESCRIPTION>DB-9 pin male</DESCRIPTION>"
                MyFile.WriteLine "        </PORTS>"
                MyFile.WriteLine "        <PORTS>"
                MyFile.WriteLine "            <TYPE>Network Port</TYPE>"
                MyFile.WriteLine "            <NAME>Ethernet</NAME>"
                MyFile.WriteLine "            <CAPTION>Ethernet RJ-45</CAPTION>"
                MyFile.WriteLine "            <DESCRIPTION>RJ-45</DESCRIPTION>"
                MyFile.WriteLine "        </PORTS>"
                MyFile.WriteLine "        <VIDEOS>"
                MyFile.WriteLine "            <NAME>Geode SC2200</NAME>"
                MyFile.WriteLine "            <CHIPSET>Geode SC2200</CHIPSET>"
                MyFile.WriteLine "        </VIDEOS>"
            Case "C10LE"
                MyFile.WriteLine "        <MEMORIES>"
                MyFile.WriteLine "            <CAPTION>System Memory</CAPTION>"
                MyFile.WriteLine "            <CAPACITY>512</CAPACITY>"
                MyFile.WriteLine "            <PURPOSE>System Memory</PURPOSE>"
                MyFile.WriteLine "            <TYPE>DDR2</TYPE>"
                MyFile.WriteLine "        </MEMORIES>"
                MyFile.WriteLine "        <MEMORIES>"
                MyFile.WriteLine "            <CAPTION>Flash Memory</CAPTION>"
                MyFile.WriteLine "            <DESCRIPTION>SYSTEM ROM (No ECC)</DESCRIPTION>"
                MyFile.WriteLine "            <CAPACITY>" & rows.Fields("StorageTotalSpace") & "</CAPACITY>"
                MyFile.WriteLine "            <PURPOSE>Flash Memory</PURPOSE>"
                MyFile.WriteLine "            <TYPE>Flash</TYPE>"
                MyFile.WriteLine "            <SERIALNUMBER>SYSTEM ROM</SERIALNUMBER>"
                MyFile.WriteLine "        </MEMORIES>"
                MyFile.WriteLine "        <PORTS>"
                MyFile.WriteLine "            <TYPE>Video Port</TYPE>"
                MyFile.WriteLine "            <NAME>DVI-Out</NAME>"
                MyFile.WriteLine "            <CAPTION>DVI-Out Other</CAPTION>"
                MyFile.WriteLine "            <DESCRIPTION>Other</DESCRIPTION>"
                MyFile.WriteLine "        </PORTS>"
                MyFile.WriteLine "        <PORTS>"
                MyFile.WriteLine "            <TYPE>USB</TYPE>"
                MyFile.WriteLine "            <NAME>USB 1</NAME>"
                MyFile.WriteLine "            <CAPTION>USB 1 Access Bus (USB)</CAPTION>"
                MyFile.WriteLine "            <DESCRIPTION>Access Bus (USB)</DESCRIPTION>"
                MyFile.WriteLine "        </PORTS>"
                MyFile.WriteLine "        <PORTS>"
                MyFile.WriteLine "            <TYPE>USB</TYPE>"
                MyFile.WriteLine "            <NAME>USB 2</NAME>"
                MyFile.WriteLine "            <CAPTION>USB 2 Access Bus (USB)</CAPTION>"
                MyFile.WriteLine "            <DESCRIPTION>Access Bus (USB)</DESCRIPTION>"
                MyFile.WriteLine "        </PORTS>"
                MyFile.WriteLine "        <PORTS>"
                MyFile.WriteLine "            <TYPE>USB</TYPE>"
                MyFile.WriteLine "            <NAME>USB 3</NAME>"
                MyFile.WriteLine "            <CAPTION>USB 3 Access Bus (USB)</CAPTION>"
                MyFile.WriteLine "            <DESCRIPTION>Access Bus (USB)</DESCRIPTION>"
                MyFile.WriteLine "        </PORTS>"
                MyFile.WriteLine "        <PORTS>"
                MyFile.WriteLine "            <TYPE>USB</TYPE>"
                MyFile.WriteLine "            <NAME>USB 4</NAME>"
                MyFile.WriteLine "            <CAPTION>USB 4 Access Bus (USB)</CAPTION>"
                MyFile.WriteLine "            <DESCRIPTION>Access Bus (USB)</DESCRIPTION>"
                MyFile.WriteLine "        </PORTS>"
                MyFile.WriteLine "        <PORTS>"
                MyFile.WriteLine "            <TYPE>Keyboard Port</TYPE>"
                MyFile.WriteLine "            <NAME>KEYBOARD</NAME>"
                MyFile.WriteLine "            <CAPTION>KEYBOARD PS/2</CAPTION>"
                MyFile.WriteLine "            <DESCRIPTION>PS/2</DESCRIPTION>"
                MyFile.WriteLine "        </PORTS>"
                MyFile.WriteLine "        <PORTS>"
                MyFile.WriteLine "            <TYPE>Mouse Port</TYPE>"
                MyFile.WriteLine "            <NAME>MOUSE</NAME>"
                MyFile.WriteLine "            <CAPTION>MOUSE PS/2</CAPTION>"
                MyFile.WriteLine "            <DESCRIPTION>PS/2</DESCRIPTION>"
                MyFile.WriteLine "        </PORTS>"
                MyFile.WriteLine "        <PORTS>"
                MyFile.WriteLine "            <TYPE>Audio Port</TYPE>"
                MyFile.WriteLine "            <NAME>Microphone Jack</NAME>"
                MyFile.WriteLine "            <CAPTION>Microphone Jack Mini-jack (headphones)</CAPTION>"
                MyFile.WriteLine "            <DESCRIPTION>Mini-jack (headphones)</DESCRIPTION>"
                MyFile.WriteLine "        </PORTS>"
                MyFile.WriteLine "        <PORTS>"
                MyFile.WriteLine "            <TYPE>Audio Port</TYPE>"
                MyFile.WriteLine "            <NAME>Headphone Jack</NAME>"
                MyFile.WriteLine "            <CAPTION>Headphone Jack Mini-jack (headphones)</CAPTION>"
                MyFile.WriteLine "            <DESCRIPTION>Mini-jack (headphones)</DESCRIPTION>"
                MyFile.WriteLine "        </PORTS>"
                MyFile.WriteLine "        <PORTS>"
                MyFile.WriteLine "            <TYPE>Network Port</TYPE>"
                MyFile.WriteLine "            <NAME>Ethernet</NAME>"
                MyFile.WriteLine "            <CAPTION>Ethernet RJ-45</CAPTION>"
                MyFile.WriteLine "            <DESCRIPTION>RJ-45</DESCRIPTION>"
                MyFile.WriteLine "        </PORTS>"
            Case "R10L"
                MyFile.WriteLine "        <MEMORIES>"
                MyFile.WriteLine "            <CAPTION>System Memory</CAPTION>"
                MyFile.WriteLine "            <CAPACITY>" & rows.Fields("ram") & "</CAPACITY>"
                MyFile.WriteLine "            <PURPOSE>System Memory</PURPOSE>"
                MyFile.WriteLine "            <TYPE>DDR2</TYPE>"
                MyFile.WriteLine "        </MEMORIES>"
                MyFile.WriteLine "        <MEMORIES>"
                MyFile.WriteLine "            <CAPTION>Flash Memory</CAPTION>"
                MyFile.WriteLine "            <DESCRIPTION>SYSTEM ROM (No ECC)</DESCRIPTION>"
                MyFile.WriteLine "            <CAPACITY>" & rows.Fields("StorageTotalSpace") & "</CAPACITY>"
                MyFile.WriteLine "            <PURPOSE>Flash Memory</PURPOSE>"
                MyFile.WriteLine "            <TYPE>Flash</TYPE>"
                MyFile.WriteLine "            <SERIALNUMBER>SYSTEM ROM</SERIALNUMBER>"
                MyFile.WriteLine "        </MEMORIES>"
                MyFile.WriteLine "        <PORTS>"
                MyFile.WriteLine "            <TYPE>Video Port</TYPE>"
                MyFile.WriteLine "            <NAME>DVI-Out</NAME>"
                MyFile.WriteLine "            <CAPTION>DVI-Out Other</CAPTION>"
                MyFile.WriteLine "            <DESCRIPTION>Other</DESCRIPTION>"
                MyFile.WriteLine "        </PORTS>"
                MyFile.WriteLine "        <PORTS>"
                MyFile.WriteLine "            <TYPE>USB</TYPE>"
                MyFile.WriteLine "            <NAME>USB 1</NAME>"
                MyFile.WriteLine "            <CAPTION>USB 1 Access Bus (USB)</CAPTION>"
                MyFile.WriteLine "            <DESCRIPTION>Access Bus (USB)</DESCRIPTION>"
                MyFile.WriteLine "        </PORTS>"
                MyFile.WriteLine "        <PORTS>"
                MyFile.WriteLine "            <TYPE>USB</TYPE>"
                MyFile.WriteLine "            <NAME>USB 2</NAME>"
                MyFile.WriteLine "            <CAPTION>USB 2 Access Bus (USB)</CAPTION>"
                MyFile.WriteLine "            <DESCRIPTION>Access Bus (USB)</DESCRIPTION>"
                MyFile.WriteLine "        </PORTS>"
                MyFile.WriteLine "        <PORTS>"
                MyFile.WriteLine "            <TYPE>USB</TYPE>"
                MyFile.WriteLine "            <NAME>USB 3</NAME>"
                MyFile.WriteLine "            <CAPTION>USB 3 Access Bus (USB)</CAPTION>"
                MyFile.WriteLine "            <DESCRIPTION>Access Bus (USB)</DESCRIPTION>"
                MyFile.WriteLine "        </PORTS>"
                MyFile.WriteLine "        <PORTS>"
                MyFile.WriteLine "            <TYPE>USB</TYPE>"
                MyFile.WriteLine "            <NAME>USB 4</NAME>"
                MyFile.WriteLine "            <CAPTION>USB 4 Access Bus (USB)</CAPTION>"
                MyFile.WriteLine "            <DESCRIPTION>Access Bus (USB)</DESCRIPTION>"
                MyFile.WriteLine "        </PORTS>"
                MyFile.WriteLine "        <PORTS>"
                MyFile.WriteLine "            <TYPE>USB</TYPE>"
                MyFile.WriteLine "            <NAME>USB 5</NAME>"
                MyFile.WriteLine "            <CAPTION>USB 5 Access Bus (USB)</CAPTION>"
                MyFile.WriteLine "            <DESCRIPTION>Access Bus (USB)</DESCRIPTION>"
                MyFile.WriteLine "        </PORTS>"
                MyFile.WriteLine "        <PORTS>"
                MyFile.WriteLine "            <TYPE>USB</TYPE>"
                MyFile.WriteLine "            <NAME>USB 6</NAME>"
                MyFile.WriteLine "            <CAPTION>USB 6 Access Bus (USB)</CAPTION>"
                MyFile.WriteLine "            <DESCRIPTION>Access Bus (USB)</DESCRIPTION>"
                MyFile.WriteLine "        </PORTS>"
                MyFile.WriteLine "        <PORTS>"
                MyFile.WriteLine "            <TYPE>Serial Port 16550A Compatible</TYPE>"
                MyFile.WriteLine "            <NAME>SERIAL A</NAME>"
                MyFile.WriteLine "            <CAPTION>SERIAL A DB-9 pin male</CAPTION>"
                MyFile.WriteLine "            <DESCRIPTION>DB-9 pin male</DESCRIPTION>"
                MyFile.WriteLine "        </PORTS>"
                MyFile.WriteLine "        <PORTS>"
                MyFile.WriteLine "            <TYPE>Serial Port 16550A Compatible</TYPE>"
                MyFile.WriteLine "            <NAME>SERIAL B</NAME>"
                MyFile.WriteLine "            <CAPTION>SERIAL B DB-9 pin male</CAPTION>"
                MyFile.WriteLine "            <DESCRIPTION>DB-9 pin male</DESCRIPTION>"
                MyFile.WriteLine "        </PORTS>"
                MyFile.WriteLine "        <PORTS>"
                MyFile.WriteLine "            <TYPE>Keyboard Port</TYPE>"
                MyFile.WriteLine "            <NAME>KEYBOARD</NAME>"
                MyFile.WriteLine "            <CAPTION>KEYBOARD PS/2</CAPTION>"
                MyFile.WriteLine "            <DESCRIPTION>PS/2</DESCRIPTION>"
                MyFile.WriteLine "        </PORTS>"
                MyFile.WriteLine "        <PORTS>"
                MyFile.WriteLine "            <TYPE>Mouse Port</TYPE>"
                MyFile.WriteLine "            <NAME>MOUSE</NAME>"
                MyFile.WriteLine "            <CAPTION>MOUSE PS/2</CAPTION>"
                MyFile.WriteLine "            <DESCRIPTION>PS/2</DESCRIPTION>"
                MyFile.WriteLine "        </PORTS>"
                MyFile.WriteLine "        <PORTS>"
                MyFile.WriteLine "            <TYPE>Audio Port</TYPE>"
                MyFile.WriteLine "            <NAME>Microphone Jack</NAME>"
                MyFile.WriteLine "            <CAPTION>Microphone Jack Mini-jack (headphones)</CAPTION>"
                MyFile.WriteLine "            <DESCRIPTION>Mini-jack (headphones)</DESCRIPTION>"
                MyFile.WriteLine "        </PORTS>"
                MyFile.WriteLine "        <PORTS>"
                MyFile.WriteLine "            <TYPE>Audio Port</TYPE>"
                MyFile.WriteLine "            <NAME>Headphone Jack</NAME>"
                MyFile.WriteLine "            <CAPTION>Headphone Jack Mini-jack (headphones)</CAPTION>"
                MyFile.WriteLine "            <DESCRIPTION>Mini-jack (headphones)</DESCRIPTION>"
                MyFile.WriteLine "        </PORTS>"
                MyFile.WriteLine "        <PORTS>"
                MyFile.WriteLine "            <TYPE>Network Port</TYPE>"
                MyFile.WriteLine "            <NAME>Ethernet</NAME>"
                MyFile.WriteLine "            <CAPTION>Ethernet RJ-45</CAPTION>"
                MyFile.WriteLine "            <DESCRIPTION>RJ-45</DESCRIPTION>"
                MyFile.WriteLine "        </PORTS>"
                MyFile.WriteLine "        <VIDEOS>"
                MyFile.WriteLine "            <NAME>AMD ATI 690E</NAME>"
                MyFile.WriteLine "            <CHIPSET>AMD ATI 690E</CHIPSET>"
                MyFile.WriteLine "        </VIDEOS>"
            Case "T10"
                MyFile.WriteLine "        <MEMORIES>"
                MyFile.WriteLine "            <CAPTION>System Memory</CAPTION>"
                MyFile.WriteLine "            <CAPACITY>" & rows.Fields("ram") & "</CAPACITY>"
                MyFile.WriteLine "            <PURPOSE>System Memory</PURPOSE>"
                MyFile.WriteLine "            <TYPE>DDR3</TYPE>"
                MyFile.WriteLine "        </MEMORIES>"
                MyFile.WriteLine "        <MEMORIES>"
                MyFile.WriteLine "            <CAPTION>Flash Memory</CAPTION>"
                MyFile.WriteLine "            <DESCRIPTION>SYSTEM ROM (No ECC)</DESCRIPTION>"
                MyFile.WriteLine "            <CAPACITY>" & rows.Fields("StorageTotalSpace") & "</CAPACITY>"
                MyFile.WriteLine "            <PURPOSE>Flash Memory</PURPOSE>"
                MyFile.WriteLine "            <TYPE>Flash</TYPE>"
                MyFile.WriteLine "            <SERIALNUMBER>SYSTEM ROM</SERIALNUMBER>"
                MyFile.WriteLine "        </MEMORIES>"
                MyFile.WriteLine "        <PORTS>"
                MyFile.WriteLine "            <TYPE>Video Port</TYPE>"
                MyFile.WriteLine "            <NAME>DVI-Out</NAME>"
                MyFile.WriteLine "            <CAPTION>DVI-Out Other</CAPTION>"
                MyFile.WriteLine "            <DESCRIPTION>Other</DESCRIPTION>"
                MyFile.WriteLine "        </PORTS>"
                MyFile.WriteLine "        <PORTS>"
                MyFile.WriteLine "            <TYPE>USB</TYPE>"
                MyFile.WriteLine "            <NAME>USB 1</NAME>"
                MyFile.WriteLine "            <CAPTION>USB 1 Access Bus (USB)</CAPTION>"
                MyFile.WriteLine "            <DESCRIPTION>Access Bus (USB)</DESCRIPTION>"
                MyFile.WriteLine "        </PORTS>"
                MyFile.WriteLine "        <PORTS>"
                MyFile.WriteLine "            <TYPE>USB</TYPE>"
                MyFile.WriteLine "            <NAME>USB 2</NAME>"
                MyFile.WriteLine "            <CAPTION>USB 2 Access Bus (USB)</CAPTION>"
                MyFile.WriteLine "            <DESCRIPTION>Access Bus (USB)</DESCRIPTION>"
                MyFile.WriteLine "        </PORTS>"
                MyFile.WriteLine "        <PORTS>"
                MyFile.WriteLine "            <TYPE>USB</TYPE>"
                MyFile.WriteLine "            <NAME>USB 3</NAME>"
                MyFile.WriteLine "            <CAPTION>USB 3 Access Bus (USB)</CAPTION>"
                MyFile.WriteLine "            <DESCRIPTION>Access Bus (USB)</DESCRIPTION>"
                MyFile.WriteLine "        </PORTS>"
                MyFile.WriteLine "        <PORTS>"
                MyFile.WriteLine "            <TYPE>USB</TYPE>"
                MyFile.WriteLine "            <NAME>USB 4</NAME>"
                MyFile.WriteLine "            <CAPTION>USB 4 Access Bus (USB)</CAPTION>"
                MyFile.WriteLine "            <DESCRIPTION>Access Bus (USB)</DESCRIPTION>"
                MyFile.WriteLine "        </PORTS>"
                MyFile.WriteLine "        <PORTS>"
                MyFile.WriteLine "            <TYPE>Audio Port</TYPE>"
                MyFile.WriteLine "            <NAME>Microphone Jack</NAME>"
                MyFile.WriteLine "            <CAPTION>Microphone Jack Mini-jack (headphones)</CAPTION>"
                MyFile.WriteLine "            <DESCRIPTION>Mini-jack (headphones)</DESCRIPTION>"
                MyFile.WriteLine "        </PORTS>"
                MyFile.WriteLine "        <PORTS>"
                MyFile.WriteLine "            <TYPE>Audio Port</TYPE>"
                MyFile.WriteLine "            <NAME>Headphone Jack</NAME>"
                MyFile.WriteLine "            <CAPTION>Headphone Jack Mini-jack (headphones)</CAPTION>"
                MyFile.WriteLine "            <DESCRIPTION>Mini-jack (headphones)</DESCRIPTION>"
                MyFile.WriteLine "        </PORTS>"
                MyFile.WriteLine "        <PORTS>"
                MyFile.WriteLine "            <TYPE>Network Port</TYPE>"
                MyFile.WriteLine "            <NAME>Ethernet</NAME>"
                MyFile.WriteLine "            <CAPTION>Ethernet RJ-45</CAPTION>"
                MyFile.WriteLine "            <DESCRIPTION>RJ-45</DESCRIPTION>"
                MyFile.WriteLine "        </PORTS>"
                If Len(rows.Fields("SoundCard")) > 0 Then
                    MyFile.WriteLine "        <SOUNDS>"
                    MyFile.WriteLine "            <NAME>" & rows.Fields("SoundCard") & "</NAME>"
                    MyFile.WriteLine "        </SOUNDS>"
                End If
                If Len(rows.Fields("VideoCard")) > 0 Then
                    MyFile.WriteLine "        <VIDEOS>"
                    MyFile.WriteLine "            <NAME>" & rows.Fields("VideoCard") & "</NAME>"
                    MyFile.WriteLine "        </VIDEOS>"
                End If
            Case Else
                MyFile.WriteLine "        <MEMORIES>"
                MyFile.WriteLine "            <CAPTION>System Memory</CAPTION>"
                MyFile.WriteLine "            <CAPACITY>" & rows.Fields("ram") & "</CAPACITY>"
                MyFile.WriteLine "            <PURPOSE>System Memory</PURPOSE>"
                MyFile.WriteLine "        </MEMORIES>"
                If rows.Fields("StorageDescription")="Disk On Chip" Then
                    MyFile.WriteLine "        <MEMORIES>"
                    MyFile.WriteLine "            <CAPTION>Flash Memory</CAPTION>"
                    MyFile.WriteLine "            <DESCRIPTION>SYSTEM ROM</DESCRIPTION>"
                    MyFile.WriteLine "            <CAPACITY>" & rows.Fields("StorageTotalSpace") & "</CAPACITY>"
                    MyFile.WriteLine "            <PURPOSE>Flash Memory</PURPOSE>"
                    MyFile.WriteLine "            <TYPE>Flash</TYPE>"
                    MyFile.WriteLine "            <SERIALNUMBER>SYSTEM ROM</SERIALNUMBER>"
                    MyFile.WriteLine "        </MEMORIES>"
                Else
                    MyFile.WriteLine "        <STORAGES>"
                    MyFile.WriteLine "            <MANUFACTURER>Wyse</MANUFACTURER>"
                    MyFile.WriteLine "            <NAME>Wyse</NAME>"
                    MyFile.WriteLine "            <DISKSIZE>" & rows.Fields("StorageTotalSpace") & "</DISKSIZE>"
                    MyFile.WriteLine "        </STORAGES>"
                End If
                If Len(rows.Fields("SoundCard")) > 0 Then
                    MyFile.WriteLine "        <SOUNDS>"
                    MyFile.WriteLine "            <NAME>" & rows.Fields("SoundCard") & "</NAME>"
                    MyFile.WriteLine "        </SOUNDS>"
                End If
                If Len(rows.Fields("VideoCard")) > 0 Then
                    MyFile.WriteLine "        <VIDEOS>"
                    MyFile.WriteLine "            <NAME>" & rows.Fields("VideoCard") & "</NAME>"
                    MyFile.WriteLine "        </VIDEOS>"
                End If
        End Select
        MyFile.WriteLine "        <ACCOUNTINFO>"
        MyFile.WriteLine "            <KEYNAME>TAG</KEYNAME>"
        MyFile.WriteLine "            <KEYVALUE>NA</KEYVALUE>"
        MyFile.WriteLine "        </ACCOUNTINFO>"
        MyFile.WriteLine "    </CONTENT>"
        MyFile.WriteLine "    <QUERY>INVENTORY</QUERY>"
        MyFile.WriteLine "</REQUEST>"

        ' Fermeture du fichier
        MyFile.Close
        Set MyFile = Nothing

    End If
    ' Enregistrement suivant
    rows.MoveNext
Wend

' Envoi d'un mail si Erreur
If strErr <> "" Then
    strTextBody = "Bonjour, " & vbCRLF & vbCRLF & _
                  "Erreur(s) lors de l'intégration des Wyses dans OCS" & vbCRLF & vbCRLF & _
                  strErr & vbCRLF & vbCRLF & _
                  "Merci de prendre en compte ce souci." & vbCRLF & vbCRLF & _
                  "Cordialement," & vbCRLF & vbCRLF & _
                  "L'équipe gestion de parc"
    ' Envoi du mail
    Call SendMail(strSMTPFrom, strSMTPTo, strSMTPCc, strTextBody, strSubject)
End If

' Fermeture de la connexion et nettoyage des objets
connection.Close
Set rows = Nothing
Set connection = Nothing
Set objFSO = Nothing

Function mac2string(n)
    'mac2string( "0080649517af") = "00:80:64:95:17:AF"
    mac2format=n
    For i = 3 to 15 Step 3
        macLeft=Left(mac2format,i-1)
        macRight=mid(mac2format,i)
        mac2format=macLeft & ":" & macRight
    Next
    mac2string=UCase(mac2format)
End Function

Function ConvertBinIPToDecimal(strBinIP)
  ' Convert binary form of an IP back to decimal

  Dim arrOctets : arrOctets = Split(strBinIP, ".")
  Dim i
  For i = 0 to UBound(arrOctets)
    Dim intOctet : intOctet = 0
    Dim j
    For j = 0 to 7
      Dim intBit : intBit = CInt(Mid(arrOctets(i), j + 1, 1))
      If intBit = 1 Then
        intOctet = intOctet + 2^(7 - j)
      End If
    Next
    arrOctets(i) = CStr(intOctet)
  Next

  ConvertBinIPToDecimal = Join(arrOctets, ".")
End Function

Function ConvertIPToBinary(strIP)
  ' Converts an IP Address into Binary

  Dim arrOctets : arrOctets = Split(strIP, ".")
  Dim i
  For i = 0 to UBound(arrOctets)
    Dim intOctet : intOctet = CInt(arrOctets(i))
    Dim strBinOctet : strBinOctet = ""
    Dim j
    For j = 0 To 7
      If intOctet And (2^(7 - j)) Then
        strBinOctet = strBinOctet & "1"
      Else
        strBinOctet = strBinOctet & "0"
      End If
    Next
    arrOctets(i) = strBinOctet
  Next
  ConvertIPToBinary = Join(arrOctets, ".")
End Function

Function CalcNetworkAddress(strIP, strMask)
  ' Generates the Network Address from the IP and Mask

  ' Conversion of IP and Mask to binary
  Dim strBinIP : strBinIP = ConvertIPToBinary(strIP)
  'WScript.Echo "strBinIP :      " & strBinIP
  Dim strBinMask : strBinMask = ConvertIPToBinary(strMask)
  'DEBUG - WScript.Echo "strBinMask :    " & strBinMask

  ' Bitwise AND operation (except for the dot)
  Dim i, strBinNetwork
  For i = 1 to Len(strBinIP)
    Dim strIPBit : strIPBit = Mid(strBinIP, i, 1)
    Dim strMaskBit : strMaskBit = Mid(strBinMask, i, 1)

    If strIPBit = "1" And strMaskBit = "1" Then
      strBinNetwork = strBinNetwork & "1"
    ElseIf strIPBit = "." Then
      strBinNetwork = strBinNetwork & strIPBit
    Else
      strBinNetwork = strBinNetwork & "0"
    End If
  Next
  'DEBUG - WScript.Echo "strBinNetwork : " & strBinNetwork

  ' Conversion of Binary IP to Decimal
  CalcNetworkAddress= ConvertBinIPToDecimal(strBinNetwork)
End Function

Function MacToDate (strMac)
' Convert 8 last characters of Mac address to interger and take this interger as a timestamp
' Then replace year by '2012' and format to the device-id date format
' Exple : mac adresse 008064d59814 --> hexa 64d59814
'                     --> timestamp 1691719700 --> 11/08/2023 02:08:20
'                     --> -2012-08-11-02-08-20
'
   strHexa= Right(strMac,8)
   intTimeStamp = CLng("&h" & strHexa)

   dtTimeStamp = DateAdd("s", intTimeStamp, "01/01/1970 00:00:00")
   strFormatDate = FormatDateTime(DateValue(dtTimeStamp))
   strFormatTime = FormatDateTime(TimeValue(dtTimeStamp))
   If Len(strFormatDate) < 10 Then
    strFormatDate = space(10 - Len(strFormatDate)) & strFormatDate
   End If
   If Len(strFormatTime) = 10 Then
    strFormatTime = " " & strFormatTime
   End If
   strFormatDateTime = strFormatDate & " " & strFormatTime
   MacToDate = "-2012-" &_
               Right("00" & Month(strFormatDateTime),2)   & "-" &_
               Right("00" & Day(strFormatDateTime),2)     & "-" &_
               Right("00" & Hour(strFormatDateTime), 2)   & "-" &_
               Right("00" & Minute(strFormatDateTime), 2) & "-" &_
               Right("00" & Second(strFormatDateTime), 2)
End Function

' Fonction SendMail
Function SendMail(strSMTPFrom, strSMTPTo, strSMTPCc, strTextBody, strSubject)
    Dim objMail,objConfig,objFields,strSMTPBcc
    Set objMail = CreateObject("CDO.Message")
    With objMail
        .Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
        .Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = strSMTPRelay
        .Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
        .Configuration.Fields.Update
        .To = strSMTPTo
        .Cc = strSMTPCc
        .Bcc = strSMTPBcc
        .From = strSMTPFrom
        .Subject = strSubject
        .TextBody = strTextBody
        .Send
    End With
End Function