FortiSIEM: Concat in Analytics

[Secusaurus]

Dear community,

Maybe anyone else already wrapped his/her around this on FortiSIEM:

I am looking for an aggregation function in the analytics/report generation that behaves like the SQL "concat" command, meaning writing all the values of all rows into the single result row.

My usecase, to make it a little bit clearer:

• From the FortiClient EMS, I receive all endpoint's vulnerabilities (search criteria is Event Type = FortiEMS-Vuln-Detected)
• As Group By/Display Fields, I selected:
  • COUNT DISTINCT( Vulnerability Id )
  • COUNT DISTINCT( Host Name )
  • Product
• This generates a wonderful list of all the products which need to be patched on the endpoints and I can sort it by how many hosts are affected (COUNT DISTINCT( Host Name ) )
• What I cannot see here, is which hosts exactly I need to go to and patch
  • If I used "Host Name" as display field, this would obviously split up my list into dozens of single lines with the same product just on another device
  • I'd prefer keeping the single line, but instead of COUNT DISTINCT( Host Name ) have all the single Host names appear in this column

Is there a way to get there or should I go with the long list and run a script through the downloaded csv then?

Thanks for your ideas!

Best,

Christian

[sioannou · Accepted solution]

Hi Christian, 

In my experience this is not currently supported in FortiSIEM analytics. If you are going through the hustle why not an API query and manipulate the info that way?  

P.S. Either an API to FortiSIEM or a Remediation script that performs the API query on EMS and then posts back the message in the format you would like. 

Sotiris

[sioannou]

As a side note I have looked at the phoenixdb, I could see that the number of vuln is reported against each device in table ph_device, but the vulnerabilities themselves are not. 

Another option would be to segment the users either in a Dynamic Watch list or a Lookup table and use a nested query to minimise the results to High, Medium and Low level vulnerabilities. 

Regards,

S

[Secusaurus]

Hi @sioannou ,

Thanks for your thoughts!

"This is not possible" is a valid answer here - not satisfying, but valid ;)

I agree that I could wrap my head around watchlists or trying to generate Incidents that eventually contain the information I was looking for. However, the main demand was just to have the most simple kind of a list of "what to patch" sent out once a month to the people of MDM.

If there is a clever way our team finds, I will post it here as well.

Best,

Christian

[FSM_FTNT]

Hi Christian,

Can I confirm the output that you would like to see is:

Hosts          |        Vulnerability

hosta, hostb, hostc | CVE-123
hostb, hostc, hostz | CVE-3445

Thanks

Dan

[Secusaurus]

Hi Dan,

Yes, you are absolutely right, that is the table I'd prefer to have.

A MySQL-query would look like this:

SELECT GROUP_CONCAT(`Host Name`), `Vulnerability` FROM ...

Best,

Christian

[FSM_FTNT]

Thanks Christian. I'm looking into this!