Accounting and billing database schema

The accounting and billing database schema is provided so that you can use a structured query language (SQL) reporting tool to audit and sort data. This tool is also used to generate queries and produce usage reports. The default database name is wgacct.

About the accounting and billing database schema

Note: Be careful when editing the wgacct database. If the data becomes corrupted, the best solution might be to turn off accounting and billing support. Then drop the wgacct tables, and then turn on accounting and billing support.
The information contained in AcctDiscInfo corresponds to packets and bytes transmitted over the link between the SafeLinx Server and the connected client. In contrast, AcctDataInfo contains information for both the original IP/PPP data packets and the fragmented or joined packets that are transmitted. To present this information when fragmentation or joining occur, entries are created in the packet data table as follows:
  • One entry for the IP/PPP data packets with the IP, Encrypted, Compressed, and Reduced field values set and the Frame and Transmit fields set to zero
  • Entries for one or more joined/fragmented packets with the Frame and Transmit fields set and the IP, Encrypted, Compressed, and Reduced field values set to zero

This means that the packet counts in the session table and packet data table for a user session might not agree. However, the session table BytesIn/BytesOut values match the packet data table TransmitSize totals for the user session.

For all tables that include the RDNType field, the type and its corresponding value are:
  • 0 - MobileNI
  • 1 - Datatac5000
  • 2 - Datatac6000
  • 3 - ModacomScr
  • 4 - ArdisX25
  • 5 - ArdisTCP
  • 6 - Rnc3000TCP
  • 7 - MobitexX25
  • 8 - MobitexTCP
  • 9 - OpenRdn
  • 10 - IP
  • 11 - DialPstn
  • 12 - DialIsdn
  • 13 - DialTCP
  • 14 - DataradioMSC
  • 15 - DataradioBDLC
  • 16 - SmsEMI
  • 17 - SmsSMPP
  • 18 - WdpUDP
  • 19 - Cluster
  • 20 - SmtpMail
  • 21 - SmsEMIX25
  • 22 - SmsSMPPX25
  • 23 - SNPP
  • 24 - SmsOIS
  • 25 - SmsOISX25
  • 26 - Reserved
  • 27 - SmsRPA
  • 28 - WCTP
  • 29 - TcpLan
  • 30 - HttpTcpLan

Although the IP address fields can handle an IPv6 address, all IP addresses fields are currently IPv4 addresses.

Table 1. AcctConnInfo - Stores initial connection information.

AcctConnInfo - Stores initial connection information.

Field Name Type Field Size Value Required Zero Length Allowed Comments
AcctType Small integer 2 Yes No Denotes the type of entry:
X'0001'
log on
X'0100'
failed log on
DevAddr Varchar[16] for bit data 5-20 Yes No in6_addr IP address value of the mobile device
DevName Varchar[256] 5-260 No Yes Device identifier string
EventTime Integer 4 Yes No The time_t value for the packet timestamp. The time in which accounting entry was written, expressed as the number of seconds since midnight (0 hour), January 1, 1970.
EventSqlTimeStamp TIMESTAMP / DATE 26 Yes No The value is a TIMESTAMP for DB2® installations or DATE for Oracle installations.
MNC Varchar[17] 5-21 Yes No MNC identifier string
RDNType Char[1] for bit data 1 Yes No Radio data network (RDN®) identifier. See the list at the beginning of the topic.
UserID Varchar[256] 4-260 No Yes User's distinguished name string
WLPVersion Small integer 2 Yes No WLP statement computer version proposed by the SafeLinx Client
PlatformType Small integer 2 Yes No Platform type
  • 1- Reserved
  • 2 - Reserved
  • 3 - Reserved
  • 4 - Reserved
  • 5 - Reserved
  • 6 - Windows 32-bit
  • 7 - Windows FileNet® Content Engine
  • 8 - Reserved
  • 9 - Reserved
  • 10 - Reserved
  • 11 - Reserved
  • 12 - Reserved
  • 13 - Reserved
  • 14 - Reserved
  • 15 - Linux
PlatformString Varchar[256] 4-260 No Yes Description of the client operation system.
ClientVersion Varchar[128] 4-132 No Yes Version and build information of the SafeLinx Client