<?xml version="1.0" encoding="utf-8"?>
<AlvaoApplication xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" ModelVersion="1">
  <Applications>
    <Application id="1005">
      <Name>Zabbix Connector</Name>
      <UniqueId>da4a7cd4-f9b3-4ea2-b646-629230284fcb</UniqueId>
      <Version>4</Version>
      <AdvancedSettings>
        <Setting>
          <Name>Zabbix.Token</Name>
          <Value />
        </Setting>
        <Setting>
          <Name>Zabbix.Url</Name>
          <Value />
        </Setting>
      </AdvancedSettings>
      <Scripts>
        <Script id="2050">
          <Name>ZabbixConnectorPeriodicAction</Name>
          <Code>using System;
using System.Data;
using Microsoft.Data.SqlClient;
using Alvao.Global;
using Alvao.API.Common;
using Alvao.Apps.API;
using Alvao.API.Common.Model.Database;
using System.Threading;
using System.Collections.Generic;
using Alvao.Context;
using Dapper;
using System.Threading.Tasks;
using System.Net.Http;
using System.Text;
using System.Net.Http.Headers;
using Newtonsoft.Json;
using Newtonsoft.Json.Converters;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Serialization;
using System.Linq;
using static Alvao.API.AM.Model.Kind;
using Alvao.API.Utils;
using static Alvao.Global.ModuleInfo;
using Alvao.API.Common.Model.CustomApps.Requests;

public class PeriodicAction : IPeriodicAction
{
    public const string ConnectorName = "Zabbix";

    public string Name 
    { 
        get =&gt; "PeriodicAction";
        set { }
    }

    private readonly ZabbixConnectorRepository ZabbixConnectorRepository = new ZabbixConnectorRepository();
    private readonly ZabbixClientService ZabbixClientService = new ZabbixClientService();
    private int ImportedObjectsNodeFolderNodeId = 0;
    private Dictionary&lt;int, bool&gt; CheckedTemplatePropMap = new();

    public void OnPeriod(SqlConnection con)
    {
        if(!Activation.IsModuleActivated(ModuleId.MonitoringConnectors))
        {
            Logger.Log.Warn($"{ConnectorName} Connector cannot be used. Module MonitoringConnectors is not activated.");
            return;
        }

        if(string.IsNullOrEmpty(DbProperty.ZabbixToken) || string.IsNullOrEmpty(DbProperty.ZabbixUrl))
        {
            Logger.Log.Warn($"{ConnectorName} Connector is not properly set. Check Zabbix.Token and Zabbix.Url in advanced settings.");
            return;
        }

        try
        {
            Task task = Task.Run(async () =&gt; await CreateNewOrUpdateChangedDevices(CancellationToken.None));
            task.Wait(); 
        }
        catch (Exception ex)
        {
            Logger.Log.Error(ex, $"{ConnectorName} Connector: Error importing devices.");
        }
    }

    private async Task CreateNewOrUpdateChangedDevices(CancellationToken cancellationToken)
    {
        var systemPersonId = Alvao.API.Common.Person.GetSystem().iPersonId;
        DateTime? lastAmIntuneCheckInDateTime = ZabbixConnectorRepository.GetZabbixLastCheckInDevicesDateTime();

        IEnumerable&lt;ZabbixDevice&gt; newOrUpdatedIntuneDevices = await GetNewOrUpdatedDevices(lastAmIntuneCheckInDateTime, cancellationToken);
        cancellationToken.ThrowIfCancellationRequested();

        if (newOrUpdatedIntuneDevices is null || !newOrUpdatedIntuneDevices.Any())
            return;

        CheckOrAddMissingPropertiesToTemplateClasses();
        //ClearScannerPropertyLockoutTable();

        var collisionChecker = new ImportCollisionChecker();
        foreach (var device in newOrUpdatedIntuneDevices.OrderBy(d =&gt; d.ZabbixLastCheckIn))
        {
            cancellationToken.ThrowIfCancellationRequested();

            using var scope = AlvaoContext.GetConnectionScope();
            try
            {
                scope.BeginTransaction();
                tblNode node;
                if (device.NodeId is null || device.NodeId == 0)
                {
                    node = CreateNewDevice(device);
                    if (node.intNodeId == 0)
                        continue;

                    Logger.Log.Info("{0} Connector: Created '{1}': {2}", ConnectorName, device.DeviceType.ToString(), device.Hostname);
                } 
                else 
                { 
                    node = new tblNode() { intNodeId = device.NodeId.Value };
                }              
                if (node is null)
                    throw new NullReferenceException($"Error: Device ID: '{device.ZabbixDeviceId}' not found in DB and creation failed.");

                //RecalculateScannerPropertyLockoutTable(managedDevice, node.lintClassId ?? 0);

                List&lt;(tblKind.KindCode kindCode, object value)&gt; updatedProperties = UpdateDeviceProperties(node.intNodeId, device);
                scope.CommitTransaction();

                foreach(var property in updatedProperties){
                    OnObjectPropertyModifiedRequest req = new OnObjectPropertyModifiedRequest(node.intNodeId, Alvao.API.AM.ObjectProperty.GetDefinition(property.kindCode).intKindId, Alvao.API.Common.Person.GetSystem().iPersonId, property.value);
                    Alvao.API.AM.CustomApps.OnObjectPropertyModified(req);
                }
            }
            catch (NullReferenceException ex)
            {
                Logger.Log.Error(ex, $"{ConnectorName} Connector: Error processing device.");
            }
        }
        Logger.Log.Info("{0} Connector: Processed {1} devices", ConnectorName, newOrUpdatedIntuneDevices?.Count());
    }

