Transcription
Export of audit trail events from Salto softwareVersion 2.0
Salto audit trail export v. 2.0Historic of changesVersionStatusDateAuthorChange description1.0Stable20/12/2011Mikel LarreategiFirst version of the specs.2.0Stable29/12/2011M. L.New field added to the data model of an audittrail event, namely, “OperationDescription”.Table of conte nts1.Introduction.32.Overvie w .33.Data mode l of the Salto audit trail events .34.Audit trail ope ration list .65.CSV file format .76.Macros for time-varying file names .77.Scheduling parameters .8Appendix A.Data types.9-2-
Salto audit trail export v. 2.01.IntroductionThis document is aimed at explaining the specifications of the audit trail export toolwithin the Salto software. This tool allows third parties to easily access audit trailinformation in the form of CSV files.Note that this is not a closed document and, as such, new features will be added inso far as new necessities arise.2.OverviewAudit trail events produced by Salto access control devices are stored within theSalto DB. This information, however, cannot be directly accessed by third parties.To overcome this issue and facilitate access to audit trail information, the Saltosoftware includes a tool for exporting audit trail events.The main characteristics of this export tool are as follows: Audit trail filter: it is possible to specify selection criteria to filter outundesired events and export only the interesting ones to the output files. Forexample, you may want to export audit trail events produced in a certaindoor or by a certain cardholder. Customised content: it is possible to customise the content of export files.For example, you may specify which of the audit trail data fields to includewithin the export files and which to leave out , the order of the columns, theseparator character, etc. Scheduled export: it is possible to schedule an export task according to aspecific date/time planning. For examp le, you may export audit trail eventsevery day at 12:00. CSV file format: the export tool produces CSV f iles, which is a well-knownformat for data analysis.The following sections will explain in more details the export tool.3.Data model of the Salto audit trail eventsSalto access control devices (such as standalone escutcheons and online doors) areprepared to record all the incidences regarding access control such as opening ofdoors and rejections. These incidences are called audit trail events.In its basic format, an audit trail event conveys information regarding w ho, when,where and what: who did produce the incidence? When was it produced? Where orin which door was the incidence produced? And finally, what kind of incidence wasit? Was it an opening, a rejection, ?Apart from this basic information, the data model of a Salto audit trail eventincludes further information: for example, the serial number of the card producingthe event, the UTC time, some general purpose fields of the door, etc. The tablebelow shows all the data fields a Salto audit trail events is comprised of. Note thatnot all these fields needs being exported but you are f ree to select any set of themto ultimately be included in the export file.-3-
Salto audit trail export v. 2.0Field nameEventDateTimeEventDateTimeUTCTypeDatetime (seeAppendix tionLocal date/time at which the event wasproduced.Date/time at which the event was producedregarding the UTC 0 timezone.Operation code (opening, rejection, etc). SeeTable 2 for the list of Salto event codes.In case the event is an opening, this fieldindicates whether it has been produced in theentry reader (0) or the exit reader (1).Description, in English, of the audit trailoperation.Indicates the type of ‘user’ that produced theincidence:0: cardholder.1: door.2: software operator.Textual representation of the user ID thatproduced the event. Its content depends on thetype of user:- If UserType 0, then UserName name of thecardholder.- If UserType 1, then UserName blank.- If UserType 2, then UserName name of thesoftware operator.If UserType 0, then this field contains the ExtIDof the cardholder. Blank, otherwise.If UserType 0, then this field contains a generalpurpose field (#1). Blank, otherwise.If UserType 0, then this field contains a generalpurpose field (#2). Blank, otherwise.If UserType 0, then this field contains a generalpurpose field (#3). Blank, otherwise.If UserType 0, then this field contains a generalpurpose field (#4). Blank, otherwise.If UserType 0, then this field contains a generalpurpose field (#5). Blank, otherwise.Serial number of the card.ID of the card (by defaults, equals to the cardserial number).Name of the door where the incidence wasproduced.General purpose field (#1) of the door.General purpose field (#2) of the door.Table 1: data model of the Salto audit trail event. See Appendix A for further informationabout data format.Each of these fields is explained in more details in the follow ing paragraphs: EventDateTime: date and time when the event was produced regarding thetimezone of the door. See see Appendix A for further information about howdatetime data type is actually represented within the export file. EventDateTimeUTC: date and time when the event was produced regardingthe UTC 0 timezone. This field must only be considered when the Saltosoftware has been configured as having multiple timezones.-4-
Salto audit trail export v. 2.0 OperationID: contains a numeric code representing the action performed ona given door. See Table 2 for the list of available operations. IsExit: certain access pointreaders (entry and exit)leaving. This boolean fieldthe ‘Entry’ reader (0) or on OperationDescription: contains a description in English of the audit trailevent operation or action. UserType: this field indicates the type of entity that produced the event.Three types of entity exist: cardholders (UserType 0) –who produce eventsby means of their cards–, doors (UserType 1) –w hich produce events in anautomatic fashion, for example, automatic openings– and software operators(UserType 2) –who produces events by means of po rtable programmerdevices or remotely from the Salto software–. UserName: textual representation of the identity of the subject whoproduced the event. In case the event was produced by a cardholder(UserType 0) or a software operator (UserType 2), this f ield contains thename of the respective entity. Otherwise, this field is blank. UserExtID: secondary user ID in alphanumeric format for integration with3rd parties. This field is only meaningf ul for cardholders (UserType 0) andis blank for the rest. User GPF1, User GPF2, User GPF3, UserGPF 4 and User GPF5: general purposefields of cardholders (UserType 0). For non-cardholders this field is blank. UserCardSerialNumber: serial number of the card that produced the event.It is represented in Salto format, that is, 14-digit long hexadecimal string.For non-cardholders this field is blank. UserCardID: ID of the card whose owner produced the event. Generally,card ID equals the card serial number but in certain installations it may referto a code stored within the card’s memory. For non-cardholders this field isblank. Door Name: name of the door in which the event was produced. Door GPF 1, Door GPF2: general purpose field of the door in w hich the eventwas produced.devices (such as IP doors) may include up to twoboth for controlling access when entering orindicates whether the action was performed onthe ‘Exit’ reader (1).Note the different types of subjects capable of producing audit trail events at doors.The most obvious one is the cardholder type (UserType 0), which takes place whencards are presented at doors. However, doors themselves may automaticallyproduce audit trail events (UserType 1), for example, when changing clock due tosummer/w inter daylight saving time. In this case, the ‘UserName’ field is left blank.Finally, audit trail events may also be produced by software operators(UserType 2) w ith no card involved, either via online commands from PC or viaportable programmer devices (PPD).-5-
Salto audit trail export v. 2.04.Audit trail operation listThe following table enumerates all the possible event codes implemented by Saltoaccess control 678798081828384858788DescriptionDoor opened: inside handle.Door opened: key.Door opened: key and keyboard.Door opened: multiple guest key.Door opened: unique opening.Door opened: switch.Door opened: mechanical key.Door opened: PPD.Door opened: keyboard.Door opened: spare card (hotel).Door opened: online command.Door most probably opened: key and PIN.Door closed: key.Door closed: key and keyboard.Door closed: keyboard.Door closed: switch.Key inserted (energy saving device).Key removed (energy saving device).Room prepared (energy saving device).Start of privacy.End of privacy.Start of office mode.End of office mode.Hotel guest cancelled.Door programmed with spare key.New hotel guest key.Start of office mode (online).End of office mode (online).Start of forced closing (online).End of forced closing (online).Alarm: intrusion (online).Alarm: tamper (online)Door left opened (DLO).End of DLO (door left opened).End of intrusion.Start of office mode (online).End of office mode (online).End of tamper.Automatic change.Expiration automatically extended (offline).Online peripheral updated.Key updated (online).Key deleted (online).Communication with Salto software lost.Communication with Salto software established.Opening not allowed: key no activated.Opening not allowed: key expired.Opening not allowed: key out of date.Opening not allowed: key not allowed in this door.Opening not allowed: out of time.Opening not allowed: key does not override privacy.Opening not allowed: old hotel guest 33835455336666666
Salto audit trail export v. 5Opening not allowed: hotel guest key cancelled.Opening not allowed: antipassback.Opening not allowed: no associated authorization.Opening not allowed: invalid PIN.Opening not allowed: door in emergency state.Opening not allowed: key cancelled.Opening not allowed: unique opening key already used.Opening not allowed: key with old renovation number.Warning: key has not been completely updated (online).Opening not allowed: run out of battery.Opening not allowed: unable to audit on the key.Opening not allowed: locker occupancy timeout.Opening not allowed: denied by host.Blacklisted key deleted.Closing not allowed: door in emergency state.New renovation code.PPD connection.Time modified (daylight saving time).Low battery level.6666666656666567777Table 2: operation codes (in decimal notation) for Salto audit trail events. Category 1:openings and closings; category 2: actions; category 3: door status changes; category 4:online commands from host; category 5: key modifications (mostly in online units); category6: Rejections; category 7: maintenance; category 8: alarms and warnings.5.CSV file formatCurrently, CSV text file is the only supported format for exporting audit trail eventsfrom the Salto DB. It is expected that, in the future, other formats will beconsidered, such as Excel files or DB tables.What follows are the CSV parameters to be specified within the Salto software for acorrect export operation:6. File name: indicates the name of the file in which the exported audit trailevents will be stored. It may contain special placeholders (also known asmacros) in order to produce a different file name for each export file (moreon this below). Column delimiter: indicates the character that will serve as a delimiterbetween consecutive columns within rows. It defaults to semicolon (;). Text qualifier: specifies which character mark is used to qualify text. Itdefaults to double quotes (“). Column titles: indicates whether or not the first row in the export file willcontain the title of the columns. It defaults to false.Macros for time-varying file namesIn general, the Salto software does not produce a single export file but seve ral. Thefrequency at which these files are produced depends on the specified planning. If allthe resulting files are named the same, f ile overw riting will occur and hence datalost. This problem can be avoided by means of macros.-7-
Salto audit trail export v. 2.0A macro is nothing more than a placeholder w ithin the “File name” parameter. Atexport time, this placeholder is replaced by a string containing time -relatedinformation, for example, current date or time. The table below enumerates thecurrently supported macros:Macro( YEAR)( MONTH)( DAY)( HOUR)( MINUTE)( SECOND)DescriptionCurrent year (4 digits).Current month (2 digits).Current day (2 digits).Current hour (2 digits).Current minute (2 digits).Current second (2 digits).Table 3: list of macros for the name of the export file.For example, the following file name contains two macros, namely, ‘HOUR’ and‘MINUTE’:“Salto Audit Trail ( HOUR)( MINUTE).csv”Let’s imagine that the corresponding export task within the Salto software has beendefined in such a way that produces an export file every half an hour starting from12:00:00 till 15:00:00. This will result in six files w ith the following names:Salto Audit Trail 1200.csv, Salto Audit Trail 1230.csv,Salto Audit Trail 1300.csv, Salto Audit Trail 1330.csv,Salto Audit Trail 1400.csv, Salto Audit Trail 1430.csv,Salto Audit Trail 1500.csv.7.Scheduling parametersExport jobs within the Salto software are executed automatically according to apre-defined day/time scheduling. The parameters for configuring the day/timescheduling are as follows: Valid time interval: specify the time interval during w hich the import job isallowed to be executed. For example, no sooner than 10 AM and no laterthan 11 PM. Execute once: specify whether the import job should be performed just onceor several times during the valid time interval in the same day. Execution time frequency: if the job is allowed to be executed several timesin the same day, then this parameter specifies the time to wait for since lastexecution before trying again. For example, every 10 minutes. Day frequency: specify how often (in terms of days) the sync job will beexecuted. If this parameter is set, you need also to provide the startingdate. For example, every 2 days starting from 1/1/2006.-8-
Salto audit trail export v. 2.0Appendix A. Data typesField values within CSV files must conform to one of the following types:TypeBooleanIntegerAlphanumericDate orDateTimeTimeFormatThe false value is represented with the zero value (e.g., ‘0’).The true value is represented with any integer value different from zero(e.g., ‘1’ for true).An integer value is 4 bytes long and it ranges from -2147483648 to2147483647 (e.g., ‘1234’).A string of any readable ASCII character (e.g., ‘John Smith’).Format: YYYY-MM-DDThh:mm:sswhere ‘YYYY’ means the year, ‘MM’ the month, ‘DD’ the day, ‘hh’ meanshours (from 0 to 24), ‘mm’ the minutes and ‘ss’ the seconds.The ‘-’ character is used as a date separator, ‘:’ as a time separator and ‘T’is the separator between date and time.E.g.: ‘2005-5-10T14:45’ represents May 10th 2005 at quarter to three inthe afternoon.Note that time value can be omitted and it defaults to either 00:00 or24:00 depending on whether it represents a starting date/time or anending date/time value.Format: hh:mm:sswhere ‘hh’ are the hours (from 0 to 24), ‘mm’ the minutes and ‘ss’ theseconds. The ‘:’ character is used as separator.E.g.: ‘23:30:00’ represents half past eleven in the night.Table 4: format of basic data types.-9-
Salto audit trail export v. 2.0 - 3 - 1. Introduction This document is aimed at explaining the specifications of the audit trail export tool within the Salto software. This tool allows third parties to easily access audit trail information in the form of CSV files. Note that this is not a closed document and, as such, new features will be added in
