3.1 Tables

3.1.1 SRS.AD_MEMBERSHIPS

Table 3-1 Active Directory Memberships Table Definition

Column Name

SQL Server Data Type

PosgreSQL Data Type

Notes

id

bigint

bigint

Primary key

group_id

integer

integer

 

member_id

integer

integer

 

3.1.2 SRS.AD_OBJECTS

Table 3-2 Active Directory Objects Table Definition

Column Name

SQL Server Data Type

PosgreSQL Data Type

Notes

id

integer

integer

Primary key

name

nvarchar(256)

varchar(256)

SAM Account Name

fdn

nvarchar(512)

varchar(512)

Full distinguished object name

domain

nvarchar(256)

varchar(256)

Domain name

guid

binary(16)

bytea

Globally Unique Identifier

sid

varbinary(68)

bytea

Security Identifier

object_type

integer

integar

  • 0 = Unknown / Other
  • 1 = User
  • 2 = Group
  • 3 = Computer
  • 4 = Container
  • 5 = Built-in domain
  • 6 = Organization
  • 7 = Organizational Unit
  • 8 = Locality
  • 9 = Domain DNS
  • 10 = Volume
  • 13 = Inet Org Person
  • 16 = Country
  • 17 = Tree Root
  • 18 = Dynamic Group
  • 20 = Alias
  • 21 = eDirectory Pseudo Security Principal

identity_system_id

integer

integer

Reference to primary key of identity_systems table

3.1.3 SRS.EDIR_DS_TRUSTEES

Table 3-3 eDirectory DS Trustees Table Definition

Column Name

SQL Server Data Type

PosgreSQL Data Type

Notes

id

bigint

bigint

Primary key

scan_id

integer

integer

Reference to scans table

object_id

integer

integer

Reference to edir_objects table entry

trustee_id

integer

integer

Reference to edir_objects table entry

rights

integer

integer

Assigned eDirectory DS rights

3.1.4 SRS.EDIR_OBJECTS

Table 3-4 eDirectory Objects Table Definition

Column Name

SQL Server Data Type

PosgreSQL Data Type

Notes

id

integer

integer

Primary key

name

nvarchar(256)

varchar(256)

Common name (CN)

fdn

nvarchar(512)

varchar(512)

Full distinguished name

guid

varbinary(68)

bytea

Globally unique Identifier

object_type

integer

integer

  • 0 = Unknown / Other
  • 1 = User
  • 2 = Group
  • 3 = Computer
  • 4 = Container
  • 5 = Built-in domain
  • 6 = Organization
  • 7 = Organizational Unit
  • 8 = Locality
  • 9 = Domain DNS
  • 10 = Volume
  • 13 = Inet Org Person
  • 16 = Country
  • 17 = Tree Root
  • 18 = Dynamic Group
  • 20 = Alias
  • 21 = eDirectory Pseudo Security Principal

identity_system_id

integer

integer

Reference to identity_systems table

3.1.5 SRS.EDIR_SECURITY_EQUALS

Table 3-5 eDirectory Security Equals Table Definition

Column Name

SQL Server Data Type

PosgreSQL Data Type

Notes

id

bigint

bigint

Primary key

object_id

integer

integer

Reference to edir_objects table

equiv_object_id

integer

integer

Reference to edir_objects table

3.1.6 SRS.IDENTITY_SYSTEMS

Table 3-6 Identity Systems Table Definition

Column Name

SQL Server Data Type

PosgreSQL Data Type

Notes

id

integer

integer

Primary key

type

integer

integer

  • 0 = Unknown
  • 1 = Active Directory
  • 2 = eDirectory
  • 3 = Windows Local

name

nvarchar(256)

varchar(256)

  • Identity system name
  • eDirectory - Tree name
  • Active Directory – Forest FDN

domain

nvarchar(256)

varchar(256)

Active Directory domain

proxy_account

nvarchar(256)

varchar(256)

 

is_primary

bit

boolean

  • 0 = Not the primary identity system
  • 1 = Primary identity system for authentication

is_managed

bit

boolean

  • 0 = Not managed (member server, built-in domain, etc.)
  • 1 = Managed, configured system

last_modified

datetime2(0)

timestamp without timezone

 

3.1.7 SRS.NCP_TRUSTEES