    public tblNode GetExistingDeviceNode(ZabbixDevice device)
    {
        if (device.ZabbixDeviceId is null)
            throw new ArgumentNullException("Missing Zabbix ID.");

        tblNode node = ZabbixConnectorRepository.GetDeviceNodeByAnotherProperty((int)KindCode.ZabbixDeviceId, device.ZabbixDeviceId.ToString(), false);

        if (node == null)
        {
            var isSnBlacklisted = ZabbixConnectorRepository.GetBlacklistedValuesForKindcode((int)KindCode.BIOS_SN).Any(blacklisted =&gt; device.SerialNo == blacklisted);
            if (!isSnBlacklisted)
            {
                node = ZabbixConnectorRepository.GetDeviceNodeByAnotherProperty((int)KindCode.BIOS_SN, device.SerialNo, true);
            }

            if (node is null)
            {
                node = ZabbixConnectorRepository.GetDeviceNodeByAnotherProperty((int)KindCode.HostName, device.Hostname, true, string.IsNullOrEmpty(device.SerialNo));
            }                                          
        }
        return node;
    }

    private void CheckOrAddMissingPropertiesToTemplateClasses()
    {
        Dictionary&lt;tblClass.ClassCode, ZabbixDevice.Type&gt; map = new();
        foreach (var classId in ZabbixDevice.ClassMap.Keys)
            map[classId] = ZabbixDevice.ClassMap[classId];

        //add default computer class - if not IN (Desktop,Notebook)
        tblClass.ClassCode defaultComputerClassId = (tblClass.ClassCode)Alvao.API.Common.DbProperty.AMDefaultComputerClass;
        if (!map.ContainsKey(defaultComputerClassId))
            map.Add(defaultComputerClassId, ZabbixDevice.Type.Computer);

        foreach (var classId in map.Keys)
        {
            var deviceType = map[classId];
            if (!Alvao.API.AM.ObjectProperty.TemplateContains((int)classId, (tblKind.KindCode)KindCode.ZabbixDeviceId))
            {
                string kindCodes = string.Join(",", ZabbixDevice.GetPropertyMappingForType(deviceType).Select(x =&gt; ((int)x.Key).ToString()));
                ZabbixConnectorRepository.AddMissingPropsToAmTemplateAndUnify((int)classId, kindCodes);
            }
        }
    }

    private tblNode CreateNewDevice(ZabbixDevice managedDevice)
    {
        if (ImportedObjectsNodeFolderNodeId == 0)
            ImportedObjectsNodeFolderNodeId = ZabbixConnectorRepository.GetOrCreateImportedObjectsFolder();

        int classId = Alvao.API.AM.ObjectType.GetDeviceTypeId(managedDevice.Hostname, managedDevice.Model, 0);
        if (classId == 0)
            classId = Alvao.API.Common.DbProperty.AMDefaultComputerClass;

        if (CheckedTemplatePropMap.TryAdd(classId, true))
        {
            if (!Alvao.API.AM.ObjectProperty.TemplateContains((int)classId, (tblKind.KindCode)KindCode.ZabbixDeviceId))
            {                
                string kindCodes = string.Join(",", ZabbixDevice.GetPropertyMappingForType(ZabbixDevice.Type.Computer).Select(x =&gt; ((int)x.Key).ToString()));
                ZabbixConnectorRepository.AddMissingPropsToAmTemplateAndUnify((int)classId, kindCodes);
            }
        }

        tblNode node = new();
        if(IsComputer(classId))
            node = ZabbixConnectorRepository.CreateSimpleComputer(classId, ImportedObjectsNodeFolderNodeId, managedDevice.Hostname, Alvao.API.Common.Person.GetSystem().iPersonId);
        else
            node = ZabbixConnectorRepository.CreateObjectByClass(classId, ImportedObjectsNodeFolderNodeId, Alvao.API.Common.Person.GetSystem().iPersonId);
        
        return node;
    }

    private bool IsComputer(int classId)
    {
        using(var scope = AlvaoContext.GetConnectionScope())
        return scope.Connection.ExecuteScalar&lt;bool?&gt;("select bComputer from tblClass where intClassId=@classId", new { classId }, scope.Transaction) ?? false;
    }

    private List&lt;(tblKind.KindCode kindCode, object value)&gt; UpdateDeviceProperties(int nodeId, ZabbixDevice device)
    {
        List&lt;(tblKind.KindCode kindCode, object value)&gt; codeValue = new List&lt;(tblKind.KindCode kindCode, object value)&gt;();
        using (var scope = AlvaoContext.GetConnectionScope())
        {
            scope.BeginTransaction();
            foreach (var mapping in device.GetPropertyMapping())
            {        
                object value = typeof(ZabbixDevice).GetProperty(mapping.Value)?.GetValue(device);      

                if(value is null)
                    continue;

                string strValue = value is DateTime? ? ((DateTime)value).ToString("yyyy-MM-ddTHH:mm:ssZ") : value.ToString();

                bool shouldLog = (tblKind.KindCode)mapping.Key != tblKind.KindCode.ZabbixLastCheckIn;
                Alvao.API.AM.ObjectProperty.Update(nodeId, (tblKind.KindCode)mapping.Key, strValue, false, shouldLog);                
                codeValue.Add(((tblKind.KindCode)mapping.Key, strValue));
            }
            scope.CommitTransaction();
        }

        return codeValue;
    }

