Exporting Events to Files

phClickHouseCSVExport

You can run the phClickHouseCSVExport tool from a Supervisor or Worker node to export events to CSV files. The file will contain these fields: Event Receive Time, Reporting IP, Event Type, and Raw Event Log.

This code block shows the commands that you can use with phClickHouseCSVExport, followed by a table that describes them in more detail.

phClickHouseCSVExport --starttime [Start Time] --endtime [End Time] --outfile [Output file] --deviceip [Reporting Device IP Address] --devicename [Reporting Device Name] --orgid [Organization ID (0 - 4294967295)] --orgname [Organization Name] --eventtype [Event Type]

pHClickHouseCSVExport Command

Description

--starttime [Start Time]

Starting time of events to be exported. It must be in the following format: "YYYY-MM-DD hh:mm:ss". The supported time zone is GMT. Make sure to enclose the Start Time with quotation marks.

Example: phClickHouseCSVExport --outfile /home/user/report.csv --starttime "2022-01-20 10:10:00" --endtime "2022-01-20 11:10:00"

--endtime [End Time]

The end time of events to be exported. It must be in the following format: "YYYY-MM-DD hh:mm:ss". The supported time zone is GMT. Make sure to enclose the End Time with quotation marks.

Example: phClickHouseCSVExport --outfile /home/user/report.csv --starttime "2022-01-20 10:10:00" --endtime "2022-01-20 11:10:00"

--outfile [Output file] The output file where the exported events are saved in FortiSIEM, CSV format.
--deviceip [Reporting Device IP Address]

Provide the IP address of the device with the events to be exported. Only one reporting device IP address is supported. For example, --deviceip 10.1.1.1.

--devicename [Reporting Device Name] Provide the host name of the device with the events to be exported. For example, --devicename router1. Host name is case insensitive.
--orgid [Organization ID] Provide the ID of the organization with the events to be exported. The number can be from 0 to 4294967295.
--orgname [Organization Name] This is used only for Service Provider deployments. Provide the name of the organization with the events to be exported. To specify multiple organizations, enter a command for each organization, for example, --org "Public Bank" --orgname "Private Bank". The organization name is case insensitive.
--eventtype [Event Type] Specify the event types to be exported.

 

phExportESEvent

You can run the phExportESEvent tool from a Supervisor or Worker node to export events to CSV files.

This code block shows the commands that you can use with phExportESEvent, followed by a table that describes them in more detail.

phExportESEvent <ESUrl> <ESPort> <ESDeploymentType> "<ESUser>" "<ESPassword>" <ESIndexName> <ReportingDevIp> <destDir> <splitThreads> <LogLevel>

pHExportESEvent Command

Description

ESUrl The Elasticsearch URL. Example, http://192.0.2.0.
ESPort The Elasticsearch coordinating node port, e.g. 9200.
ESType

Provide the Elasticsearch type.

1: Native

2: AWS Elasticsearch Service

3: Elasticsearch Cloud

ESUser Provide the Elasticsearch username. "" means no username.
ESPassword Provide the Elasticsearch password. "" means no password.
ESIndexName The name of the Elasticsearch index to be exported, for example, fortisiem-event-2020.06.17-1.
ReportDevIp The IP address of the report device to be used to select events to export. "" means select all devices.
destDir The export directory: output_dir.

splitThreads

The number of threads to be used for export, e.g., 10.

logDevel

The debug level for script output printing: INFO or DEBUG.

Example Usage

  • Native Elasticsearch Deployment Example

  • AWS Elasticsearch Service Deployment Example

  • Elasticsearch Cloud Deployment Example

Native Elasticsearch Deployment Example

phExportESEvent https://192.0.2.0 9200 1 "Joe.123--test" "password" fortisiem-event-2021.08.05-1-000001 "192.0.2.4" /archive/ 10 INFO

AWS Elasticsearch Service Deployment Example