Table 3-7 NCP Trustees Table Definition

Column Name

SQL Server Data Type

PosgreSQL Data Type

Notes

id

bigint

bigint

Primary key

scan_data_id

bigint

bigint

Reference to scan_data table

trustee_guid

varbinary(68)

bytea

Reference to edir_objects (using GUID)

rights

integer

integer

  • 0x01 = Read
  • 0x02 = Write
  • 0x04 = [undefined]
  • 0x08 = Create
  • 0x10 = Delete
  • 0x20 = Access Control
  • 0x40 = File Scan
  • 0x80 = Modify
  • 0x100 = Supervisor

3.1.8 SRS.NTFS_ACES

Table 3-8 NTFS ACEs Table Definition

Column Name

SQL Server Data Type

PosgreSQL Data Type

Notes

id

bigint

bigint

Primary key

scan_data_id

bigint

bigint

Reference to scan_data table

flags

smallint

smallint

  • 0x1 = Object Inherit
  • 0x2 = Container Inherit
  • 0x4 = No Propagate
  • 0x8 = Inherit Only
  • 0x10 = Inherited
  • 0x40 = Successful Access
  • 0x80 = Failed Access

ace_type

smallint

smallint

  • 0 = Access Allowed
  • 1 = Access Denied
  • 2 = System Audit
  • 9 = Allowed Callback
  • 10 = Denied Callback
  • 13 = System Audit Callback
  • 17 = System Mandatory Label

access_mask

integer

integer

  • 0x1 = Read Data / List Directory
  • 0x2 = Write Data / Create File
  • 0x4 = Append Data / Create Subdirectory
  • 0x8 = Read Extended Attributes
  • 0x10 = Write Extended Attributes
  • 0x20 = File Execute / Traverse
  • 0x40 = Delete Child
  • 0x80 = Read Attributes
  • 0x100 = Write Attributes
  • 0x10000 = Delete
  • 0x20000 = Read Permissions
  • 0x40000 = Change Permissions0x80000 = Change Owner
  • 0x100000 = Synchronize
  • 0x1000000 = Access System Security
  • 0x10000000 = Generic All
  • 0x20000000 = Generic Execute
  • 0x40000000 = Generic Write
  • 0x80000000 = Generic Read

sid

varbinary(68)

bytea

Trustee SID

index_on_disk

smallint

smallint

Discovered order of this ACE for the associated entry as read from the file system

canonical_index

smallint

smallint

Preferred order in which ACE should appear for the associated entry

3.1.9 SRS.SCANS

Table 3-9 Scans Table Definition

Column Name

SQL Server Data Type

PosgreSQL Data Type

Notes

id

bigint

bigint

Primary key

scan_policy_id

integer

integer

Reference to scan_policies table

triggered_start_time

datetime2(3)

timestamp without time zone

Initial time scan delegation starts

scan_start_time

datetime2(3)

timestamp without time zone

Start time when agent begins physical scan

scan_stop_time

datetime2(3)

timestamp without time zone

Stop time when agent completes physical scan

enum_start_time

datetime2(3)

timestamp without time zone

Agent metrics related to file system object enumeration

enum_stop_time

datetime2(3)

timestamp without time zone

Agent metrics related to file system object enumeration

enum_file_count

integer

integer

Agent metrics related to file system object enumeration

enum_directory_count

integer

integer

Agent metrics related to file system object enumeration

enum_link_count

integer

integer

Agent metrics related to file system object enumeration

caching_start_time

datetime2(3)

timestamp without time zone

Metrics related to agent caching

caching_stop_time

datetime2(3)

timestamp without time zone

Metrics related to agent caching

cached_file_count

integer

integer

Metrics related to agent caching

cached_directory_count

integer

integer

Metrics related to agent caching

cached_link_count

integer

integer

Metrics related to agent caching

cache_size

integer

integer

Metrics related to agent caching

cache_size_max

integer

integer

Metrics related to agent caching

metadata_start_time

datetime2(3)

timestamp without time zone

Agent metrics related to filesystem metadata collection

metadata_stop_time

datetime2(3)

timestamp without time zone

Agent metrics related to filesystem metadata collection

metadata_file_count

integer

integer

Agent metrics related to filesystem metadata collection

metadata_directory_count

integer

integer

