This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.

You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Syslog messages to MS Sql, parsed out for searchable results.

jpjones over 13 years ago

Works with Cisco ASA 5505 and 5520 , with 8.2 versions.

This script parses Source/Destination IP addresses, ports and interfaces, and puts the results back into Kiwisyslog server, enabling the message to be written to MS SQL server.

Once the messages are in SQL, you can either attach Excel/Pivot Chart/Table to the db, or use the reporting services in SQL to get reports on the error messages, quantities, etc, etc.

Script has been sanitized.

There is some extra stuff in there, as the Case/Select statements where going to be the first method, but changed it to a RegExp to simplify the searching of all the messages.

Hope this helps

Script_Capture_Cisco_ASA_message.vbs

Parents

0 jpjones over 13 years ago

To Set this up.

Step 1 - Export Kiwi Server setting into a INI file and paste this in as a new database format into that file. Re-Import the INI file to Kiwi. Make sure you update the DBFormatCount key to the amount of database formats you have.

DB001-L001=ASA_MSSQLServer_Database
DB001-L002=01|02|03|04|05|06|07|08|09|10|11|12|13|14|15|16|17|18|19|20|21|22|23|24|25|26|27|28|29|30|31|32|33|34
DB001-L003=1
DB001-L004=1|MsgUnique|10|Int Identity|
DB001-L005=0|MsgDate|10|VarChar|YYYY-MM-DD
DB001-L006=0|MsgTime|8|VarChar|HH:MM:SS
DB001-L007=1|MsgDateTime|19|DateTime|YYYY-MM-DD HH:MM:SS
DB001-L008=0|MsgUTCDate|10|VarChar|YYYY-MM-DD
DB001-L009=0|MsgUTCTime|8|VarChar|HH:MM:SS
DB001-L010=1|MsgUTCDateTime|19|DateTime|YYYY-MM-DD HH:MM:SS
DB001-L011=0|MsgTimeMS|3|Int|000
DB001-L012=0|MsgPriorityNum|3|Int|000
DB001-L013=0|MsgFacilityNum|3|Int|000
DB001-L014=0|MsgLevelNum|3|Int|000
DB001-L015=0|MsgPriority|30|VarChar|
DB001-L016=1|MsgFacility|15|VarChar|
DB001-L017=1|MsgLevel|15|VarChar|
DB001-L018=1|MsgHostAddress|15|VarChar|Normal
DB001-L019=1|MsgHostname|255|VarChar|
DB001-L020=0|MsgInputSource|10|VarChar|
DB001-L021=1|MsgText|1024|VarChar|
DB001-L022=1|MsgCode|8|VARCHAR|
DB001-L023=1|MsgSrcIP|15|VarChar|
DB001-L024=1|MsgSrcPort|5|VarChar|
DB001-L025=1|MsgDstIP|15|VarChar|
DB001-L026=1|MsgDstPort|5|VarChar|
DB001-L027=1|MsgSrcInterface|64|VarChar|
DB001-L028=1|MsgDstInterface|64|VarChar|
DB001-L029=0|MsgCustom08|255|VarChar|
DB001-L030=0|MsgCustom09|255|VarChar|
DB001-L031=0|MsgCustom10|255|VarChar|
DB001-L032=0|MsgCustom11|255|VarChar|
DB001-L033=0|MsgCustom12|255|VarChar|
DB001-L034=0|MsgCustom13|255|VarChar|
DB001-L035=0|MsgCustom14|255|VarChar|
DB001-L036=0|MsgCustom15|255|VarChar|
DB001-L037=0|MsgCustom16|255|VarChar|

Step 2. Create and action. that "Runs a script."
Select the script.

Step 3 Create an Action that " logs to database"
Select the new database format created in Step 1.
Must already have the SQL server running and have an appropriate account for Kiwi Syslog Server to access the database server with.
Use the create table function in Syslog server to create the table based on the new format.

should be good to go.
Cancel
Vote Up 0 Vote Down

Cancel

Reply

0 jpjones over 13 years ago

To Set this up.

Step 1 - Export Kiwi Server setting into a INI file and paste this in as a new database format into that file. Re-Import the INI file to Kiwi. Make sure you update the DBFormatCount key to the amount of database formats you have.

DB001-L001=ASA_MSSQLServer_Database
DB001-L002=01|02|03|04|05|06|07|08|09|10|11|12|13|14|15|16|17|18|19|20|21|22|23|24|25|26|27|28|29|30|31|32|33|34
DB001-L003=1
DB001-L004=1|MsgUnique|10|Int Identity|
DB001-L005=0|MsgDate|10|VarChar|YYYY-MM-DD
DB001-L006=0|MsgTime|8|VarChar|HH:MM:SS
DB001-L007=1|MsgDateTime|19|DateTime|YYYY-MM-DD HH:MM:SS
DB001-L008=0|MsgUTCDate|10|VarChar|YYYY-MM-DD
DB001-L009=0|MsgUTCTime|8|VarChar|HH:MM:SS
DB001-L010=1|MsgUTCDateTime|19|DateTime|YYYY-MM-DD HH:MM:SS
DB001-L011=0|MsgTimeMS|3|Int|000
DB001-L012=0|MsgPriorityNum|3|Int|000
DB001-L013=0|MsgFacilityNum|3|Int|000
DB001-L014=0|MsgLevelNum|3|Int|000
DB001-L015=0|MsgPriority|30|VarChar|
DB001-L016=1|MsgFacility|15|VarChar|
DB001-L017=1|MsgLevel|15|VarChar|
DB001-L018=1|MsgHostAddress|15|VarChar|Normal
DB001-L019=1|MsgHostname|255|VarChar|
DB001-L020=0|MsgInputSource|10|VarChar|
DB001-L021=1|MsgText|1024|VarChar|
DB001-L022=1|MsgCode|8|VARCHAR|
DB001-L023=1|MsgSrcIP|15|VarChar|
DB001-L024=1|MsgSrcPort|5|VarChar|
DB001-L025=1|MsgDstIP|15|VarChar|
DB001-L026=1|MsgDstPort|5|VarChar|
DB001-L027=1|MsgSrcInterface|64|VarChar|
DB001-L028=1|MsgDstInterface|64|VarChar|
DB001-L029=0|MsgCustom08|255|VarChar|
DB001-L030=0|MsgCustom09|255|VarChar|
DB001-L031=0|MsgCustom10|255|VarChar|
DB001-L032=0|MsgCustom11|255|VarChar|
DB001-L033=0|MsgCustom12|255|VarChar|
DB001-L034=0|MsgCustom13|255|VarChar|
DB001-L035=0|MsgCustom14|255|VarChar|
DB001-L036=0|MsgCustom15|255|VarChar|
DB001-L037=0|MsgCustom16|255|VarChar|

Step 2. Create and action. that "Runs a script."
Select the script.

Step 3 Create an Action that " logs to database"
Select the new database format created in Step 1.
Must already have the SQL server running and have an appropriate account for Kiwi Syslog Server to access the database server with.
Use the create table function in Syslog server to create the table based on the new format.

should be good to go.
Cancel
Vote Up 0 Vote Down

Cancel

Children

No Data