phExportESEvent https://search-eesna78-aaaa4ysukru3ui4ayaz2yya3km.us-east-1.es.amazonaws.com 443 2 "key" "secret" fortisiem-event-2021.09.29-1 "" /archive/ 10 INFO

Elasticsearch Cloud Deployment Example

phExportESEvent https://cpaagg33-d11e01.es.us-central1.gcp.cloud.es.io 9243 3 "elastic" "password" fortisiem-event-2021.10.01-1-000001 "" /archive/ 10 INFO

Output File Name Format

When exporting events from all devices, the output file name is like CSVExport_<ES Index Name>_<thread_no>

Example: CSVExport_fortisiem-event-2021.08.30-1_16

When exporting events from one specific device, the output file name is like CSVExport_<ES Index Name>_<reportDevIp>_<thread_no>

Example: CSVExport_fortisiem-event-2021.08.30-1_192.168.20.1_10

Note that each thread will write its own output file and thus if you are using 20 threads, there will be twenty output files. thread_no will be empty if you are using only 1 thread to do export.

Example Files

$ /opt/phoenix/bin/phExportESEvent http://192.0.2.5 "" "" fortisiem-event-2021.08.30-1 "" /opt/phoenix/bin/result/ 20 INFO

The above command will use 20 threads to export events. The result directory will contain the following files, with each thread having its own file.

-rw-rw-r-- 1 admin admin 9396665 Sep  2 14:39 CSVExport_fortisiem-event-2021.08.30-1_16
-rw-rw-r-- 1 admin admin 9412763 Sep  2 14:39 CSVExport_fortisiem-event-2021.08.30-1_19
-rw-rw-r-- 1 admin admin 9442517 Sep  2 14:39 CSVExport_fortisiem-event-2021.08.30-1_17
-rw-rw-r-- 1 admin admin 9433077 Sep  2 14:39 CSVExport_fortisiem-event-2021.08.30-1_14
-rw-rw-r-- 1 admin admin 9435935 Sep  2 14:39 CSVExport_fortisiem-event-2021.08.30-1_7
-rw-rw-r-- 1 admin admin 9413179 Sep  2 14:39 CSVExport_fortisiem-event-2021.08.30-1_9
-rw-rw-r-- 1 admin admin 9363945 Sep  2 14:39 CSVExport_fortisiem-event-2021.08.30-1_10
-rw-rw-r-- 1 admin admin 9386964 Sep  2 14:39 CSVExport_fortisiem-event-2021.08.30-1_18
-rw-rw-r-- 1 admin admin 9397264 Sep  2 14:39 CSVExport_fortisiem-event-2021.08.30-1_13
-rw-rw-r-- 1 admin admin 9436265 Sep  2 14:39 CSVExport_fortisiem-event-2021.08.30-1_11
-rw-rw-r-- 1 admin admin 9422549 Sep  2 14:39 CSVExport_fortisiem-event-2021.08.30-1_8
-rw-rw-r-- 1 admin admin 9422993 Sep  2 14:39 CSVExport_fortisiem-event-2021.08.30-1_4
-rw-rw-r-- 1 admin admin 9416394 Sep  2 14:39 CSVExport_fortisiem-event-2021.08.30-1_15
-rw-rw-r-- 1 admin admin 9386560 Sep  2 14:39 CSVExport_fortisiem-event-2021.08.30-1_3
-rw-rw-r-- 1 admin admin 9442445 Sep  2 14:39 CSVExport_fortisiem-event-2021.08.30-1_5
-rw-rw-r-- 1 admin admin 9355790 Sep  2 14:39 CSVExport_fortisiem-event-2021.08.30-1_12
-rw-rw-r-- 1 admin admin 9396961 Sep  2 14:39 CSVExport_fortisiem-event-2021.08.30-1_0
-rw-rw-r-- 1 admin admin 9336639 Sep  2 14:39 CSVExport_fortisiem-event-2021.08.30-1_2
-rw-rw-r-- 1 admin admin 9381330 Sep  2 14:39 CSVExport_fortisiem-event-2021.08.30-1_6
-rw-rw-r-- 1 admin admin 9371624 Sep  2 14:39 CSVExport_fortisiem-event-2021.08.30-1_1

