Update isconnected query for Fortinet FortiGate Next-Generation Firewall legacy dataconnector

[thom2804]

Currently, the legacy dataconnector uses the following isconnected query:

CommonSecurityLog
| where DeviceVendor == "Fortinet"
| where DeviceProduct startswith "Fortigate"
| summarize LastLogReceived = max(TimeGenerated)
| project IsConnected = LastLogReceived > ago(7d)

This query has no filter built in to exclude logs from the AMA connector, so when both dataconnectors are present when creating a connection through the AMA connector where the legacy connector doesn't exist anymore. I have already created a new query to replace this and filters out results from the AMA agent.

let securityLog = 
    CommonSecurityLog
    | where DeviceVendor == "Fortinet"
    | where DeviceProduct startswith "Fortigate"
    | extend ingestion_time = bin(TimeGenerated, 1m)
    | join kind=inner (
        Heartbeat
        | where Category != "Azure Monitor Agent"
        | project TimeGenerated, _ResourceId
        | summarize by _ResourceId, ingestion_time = bin(TimeGenerated, 1m)
    ) on _ResourceId, ingestion_time
    | project-away _ResourceId1, ingestion_time, ingestion_time1
    | summarize LastLogReceived = max(TimeGenerated)
    | project IsConnected = LastLogReceived > ago(7d);
let fallback = datatable(IsConnected: bool)
    [
        false
    ];
securityLog
| union fallback
| summarize IsConnected = max(IsConnected)

[v-sudkharat]

Hi @thom2804, Thanks for flagging this issue, we will investigate this issue and get back to you with some updates. Thanks!

[v-sudkharat]

Hi @thom2804, Is your below query helps you to get disconnect the legacy data connector?

let securityLog = 
    CommonSecurityLog
    | where DeviceVendor == "Fortinet"
    | where DeviceProduct startswith "Fortigate"
    | extend ingestion_time = bin(TimeGenerated, 1m)
    | join kind=inner (
        Heartbeat
        | where Category != "Azure Monitor Agent"
        | project TimeGenerated, _ResourceId
        | summarize by _ResourceId, ingestion_time = bin(TimeGenerated, 1m)
    ) on _ResourceId, ingestion_time
    | project-away _ResourceId1, ingestion_time, ingestion_time1
    | summarize LastLogReceived = max(TimeGenerated)
    | project IsConnected = LastLogReceived > ago(7d);
let fallback = datatable(IsConnected: bool)
    [
        false
    ];
securityLog
| union fallback
| summarize IsConnected = max(IsConnected)

[thom2804]

Hi @v-sudkharat yes this query filters out all the results which are collected by the AMA agent, this query needs to be used for the isconnected query on the legacy dataconnector, I cannot change that query myself, this needs to be updated through the content hub with these changes included.

[v-sudkharat]

Hi @thom2804, I have updated the query in solution template, could you please deploy the below file into your env, and let us know if you're getting expected result : Custom Deployment - CustomDeploymentSteps.docx Template file - mainTemplate.json

Thanks!

[v-sudkharat]

Hi @thom2804, Did you get a chance to deploy the above workaround, waiting for your response. Thanks!

[thom2804]

Hi @v-sudkharat I haven't had a chance to try it yet, I am hoping to be able to do so today or tomorrow, I will give you an update once I have had a chance to do so.

[v-sudkharat]

@thom2804, Ok. Thanks!

[thom2804]

Hi @v-sudkharat I have had a chance today to deploy your template and after deleting the Fortigate solution I can confirm that now the legacy dataconnector does indeed show up as disconnected and I can delete it from the environment. Aslong as I dont reinstall the solution from the content hub this works, otherwise it shows back up as connected again.

Can you please deploy this change to the verion available on the content hub? if you could do the same with the template you gave me in #10937 for the Cisco ASA legacy connector then that will also be updated on the content hub to, as I see that is not updated yet neither. Thanks in advance!

[v-sudkharat]

Hi @thom2804, Thank you for your response. Actually, as of now we can't make this change get available into the content hub as it may affect to other customers who are still using this legacy connector. But we will be noted your point and shared with our respective team on this. So, closing this issue. If you still need support for this issue, feel free to re-open it any time. Thank you for your co-operation.