Agent metrics related to filesystem metadata collection

metadata_link_count

integer

integer

Agent metrics related to filesystem metadata collection

accounts_start_time

datetime2(3)

timestamp without time zone

Agent metrics related to security principal collection

accounts_stop_time

datetime2(3)

timestamp without time zone

Agent metrics related to security principal collection

accounts_object_count

integer

integer

Agent metrics related to security principal collection

transfer_start_time

datetime2(3)

timestamp without time zone

Related to transfer of scan file from the Agent to the Engine

transfer_stop_time

datetime2(3)

timestamp without time zone

Related to transfer of scan file from the Agent to the Engine

db_start_time

datetime2(3)

timestamp without time zone

Database insert start time*

db_stop_time

datetime2(3)

 

Database insert stop time*

scan_type

integer

integer

  • 0 = None
  • 1 = File System Data
  • 2 = Permissions
  • 4 = Volume Free Space

scan_target_id

integer

integer

Reference to scan_targets table

local_identity_system_id

integer

integer

 

retry_count

integer

integer

Current number of scan attempts

status_code

integer

integer

Internal status code

error_string

nvarchar(1024)

varchar(1024)

 

progress_status

integer

integer

  • -2 = Waiting for retry
  • -1 = Ready for cleanup
  • 0 = Waiting for delegation
  • 1 = Delegated / scan in progress
  • 2 = Scan file transfer in progress
  • 3 = Database update in progress
  • 4 = Current - scan process complete
  • 5 = Database update pending
  • 6 = Previous
  • 7 = Retained

next_retry_time

datetime2(0)

timestamp without time zone

Next scheduled time to retry a failed scan

ntfs_abe_enabled

bit

boolean

Flag indicating that the Windows share has ABE enabled

is_valid

bit

boolean

[Deprecated]

agent_name

nvarchar(256)

varchar(256)

 

* Database insert times do not include security equivalence, group membership, or eDirectory Directory Service trustee processing, all of which runs in the background.

3.1.10 SRS.SCAN_DATA

Table 3-10 Scan Data Table Definition

Column Name

SQL Server Data Type

PosgreSQL Data Type

Notes

id

bigint

bigint

Primary key

scan_id

integer

integer

Reference to scans table

path_type

integer

integer

  • 0 = Unknown
  • 1 = File
  • 2 = Directory
  • 3 = File Symbolic Link
  • 4 = Directory Symbolic Link
  • 5 = Junction
  • 6 = Mount Point
  • 7 = Share
  • 8 = Volume
  • 9 = DFS Link
  • 10 = DFS Folder
  • 11 = DFS Root
  • 12 = HSM Stub
  • 13 = Reparse Point Unknown
  • 17 = Single Instance Storage Stub
  • 18 = Named Stream

is_link

bit

boolean

Flag indicating entry is a link (symlink, hardlink, etc.)

name

nvarchar(256)

varchar(256)

File or directory name

fullpath

nvarchar(max)

text

Full UNC path to the file system entry

fullpath_hash

binary(20)

bytea

SHA-1 hash of lowercase fullpath

filename_extension

nvarchar(32)

varchar(32)

Extensions having more than 32 characters are treated as if they have none

owner_id

varbinary(68)

bytea

Maps to either a GUID or a SID

attributes

integer

integer

  • 0x0 = None
  • 0x1 = Read Only
  • 0x2 = Archive
  • 0x4 = System
  • 0x8 = Hidden
  • 0x10 = Directory
  • 0x20 = Compressed
  • 0x40 = Offline
  • 0x80 = NTFS device
  • 0x100 = NTFS Normal
  • 0x200 = NTFS Temporary
  • 0x400 = NTFS Sparse File
  • 0x800 = NTFS Reparse Point
  • 0x1000 = NTFS Not content indexed
  • 0x2000 = NTFS Encrypted
  • 0x4000 = NTFS Virtual
  • 0x8000 = NSS Execute only
  • 0x10000 = NSS Shareable
  • 0x20000 = NSS Don’t Sub-allocate
  • 0x40000 = NSS Transactional
  • 0x80000 = NSS Indexed
  • 0x100000 = NSS Read Audit
  • 0x200000 = NSS Write Audit
  • 0x400000 = NSS Immediate Purge
  • 0x800000 = NSS Rename Inhibit
  • 0x1000000 = NSS Delete Inhibit
  • 0x2000000 = NSS Copy Inhibit
  • 0x4000000 = NSS Don’t Migrate
  • 0x8000000 = NSS Immediate Compress
  • 0x10000000 = NSS Don’t Compress
  • 0x20000000 = NSS Can’t Compress
  • 0x40000000 = NSS File Audit
  • 0x80000000 = NSS Is a Link

