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

Overview

Report queries

Recorded films

Average size of film chapters

CODE

visulox report -query averageFilm -tframe CURRMONTH -raw

Average size of film chapters (in KB)
221.52

Show total film size

Total film size of the day

CODE

visulox report -query totalFilmSize -tframe CURRDAY -raw

Size of all files (in KB)
1046

Usage

List unused applications via report command

Unused applications of the current month

CODE

visulox report -query unusedApplications -tframe CURRMONTH -raw

APPLICATION
o=applications/cn=3270
o=applications/cn=5250
o=applications/cn=Unix Desktop
o=applications/cn=Windows Desktop
o=applications/cn=gnome-edit (mp-ol6u3-devel)
o=applications/cn=xclock (mp-ol6u3-devel)
o=applications/ou=VISULOX Examples/cn=@VLX XSTRESS (R)
o=applications/ou=VISULOX Examples/cn=VLX Assist
o=applications/ou=VISULOX Examples/cn=VLX CITRIX ICA
o=applications/ou=VISULOX Examples/cn=VLX CITRIX MGR
o=applications/ou=VISULOX Examples/cn=VLX Command Connect
o=applications/ou=VISULOX Examples/cn=VLX Command Guard
o=applications/ou=VISULOX Examples/cn=VLX Devel GUI ()
o=applications/ou=VISULOX Examples/cn=VLX FT Client
o=applications/ou=VISULOX Examples/cn=VLX Management (all,de)
o=applications/ou=VISULOX Examples/cn=VLX Management (all,es)
o=applications/ou=VISULOX Examples/cn=VLX Management (all,pt)
o=applications/ou=VISULOX Examples/cn=VLX Management (testrole)
o=applications/ou=VISULOX Examples/cn=VLX RDP
o=applications/ou=VISULOX Examples/cn=VLX RDP SSO
o=applications/ou=VISULOX Examples/cn=VLX TTASYS TEST Console
o=applications/ou=VISULOX Examples/cn=VLX VNC CONNECT

Top used applications

Top applications of the day

CODE

visulox report -query topApplication -tframe CURRDAY -raw

vlxapplication;count
o=applications/ou=VISULOX Examples/cn=VLX JUMP SHELL;2
o=applications/cn=gnome terminal (mp-ol6u3-devel);1
o=applications/cn=xterm;1
o=applications/ou=VISULOX Examples/cn=VLX FIREFOX KIOSK (R);1

Login failures

Show login failures this month

CODE

visulox report -query loginFailure -tframe CURRMONTH -raw

User;Fail login current month
root;1
miller;1
roberts;1

Database queries

Recorded films

Size of all files (chapters, snapshots, transferred files)

CODE

visulox database query -sql "
    SELECT (SUM(postsize) /1024) AS 'Size of all files in the last 7 days (in KB)'
    FROM files WHERE 'createtime' > 'DATE_SUB(CURDATE(),INTERVAL 7 DAY)' 
"

Database Query 152ms

    ------------------------------------------------------
    | Size of all files in the last 7 days (in KB)       |
    ------------------------------------------------------
    |                                                524 |
    ------------------------------------------------------

Size of film chapters only

CODE

visulox database query -sql "
    SELECT (SUM(postsize) /1024) AS 'Size of all films in the last 7 days (in KB)'
    FROM files WHERE filetype LIKE 'chapter' AND 'createtime' > 'DATE_SUB(CURDATE(),INTERVAL 7 DAY)'
"

Database Query 149ms

    ------------------------------------------------
    | Size of all films in the last 7 days (in KB) |
    ------------------------------------------------
    |                                          398 |
    ------------------------------------------------

Size of snapshots only

CODE

visulox database query -sql "
    SELECT (SUM(postsize) /1024) AS 'Size of all snapshots in the last 7 days (in KB)'
    FROM files WHERE filetype LIKE '%-snapshot' AND 'createtime' > 'DATE_SUB(CURDATE(),INTERVAL 7 DAY)' 
"

Database Query 148ms

    ----------------------------------------------------
    | Size of all Snapshots in the last 7 days (in KB) | 
    ----------------------------------------------------
    |                                               48 |
    ----------------------------------------------------

Size of transferred files only

CODE

visulox database query -sql "
    SELECT (SUM(postsize) /1024) AS 'Size of all transferred files in the last 7 days (in KB)'
    FROM files WHERE filetype LIKE 'file' AND 'createtime' > 'DATE_SUB(CURDATE(),INTERVAL 7 DAY)' 
"

Database Query 153ms

    ------------------------------------------------------------
    | Size of all transferred files in the last 7 days (in KB) |
    ------------------------------------------------------------
    |                                                       77 |
    ------------------------------------------------------------

Average chapter size

CODE

visulox database query -sql "
    SELECT ROUND((AVG(postsize) /1024),2) AS 'Average size of film chapters (in KB)'
    FROM files WHERE filetype LIKE 'chapter'
"

Database Query 149ms

    -----------------------------------------
    | Average size of film chapters (in KB) |
    -----------------------------------------
    |                                199.06 |
    -----------------------------------------

Usage

Most used application

CODE

 visulox database query -sql "select vlxapplication,count(*) AS c from sessions GROUP BY vlxapplication ORDER BY C DESC" -raw

Database Query 107ms
    ------------------------------------------------------------
    |                                       vlxapplication | c |
    ------------------------------------------------------------
    |                      o=applications/cn=xterm (TEST2) | 1 |
    | o=applications/ou=VISULOX Examples/cn=VLX JUMP SHELL | 1 |
    ------------------------------------------------------------

Most active user

CODE

visulox database query -sql "select vlxowner,count(*) AS c from sessions GROUP BY vlxowner ORDER BY C DESC" -raw

Database Query 76ms
    ----------------------------------------------------
    |                                     vlxowner | c |
    ----------------------------------------------------
    | o=Tarantella System Objects/cn=Administrator | 2 |
    ----------------------------------------------------
Columns: vlxowner c