VISULOX 4 Documentation
VISULOX 4 Documentation
Breadcrumbs

How to control reports from the command line


Overview

The command line tool "VISULOX report" allows to control all kind of reports.


Usage

The following subcommands are available:

Command

Description

-help

Prints a help on report (useful for creating own reports) 1

-list

List available predefined reports

-title <>

Title of the report

-mctitle <>

messagecat title

-name <>

Name of the report, default: <report>

-type <>

Type of the report pdf / csv / json / xml / html, default: <csv>

-xslt <>

xslt filename for pdf <>

-metadata <>

metaData for xml

-query <>

Name of query or path to queryfile

-tframe <>

TFRAME() in SQL will be substituted. Use -tframe HELP

2

-from <>

Starttime instead of tframe (dd/mm/yyyy)

-to <>

Endtime instead of tframe (dd/mm/yyyy)

-sql <>

SQL statement of the report

-lang <>

Language for the report, default: <en>, in case of a strange output, try: "-lang -"

-filename <>

Timefile to store the result

-mailto <>

Send email

-mailsubject <>

Subject of the mail, default: <VISULOX REPORT>

-maildescription <>

Mail description

-maildescriptionfile <>

Mail description file

-archive <>

Use database on archive node

-dry

Show SQL only

1 Using visulox report help

With help the report tables and the tframe options are displayed.

Using help -table <table name> lists the available columns of a table for report queries

For example: Table: Sessions

Columns

Format

Description

sessions.chaptercounter

INTEGER

Number of chapters

sessions.chaptersize

INTEGER

Total size of all chapters

sessions.chaptertype

TEXT

Type of the chapter

sessions.snapshotcounter

INTEGER

Number of snapshots

sessions.vlx_create_timems

INTEGER

Create time of the session in milliseconds

sessions.vlx_created_by

TEXT

Created by

sessions.vlx_expiration_time

INTEGER

Session expiration time in seconds

sessions.vlx_update_timems

INTEGER

Update time in milliseconds

sessions.vlx_updated_by

TEXT

Updated by

sessions.vlxaccesspoint

TEXT

URL of the access point

sessions.vlxapplication

TEXT

Application object

sessions.vlxapplicationarguments

TEXT

Parameter for the application

sessions.vlxapplicationcommand

TEXT

Command of the application

sessions.vlxapplicationhost

TEXT

Host, where the application was started

sessions.vlxapplicationuser

TEXT

User of the application

sessions.vlxclientip

TEXT

Client IP

sessions.vlxlisthash

TEXT

Hash value

sessions.vlxloginscript

TEXT

Login script

sessions.vlxowner

TEXT

Owner of the session

sessions.vlxpolicy

TEXT

Applied Policy

sessions.vlxremoteip

TEXT

Remote IP

sessions.vlxsessionendtime

INTEGER

End time of the session in seconds

sessions.vlxsessionid

TEXT

VISULOX Session ID

sessions.vlxsessionstarttime

INTEGER

Start time of the session in seconds

sessions.vlxsessiontype

TEXT

Type of the session

sessions.vlxticketid

TEXT

Ticket ID from an incident management system

sessions.vlxvalidationdata

TEXT

Validation data

2 More tframe options

tframe is a timeframe and presets -from and -to,
based on the value provided to -tframe
The value can be
current: CURRDAY | CURRWEEK | CURRMONTH
CURRQUARTER | CURRHALFYEAR | CURRYEAR

last: LASTDAY | LASTWEEK | CURRMONTH
LASTQUARTER | LASTHALFYEAR| LASTYEAR

next to last: NEXT2LASTDAY | NEXT2LASTWEEK | NEXT2LASTMONTH
NEXT2LASTQUARTER | NEXT2LASTHALFYEAR| NEXT2LASTYEAR

absolute: W<week number>
M<month number>
Q<quarter>
<year>
- year suffix: <year>W<week number>
<year>M<month number>
<year>Q<quarter>

examples: -tframe CURRQUARTER | -tframe 2019W10 | -tframe 2018