Exported CSV File Content

The following event fields are exported:

"event receive time", "report device IP","report device name", and "raw event message"

Below is sample output:

1630359024,192.168.19.1,HOST-192.168.19.1,<134>Jul 11 2008 14:38:23: %ASA-6-302014: Teardown TCP connection 14374203 for outside:192.168
.1.146/21 to inside:192.168.1.42/42005 duration 0:00:30 bytes 0 SYN Timeout
1630359026,192.168.19.1,HOST-192.168.19.1,<134>Jul 11 2008 14:39:24: %ASA-6-302016: Teardown UDP connection 14374987 for outside:192.168
.1.126/161 to inside:192.168.1.42/42005 duration 0:02:01 bytes 0
1630359340,192.168.1.2,Sj-Dev-W-FDR-Web-01,<7>Aug 30 14:35:40 Sj-Dev-W-FDR-Web-01 kernel: [28068]: host clock rate change request 3327 -
> 1619
1630359341,192.168.0.30,HOST-192.168.0.30,"<4>kernel:   ""42 02 40 01 00 00 00 00 10 00 00 00 00 00 00 00 """
1630359341,192.168.0.30,HOST-192.168.0.30,<139>httpd[20001]: [error] [client 192.168.20.43] File does not exist: /var/www/html/favicon.i
co
1630359343,192.168.19.1,HOST-192.168.19.1,<134>Jul 11 2008 17:37:02: %ASA-6-302021: Teardown ICMP connection for faddr 192.168.20.15/0 g
addr 192.168.19.1/0 laddr 192.168.19.1/0
1630359344,192.168.0.30,HOST-192.168.0.30,<3>kernel: ATAPI device hdc:
1630359345,192.168.0.30,HOST-192.168.0.30,"<3>kernel:   Cannot read medium - incompatible format -- (asc=0x30, ascq=0x02)"
1630359349,192.168.0.30,HOST-192.168.0.30,<4>kernel: hdc: packet command error: error=0x54
1630359350,192.168.0.30,HOST-192.168.0.30,<4>kernel: ide: failed opcode was 100

phExportEvent

You can run the phExportEvent tool from a Supervisor or Worker node to export events to CSV files. The file will contain these fields:

  • Customer Id (applicable to SP license)

  • Reporting Device IP

  • Reporting Device Name

  • Event Received Time

  • Raw Message

This code block shows the commands that you can use with phExportEvent, followed by a table that describes them in more detail.

phExportEvent {--dest DESTINATION_DIR} {--starttime START_TIME | --relstarttime RELATIVE_START_TIME} {--endtime END_TIME | --relendtime RELATIVE_END_TIME} [--dev DEVICE_NAME] [--org ORGANIZATION_NAME] [-t TIME_ZONE]

 

pHExportEvent Command Description
DESTINATION_DIR Destination directory where the exported event files are saved.
START_TIME Starting time of events to be exported. The format is YYYY-MM-DD HH:MM:SS {+|-} TZ. If TZ is not given, the local time zone of the machine where the script is running will be used. Example: 2010-03-10 23:00:00 -8 means Pacific Standard Time, 23:00:00 03/10/2010. 2010-07-29 10:20:00 +5:30 means India Standard Time 10:20:00 07/29/2010.
RELATIVE_ START_TIME

This must be used together with END_TIME. Starting time of events to be exported is relative backwards to the end time, specified using --endtime END_TIME. The format is

{NUM}{d|h|m}

where NUM is the number of days or hours or minutes. For example, -- relstarttime 5d means the starting time is 5 days prior to the ending time.

