Reporting How to guide

From Swivel Knowledgebase
Revision as of 07:59, 29 August 2019 by RWithey (talk | contribs) (→‎Lists all usernames in the Swivel database and their string destination attributes)
Jump to: navigation, search


Contents

Overview

The Swivel Administration Console allows a limited number of reports to be generated and scheduled. This document outlines some of the information that can be queried through the reporting.

The Audit Log is where Swivel maintains an activity log for users. For more information on the Audit Log feature within Swivel, see the Audit Log How To Guide.

For information on querying the Internal Swivel database see Reporting Using Agent-XML How to Guide

For information on querying a MySQL database see MySQL Queries How To Guide and for a MS SQL database see MS SQL Queries How To Guide

Also see ReportingAPI


Prerequisites

Swivel 3.8 - Reporting

Swivel 3.9 - Scheduled Reporting


Reporting access and restrictions

Swivel reports from a global database. Where access is restricted for Helpdesk users to specific repositories and groups it may be ncessary to disable the reporting function for Helpdesk users. However reports can be produced by Administrative level access users creating scheduled reporting based on the required groups and making those reports available to the relevant Helpesk users managing those groups such as through automated emails.


Producing Instant Reports

From the Swivel Administration console select Reporting then Instant.


Swivel 3-9-2 Reporting select a report.jpg


Depending on the report, additional parameters may be selected.


Swivel 3-9-2 Reporting enter parameters.jpg


Click on Run Report to generate data.


Swivel 3-9-2 Reporting run report.jpg


Clicking on Export as XML or Export as CSV allows the report to be saved from the browser.


Producing Scheduled Reports

From the Swivel Administration console select Reporting then Scheduled. Existing scheduled reports are listed. For information on creating custom schedules see Schedule.


Swivel 3-9-2 Reporting scheduled list.jpg


To create a new scheduled report click on Add

Swivel 3-9-2 Reporting scheduled new report.jpg


Enter the following information:

Name: Name for the report

Report: Select the required report

Schedule: How often the report is run

Format: The output format for the report, options are XML or CSV

Filename: The filename given to the report, for multiple reports to be run at the same time this should be unique, example inactive_users_%d-%t.txt creates a file called inative_users_date-time.txt

Email Ticking this box enables the reporting by email. This function is available from Swivel 3.10 onwards. The email address is set under Policy/Reporting

Enabled Ticking this box enables the report to be run.


Apply the settings to save them.


Scheduled report file locations

Scheduled reports are stored within the Swivel installation, and varies depending upon the install type and version.

Swivel 3.9.1 onwards stores the reports in <swivel home>/.swivel/reporting for Swivel appliances and software installations.

Swivel 3.9 stores the scheduled reports data in <path to pinsafe>/WEB-INF/reports

For an appliance <path to pinsafe> is: /usr/local/tomcat/webapps/pinsafe

For a Windows 3.9.1 installation, <swivel home> will depend on the user account under which Tomcat is running, and will typically be C:\Users\Account. You can check the actual directory from the PINsafe Administration Console Status page. Look for Data Storage Root.

For a Swivel 3.9 software install under Windows, <path to pinsafe> is usually: C:\Program Files\Apache Software Installation\Tomcat 5.5\webapps\pinsafe. Obviously, if you are using a different version of Tomcat, adjust accordingly.


Report date Format

The report date format can be set on the Swivel Administration console under Server/Language/Date Format


Available Reports

List all users

List all users with no activity since a given date

List all users with no activity within a given number of days

List login failures and self-resets since the last successful login

List creation time and latest login for all users

List users that have never logged in

List number of users hourly logged


Custom Reports

Always backup files before editing.

The report definition file is under the following locations:

Appliances after 3.9: /home/swivel/.swivel/conf/reports.xml

Appliances up to 3.9: /usr/local/tomcat/webapps/pinsafe/WEB-INF/conf/reports.xml