If a limit is used inside the SQL query -force has to be added to the command to bypass an SQL limit error.

Available predefined queries 

visulox report list

List of available reports in
   averageFiletransferPerMonth : /visulox/etc/reports/averageFiletransferPerMonth.rpt
                   averageFilm : /visulox/etc/reports/averageFilm.rpt
           averageFilmPerMonth : /visulox/etc/reports/averageFilmPerMonth.rpt
   averageStorageUsagePerMonth : /visulox/etc/reports/averageStorageUsagePerMonth.rpt
            examplePlaceholder : /visulox/etc/reports/examplePlaceholder.rpt
                 inactiveUsers : /visulox/etc/reports/inactiveUsers.rpt
                 listUserGroup : /visulox/etc/reports/listUserGroup.rpt
                  loginFailure : /visulox/etc/reports/loginFailure.rpt
                     session30 : /visulox/etc/reports/session30.rpt
           session30TimeFormat : /visulox/etc/reports/session30TimeFormat.rpt
                     top25User : /visulox/etc/reports/top25User.rpt
                topApplication : /visulox/etc/reports/topApplication.rpt
                 totalFilmSize : /visulox/etc/reports/totalFilmSize.rpt
            unusedApplications : /visulox/etc/reports/unusedApplications.rpt


Keep in mind, that the information is always based on the provided lifetimes.


Depending on the lifetime (30/60/90/etc days) the session information is deleted. If a report of the application usage or of the users is needed to clean up the datastore / LDAP, the set lifetimes mostly are too low.

A report based on "LASTYEAR" is not possible because the information is not available.

Therefore the variable "sgdusage" has been implementred, for which an additional lifetime can be set:


visulox config -name lifetime.sgdusage=365


This lifetime is set for:

  • loginResultRejected

  • webtopSessionStartedDetails

  • sessionStartedDetails


Placeholder

It is possible to use placeholders via command line in the SQL statements e.g. SELECT ... WHERE ... columname='%P1%' ...

The placeholder has the format "key=value", where key can be any alpnumerical string. If value has a space, the value must be quoted, e.g. NAME="surname name".

Usage:

visulox report -query <name> P1=abc P2=ext

Extended report with vendor and project field

If a user repository has no vendor field, it is still possible to generate reports based on such a vendor field.

The vendor can be extracted from the distinguished name in the datastore profile or from the distinguished name in the Active Directory.

Example for user naming

There are two naming conventions:

cn=user,ou=procjet,ou=vendor,......
... ou=vendor/ou=project/cn=user

Deliverable

The vendor is extracted from the username (vlxowner) during user import into "vlxvendor". The vendor field is listed in the VISULOX Cockpit.

This vendor field cannot be in the audit data (session or event) information. Therefore an extended database function is provided to list the vendor and/or project out of vlxowner during database querries.

SELECT  vlxsessionid, (vlxeventtimems/1000) as vlxeventstamp, vlxevent, vlxpolicy, vlxowner, getUnit(vlxowner,1) as project, getUnit(vlxowner,2) as customer, vlxticketid, vlxvalidationdata
FROM    events
WHERE   %TFRAME(vlxeventtimems)% and vlxevent in ('2faPinAccepted','2faPinRejected')

Adjusting naming convention

If necessary it is possible to adjust the naming convention in VISULOX with the following parameters:

visulox config -name importer.map
    ------------------------------------------
    | changed | key                  | value |
    ------------------------------------------
    |         | importer.map.project | 0     |
    |         | importer.map.vendor  | 0     |
    ------------------------------------------

Examples 


visulox report -query unusedApplications -tframe CURRMONTH -raw -format json

The report "unused applications" shows all applications, that have not been used in the last 14 days.


visulox report -query topApplication -tframe CURRYEAR -raw



visulox report -query loginFailure -tframe LASTWEEK -raw



visulox report -query top25User -lang - -tframe CURRMONTH -force

In this example "-lang -" has to be used for a better output and "-force" to bypass an SQL limit error.


Related information

Useful database queries: Size of film chapters, snapshots, files, usage

VISULOX Command