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)¹
-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 ²
-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

¹ 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

² More tframe options

CODE

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

CODE

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 implemented, for which an additional lifetime can be set:

Setting the lifetime for sgdusage

CODE

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:

CODE

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.

Example report with vendor and project

CODE

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:

CODE

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

Examples

List unused applications of the current month

CODE

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.

List most used application of the current year

CODE

visulox report -query topApplication -tframe CURRYEAR -raw

List all login failures of the last week

CODE

visulox report -query loginFailure -tframe LASTWEEK -raw

CODE

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