Software install after 3.9 <swivel home>/.swivel/conf/reports.xml

Software install up to 3.9 <path to Tomcat>/webapps/pinsafe/WEB-INF/conf/reports.xml

See above for details on finding the location of <swivel home>.

The default contents of this file at the time of writing is as follows: 


<?xml version="1.0" encoding="UTF-8"?>
<reports>
       <report name="">
               <title>---- Please select a report to run ----</title>
       <description>No report selected</description>
       </report>
       <report name="allUsers">
       <title>List all users</title>
               <description>Lists all usernames in the PINsafe database</description>
               <headers>
                       <header>Username</header>
               </headers>
               <fields>H</fields>
               <tables>PINSAFEJ</tables>
       </report>
       <report name="idleUsers">
               <title>List idle users</title>
       <description>Lists users that have not logged in since a specified date</description>
               <headers>
                       <header>Username</header>
                       <header>Last Login</header>
               </headers>
               <fields>U.H, A.D</fields>
               <tables>PINSAFEJ U LEFT OUTER JOIN PINSAFEN A ON U.G = A.A AND A.C = 0</tables>
               <query>A.D IS NULL OR A.D < ?</query>
               <params>
                       <param name="idledate" type="Date" label="Idle since" />
               </params>
       </report>
       <report name="failures">
          <title>User fail count and reset count</title>
      <description>Lists the number of consecutive login failures and self-resets since the last successful login for all users</description>
          <headers>
              <header>Username</header>
              <header>Fail Count</header>
              <header>Reset Count</header>
          </headers>
          <fields>H, B, F</fields>
          <tables>PINSAFEJ</tables>
          <query />
       </report>
       <report name="connection">
       <title>Latest connection for all users</title>
          <description>Lists the creation time and last login time for all users</description>
          <headers>
              <header>Username</header>
              <header>Created</header>
              <header>Last Login</header>
          </headers>
          <fields>U.H, A1.D, A2.D</fields>
          <tables>PINSAFEJ U INNER JOIN PINSAFEN A1 ON U.G=A1.A AND A1.C=3 LEFT OUTER JOIN PINSAFEN A2 ON U.G=A2.A AND A2.C=0</tables>
          <query />
       </report>
       <report name="noconnect">
       <title>Users that have never logged in</title>
          <description>Lists all users that have never successfully logged into PINsafe</description>
          <headers><header>Username</header></headers>
          <fields>H</fields>
          <tables>PINSAFEJ</tables>
          <query>G NOT IN (SELECT DISTINCT A FROM PINSAFEN WHERE C=0)</query>
       </report>
 </reports>


Adding new reports

Currently, requests for new reports can be made via your reseller to PINsafe support support@swivelsecure.com.

Once you've obtained a new report, the easiest way to add it to the configuration file above, is to use WinSCP. See the WinSCP How To Guide for further information on using WinSCP.

To add custom reports to the built-in reporting feature of PINsafe, you would append this to the reports.xml file. Insert new reports immediately before the last line: 

 </reports>

Once you have installed the report, go to Reporting -> Instant, and the new report should be available from the drop-down list.

You don't need to stop and restart Tomcat for the report to become available: the list is reloaded every time the report page is displayed.

We do not recommend writing your own reports, but for those who are competent with SQL and recognise that the reports are essentially XML-encoded fragments of SQL statements, you can get the full database schema for the Swivel database from here.

Creation time, last login and number of logins