END_TIME Ending time of events to be exported. The format is the same as described for START_TIME.
RELATIVE_END_ TIME This must be used together with START_TIME. Ending time of events to be exported is relative forward to the start time, specified using START_TIME. The format is the same that is used for RELATIVE_START_TIME.
DEVICE_NAME Provide the host name or IP address of the device with the events to be exported. Use a comma-separated list to specify multiple IPs or host names, for example, --dev 10.1.1.1,10.10.10.1,router1,router2. Host name is case insensitive.
ORGANIZATION_ NAME This is used only for Service Provider deployments. Provide the name of the organization with the events to be exported. To specify multiple organizations, enter a command for each organization, for example, --org "Public Bank" --org "Private Bank". The organization name is case insensitive.
TIME_ZONE Specifies the time zone used to format the event received time in the exported event files. The format is {+|-}TZ, for example, -8 means Pacific Standard Time, +5:30 means India Standard Time.

TestESSplitter

You can run the TestESSplitter tool from a Supervisor or Worker node to export events from ElasticSearch to FortiSIEM eventDB format. It is located in n /opt/phoenix/bin/.

This code block shows the commands you can use with TestESSplitter followed by a table that describes them in more detail.

TestESSplitter <ESBroker> <ESPort> <ESClusterType<ESUser> <ESPassword> <IndexName> <destDir> <splitThreads> <logLevel>

Example: /opt/phoenix/bin/TestESSplitter https://<destination>/ 443 2 elasticuser elasticpassword fortisiem-event-2021.07.13-1-000001 /archivedirectory 10 INFO

Note: For <destDir>, a trailing slash is mandatory. Example: https://<destDir>/.

TestESSplitter Command Description
ESBroker The IP of ElasticSearch Co-ordinator node.

ESPort

The port used for ElasticSearch.

ESClusterType

The ElasticSearch Cluster type. Values are "1" for Native, "2" for Amazon OpenSearch Service (previously known as Amazon Elasticsearch Service), and "3" for Elastic Cloud.

ESUser The ElasticSearch username for authentication.
ESPassword The ElasticSearch password for authentication.
IndexName Provide an Index name. A new Index is created per day. Here is an example index name, fortisiem-event-2021.05.14-2000-000001 where“fortisiem-event-2021.05.14” is the day and “2000” is the Organization ID. To find a list of indexes, run this command:
curl -XGET '10.10.2.5:9200/_cat/shards?v'
replacing 10.10.2.5 with the IP of a Co-ordinator node.
destDir Destination directory where the exported events are saved in FortiSIEM eventDB format.

splitThreads

Number of threads.

logLevel

INFO or DEBUG level log messages.

See TestESSplitter Example for an example.

Example Usage

TestESSplitter Example

 

[root@fsm]# /opt/phoenix/bin/TestESSplitter 10.10.2.5 "" "" fortisiem-event-2021.05.14-2000-000001 /root/output 10 INFO
 
