I’m looking to create a simple table-based report which displays the number of operators assigned to a given role. I would like the report output to look like this:
Role 1: 12
Role 2: 4
Role 3: 0
As an enhancement to that, I would love to be able to display the count of actions deployed by users in that role, followed by last login time for a user in that role.
So, this would look like this:
Rolename Users Actions Last Login Time
Role 1: 12: 23: 03 20 2013 12:01pm
I have tried to do this by piecing together bits of relevance I have found here but I can’t seem to get it to work.
So, I have a very basic query working for the count of users per role, plus the number of actions issued by each role. I would like to include the last login time in this report, and it would be great to get this in tabular format. I’m having trouble coming up with the syntax for the last login time as well as putting this into the better looking table format versus the CSV format I have in.
Any help would be much appreciated.
Working relevance:
<?relevance
( (name of it, number of users of it, number of issued actions of users of it) of bes roles
)
?>
All the Relevances below should be enclosed with the <?relevance ?> as you have done if you want to run them within Web Reports.
Here is the the example with last login time.
( name of it, number of users of it, number of issued actions of users of it, maximum of last login times of users of it) of bes roles
Additional formatting to make the time a little easier to read.
( name of it, number of users of it, number of issued actions of users of it, ((year of it as string &
"/" & month of it as two digits &
"/" & day_of_month of it as two digits) of date(local time zone) of it &
" " & (two digit hour of it as string &
":" & two digit minute of it as string &
":" & two digit second of it as string) of time (local time zone) of it) of maximum of last login times of users of it) of bes roles
With HTML formatting
(html
"<table cellpadding=%225px%22 border=1 style=%22border-collapse: collapse; border: medium none;%22>" & html
"<tr><th>Role</th><th>Users</th><th>Actions</th><th>Last Login</th></tr>" & html (it) & html
"</table>") of concatenation of (unique values of (it as string)) of trs of ( td of name of it & td of (it as string) of number of users of it & td of (it as string) of number of issued actions of users of it & td of ((year of it as string &
"/" & month of it as two digits &
"/" & day_of_month of it as two digits) of date(local time zone) of it &
" " & (two digit hour of it as string &
":" & two digit minute of it as string &
":" & two digit second of it as string) of time (local time zone) of it) of maximum of last login times of users of it) of bes roles
Thanks for taking the time to help. I am running into a problem with the last relevance. The error I am returned when I preview this report is “Singular expression refers to non-unique object.”
After some poking around, I beleive what may be happening is that the relevance is erroring out on the ‘maximum of last login times’ because it sees an empty set of login times for a given role. I.E. no user in a particular role has ever logged in, and therefore there isn’t a value in this field to compare to.
Is there a way to include some syntax to display “Never” if there is no login time found for users in a given role?
Sorry, my mistake, the error message is ‘Singular expression refers to non-existant object’. When trying a few different things (changing ‘last login times’ to ‘last login time’) I received the ‘non-unique’ version of this error.
This should fix it. I added the | “Never” towards the end.
The pipe | character means if there is an error, return “Never” instead.
This syntax only works in version 8.2 or later of TEM. If you have earlier version, we will have to do something different.
(html
"<table cellpadding=%225px%22 border=1 style=%22border-collapse: collapse; border: medium none;%22>" & html
"<tr><th>Role</th><th>Users</th><th>Actions</th><th>Last Login</th></tr>" & html (it) & html
"</table>") of concatenation of (unique values of (it as string)) of trs of ( td of name of it & td of (it as string) of number of users of it & td of (it as string) of number of issued actions of users of it & td of (((year of it as string &
"/" & month of it as two digits &
"/" & day_of_month of it as two digits) of date(local time zone) of it &
" " & (two digit hour of it as string &
":" & two digit minute of it as string &
":" & two digit second of it as string) of time (local time zone) of it) of maximum of last login times of users of it |
"Never")) of bes roles
Note that the action counts are a little deceiving because it’s counting total actions, so if you push a baseline, it counts each subaction of the baseline as an action. But still, a very helpful report.