date: 2017-03-14
The first thing to answer is why are the native tools not preferred?
The frustration is created by the daily log rollover, meaning you would have to repeat searches and filtering for every rule, and repeat for each days worth’s of logs. We can use the console to grep through the logs but if rule orders are changed added or removed, and compounded by rules without names I’m left with only the rule guid which yields no results when I try to find it with the following command;
| fw log -n ./2017-02*.log | egrep -E “Date: | {F46A067D-FAFA-469F-BEEC-DD902A516B31}” |
Therefore via SmartView Tracker I have opened and exported each days logs.
These exported logs are space delimited, therefore I created another job in Talend Studio to iterate over these and to transform into comma delimited format (See Job definition at end of this section).
I download the windows zip version of ElasticSearch (5.2.1) and Kibana (5.2.1) from https://www.elastic.co/downloads and extract them into c:\bin\
# Start elastic search service
C:\bin\elasticsearch-5.2.1\bin\elasticsearch.bat
# Start kibana service
C:\bin\kibana-5.2.1-windows-x86\bin\kibana.bat
With the work I did a year ago to allow import from CSV into ElasticSearch, I forked the csv2es code to allow appending to an index instead, as the default behavior wanted to delete and recreate which is no good when I have several files to import. This can be obtained from https://github.com/kempy007/csv2es with only https://github.com/kempy007/csv2es/blob/master/csv2es.py of interest.
In order to get the tools setup properly I installed Python2.7, and because of the proxy I had to open command prompt as admin, and run the following commands with username and password changed to your details;
set HTTP_PROXY=http://username:password@proxy.local:8080
set HTTPS_PROXY=http://username:password@proxy.local:8080
I was then able install the official csv2es package plus dependancies with the following command;
C:\python27\scripts\easy_install-2.7.exe csv2es
I download my custom csv2es.py to c:\python27\ and CD here, ensure elastic search is running.
# to purge an existing index
c:\Python27>python.exe csv2es.py –index-name cplfiles –delete-index –doc-type none –import-file none
# to create and load first set of data
c:\Python27>python.exe csv2es.py –index-name cplfiles –create-index –doc-type none –import-file c:\Users\kemp\Desktop\MISC\CPLogs\CSV\Feb8.txt.csv
# to append more data to existing index
c:\Python27>python.exe csv2es.py –index-name cplfiles –doc-type none –import-file c:\Users\kemp\Desktop\MISC\CPLogs\CSV\Feb9.txt.csv
Complete the append step on the remaining files.
Ensure the kibana service is running and elastic search is still running. Browse to http://localhost:5601
First time you’ll be nagged to set an index pattern, I just use * and untick ‘Index contains time-based events‘ see ‘Figure 4 - Kibana Index Pattern’
Figure 4 - Kibana Index Pattern
Now on the discover panel, we can 1. Scroll available fields and then 2. Hover over and ‘Add’ until 3. We have ‘timestamp’ ‘Rule’ ‘Protocol’ ‘Source’ ‘Destination’ ‘Action’ ‘Service’ and then you can 4. Search for Rule:x and any other filtering required to perform the analysis of the rulebase, see ‘Figure 5 - Kibana Search/Discover’.
Figure 5 - Kibana Search/Discover
Alternatively omit step 4, and save the search for all rules, then build visualizations using barcharts and metrics for Source, Destination and Service, then use these to build a dashboard. Once in the dashboard, apply filters to review each rule.
Talend Job - CheckpointLogTransform
Jobs
Generated by Talend Open Studio for Data Integration
Project Name
CheckpointLogTransform
GENERATION DATE
22-Feb-2017 11:32:15
AUTHOR
user@talend.com
Talend Open Studio VERSION
6.3.0.20161026_1219
Summary
Project Description
Properties
Values
Name
CheckpointLogTransform
Language
java
Description
Description
Properties
Values
Name
LogTransform
Author
user@talend.com
Version
0.1
Purpose
lol
Status
Description
lol
Creation
20-Feb-2017 10:47:39
Modification
20-Feb-2017 16:55:07
Preview Picture
Settings
Extra settings
Name
Value
COMP_DEFAULT_FILE_DIR
Multi thread execution
false
Implicit tContextLoad
false
Status & Logs
Name
Value
Use statistics (tStatCatcher)
false
Use logs (tLogCatcher)
false
Use volumetrics (tFlowMeterCatcher)
false
On Console
false
On Files
false
On Databases
false
Catch components statistics
false
Catch runtime errors
true
Catch user errors
true
Catch user warnings
true
Context List
ContextDefault
Name
Prompt
Need Prompt?
Type
Value
Source
Component List
Component Name
Component Type
tFileInputDelimited
tFileList
tFileOutputDelimited
tMap
Components Description
Component tFileInputDelimited
UNIQUE NAME
tFileInputDelimited_1
INPUT(S)
LABEL
CheckpointLogFormat
OUTPUT(S)
Component Parameters:
Properties
Values
Unique Name
tFileInputDelimited_1
Component Name
tFileInputDelimited
Version
0.102 (ALPHA)
Family
File/Input
Start
false
Startable
true
SUBTREE_START
false
END_OF_FLOW
false
Activate
true
DUMMY
false
tStatCatcher Statistics
false
Help
org.talend.help.tFileInputDelimited
Update components
true
IREPORT_PATH
JAVA_LIBRARY_PATH
C:\TOS_DI-20161026_1219-V6.3.0\configuration\lib\java
Subjob color
Title color
Property Type
Built-In
File name/Stream
((String)globalMap.get(“tFileList_1_CURRENT_FILEPATH”))
CSV options
true
Row Separator
”\n”
CSV Row Separator
”\n”
Field Separator
” “
Escape char
”””
Text enclosure
”\””
Header
1
Footer
0
Limit
Skip empty rows
false
Uncompress as zip file
false
Die on error
false
REPOSITORY_ALLOW_AUTO_SWITCH
true
Schema
repository: DELIM:CheckpointLogFormat - CLF-metadata
Schema
repository: DELIM:CheckpointLogFormat - CLF-metadata
!!!TEMP_DIR.NAME!!!
“C:/TOS_DI-20161026_1219-V6.3.0/workspace”
Advanced separator (for numbers)
false
Thousands separator
”,”
Decimal separator
”.”
Extract lines at random
false
Number of lines
10
Trim all columns
false
Check column to trim
[{TRIM=false, SCHEMA_COLUMN=Number}, {TRIM=false, SCHEMA_COLUMN=Date}, {TRIM=false, SCHEMA_COLUMN=Time}, {TRIM=false, SCHEMA_COLUMN=Interface}, {TRIM=false, SCHEMA_COLUMN=Origin}, {TRIM=false, SCHEMA_COLUMN=Type}, {TRIM=false, SCHEMA_COLUMN=Action}, {TRIM=false, SCHEMA_COLUMN=Service}, {TRIM=false, SCHEMA_COLUMN=Source_Port}, {TRIM=false, SCHEMA_COLUMN=Source}, {TRIM=false, SCHEMA_COLUMN=Destination}, {TRIM=false, SCHEMA_COLUMN=Protocol}, {TRIM=false, SCHEMA_COLUMN=Rule}, {TRIM=false, SCHEMA_COLUMN=Rule_Name}, {TRIM=false, SCHEMA_COLUMN=Current_Rule_Number}, {TRIM=false, SCHEMA_COLUMN=User}, {TRIM=false, SCHEMA_COLUMN=Information}, {TRIM=false, SCHEMA_COLUMN=Product}, {TRIM=false, SCHEMA_COLUMN=Source_Machine_Name}, {TRIM=false, SCHEMA_COLUMN=Source_User_Name}]
Check each row structure against schema
false
Check date
false
Encoding
“UTF-8”
Split row before field
false
Permit hexadecimal (0xNNN) or octal (0NNNN) for numeric types
false
Decode table
[{DECODE=false, SCHEMA_COLUMN=Number}, {DECODE=false, SCHEMA_COLUMN=Date}, {DECODE=false, SCHEMA_COLUMN=Time}, {DECODE=false, SCHEMA_COLUMN=Interface}, {DECODE=false, SCHEMA_COLUMN=Origin}, {DECODE=false, SCHEMA_COLUMN=Type}, {DECODE=false, SCHEMA_COLUMN=Action}, {DECODE=false, SCHEMA_COLUMN=Service}, {DECODE=false, SCHEMA_COLUMN=Source_Port}, {DECODE=false, SCHEMA_COLUMN=Source}, {DECODE=false, SCHEMA_COLUMN=Destination}, {DECODE=false, SCHEMA_COLUMN=Protocol}, {DECODE=false, SCHEMA_COLUMN=Rule}, {DECODE=false, SCHEMA_COLUMN=Rule_Name}, {DECODE=false, SCHEMA_COLUMN=Current_Rule_Number}, {DECODE=false, SCHEMA_COLUMN=User}, {DECODE=false, SCHEMA_COLUMN=Information}, {DECODE=false, SCHEMA_COLUMN=Product}, {DECODE=false, SCHEMA_COLUMN=Source_Machine_Name}, {DECODE=false, SCHEMA_COLUMN=Source_User_Name}]
!!!DESTINATION.NAME!!!
Min column number of optimize code
100
Label format
CheckpointLogFormat
Hint format
__UNIQUE_NAME__
__COMMENT__
Connection format
row
Show Information
false
Comment
Use an existing validation rule
false
Validation Rule Type
Schema for CLF-metadata :
Column
Key
Type
Length
Precision
Nullable
Comment
Number
false
Integer
2
true
Date
false
String
true
Time
false
String
true
Interface
false
String
6
true
Origin
false
String
10
true
Type
false
String
7
true
Action
false
String
7
true
Service
false
String
10
true
Source_Port
false
String
10
true
Source
false
String
43
true
Destination
false
String
51
true
Protocol
false
String
4
true
Rule
false
String
2
true
Rule_Name
false
String
27
true
Current_Rule_Number
false
String
23
true
User
false
String
true
Information
false
String
69
true
Product
false
String
27
true
Source_Machine_Name
false
String
true
Source_User_Name
false
String
true
Original Function Parameters:
Component tFileList
UNIQUE NAME
tFileList_1
INPUT(S)
none
LABEL
__UNIQUE_NAME__
OUTPUT(S)
Component Parameters:
Properties
Values
Unique Name
tFileList_1
Component Name
tFileList
Version
0.102 (ALPHA)
Family
| File/Management | Orchestration |
Start
true
Startable
true
SUBTREE_START
true
END_OF_FLOW
true
Activate
true
DUMMY
false
tStatCatcher Statistics
false
Help
org.talend.help.tFileList
Update components
true
IREPORT_PATH
JAVA_LIBRARY_PATH
C:\TOS_DI-20161026_1219-V6.3.0\configuration\lib\java
Subjob color
Title color
Directory
“C:/Users/mkemp/Desktop/MISC/CPLogs”
FileList Type
FILES
Includes subdirectories
false
Case Sensitive
YES
Generate Error if no file found
true
Use Glob Expressions as Filemask (Unchecked means Perl5 Regex Expressions)
false
Files
[]
By default
false
By file name
true
By file size
false
By modified date
false
ASC
true
DESC
false
Use Exclude Filemask
false
Exclude Filemask
”*.txt”
Format file path to slash(/) style (useful on Windows)
false
Label format
__UNIQUE_NAME__
Hint format
__UNIQUE_NAME__
__COMMENT__
Connection format
row
Show Information
false
Comment
Use an existing validation rule
false
Validation Rule Type
Original Function Parameters:
Component tFileOutputDelimited
UNIQUE NAME
tFileOutputDelimited_1
INPUT(S)
LABEL
__UNIQUE_NAME__
OUTPUT(S)
none
Component Parameters:
Properties
Values
Unique Name
tFileOutputDelimited_1
Component Name
tFileOutputDelimited
Version
0.101 (ALPHA)
Family
File/Output
Startable
false
SUBTREE_START
false
END_OF_FLOW
true
Activate
true
DUMMY
false
tStatCatcher Statistics
false
Help
org.talend.help.tFileOutputDelimited
Update components
true
IREPORT_PATH
JAVA_LIBRARY_PATH
C:\TOS_DI-20161026_1219-V6.3.0\configuration\lib\java
Subjob color
Title color
Property Type
Built-In
Use Output Stream
false
Output Stream
outputStream
File Name
“C:/Users/mkemp/Desktop/MISC/CPLogs/CSV/”+ ((String)globalMap.get(“tFileList_1_CURRENT_FILE”)) +”.csv”
Row Separator
”\n”
Use OS line separator as row separator when CSV Row Separator is set to CR,LF or CRLF.
true
CSV Row Separator
”\n”
Field Separator
”,”
Append
false
Include Header
true
Compress as zip file
false
REPOSITORY_ALLOW_AUTO_SWITCH
true
Schema
Built-In
Advanced separator (for numbers)
false
Thousands separator
”,”
Decimal separator
”.”
CSV options
true
Escape char
”””
Text enclosure
”””
Create directory if does not exist
true
Split output in several files
false
Rows in each output file
1000
Custom the flush buffer size
false
Row number
1
Output in row mode
false
Encoding
“ISO-8859-15”
Don’t generate empty file
false
Min column number of optimize code
90
Label format
__UNIQUE_NAME__
Hint format
__UNIQUE_NAME__
__COMMENT__
Connection format
row
Show Information
false
Comment
Use an existing validation rule
false
Validation Rule Type
Schema for tFileOutputDelimited_1 :
Column
Key
Type
Length
Precision
Nullable
Comment
Number
false
Integer
2
true
timestamp
false
String
20
true
Date
false
String
8
true
Time
false
String
8
true
Interface
false
String
6
true
Origin
false
String
10
true
Type
false
String
7
true
Action
false
String
7
true
Service
false
String
10
true
Source_Port
false
String
10
true
Source
false
String
43
true
Destination
false
String
51
true
Protocol
false
String
4
true
Rule
false
String
2
true
Rule_Name
false
String
27
true
Current_Rule_Number
false
String
23
true
User
false
String
true
Information
false
String
69
true
Product
false
String
27
true
Source_Machine_Name
false
String
true
Source_User_Name
false
String
true
Original Function Parameters:
Component tMap
UNIQUE NAME
tMap_1
INPUT(S)
LABEL
__UNIQUE_NAME__
OUTPUT(S)
Component Parameters:
Properties
Values
tStatCatcher Statistics
false
Mapping links display as:
AUTO
Temp data directory path:
Max buffer size (nb of rows):
2000000
Ignore trailing zeros for BigDecimal
false
Show Information
false
Comment
Use an existing validation rule
false
Mapper table for tMap_1 ( input ):
Mapper table Properties( row1 ):
Properties
Values
Name
row1
Matching-mode
UNIQUE_MATCH
isMinimized
false
isReject
false
isRejectInnerJoin
false
isInnerJoin
false
expressionFilter
null
Metadata Table Entries( row1 ):
Name
Type
Expression
isNullable
Number
Integer
true
Date
String
true
Time
String
true
Interface
String
true
Origin
String
true
Type
String
true
Action
String
true
Service
String
true
Source_Port
String
true
Source
String
true
Destination
String
true
Protocol
String
true
Rule
String
true
Rule_Name
String
true
Current_Rule_Number
String
true
User
String
true
Information
String
true
Product
String
true
Source_Machine_Name
String
true
Source_User_Name
String
true
Constraint Table Entries( row1 ):
Name
Type
Expression
isNullable
Mapper table for tMap_1 ( output ):
Mapper table Properties( out1 ):
Properties
Values
Name
out1
Matching-mode
isMinimized
false
isReject
false
isRejectInnerJoin
false
isInnerJoin
false
expressionFilter
null
Metadata Table Entries( out1 ):
Name
Type
Expression
isNullable
Number
Integer
row1.Number
true
timestamp
String
TalendDate.formatDate(“yyyy-MM-dd”,(TalendDate.parseDateLocale(“ddMMMyyyy”,row1.Date,”EN”)) ) + (“T”) + row1.Time
true
Date
String
row1.Date
true
Time
String
row1.Time
true
Interface
String
row1.Interface
true
Origin
String
row1.Origin
true
Type
String
row1.Type
true
Action
String
row1.Action
true
Service
String
row1.Service
true
Source_Port
String
row1.Source_Port
true
Source
String
row1.Source
true
Destination
String
row1.Destination
true
Protocol
String
row1.Protocol
true
Rule
String
row1.Rule
true
Rule_Name
String
row1.Rule_Name
true
Current_Rule_Number
String
row1.Current_Rule_Number
true
User
String
row1.User
true
Information
String
row1.Information
true
Product
String
row1.Product
true
Source_Machine_Name
String
row1.Source_Machine_Name
true
Source_User_Name
String
row1.Source_User_Name
true
Constraint Table Entries( out1 ):
Name
Type
Expression
isNullable
Mapper table for tMap_1 ( var ):
Mapper table Properties( Var ):
Properties
Values
Name
Var
Matching-mode
isMinimized
true
isReject
false
isRejectInnerJoin
false
isInnerJoin
false
expressionFilter
null
Metadata Table Entries( Var ):
Name
Type
Expression
isNullable
Constraint Table Entries( Var ):
Name
Type
Expression
isNullable