[PH_MODULE_LOG_LEVEL_CHANGE]:[eventSeverity]=LM_INFO,[procName]=<unknown>,[fileName]=phBaseProcess.cpp,[lineNumber]=675,[oldLogLevel]=2047,[newLogLevel]=424,[phLogDetail]=Module received log level change
[PH_MODULE_LOCAL_CONFIG_LOADED]:[eventSeverity]=LM_INFO,[procName]=<unknown>,[fileName]=phConfigLoader.cpp,[lineNumber]=166,[configName]=global,[phLogDetail]=Module loaded local config successfully
[PH_MODULE_LOCAL_CONFIG_LOADED]:[eventSeverity]=LM_INFO,[procName]=<unknown>,[fileName]=phConfigLoader.cpp,[lineNumber]=166,[configName]=phdatamanager,[phLogDetail]=Module loaded local config successfully
[PH_GENERIC_INFO]:[eventSeverity]=LM_INFO,[procName]=<unknown>,[fileName]=phHttpClientPool.cpp,[lineNumber]=46,[phLogDetail]=phHttpClientPool: init hosts/port/auth/header=10.10.2.5/9200/:****/Content-Type: application/json
*   Trying 10.10.2.5...
* TCP_NODELAY set
* Connected to 10.10.2.5 (10.10.2.5) port 9200 (#0)
> GET / HTTP/1.1
Host: 10.10.2.5:9200
Accept: */*
Content-Type: application/json
 
< HTTP/1.1 200 OK
< content-type: application/json; charset=UTF-8
< content-length: 530
< 
* Connection #0 to host 10.10.2.5 left intact
[PH_GENERIC_INFO]:[eventSeverity]=LM_INFO,[procName]=<unknown>,[fileName]=EventLoader.cpp,[lineNumber]=1732,[phLogDetail]=Elastic init success: http://10.10.2.5:9200/
* Found bundle for host 10.10.2.5: 0x18f0870 [can pipeline]
* Re-using existing connection! (#0) with host 10.10.2.5
* Connected to 10.10.2.5 (10.10.2.5) port 9200 (#0)
> GET /_cat/indices/fortisiem-event-2021.05.14-2000-000001?h=pri,rep,docs.count HTTP/1.1
Host: 10.10.2.5:9200
Accept: */*
Content-Type: application/json
…
…
…
…
 
< 
[PH_GENERIC_INFO]:[eventSeverity]=LM_INFO,[procName]=<unknown>,[fileName]=EventLoader.cpp,[lineNumber]=1974,[phLogDetail]=Elastic succeed hits total 66 for index fortisiem-event-2021.05.14-2000-000001 slice 1 max 10
* Connection #0 to host 10.10.2.5 left intact
[PH_GENERIC_INFO]:[eventSeverity]=LM_INFO,[procName]=<unknown>,[fileName]=EventLoader.cpp,[lineNumber]=1974,[phLogDetail]=Elastic succeed hits total 61 for index fortisiem-event-2021.05.14-2000-000001 slice 8 max 10
[PH_GENERIC_INFO]:[eventSeverity]=LM_INFO,[procName]=<unknown>,[fileName]=EventLoader.cpp,[lineNumber]=2002,[phLogDetail]=Elastic index query completed 0 seconds
[PH_GENERIC_INFO]:[eventSeverity]=LM_INFO,[procName]=<unknown>,[fileName]=EventLoader.cpp,[lineNumber]=2002,[phLogDetail]=Elastic index query completed 0 seconds
< HTTP/1.1 200 OK
< content-type: application/json; charset=UTF-8
< content-length: 47737
< 
* Connection #0 to host 10.10.2.5 left intact
[PH_GENERIC_INFO]:[eventSeverity]=LM_INFO,[procName]=<unknown>,[fileName]=EventLoader.cpp,[lineNumber]=1974,[phLogDetail]=Elastic succeed hits total 53 for index fortisiem-event-2021.05.14-2000-000001 slice 3 max 10
< HTTP/1.1 200 OK
< content-type: application/json; charset=UTF-8
< content-length: 47178
< 
* Connection #0 to host 10.10.2.5 left intact
< HTTP/1.1 200 OK
< content-type: application/json; charset=UTF-8
< content-length: 41910
< 
* Connection #0 to host 10.10.2.5 left intact
< HTTP/1.1 200 OK
< content-type: application/json; charset=UTF-8
< content-length: 53258
< 
* Connection #0 to host 10.10.2.5 left intact
< HTTP/1.1 200 OK
< content-type: application/json; charset=UTF-8
< content-length: 60587
< 
* Connection #0 to host 10.10.2.5 left intact
[PH_GENERIC_INFO]:[eventSeverity]=LM_INFO,[procName]=<unknown>,[fileName]=EventLoader.cpp,[lineNumber]=1974,[phLogDetail]=Elastic succeed hits total 59 for index fortisiem-event-2021.05.14-2000-000001 slice 4 max 10
[PH_GENERIC_INFO]:[eventSeverity]=LM_INFO,[procName]=<unknown>,[fileName]=EventLoader.cpp,[lineNumber]=1974,[phLogDetail]=Elastic succeed hits total 53 for index fortisiem-event-2021.05.14-2000-000001 slice 7 max 10
[PH_GENERIC_INFO]:[eventSeverity]=LM_INFO,[procName]=<unknown>,[fileName]=EventLoader.cpp,[lineNumber]=1974,[phLogDetail]=Elastic succeed hits total 68 for index fortisiem-event-2021.05.14-2000-000001 slice 6 max 10
[PH_GENERIC_INFO]:[eventSeverity]=LM_INFO,[procName]=<unknown>,[fileName]=EventLoader.cpp,[lineNumber]=1974,[phLogDetail]=Elastic succeed hits total 46 for index fortisiem-event-2021.05.14-2000-000001 slice 2 max 10
[PH_GENERIC_INFO]:[eventSeverity]=LM_INFO,[procName]=<unknown>,[fileName]=EventLoader.cpp,[lineNumber]=2002,[phLogDetail]=Elastic index query completed 0 seconds
[PH_GENERIC_INFO]:[eventSeverity]=LM_INFO,[procName]=<unknown>,[fileName]=EventLoader.cpp,[lineNumber]=2002,[phLogDetail]=Elastic index query completed 0 seconds
[PH_GENERIC_INFO]:[eventSeverity]=LM_INFO,[procName]=<unknown>,[fileName]=EventLoader.cpp,[lineNumber]=2002,[phLogDetail]=Elastic index query completed 0 seconds
[PH_GENERIC_INFO]:[eventSeverity]=LM_INFO,[procName]=<unknown>,[fileName]=EventLoader.cpp,[lineNumber]=2002,[phLogDetail]=Elastic index query completed 0 seconds
[PH_GENERIC_INFO]:[eventSeverity]=LM_INFO,[procName]=<unknown>,[fileName]=EventLoader.cpp,[lineNumber]=2002,[phLogDetail]=Elastic index query completed 0 seconds
[PH_GENERIC_INFO]:[eventSeverity]=LM_INFO,[procName]=<unknown>,[fileName]=TestESSplitter.cpp,[lineNumber]=82,[phLogDetail]=Events processed for split: 559 3.15

The result will be eventDB structured directories and files.

[root@fsm]# ls -l /root/output/
total 0
drwx------ 3 root root 22 May 14 15:25 CUSTOMER_2000
[root@fsm]# ls -l /root/output/CUSTOMER_2000/
total 0
drwx------ 3 root root 19 May 14 15:25 internal
[root@fsm]# ls -l /root/output/CUSTOMER_2000/internal/
total 0
drwx------ 3 root root 37 May 14 15:25 18761
[root@fsm]# ls -l /root/output/CUSTOMER_2000/internal/18761/
total 4
drwx------ 12 root root 4096 May 14 15:25 450264-450287-168428094
[root@fsm]# ls -l /root/output/CUSTOMER_2000/internal/18761/450264-450287-168428094/
total 0
drwx------ 3 root root 18 May 14 15:25 seg-1-0-48-1620951010-1620971132
drwx------ 3 root root 18 May 14 15:25 seg-1-1-70-1620950470-1620971172
drwx------ 3 root root 18 May 14 15:25 seg-1-2-35-1620950916-1620971172
drwx------ 3 root root 18 May 14 15:25 seg-1-3-66-1620951819-1620969371
drwx------ 3 root root 18 May 14 15:25 seg-1-4-61-1620950830-1620970642
drwx------ 3 root root 18 May 14 15:25 seg-1-5-59-1620950830-1620971132
drwx------ 3 root root 18 May 14 15:25 seg-1-6-53-1620950482-1620970632
drwx------ 3 root root 18 May 14 15:25 seg-1-7-46-1620951278-1620971182
drwx------ 3 root root 18 May 14 15:25 seg-1-8-53-1620950470-1620970452
drwx------ 3 root root 18 May 14 15:25 seg-1-9-68-1620950650-1620971132