create_time

datetime2(0)

timestamp without time zone

 

modify_time

datetime2(0)

timestamp without time zone

 

access_time

datetime2(0)

timestamp without time zone

 

size

bigint

bigint

For files, actual size; for directories, accumulative size of all subordinate files

size_on_disk

bigint

bigint

Assumes typical allocation unit size of 4K

size_compressed

bigint

bigint

Only accurate for NTFS file systems

idx

integer

integer

Scan index; unique per scan

parent_idx

integer

integer

Parent index. Used for hierarchical relation processing

path_depth

integer

integer

Entry depth with respect to the scan target’s root path

ns_left

integer

integer

Nested-set Left index – used for hierarchical relation processing

ns_right

integer

integer

Nested-set Right index – used for hierarchical relation processing

status_code

integer

integer

 

3.1.11 SRS.SCAN_DIRECTORY_DATA

Table 3-11 Scan Directory Data Table Definition

Column Name

SQL Server Data Type

PosgreSQL Data Type

Notes

id

bigint

bigint

Primary key

scan_data_id

bigint

bigint

Reference to scan_data table

file_count

integer

integer

Count of all files subordinate to this directory

directory_count

integer

integer

Count of all subdirectories

directory_quota

bigint

bigint

Directory quota for this directory

directory_quota_flags

integer

integer

  • 0 = Unknown
  • 1 = Enforced
  • 2 = Disabled
  • 4 = Incomplete
  • 8 = Rebuilding

child_file_count

integer

integer

Count of all immediately subordinate files

child_link_count

integer

integer

Count of all immediately subordinate links

child_directory_count

integer

integer

Count of all immediately subordinate directories

child_size

bigint

bigint

Size of all immediately subordinate files

child_size_on_disk

bigint

bigint

Size on disk of all immediately subordinate files (assumes 4K allocation size)

child_size_compressed

bigint

bigint

Size on disk of all immediately subordinate compressed files (only accurate with NTFS)

child_link_size

bigint

bigint

Size of all immediately subordinate links

3.1.12 SRS.SCAN_HISTORY

Table 3-12 Scan History Table Definition

Column Name

SQL Server Data Type

PosgreSQL Data Type

Notes

id

integer

integer

Primary key

identity_system

nvarchar(256)

text

Identity system associated with this scan target

scan_target

nvarchar(1024)

text

UNC path of scan target

file_size

bigint

bigint

Total aggregate size of all files

file_count

integer

integer

Total count of all files

directory_count

integer

integer

Total count of all directories

scan_policy_name

nvarchar(64)

varchar(64)

Scan policy associated with this scan

agent_name

nvarchar(256)

text

 

scan_id

integer

integer

Scan ID

scan_type

integer

integer

  • 0 = None
  • 1 = File System Data
  • 2 = Permissions
  • 4 = Volume Free Space

triggered_start_time

datetime2(3)

timestamp without time zone

Initial time scan delegation starts

scan_start_time

datetime2(3)

timestamp without time zone

Start time when agent begins physical scan

scan_stop_time

datetime2(3)

timestamp without time zone

Stop time when agent completes physical scan

enum_start_time

datetime2(3)

timestamp without time zone

Agent metrics related to file system object enumeration

enum_stop_time

datetime2(3)

timestamp without time zone

Agent metrics related to file system object enumeration

enum_file_count

integer

integer

Agent metrics related to file system object enumeration

enum_directory_count

integer

integer

Agent metrics related to file system object enumeration

enum_link_count

integer

integer

Agent metrics related to file system object enumeration

caching_start_time

datetime2(3)

timestamp without time zone

Metrics related to agent caching

caching_stop_time

datetime2(3)

timestamp without time zone

Metrics related to agent caching

cached_file_count

integer

integer

Metrics related to agent caching

cached_directory_count

integer

integer

Metrics related to agent caching

cached_link_count