    private async Task&lt;IEnumerable&lt;ZabbixDevice&gt;&gt; GetNewOrUpdatedDevices(DateTime? lastSync, CancellationToken cancellationToken) {
        var devices = await ZabbixClientService.GetNewOrUpdatedDevices(lastSync, cancellationToken);
        var collisionChecker = new ImportCollisionChecker();
        foreach(var device in devices) 
        {
            tblNode node = GetExistingDeviceNode(device);
            if (node is null) 
            {
                collisionChecker.AddImportedInfo(0, device);
            }
            else 
            {
                device.NodeId = node.intNodeId;
                collisionChecker.AddImportedInfo(node.intNodeId, device, node.ImportDuplicateAlert);
            }                     
        }
        SetAndLogCollisions(collisionChecker);
        var toRemove = collisionChecker.GetCollidingRecords().SelectMany(c =&gt; c.Value.CollidingObjects);
        devices.ToList().RemoveAll(d =&gt; toRemove.Any(r =&gt; d.ZabbixDeviceId == r.ZabbixDeviceId));
        return lastSync is null ? devices : devices.Where(d =&gt; d.ZabbixLastCheckIn &gt; lastSync);
    }

    private void SetAndLogCollisions(ImportCollisionChecker collisionChecker) 
    {
        var collisions = collisionChecker.GetCollidingRecords();
        var systemPersonId = Alvao.API.Common.Person.GetSystem().iPersonId;
        foreach (var collision in collisions)
        {
            if (collisionChecker.ShouldLogCollision(collision.Key))
            {
                var log = collisionChecker.GetCollisionLog(collision.Key, systemPersonId);
                using var scope = AlvaoContext.GetConnectionScope();
                scope.BeginTransaction();
                ZabbixConnectorRepository.InsertIntoObjectLog(log);
                ZabbixConnectorRepository.SetImportConflict(collision.Key, true);
                scope.CommitTransaction();
            } 
        }

        var noMoreColliding = collisionChecker.GetNodesToUnsetCollisionFlag();
        foreach (var nodeId in noMoreColliding)
        {
            ZabbixConnectorRepository.SetImportConflict(nodeId, false);
        }
    }
}

