Reporting How To Guide

From Swivel Knowledgebase Wiki

Contents 1 Overview 2 Configuration 3 Adding new reports 4 Considerations

Overview

This article describes how to deploy reports into the new built-in reporting feature of PINsafe 3.8. This should not be confused with other reporting methods.

Other articles pertaining to reports are: ReportingAPI, Reporting Using Agent-XML How to Guide and MySQL Queries How To Guide.

Built in reporting allows you to access custom reports through the built-in Reporting link on the PINsafe Administration Console:

Configuration

The report definition file is under /usr/local/tomcat/webapps/pinsafe/WEB-INF/conf/reports.xml. 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.

Here is an example of some additional reporting functionality:

<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>

To add this to the built-in reporting feature of PINsafe, you would append this to the /usr/local/tomcat/webapps/pinsafe/WEB-INF/conf/reports.xml file immediately before the last line:

 </reports>

Considerations

Before adding new content to this configuration file, be sure to back it up.