integer

integer

Metrics related to agent caching

cache_size

integer

integer

Metrics related to agent caching

cache_size_max

integer

integer

Metrics related to agent caching

metadata_start_time

datetime2(3)

timestamp without time zone

Agent metrics related to filesystem metadata collection

metadata_stop_time

datetime2(3)

timestamp without time zone

Agent metrics related to filesystem metadata collection

metadata_file_count

integer

integer

Agent metrics related to filesystem metadata collection

metadata_directory_count

integer

integer

Agent metrics related to filesystem metadata collection

metadata_link_count

integer

integer

Agent metrics related to filesystem metadata collection

accounts_start_time

datetime2(3)

timestamp without time zone

Agent metrics related to security principal collection

accounts_stop_time

datetime2(3)

timestamp without time zone

Agent metrics related to security principal collection

accounts_object_count

integer

integer

Agent metrics related to security principal collection

transfer_start_time

datetime2(3)

timestamp without time zone

Related to transfer of scan file from the Agent to the Engine

transfer_stop_time

datetime2(3)

timestamp without time zone

Related to transfer of scan file from the Agent to the Engine

db_start_time

datetime2(3)

timestamp without time zone

Database insert start time*

db_stop_time

datetime2(3)

 

Database insert stop time*

status_code

integer

integer

Internal status code

error_string

nvarchar(1024)

varchar(1024)

 

3.1.13 SRS.SCAN_TARGETS

Table 3-13 Scan Targets Table Definition

Column Name

SQL Server Data Type

PosgreSQL Data Type

Notes

id

bigint

bigint

Primary key

network_path

nvarchar(256)

varchar(256)

Root path for scan target

network_path_lower

nvarchar(256)

[ Not applicable ]

Computed column

server

nvarchar(256)

varchar(256)

 

identity_system_id

integer

integer

Reference to identity_systems table

platform

smallint

smallint

  • 0 = Unknown
  • 1 = Windows
  • 2 = Linux
  • 3 = Open Enterprise Server

filesystem

smallint

smallint

  • 0 = Unknown
  • 1 = NTFS
  • 3 = NSS
  • 4 = TFS
  • 5 = Ext3
  • 6 = ReiserFS
  • 10 = Ext2
  • 11 = Ext4
  • 12 = Xfs
  • 13 = Ramdisk
  • 14 = OES_VFS
  • 16 = Linux_generic
  • 17 = NFS

cost_per_unit

money

money

Not currently used

3.1.14 SRS.SECURITY_DESCRIPTORS

Table 3-14 Security Descriptors Table Definition

Column Name

SQL Server Data Type

PosgreSQL Data Type

Notes

id

bigint

bigint

Primary key

scan_data_id

bigint

bigint

Reference to scan_data table

control

integer

integer

Security descriptor control flags

dacl_present

bit

boolean

Indicates presence of DACL entries for this security descriptor

sacl_present

bit

boolean

Indicates presence of SACL entries for this security descriptor

3.1.15 SRS.TREND_VOLUME_FREESPACE

Table 3-15 Trend Volume Freespace Table Definition

Column Name

SQL Server Data Type

PosgreSQL Data Type

Notes

id

integer

integer

Primary key

scan_id

integer

integer

Scan ID

identity_system

nvarchar(256)

text

 

network_path

nvarchar(max)

text

Scan target path

server

nvarchar(256)

text

 

filesystem

integer

integer

  • 0 = Unknown
  • 1 = NTFS
  • 3 = NSS
  • 4 = TFS
  • 5 = Ext3
  • 6 = ReiserFS
  • 10 = Ext2
  • 11 = Ext4
  • 12 = Xfs
  • 13 = Ramdisk
  • 14 = OES_VFS
  • 16 = Linux_generic
  • 17 = NFS

volume_guid

uniqueidentifier

uuid

 

volume_label

nvarchar(256)

text

 

volume_bytes_total

bigint

bigint

 

volume_bytes_free

bigint

bigint

 

volume_bytes_used

bigint

bigint

 

allocation_unit_size

integer

integer

 

allocation_units_total

bigint

bigint

 

allocation_units_free

bigint

bigint

 

allocation_units_used

bigint

bigint

 

status

integer

integer

 

scan_time

datetime2(0)

timestamp without time zone