This report lists all successful logins within the last 30 days (the audit trail is only kept for 30 days by default, but can be set to a different value. 

  <report name="loginCount">
  <title>Login count and latest login for all users</title>
  <description>Lists the creation time, last login time and number of logins for all users</description>
  <headers>
  <header>Username</header>
  <header>Created</header>
  <header>Last Login</header>
  <header>Login Count</header>
  </headers>
  <fields>U.H, MAX(A1.D), MAX(A2.D), COUNT(AU.G)</fields>
  <tables>PINSAFEJ U INNER JOIN PINSAFEN A1 ON U.G=A1.A AND A1.C=3 LEFT OUTER
  JOIN PINSAFEN A2 ON U.G=A2.A AND A2.C=0 INNER JOIN PINSAFEM AU ON U.G=AU.G AND AU.A=0
  GROUP BY U.G, U.H</tables>
  <query />
  </report>


List all users, together with their login count

This report lists the following information:

First Name

Last Name

Username

Phone Number

Creation Date

Last Login Date

Login Count

Note: creation date if within length of audit time

This report has been tested on both Internal and MySQL databases. 

 	<report name="user-login-count">
 		<title>User details with login count</title>
 		<description>Lists all users, together with their login count over the last 30 days, and last login time</description>
 		<headers>
 			<header>Username</header>
 			<header>First name</header>
 			<header>Last name</header>
 			<header>Phone</header>
 			<header>Created</header>
 			<header>Latest login</header>
 			<header>Login count</header>
 		</headers>
 		<fields>U.H, ATT1.C, ATT2.C, ATT3.C, ACT1.D, ACT2.D, AUD.CT</fields>
 		<tables><![CDATA[
 		PINSAFEJ U 
 		LEFT OUTER JOIN PINSAFEP ATT1 ON U.G=ATT1.A AND ATT1.B='givenname' 
 		LEFT OUTER JOIN PINSAFEP ATT2 ON U.G=ATT2.A AND ATT2.B='familyname' 
 		LEFT OUTER JOIN PINSAFEP ATT3 ON U.G=ATT3.A AND ATT3.B='phone' 
 		LEFT OUTER JOIN PINSAFEN ACT1 ON U.G=ACT1.A AND ACT1.C=3 
 		LEFT OUTER JOIN PINSAFEN ACT2 ON U.G=ACT2.A AND ACT2.C=0 
 		LEFT OUTER JOIN (SELECT G, COUNT(*) CT FROM PINSAFEM WHERE A=0 GROUP BY G) AUD ON U.G=AUD.G
 		]]></tables>
 	</report>


List users of a particular group showing their last login over the last month

 <report name="connectbygroup">
 		<title>Authentication by User in a Group</title>
 		<description>List of users in a particular group, listing their logins over the last month</description>
 		<headers>
 			<header>Group</header>
 			<header>Username</header>
 			<header>Login Date</header>
 		</headers>
 		<fields>G.A, U.H, A.E</fields>
 		<tables>PINSAFEM A INNER JOIN PINSAFEJ U ON A.G=U.G INNER JOIN PINSAFEI G ON U.G=G.B</tables>
 		<query>G.A=? ORDER BY U.C</query>
 		<params>
 			<param name="group" type="String" label="Group Name" />
 		</params>
 	</report>

You will be prompted for the group name:, type in the actual name of the group, remember it's the Swivel group name, not the Active Directory FQDN.

Lists all usernames in the Swivel database and their alert destination attributes

 <report name="allUsersAlerts">
         <title>List all users alert destination attribute</title>
                 <description>Lists all usernames in the Swivel database and their alert destination attributes</description>
                 <headers>
                         <header>Username</header>
                         <header>Alerts destination attribute</header>
                 </headers>
                 <fields>U.H, A.A</fields>
                 <tables>PINSAFEJ U LEFT OUTER JOIN PINSAFEA A ON U.G=A.C</tables>
 </report>


List Inactive Users Within a Period By Group

Note that for it to work properly, set the Policy -> General, Audit Log length to a suitably large value (example 365 days) 

 <report name="idleUsersByGroup">
 <title>List Inactive Users Within a Period By Group</title>
 <description>List members of a given group who did not log in within a specified time</description>
 <headers>
 	<header>Username</header>
 	<header>Last Login</header>
 </headers>
 <fields>U.H, A.D</fields>
 <tables>PINSAFEJ U LEFT OUTER JOIN PINSAFEN A ON A.A = U.G AND A.C = 0 JOIN PINSAFEI G ON G.B = U.G AND G.A = ?</tables>
 <query>U.G NOT IN (SELECT G FROM PINSAFEM WHERE A=0 AND E &gt;= ? AND E &lt;= ?) </query>
 <params>
   <param name="group" type="String" label="Group" />
 	<param name="startdate" type="Date" label="Start Date" />
 	<param name="enddate" type="Date" label="End Date" />
 </params>
 </report>

Total number of users in each group

<report name="groupMemberCount">
  <title>Count of users in each group by repository</title>
  <description>List the number of users in each group within each repository</description>
  <headers>
    <header>Repository</header>
    <header>Group</header>
    <header>Num. Users</header>
  </headers>
  <fields>R.B, G.A, COUNT(U.G)</fields>
  <tables>PINSAFEJ U JOIN PINSAFEL R ON U.I=R.A JOIN PINSAFEI G ON U.G=G.B GROUP BY R.B, G.A ORDER BY R.B, G.A</tables>
</report>

List users in each group

<report name="groupMembers">
  <title>List of users in each group by repository</title>
  <description>List the users in each group by repository</description>
  <headers>
    <header>Repository</header>
    <header>Group</header>
    <header>Users</header>
  </headers>
  <fields>R.B, G.A, U.C</fields>
  <tables>PINSAFEJ U JOIN PINSAFEL R ON U.I=R.A JOIN PINSAFEI G ON U.G=G.B ORDER BY R.B, G.A, U.C</tables>
</report>

List all OATH users

<report name="TokenUsers">
  <title>List of users allocated OATH tokens</title>
  <description>Lists all users that have been allocated OATH tokens</description>
  <headers>
    <header>User</header>
  </headers>
  <fields>U.H</fields>
  <tables>PINSAFEJ U JOIN PINSAFEQ T ON U.G=T.C</tables>
</report>

List users with Mobile permissions

<report name="MobileUsers">
  <title>List of users entitle to use mobile client</title>
  <description>Lists users that have the "Mobile" right</description>
  <headers><header>User</header></headers>
  <fields>U.H</fields>
  <tables>PINSAFEJ U JOIN PINSAFEB R ON U.G = R.B</tables>
  <query>R.A = 2</query>
</report>

List users with a provisioned Mobile device

<report name="AllocatedMobileUsers">
  <title>List of users with provisioned mobile clients</title>
  <description>Lists users that have provisioned their mobile clients</description>
  <headers><header>User</header></headers>
  <fields>U.H</fields>
  <tables>PINSAFEJ U JOIN PINSAFEO M ON U.G = M.C</tables>
</report>

List users with Mobile client rights that have not yet provisioned a device

<report name="UnprovisionedMobileUsers">
  <title>List of users that have not yet provisioned a mobile device</title>
  <description>Lists users that have the right to use a mobile client, but have not yet provisioned one</description>
  <headers><header>User</header></headers>
  <fields>U.H</fields>
  <tables>PINSAFEJ U JOIN PINSAFEB R ON U.G = R.B AND R.A=2</tables>
  <query>U.G NOT IN (SELECT C FROM PINSAFEO)</query>
</report>

List the number of login failures for all users

<report name="loginFailCount">
	<title>Count of failed logins for all users</title>
	<description>Lists the number of failed logins for each user, to the extent of the audit records</description>
	<headers>
		<header>Username</header>
		<header>Fail Count</header>
	</headers>
	<fields>I, Count(A)</fields>
	<tables>PINSAFEM</tables>
	<query>A=14 group by I order by I</query>
</report>

List of users that provisioned before a given date

<report name="oldProvisions"> <title>List of users that provisioned before a given date</title> <description>Lists users that last provisioned their mobile app earlier than a specified date.</description> <headers> <header>Username</header> <header>Provision date</header> </headers> <params> <param name="cutoffdate" type="Date" label="Provisioned before"/> </params> <fields>U.H, A.D</fields> <tables>PINSAFEJ U JOIN PINSAFEO M on U.G = M.C JOIN PINSAFEN A on U.G = A.A and A.C=15</tables> <query>A.D < ?</query> </report>

List of users that have not yet provisioned a mobile device

<report name="UnprovisionedMobileUsers"> 

 <title>List of users that have not yet provisioned a mobile device</title>
 <description>Lists users that have the right to use a mobile client, but have not yet provisioned one</description>
 <headers><header>User</header></headers>
 <fields>U.H</fields>
 <tables>PINSAFEJ U JOIN PINSAFEB R ON U.G = R.B AND R.A=2</tables>
 <query>U.G NOT IN (SELECT C FROM PINSAFEO)</query>

</report>

List unprovisioned Mobile Users for a given date

<report name="Unprovisionedbydate" supporteddbs="mariadb,mysql,mssql"> <title>List Unprovisioned Mobile Users older than given date</title> <description>Lists users, together with the latest date that they Provisioned</description> <headers> <header>Username</header> <header>Last Changed</header> </headers> <fields>U.H, U.C name, MAX(A.D) lastdate</fields> <tables>PINSAFEJ U JOIN PINSAFEB R ON U.G = R.B AND R.A=2; PINSAFEJ U JOIN PINSAFEN A on U.G=A.A and A.C in (1,2,3,6) group by A.A having date < ? order by lastdate</tables> <query>U.G NOT IN (SELECT C FROM PINSAFEO)</query> <params> <param name="changedate" type="Date" label="Latest date" /> </params> </report>

List the number of login failures since a given date, for all users

<report name="loginFailCountSinceDate">
	<title>Count of failed logins since a given date for all users</title>
	<description>Lists the number of failed logins for each user, since a given date</description>
	<headers>
		<header>Username</header>
		<header>Fail Count</header>
	</headers>
	<fields>I, Count(A)</fields>
	<tables>PINSAFEM</tables>
	<query>A=14 and E>? group by I order by I</query>
	<params>
		<param name="sinceDate" type="Date" label="Cutoff Date" />
	</params>
</report>

List the last PIN change for all users

NOTE: this report has two different versions, as the Internal database doesn't support the features used by the first version. You may wish to include only the version that matches your database. 

<report name="lastchanged" supporteddbs="mysql,mariadb,mssql">
	<title>List of PIN changes by date</title>
	<description>Lists all users, together with the latest date that their PIN was changed, or the creation date if never changed, starting with the oldest</description>
	<headers>
		<header>Username</header>
		<header>Last Changed</header>
	</headers>
	<fields>U.C name, MAX(A.D) lastdate</fields>
	<tables>PINSAFEJ U JOIN PINSAFEN A on U.G=A.A and A.C in (1,2,3,6) group by A.A order by lastdate</tables>
</report>

<report name="lastchanged" supporteddbs="internal">
	<title>List of PIN changes by date</title>
	<description>Lists all users, together with the latest date that their PIN was changed, or the creation date if never changed, starting with the oldest</description>
	<headers>
		<header>Username</header>
		<header>Last Changed</header>
	</headers>
	<fields>U.C name, Q.DD lastdate</fields>
	<tables>PINSAFEJ U JOIN (SELECT A, Max(D) DD FROM PINSAFEN where C in (1,2,3,6) group by A) Q on U.G = Q.A order by lastdate</tables>
</report>

List PIN changes older than a given date

NOTE: as above, there are different versions for different databases. 

<report name="lastchanged2" supporteddbs="mariadb,mysql,mssql">
	<title>List of PIN changes older than given date</title>
	<description>Lists users, together with the latest date that their PIN was changed, or the creation date if never changed, starting with the oldest, given a date cutoff</description>
	<headers>
		<header>Username</header>
		<header>Last Changed</header>
	</headers>
	<fields>U.C name, MAX(A.D) lastdate</fields>
	<tables>PINSAFEJ U JOIN PINSAFEN A on U.G=A.A and A.C in (1,2,3,6) group by A.A having date &lt; ? order by lastdate</tables>
	<params>
		<param name="changedate" type="Date" label="Latest date" />
	</params>
</report>

<report name="lastchanged2" supporteddbs="internal">
	<title>List of PIN changes older than given date</title>
	<description>Lists users, together with the latest date that their PIN was changed, or the creation date if never changed, starting with the oldest, given a date cutoff</description>
	<headers>
		<header>Username</header>
		<header>Last Changed</header>
	</headers>
	<fields>U.C name, Q.DD lastdate</fields>
	<tables>PINSAFEJ U JOIN (SELECT A, Max(D) DD FROM PINSAFEN where C in (1,2,3,6) group by A) Q on U.G = Q.A</tables>
	<query>Q.DD &lt; ? order by lastdate</query>
	<params>
		<param name="changedate" type="Date" label="Latest date" />
	</params>
</report>


Lists the number of logins for each hour

NOTE: For mariadb and mysql only 

	<report name="loginsByHour">
	<title>List of logins by hour</title>
	<description>Lists the number of logins for each hour</description>
	<headers>
	 	<header>Date/Time</header>
	<header>Count</header>
	</headers>
	<fields>HOUR(E), COUNT(*)</fields>
	<tables>PINSAFEM</tables>
	<query>A=0 GROUP BY HOUR(E)</query>
	</report>

List number of logins for each day and hour for the last 7 days

	<report name="loginsByDayHour">
	<title>List of logins by Day and Hour</title>
	<description>Lists the number of logins for each day and hour for the last 7 days</description>
	<headers>
	 	<header>Date</header>
	 	<header>Hour</header>
	 	<header>Count</header>
	</headers>
	<fields>DATE(E), HOUR(E), COUNT(*)</fields>
	<tables>PINSAFEM</tables>
	<query>A=0 AND E<CURDATE() AND E>DATE_SUB(CURDATE(), INTERVAL 7 DAY) GROUP BY DATE(E), HOUR(E)</query>
	</report>


Internal database reports

Edit reports.xml and search for the following line: 

<report name="idleUsers2" supporteddbs="mysql,mssql,oracle">

If this line cannot be found, or there is no supporteddbs attribute, then it may be an older version, that does not suppport this.

Modify the above line as follows to add internal to the list of supported databases: 

<report name="idleUsers2" supporteddbs="mysql,mssql,oracle,internal">


Internal database, lists users that have not logged in since a specified date

Add a query as follows, Insert this after the other <query ... lines and before <params>. 

<query db="internal">A.D IS NULL OR {fn TIMESTAMPDIFF(SQL_TSI_DAY, CURRENT_DATE, A.D)} > ? </query>

Example 

<query>A.D IS NULL OR A.D < ?</query>
<query db="internal">A.D IS NULL OR {fn TIMESTAMPDIFF(SQL_TSI_DAY, CURRENT_DATE, A.D)} > ? </query>
<params>
  <param name="idledate" type="Date" label="Idle since" />
</params>

Custom reports for multiple database types

The following reports will work with differing database types as the database is specified within the report as db="mysql db="mssql" db="oracle" db="internal"


Users marked as deleted

 	<report name="deleted-users">
 		<title>List of users marked as deleted</title>
 		<description>List all users who are currently marked as deleted.</description>
 		<headers><header>Username</header></headers>
 		<fields>U.H</fields>
 		<tables>PINSAFEJ U INNER JOIN PINSAFEC F ON U.G=F.C AND F.B=4 AND F.D=1</tables>
 	</report>
 	<report name="deleted-users-by-date">
 		<title>List of users marked as deleted recently</title>
 		<description>List all users who have been marked as deleted in the last #days.</description>
 		<headers>
 			<header>Username</header>
 			<header>Date</header>
 		</headers>
 		<fields>U.H, A.D</fields>
 		<tables>PINSAFEJ U INNER JOIN PINSAFEC F ON U.G=F.C AND F.B=4 AND F.D=1 INNER JOIN PINSAFEN A ON U.G=A.A AND A.C=10</tables>
 		<query db="mysql">A.D > DATE_SUB(NOW(), INTERVAL ? DAY)</query>
 		<query db="mssql">A.D > DATEADD(day, -?, GETDATE())</query>
 		<query db="oracle">A.D > ADD_DAYS(SYSDATE, -?)</query>
 		<query db="internal">{fn TIMESTAMPDIFF(SQL_TSI_DAY, A.D, CURRENT_DATE)} < ? </query>
 		<params>
 			<param name="days" type="Integer" label="Cutoff days" />
 		</params>
 	</report>

All User Logins, including login location

NOTE: this will only report logins as far back as audit records are retained (by default, 30 days). Also, the location field is not always reported. Most AgentXML integrations (e.g. OWA) do not report the user's location. RADIUS-based integrations will report the calling workstation ID if the NAS reports it, or else the NAS IP address. 

 <report name="connections"> 
   <title>All recent connections for all users</title> 
   <description>Lists the login times and location (where available) for all users, within the audit retention time</description> 
   <headers> 
     <header>Username</header> 
     <header>Created</header> 
     <header>Last Login</header> 
     <header>Location</header> 
   </headers> 
   <fields>U.H, A1.D, AU.E, AU.B</fields> 
   <tables>PINSAFEJ U INNER JOIN PINSAFEN A1 ON U.G=A1.A AND A1.C=3 LEFT OUTER JOIN PINSAFEM AU ON U.G=AU.G AND AU.C=0</tables> 
   <query>1 ORDER BY U.H, AU.E DESC</query>
 </report>


Other reports

The following reports were tested and made available regarding specific needs:


Idle Users for the last 180 days but created over 30 days

       <report name="idleusers180createdover30">
         <title>Users who never logged in over 180 days but created over 30</title>
           <description>List the number of users who have never logged in over the last 180 days but created more than 30 days ago</description>
         <headers>
         <header>Username</header>
         </headers>
         <fields>U.H</fields>
         <tables>PINSAFEJ U JOIN PINSAFEN A1 ON U.G = A1.A AND A1.C = 3 LEFT OUTER JOIN PINSAFEN A2 ON U.G = A2.A AND A2.C=0</tables>
         <query><![CDATA[(A1.D < DATE_SUB(NOW(), INTERVAL 30 DAY)) AND (A2.D IS NULL OR A2.D < DATE_SUB(NOW(), INTERVAL 180 DAY))]]></query>
       </report>

List inactive users, locked, disabled, deleted and show creation date, login and last PIN change date

       <report name="inactivelockeddisabledUsers">
          <title>Users inactive, locked, disabled, deleted and show creation, login, PIN change date</title>
          <description>List all Users inactive, locked, disabled, deleted and show creation, login, PIN change date</description>
         <headers>
           <header>UID</header>
           <header>REPOS</header>
           <header>DISABLED</header>
           <header>LOCKED</header>
           <header>DELETED</header>
           <header>INACTIVE</header>
           <header>CREATED</header>
           <header>LOGGEDIN</header>
           <header>PINCHANGE</header>
        </headers>
         <fields>U.H, R.B, S1.D, S2.D, S3.D, S4.D, A1.D, A2.D, A3.D</fields>
         <tables><![CDATA[PINSAFEJ U JOIN PINSAFEL R ON U.I=R.A LEFT OUTER JOIN PINSAFEC S1 ON U.G=S1.C AND S1.B=0 LEFT OUTER JOIN PINSAFEC S2 ON U.G=S2.C AND S2.B=1 LEFT OUTER JOIN PINSAFEC S3 ON U.G=S3.C AND S3.B=4 LEFT OUTER JOIN PINSAFEC S4 ON U.G=S4.C AND S4.B=5 LEFT OUTER JOIN PINSAFEN A1 ON U.G=A1.A AND A1.C=3 LEFT OUTER JOIN PINSAFEN A2 ON U.G=A2.A AND A2.C=0 LEFT OUTER JOIN PINSAFEN A3 ON U.G=A3.A AND A3.C=1]]></tables>
       </report>

List Users IDs, email, creation date, last login, last pin change and repository name

       <report name="allUsersdatalastloginandrepo">
              <title>Users IDs, email, date, last login, last pin change and repo name</title>
             <description>Lists all the Users Ids, email address, created date, last login, last pin change and repository name</description>
         <headers>
          <header>Username</header>
          <header>ReposName</header>
          <header>Email</header>
          <header>CreateDate</header>
          <header>LoginDate</header>
          <header>PINChange</header>
         </headers>
         <fields>U.H, U.E, T.A, A1.D, A2.D, A3.D</fields>
         <tables><![CDATA[PINSAFEJ U LEFT OUTER JOIN PINSAFEA T ON U.G = T.C LEFT OUTER JOIN PINSAFEN A1 ON U.G = A1.A AND A1.C = 3 LEFT OUTER JOIN PINSAFEN A2 ON U.G = A2.A AND A2.C = 0 LEFT OUTER JOIN PINSAFEN A3 ON U.G = A3.A AND A3.C = 1]]></tables>
        </report>


Writing Your Own Reports

This section is intended only for customers who are familiar with writing SQL queries, and with XML.

Database Schema

While writing your report, refer to the Database Schema page for reference on the database table and column names used by Swivel.

Report Definition Format

A report definition is an xml element, which includes elements of a SQL query, plus display information. The format is: 

<report name="reportName">
 <title>Report Title</title>
 <description>Report description</description>
 <headers>
   <header>header1</header>
   ...
 </headers>
 <fields>SQL-fields</fields>
 <tables>SQL-tables-and-joins</tables>
 <query>SQL-query</query>
 <params>
   <param name="param-name" label="param-label" type="String|Integer|Date" />
   ...
 </params>
</report>

The report name is used internally, but must be unique.

<title>, <description> and <headers> are display elements: the title is displayed in the drop-down and at the top of the report, while the description is shown when the report is selected. Headers are individual column headers, and the number of headers should match the number of columns in the field list.

The SQL query is built as 

SELECT SQL-fields FROM SQL-tables-and-joins WHERE SQL-query

The <query> element is optional: if missing, no WHERE clause is added. There are no elements for ORDER BY or GROUP BY, but you can add "ORDER BY" or "GROUP BY" to the query element, or to the tables element if there is no query.

The <params> elements are used to set values for replaceable parameters within the query. <param> elements are applied in the order listed. The name attribute is used to identify the parameter internally. The label attribute is displayed on-screen when requesting parameter values. The type attribute must be "String", "Integer" or "Date" - these values are case-sensitive.

Troubleshooting

Versions 3.8 to 3.9.3 may produce a HTTP Status 500 error when selecting a new report:

java.lang.IllegalArgumentException: fromIndex(50) > toIndex(0)

java.util.SubList.<init>(Unknown Source)

java.util.AbstractrList.subList (Unknown Source)

com.swiveltechnologies.pinsafe.server.reporting.ReportResult.getRows (ReportResult.java:122)

This is caused by last page you were on when you used a report. For example if you were on page 4 of one particular report, but then attempt to access another report which does not contain 4 pages, it will attempt to access page 4 of that report instead of page 1 and fail with the error you sent.

So the solution is to upgrade, or as a workaround, make sure that you request page 1 of a report before leaving the reporting page. Closing down the web browser should also alleviate this.

Retrieved from "https://kb.swivelsecure.com/w/index.php?title=Reporting_How_to_guide&oldid=5111"