public class ZabbixConnectorRepository 
{
    public DateTime? GetZabbixLastCheckInDevicesDateTime()
    {
        using var scope = AlvaoContext.GetConnectionScope();
        return scope.Connection.ExecuteScalar&lt;DateTime?&gt;(@$"select top 1
            ZabbixLastCheckIn
        from NodeCust nc
        join tblNode n on n.intNodeId = nc.NodeId
        where n.IsHidden = 0
            and isdate(substring(ZabbixLastCheckIn,1,10))=1    --Zabbix values: 2024-07-09T10:44:13.9769700Z
        order by ZabbixLastCheckIn desc", new { kind = KindCode.ZabbixLastCheckIn }, transaction: scope.Transaction);
    }

    public tblNode GetDeviceNodeByAnotherProperty(int keyPropKindCode, string keyPropValue, bool onlyInComputers, bool isBiosSNInSourceEmpty = false)
    {
        using var scope = AlvaoContext.GetConnectionScope();
        return scope.Connection.Query&lt;tblNode&gt;(@$"
            declare @columnName nvarchar(255)
            set @columnName = (select k.ColumnName from tblKind k where k.intKindCode = @kindCode)

            declare @valueType nvarchar(255)
            set @valueType = (select DATA_TYPE from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'NodeCust' and COLUMN_NAME = @columnName)
            if (@valueType = 'varchar' or @valueType = 'nvarchar')
                set @valueType = @valueType + '(' + cast((select CHARACTER_MAXIMUM_LENGTH from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'NodeCust' and COLUMN_NAME = @columnName) as nvarchar(255)) + ')'


            declare @sql nvarchar(max)
            set @sql = '
            select top 1
                n.intNodeId, 
                n.lintClassId,
                n.ImportDuplicateAlert
               -- case when isdate(substring(nc.IntuneLastCheckIn,1,10))=1 then nc.IntuneLastCheckIn else null end as IntuneLastCheckIn
            from tblNode n
                {(onlyInComputers ? "join tblClass c on c.intClassId=n.lintClassId and c.bComputer=1" : "")}
                join ClassKind ck on ck.ClassId=n.lintClassId
                join tblKind k on k.intKindId=ck.KindId and k.intKindCode=@kindCode
                join NodeCust nc on nc.NodeId = n.intNodeId
            where n.IsHidden=0
                and ltrim(rtrim(nc.' + @columnName + '))=@value 
                {(keyPropKindCode == (int)KindCode.HostName &amp;&amp; !isBiosSNInSourceEmpty ? $@"
                    and (nc.BiosSerialNumber is null or nc.BiosSerialNumber in {CreateBlacklistedForDynamicSql(GetCachedBlacklistedValuesForKindcode((int)KindCode.BIOS_SN))}) " : "")}
            order by n.intNodeId asc'

            declare @prmsDecl nvarchar(max)
            set @prmsDecl = N'@kindCode int, @value ' + @valueType

            exec sp_executesql @sql, @prmsDecl, @kindCode, @value",
            new { kindCode = keyPropKindCode, value = (keyPropValue != null) ? keyPropValue.Trim() : null },
            transaction: scope.Transaction).FirstOrDefault();
    }

    private string CreateBlacklistedForDynamicSql(IEnumerable&lt;string&gt; blacklisted)
    {
        StringBuilder sb = new();
        sb.Append('(').Append(string.Join(',', blacklisted.Select(it =&gt; "''" + it + "''"))).Append(')');
        return sb.ToString();
    }

    public IEnumerable&lt;string&gt; GetCachedBlacklistedValuesForKindcode(int kindCode)
    {
        return CacheUtil.GetOrCreateCachedItem("blacklistedValuesForKindCode_" + kindCode, () =&gt; GetBlacklistedValuesForKindcode(kindCode), minutesToCache: 30);
    }

    public IEnumerable&lt;string&gt; GetBlacklistedValuesForKindcode(int kindCode)
    {
        var wbemProp = ObjectWbemProcess.GetWbemEquivalentNameAndClass(kindCode);
        if (wbemProp is null)
        {
            return Enumerable.Empty&lt;string&gt;();
        }
        using (var scope = AlvaoContext.GetConnectionScope())
        {
            return scope.Connection.Query&lt;string&gt;(
                @$"select 
                    txtPropValue
                from tblWbemObjectProcess
                where txtPropName = '{wbemProp.Name}'
                    and txtCLASS = '{wbemProp.Class}'
                ", new { }, scope.Transaction);
        }
    }

    public int GetOrCreateImportedObjectsFolder()
    {        
        using var scope = AlvaoContext.GetConnectionScope();
        return scope.Connection.ExecuteScalar&lt;int&gt;($@"declare @nodeId int
        select top 1
            @nodeId=n.intNodeId
        from tblNode n
        where n.lintClassId=@classId
            and n.IsActive = 1

        if @nodeId is null
        begin
            insert tblNode (lintIconId,intState,txtName,lintClassId)
            select i.intIconId,128,d.txtText,d.lintClassId
            from tblDict d
                join tblIcon i on i.uid=@iconUid
            where d.lintClassId=@classId

            select @nodeId=scope_identity()

            insert tblNodeParent values (@nodeId,@nodeId)
        end

        select @nodeId", new { classId = tblClass.ClassCode.ImportedObjects, iconUid = tblIcon.IconUid.Subnet }, transaction: scope.Transaction);
    }

    public int GetActualComputerCount()
    {        
        using var scope = AlvaoContext.GetConnectionScope();
        return scope.Connection.ExecuteScalar&lt;int&gt;(@"select count(1) cnt 
        from tblNode n
        join tblClass c on c.intClassId=n.lintClassId
        where c.bComputer=1
        and n.IsActive = 1", transaction: scope.Transaction);
    }

    public tblNode CreateSimpleComputer(int classId, int parentNodeId, string hostname, int personId)
    {
        using var scope = AlvaoContext.GetConnectionScope();
        return scope.Connection.QueryFirst&lt;tblNode&gt;(@"declare @pcNodeId int, @setNodeId int
exec Internal.spCreateSimpleComputer @hostname, @parentNodeId, @personId, @classId, 1, @pcNodeId output, @setNodeId output
select @pcNodeId intNodeId, @classId lintClassId",
            new { hostname, parentNodeId, personId, classId }, transaction: scope.Transaction);
    }

    public tblNode CreateObjectByClass(int classId, int importedObjectsNodeFolderNodeId, int personId)
    {
        using var scope = AlvaoContext.GetConnectionScope();
        return scope.Connection.QueryFirst&lt;tblNode&gt;(@"declare @id int
exec @id=spCreateNodeFromTemplate @classId, '', @parentId, @personId
select @id intNodeId, @classId lintClassId", new { classId, parentId = importedObjectsNodeFolderNodeId, personId }, transaction: scope.Transaction);
    }

    public void AddMissingPropsToAmTemplateAndUnify(int classId, string kindCodes)
    {        
        using var scope = AlvaoContext.GetConnectionScope();
            scope.Connection.Execute($@"
if object_id('tempdb..#kinds') is not null
	drop table #kinds

create table #kinds (id int primary key)
insert #kinds (id)
select id from dbo.ftCommaListToTableIds(@kindCodes)

--insert new to template
insert into ClassKind (KindId, ClassId)
select
	k.intKindId,
	@classId classId
from #kinds ids
	join tblKind k on k.intKindCode = ids.id
	left join ClassKind ck on ck.ClassId = @classId and k.intKindId = ck.KindId
where ck.KindId is null
", new { classId, kindCodes }, transaction: scope.Transaction);
        }

    public void InsertIntoObjectLog(tblLog log)
    {
        Logger.Log.Debug(nameof(InsertIntoObjectLog));
        using var scope = AlvaoContext.GetConnectionScope();
        scope.Connection.Execute(@"
            insert into tblLog (lintNodeId, liLogPersonId, dteLog, txtLog)
            values (@lintNodeId, @liLogPersonId, @dteLog, @txtLog)   
        ", new {log.lintNodeId, log.liLogPersonId, log.dteLog, log.txtLog}, transaction: scope.Transaction);  
    }

    public void SetImportConflict(int nodeId, bool hasConflict)
    {
        using var scope = AlvaoContext.GetConnectionScope();
        scope.Connection.Execute(@"
            update tblNode
            set ImportDuplicateAlert = @hasConflict
            where intNodeId = @nodeId
        ", new { nodeId, hasConflict }, transaction: scope.Transaction);
    }
}

public class ZabbixClientService 
{
    private string Token { get; set; }
    private static readonly HttpClient client = new HttpClient();

    private Task&lt;HttpRequestMessage&gt; PrepareRequest(HttpMethod method, string url, object content = null)
    {
        var completeUrl = $"{DbProperty.ZabbixUrl}/{url}";
        var httpRequest = new HttpRequestMessage(method, completeUrl);
        
        if(content != null){
            DefaultContractResolver contractResolver = new DefaultContractResolver
            {
                NamingStrategy = new CamelCaseNamingStrategy()
            };
            var settings = new JsonSerializerSettings { NullValueHandling = NullValueHandling.Ignore, ContractResolver = contractResolver, };

            var strContent =  JsonConvert.SerializeObject(content, settings);

            httpRequest.Content = new StringContent(strContent, Encoding.UTF8, "application/json");
        }
        return Task.FromResult(httpRequest);
    }

    private Task&lt;string&gt; GetToken()
    {
        return Task.FromResult(DbProperty.ZabbixToken);    
    }

    public async Task&lt;IEnumerable&lt;ZabbixDevice&gt;&gt; GetNewOrUpdatedDevices(DateTime? lastCheckInDateTime, CancellationToken cancellationToken)
    {
        var requestBody = 
            new {
                jsonrpc = "2.0",
                @method = "host.get",
                @params = new 
                {
                    selectInventory = new string[]{"serialno_a", "name", "macaddress_a","macaddress_b", "model", "os"},
                    selectHostDiscovery = new string[] {"lastcheck"},
                    selectItems =  new string[] {"name", "lastvalue"}
                },
                id = 1,
                auth = DbProperty.ZabbixToken
            };
        
        var request = await PrepareRequest(HttpMethod.Post, "api_jsonrpc.php", requestBody);
        var response = await client.SendAsync(request);
        var responseContent = await response.Content.ReadAsStringAsync();
        if (response.IsSuccessStatusCode)
        {
            var responseObject = JsonConvert.DeserializeObject&lt;ZabbixResponse&gt;(CleanResponse(responseContent));
            var devices = responseObject.Result;            
            return devices.Select(d =&gt; d.ToZabbixDevice()).ToList();
        }
        else
        {
            throw new Exception($"Unsuccessful response from {PeriodicAction.ConnectorName}: {response.StatusCode} {responseContent}");
        }              
    }

    public string CleanResponse(string response)
    {
        return response.Replace(":[]", ":null"); // when eg. hostDiscovery does not contain any data, api sends an empty array (why?!). Really hard to deserialize.
    }
    
}

// MODELS
public record ZabbixResponse(string JsonRpc, IEnumerable&lt;ZabbixApiDevice&gt; Result);
public record HostDiscovery(int HostId, long LastCheck);
public record Item(string Name, string LastValue);
public record Inventory(string SerialNo_a, string MacAddress_a, string MacAddress_b, string Os, string Model);
public class ZabbixApiDevice : ZabbixApiModel
{
    public int HostId { get; set; }
    public string Host { get; set; }
    public HostDiscovery HostDiscovery { get; set; }
    public IEnumerable&lt;Item&gt; Items { get; set; }
    public Inventory Inventory { get; set; }

    public override ZabbixDevice ToZabbixDevice()
    {
        return new ZabbixDevice()
        {
            ZabbixDeviceId = HostId.ToString(),
            ZabbixLastCheckIn = HostDiscovery != null ? DateTimeFromEpoch(HostDiscovery.LastCheck) : null,
            Hostname = Host,
            OperatingSystem = Items?.Where(i =&gt; i.Name == "Operating system").FirstOrDefault()?.LastValue ?? Inventory?.Os,
            Model = Inventory?.Model,
            SerialNo = Inventory?.SerialNo_a,
            MacAddresses = GetMacAddresses([Inventory?.MacAddress_a, Inventory?.MacAddress_b]),
            DeviceType = ZabbixDevice.Type.Computer
        };
    }

    public DateTime DateTimeFromEpoch(long ticks){
        return new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc) + TimeSpan.FromSeconds(ticks);
    }
}

public class ZabbixDevice 
{
    public enum Type
    {
        Computer
    }
    public string ZabbixDeviceId { get; set; }
    public DateTime? ZabbixLastCheckIn { get; set; }
    public string Hostname { get; set; }
    public string OperatingSystem { get; set; }  // OS + OS version
    public string Model { get; set; }
    public string SerialNo { get; set; }
    public string MacAddresses { get; set; }
    public Type DeviceType { get; set; }
    public int? NodeId { get; set; }
    
    public static Dictionary&lt;tblClass.ClassCode, Type&gt; ClassMap = new()
    {
        { tblClass.ClassCode.Computer, Type.Computer }
    };

    public static int? GetFirstClassIdForDeviceType(Type type)
    {
        foreach(var classId in ClassMap.Keys)
        {
            if (ClassMap[classId] == type)
                return (int)classId;
        }
        return null;
    }

    public Dictionary&lt;KindCode, string&gt; GetPropertyMapping()
    {
        return GetPropertyMappingForType(DeviceType);
    }

    public static Dictionary&lt;KindCode, string&gt; GetPropertyMappingForType(Type type)
    {
        Dictionary&lt;KindCode, string&gt; mapping = new()
        {
            {KindCode.ZabbixDeviceId, nameof(ZabbixDeviceId)},
            {KindCode.ZabbixLastCheckIn, nameof(ZabbixLastCheckIn)},
            {KindCode.HostName, nameof(Hostname)},
            {KindCode.OperatingSystem, nameof(OperatingSystem)},
            {KindCode.Model, nameof(Model)},
            {KindCode.SerialNo, nameof(SerialNo)},
            {KindCode.MACAddresses, nameof(MacAddresses)},
        };
        return mapping;
    }

    public override string ToString()
    {
            return $"ZabbixDevice [\n" +
           $"  ZabbixDeviceId={ZabbixDeviceId},\n" +
           $"  ZabbixLastCheckIn={ZabbixLastCheckIn},\n" +
           $"  Hostname={Hostname},\n" +
           $"  OperatingSystem={OperatingSystem},\n" +
           $"  Model={Model},\n" +
           $"  SerialNo={SerialNo},\n" +
           $"  DeviceType={DeviceType},\n" +
           $"  MacAddresses={MacAddresses}\n" +
           $"]";
    }
}

public abstract class ZabbixApiModel 
{
    public abstract ZabbixDevice ToZabbixDevice();

    protected string GetMacAddresses (params string[] MacAddresses)
    { 
        return string.Join("; ", MacAddresses.Where(x =&gt; !string.IsNullOrEmpty(x)).Select(x =&gt; FormatMAC(x)));
    }
    
    private static string FormatMAC(string mac)
    {
        if( mac == null)
            return string.Empty;

        if (mac.Length != 12)
            return mac;

        
        return string.Format("{0}:{1}:{2}:{3}:{4}:{5}",
            mac.Substring(0, 2),
            mac.Substring(2, 2),
            mac.Substring(4, 2),
            mac.Substring(6, 2),
            mac.Substring(8, 2),
            mac.Substring(10, 2)).ToUpper();
    }

}

internal static class ObjectWbemProcess
{
    internal class WbemProp
    {
        public string Class { get; set; }
        public string Name { get; set; }

        public WbemProp(string txtclass, string txtname)
        {
            Class = txtclass;
            Name = txtname;
        }
    }
    private static Dictionary&lt;int, WbemProp&gt; KindToWbemMap = new()
        {
            {87, new("BIOS", "SerialNumber") }
        };
    internal static WbemProp GetWbemEquivalentNameAndClass(int kindCode)
    {
        WbemProp wbemProp = null;
        KindToWbemMap.TryGetValue(kindCode, out wbemProp);
        return wbemProp;
    }
}

/*Groups incoming devices based on Alvao object they matched to. Devices that do not exist in Alvao yet are matched to object ID 0.
     * Collisions are detected when two or more incoming devices match to the same Alvao object (ID &gt; 0) or when two or more incoming devices do not match to any Alvao object (ID = 0) but match each other based on BIOS SN or Hostname.
     */
    public class ImportCollisionChecker
    {
        private Dictionary&lt;int, CollisionInfo&gt; MatchMap { get; set; } = new Dictionary&lt;int, CollisionInfo&gt;();

        public void AddImportedInfo(int nodeId, ZabbixDevice zabbixDevice, bool? errorSetBeforeImport = null) 
        {
            var isFirstOccurence = !MatchMap.ContainsKey(nodeId);
            if (isFirstOccurence)
            {
                MatchMap.Add(nodeId, new CollisionInfo());
            }
            var collisionInfo = MatchMap[nodeId];
            if (errorSetBeforeImport is not null &amp;&amp; isFirstOccurence) collisionInfo.PresentBeforeImport = errorSetBeforeImport.Value;
            collisionInfo.CollidingObjects.Add(zabbixDevice);            
        }

        public bool ShouldLogCollision(int nodeId)
        {
            return nodeId &gt; 0 &amp;&amp; HasCollision(nodeId) &amp;&amp; !HadCollisionBeforeImportStarted(nodeId);
        }

        public bool HasCollision(int nodeId)
        {
            MatchMap.TryGetValue(nodeId, out var collisionInfo);
            if (collisionInfo is null) 
            { 
                return false; 
            }
            return collisionInfo.CollidingObjects.Count &gt;= 2;
        }

        public bool HadCollisionBeforeImportStarted(int nodeId)
        {
            MatchMap.TryGetValue(nodeId, out var collisionInfo);
            return collisionInfo?.PresentBeforeImport ?? false;
        }

        public tblLog GetCollisionLog(int nodeId, int personId)
        {
            return new tblLog()
            {
                lintNodeId = nodeId,
                liLogPersonId = personId,
                dteLog = DateTime.UtcNow,
                txtLog = GetCollisionLogMessage(nodeId)
            };
        }

        public Dictionary&lt;int, CollisionInfo&gt; GetCollidingRecords()
        {
            var collisionsOnExistingObjects = MatchMap.Where(it =&gt; it.Key &gt; 0 &amp;&amp; it.Value.CollidingObjects.Count &gt;= 2).ToDictionary(); // two incoming devices matched on one existing AM object (does not matter by which parameter)
            
            if (!MatchMap.ContainsKey(0))
            {
                return collisionsOnExistingObjects;
            }
            
            var collisionsOnObjectsToCreateByBiosSn = MatchMap[0].CollidingObjects.GroupBy(o =&gt; o.SerialNo).Where(gr =&gt; gr.Count() &gt;= 2).SelectMany(gr =&gt; gr); // two incoming devices do not have matching AM object yet, but they match each other based on BIOS SN or Hostname
            var collisionsOnObjectsToCreateByHostname = MatchMap[0].CollidingObjects.GroupBy(o =&gt; o.Hostname).Where(gr =&gt; gr.Count() &gt;= 2).SelectMany(gr =&gt; gr);

            var collisionsOnObjectsToCreate = collisionsOnObjectsToCreateByBiosSn.Union(collisionsOnObjectsToCreateByHostname).DistinctBy(d =&gt; d.ZabbixDeviceId).ToList();
            return collisionsOnExistingObjects.Concat(new Dictionary&lt;int, CollisionInfo&gt;()
            {
                { 0, new CollisionInfo() { PresentBeforeImport = false, CollidingObjects = collisionsOnObjectsToCreate } }
            }).ToDictionary();
        }

        public IEnumerable&lt;int&gt; GetNodesToUnsetCollisionFlag()
        {
            return MatchMap.Where(it =&gt; it.Key &gt; 0 &amp;&amp; it.Value.CollidingObjects.Count == 1 &amp;&amp; it.Value.PresentBeforeImport).Select(it =&gt; it.Key);
        }

        private string GetCollisionLogMessage(int nodeId)
        {
            var sb = new StringBuilder();
            sb.Append("Duplicate devices were found during import. Zabbix device IDs: ");

            var ids = MatchMap[nodeId].CollidingObjects.Select(o =&gt; o.ZabbixDeviceId).ToList();
            var threeDots = "";
            if (ids.Count &gt; 3) threeDots = "...";

            var idsPart = string.Join(", ", ids.Take(3));
            sb.Append($"{idsPart}{threeDots}");
            return sb.ToString();
        }

        public class CollisionInfo
        {
            public bool PresentBeforeImport { get; set; } = false;
            public List&lt;ZabbixDevice&gt; CollidingObjects { get; set; } = [];
        }
    }</Code>
          <IsLibCode>false</IsLibCode>
          <Codesign>kjf8zAn2DA3BUnmnlnN0e2Nrxw1Es6mKoPkdzMJGBTd5Hr3Hv8oGSa471C0n+XYeJKeX1kJT3DmT3utF9KYp888DTbVPFewMk5Q6j+93c8Y1ATczUKDe2QqkyImtuDkOiskr4xVtoqjRp0GejdcVniR/4vdfjckWYTp6UOCH4phHAn620hyk7/OtpEycQ5Nsl9IOVzY636Ee6I1hVyqp919c22jAiR2pHq+inNxtye7Nsw8Eu9am40WQnejNSEwv69PwN0jH7ZMXrWXQb8+BDmdUcuj/ptiKVUOvgqj5LnDf5aGdBroEzh7zK2Kqe5GbCQftzrthLSZ3uVIIxDbEKw==</Codesign>
        </Script>
        <Script id="2051">
          <Name>Logger</Name>
          <Code>using System.IO;
using System;
using NLog;
using Alvao.API.Internal;

public static class Logger 
{
    public static ILogger Log = TenantDiagnosticsLog.Get();
}</Code>
          <IsLibCode>true</IsLibCode>
          <Codesign>sqUrA9TtUJOL3D7RxdMmLlTFaDMtcvgGwDoR4LhBpiCEKrb2zGhBa6eXV8mIlynGBPHZNJ+jmuQzrhKSs33lOTUF7zCW218NsblfdsE6NrwktVYltwV80YwWVCWISBLQ8ROcBnnUOHT4kxRdcqrLgKaQatiWZbFWAg66D6jLLlwI+UuDWr8EEFJse5XbmYsDJNudlHxChiFYWGFT7e0Yyt+PxI4lGOiZfOG9fXfMHFfGCJyrTWtxyFoj/L/UQvZ0S8LfIfow3kSfgzLJvjB/uVgs2z5zhCT3bmQZaf+FFcDkoLhcohEwuXf+zp2ixw5G5/rl9CNUdeA+mDYqS+779w==</Codesign>
        </Script>
        <Script id="2052">
          <Name>OpenObjectInZabbixEntityCommand</Name>
          <Code>using System;
using System.Data;
using Alvao.Global;
using Alvao.API.Common;
using Alvao.API.Common.Model.CustomApps;
using Alvao.Apps.API;
using Alvao.Context;
using Dapper;
using Alvao.API.AM.Model;
using static Alvao.Global.ModuleInfo;

public class OpenInZabbixObjectCommand : IEntityCommand 
{
    public string Id {get; set;}
    public Entity Entity {get; set;}

    public OpenInZabbixObjectCommand()
    {
        Id = "OpenInZabbixObjectCommand";
        Entity = Entity.Object;
    }

    public EntityCommandShowResult Show(int entityId, int personId)
    {   
        int position = 2; 
        string icon = "open_20_regular";
        string name = "Open in Zabbix"; 
        bool show = GetZabbixId(entityId) != null; 
        return new EntityCommandShowResult (show, name, icon, position);
    }

    public CommandResult Run(int entityId, int personId)
    {
        MessageType messageType = MessageType.None;
        string messageText = null; 
        string navigateToUrl = $"{DbProperty.ZabbixUrl}/hostinventories.php?hostid={GetZabbixId(entityId)}";
        return new CommandResult(messageType, messageText, navigateToUrl);
    }
    
    private int? GetZabbixId(int objectId)
    {
        if(string.IsNullOrEmpty(DbProperty.ZabbixUrl) || !Activation.IsModuleActivated(ModuleId.MonitoringConnectors))
            return null;

        using(var scope = AlvaoContext.GetConnectionScope())        
        
        return scope.Connection.ExecuteScalar&lt;int?&gt;($@"
                select ZabbixDeviceId from NodeCust where NodeId=@objectId", new { objectId }, scope.Transaction);
    }
}</Code>
          <IsLibCode>false</IsLibCode>
          <Codesign>Z26bdpvFaIw9lkfYMcAu+2nPcINUXUAqdSA/OD5gMo+OSpnan0BTkhK/lnmFzeWEKi63uJVUIlVEYun6DquifuyNZTBgyBZAJcehjmTC5GXZdKuYlkDbpFH3HCkDDGHumUVNABkvxTIc5dxdtvZ+kkdQYVcoPQAaXjw9+tTLYkNxYA9JIlZM66PK/lBxZKotZ+yvqg1x4laZ8+fsw42TkrTQxX1L/mcUcVKDi+brPoMXCo945L9gjf2QoRZGHnmafVTmgJpEC+jovroGXgt3k5sxo6o168Fh4psG2ouP+jIsAv9PYqb+/X/yq7gx97WHv8Xvw834u/3hQ4vexZoFzw==</Codesign>
        </Script>
        <Script id="2053">
          <Name>OpenTicketInZabbixEntityCommand</Name>
          <Code>using System;
using System.Data;
using Alvao.Global;
using Alvao.API.Common;
using Alvao.API.Common.Model.CustomApps;
using Alvao.Apps.API;
using Alvao.Context;
using Dapper;
using System.Linq;
using static Alvao.Global.ModuleInfo;

public class OpenTicketInZabbixEntityCommand : IEntityCommand 
{
    public string Id {get; set;}
    public Entity Entity {get; set;}

    public OpenTicketInZabbixEntityCommand()
    {
        Id = "OpenTicketInZabbixEntityCommand";
        Entity = Entity.Request;
    }

    public EntityCommandShowResult Show(int entityId, int personId)
    {           
        int position = 2;
        string icon = "open_20_regular" ; 
        string name = "Open in Zabbix" ; 
        bool show = GetZabbixEventData(entityId, personId) != null; 
        return new EntityCommandShowResult (show, name, icon, position);
    }

    public CommandResult Run(int entityId, int personId)
    {
        var eventData = GetZabbixEventData(entityId, personId);
        MessageType messageType = MessageType.None; 
        string messageText = ""; 
        string navigateToUrl = Alvao.API.Common.DbProperty.ZabbixUrl + $"/tr_events.php?triggerid={eventData.TriggerId}&amp;eventid={eventData.EventId}"; //url that opens after executing the command
        return new CommandResult(messageType, messageText, navigateToUrl);
    }

    private ZabbixEventData GetZabbixEventData(int ticketId, int personId)
    {
        if(string.IsNullOrEmpty(DbProperty.ZabbixUrl) || !Activation.IsModuleActivated(ModuleId.MonitoringConnectors) || !PersonRights.IsTicketSolverTeamMember(personId, ticketId))
            return null;

        using var scope = AlvaoContext.GetConnectionScope();
        return scope.Connection.Query&lt;ZabbixEventData&gt;("select zabbixEventId EventId, zabbixTriggerId TriggerId from tHdTicketCust where zabbixTriggerId is not null and zabbixEventId is not null and liHdTicketId = @ticketId", new { ticketId }, scope.Transaction).FirstOrDefault();
    }
}

class ZabbixEventData {
    public string EventId { get; set; }
    public string TriggerId { get; set; }
}</Code>
          <IsLibCode>false</IsLibCode>
          <Codesign>g6MDFNP217HLbjnZpDDmZpUroO/DXAieoIqcJQrkPKA1pyfvsW4bYWcYnITelWKOq6LH1+3KMGBVltRoJl57H7MsqYI7SIql0+Ff353tGESVBrq8L3xfMxOpx7RqKKIjpU6dqeYJjTFD1yjtIK1BSJjiWyZKnScy6MUGXRDaQd+hQJtGTHrEbqBH+yDCim1kPx7SHvXI6ZHs8gKMHs4y4lEbGKuzehRU7+lELOa+CV4HBf3CnAQ8eSokMhxuBzJ4zbFG2JCnA5drD2JW/2ykhlryUNvT1xT3bAc6R/bsYvnXCvLYc5LxFrSOxk08vUZ8E2lmgfORcyLz6YskSm4+XQ==</Codesign>
        </Script>
      </Scripts>
    </Application>
  </Applications>
</AlvaoApplication>