Sparx Systems Forum
Enterprise Architect => General Board => Topic started by: Typia on March 03, 2020, 10:04:09 pm
-
Hi,
I'm trying to produce some kind of EA usage stats for the stakeholders.
We have a SQL server based repos with one db per project and floating licenses.
Regarding projects, I'd like to get stats on last modified date per project for example, or last modified model.
Regarding users, I'd like to have stats about how many users use the system, tried to produce something with ssks server but the log is hardly exploitable.
Would you guys have a solution for it ?
Many thanks
-
Hi there Typia,
I have something which may help as I produced some usage stats a few years ago.
Will get it together and attach it for you.
Cheers,
Rupert
-
I'm pretty sure that in ancient EA versions there was some kind of statistics. But it seems to have gone. I search for it a while ago too. Probly a simple count with SQL could give quite some interesting numbers.
q.
-
I'm pretty sure that in ancient EA versions there was some kind of statistics. But it seems to have gone. I search for it a while ago too. Probly a simple count with SQL could give quite some interesting numbers.
q.
Yeah I remember that. I came across it accidentally in a more recent version V13. or V14 and remember thinking thats not easy to find.
Can't seem to find it again on V14.1
-
Maybe a Sparxian can help out. The Start menu does not show anything when looking for "stati"(stics).
q.
-
Try the ribbon Construct > Project Management > QA > Statistics. It is, however, a count of model structures, and might not give you the information you want.
You might be able to extract the data you need from Resource Management, or one of the built-in searches (such as Common Searches | Recently Modified Elements). Alternatively, a tailored search like the one offered by rupertkiwi.
-
Hi Typia
1. Create a new Excel workbook with two worksheets: 'PivotTable' and 'Datasheet'
2. Navigate to this location on the machine where you are running the Keystore service for SPARX EA: C:\Program Files (x86)\Sparx Systems\Keystore\Service\Logs
3. There should be 3 log files there named : ssksLog-?
4. Open one of the log files, copy the contents and paste them into cell A1 of the worksheet 'Datasheet'
5. Paste the code below into the VB editor of the Excel workbook and run it :
Sub HideRows()
beginrow = 1
chkcol = 1
rowcnt = beginrow
Do Until IsEmpty(Cells(rowcnt, chkcol))
If InStr(1, Cells(rowcnt, chkcol), "[INFO]: CHECKOUT SUCCESS") = 0 And InStr(1, Cells(rowcnt, chkcol), "[INFO]: CHECKIN SUCCESS") = 0 Then
Cells(rowcnt, chkcol).EntireRow.Delete
Else: rowcnt = rowcnt + 1
End If
Loop
Txt2Col
End Sub
Sub Txt2Col()
Dim rng As Range
Set rng = [A1]
Set rng = Range(rng, Cells(Rows.Count, rng.Column).End(xlUp))
rng.TextToColumns Destination:=rng, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=True, Other:=False, _
FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), TrailingMinusNumbers:=True
Rows(1).Insert shift:=xlShiftDown
Cells(1, 1).Value = "Date"
Cells(1, 2).Value = "Time"
Cells(1, 3).Value = "c"
Cells(1, 4).Value = "State"
Cells(1, 5).Value = "e"
Cells(1, 6).Value = "User"
Cells(1, 7).Value = "g"
Cells(1, 8).Value = "h"
Cells(1, 9).Value = "i"
Cells(1, 10).Value = "j"
Cells(1, 11).Value = "k"
Cells(1, 12).Value = "l"
Cells(1, 13).Value = "Key"
Cells(1, 14).Value = "n"
Cells(1, 15).Value = "o"
Cells(1, 16).Value = "p"
Cells(1, 17).Value = "Userpc"
Cells(1, 18).Value = "ExpiryDate"
Cells(1, 19).Value = "ExpiryTime"
newPVT
End Sub
Sub newPVT()
'Declare Variables
Dim PSheet As Worksheet
Dim DSheet As Worksheet
Dim PCache As PivotCache
Dim PTable As PivotTable
Dim PRange As Range
Dim LastRow As Long
Dim LastCol As Long
'Delete Preivous Pivot Table Worksheet & Insert a New Blank Worksheet With Same Name
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("PivotTable").Delete
Sheets.Add Before:=ActiveSheet
ActiveSheet.Name = "PivotTable"
Application.DisplayAlerts = True
Set PSheet = Worksheets("PivotTable")
Set DSheet = Worksheets("DataSheet")
'Define Data Range
LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)
'Define Pivot Cache
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange). _
CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _
TableName:="SalesPivotTable")
'Insert Blank Pivot Table
Set PTable = PCache.CreatePivotTable(TableDestination:=PSheet.Cells(1, 1), TableName:="SalesPivotTable")
'Insert Row Fields
With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("User")
.Orientation = xlRowField
.Position = 1
End With
'With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Month")
' .Orientation = xlRowField
' .Position = 2
'End With
'Insert Column Fields
With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Date")
.Orientation = xlColumnField
.Position = 1
End With
'Insert Data Field
With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Date")
.Orientation = xlDataField
.Position = 1
.Function = xlCount
' .NumberFormat = "#,##0"
.Name = "Users"
'Insert Column Fields
With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("State")
.Orientation = xlPageField
.Position = 1
.CurrentPage = "CHECKIN"
End With
'Format Pivot Table
ActiveSheet.PivotTables("SalesPivotTable").ShowTableStyleRowStripes = True
ActiveSheet.PivotTables("SalesPivotTable").TableStyle2 = "PivotStyleMedium9"
ActiveSheet.PivotTables("SalesPivotTable").ColumnGrand = False
ActiveSheet.PivotTables("SalesPivotTable").RowGrand = False
End With
End Sub
-
Try the ribbon Construct > Project Management > QA > Statistics. It is, however, a count of model structures, and might not give you the information you want.
You might be able to extract the data you need from Resource Management, or one of the built-in searches (such as Common Searches | Recently Modified Elements). Alternatively, a tailored search like the one offered by rupertkiwi.
V13 does not have that menu. Geez . How can you maintain a product that's unstable in what users experience first: the UI??? U stands for USER, not UNIQUE. Gaah... (don't take it personally, Roy. Others are to blame.)
q.
-
OK, for Release 13.0 try:
Construct > Project > Manage > Project Status (Tasks and Issues) > Project Statistics
But what rupertkiwi has given you will be more relevant to your needs.
-
Yes, that's the one I remembered. Honestly, Sparx should get a grip on this. Moving everything around each release will not improve user experience. But what am I talking?
As another fun fact: V13.0 never saw the light. There's only 13.5 around here.
q.
-
Have you looked into Sparx charting capabilities? We have put together some useful dashboards.
Please see https://sparxsystems.com/enterprise_architect_user_guide/15.1/model_publishing/charts.html
-
As another fun fact: V13.0 never saw the light. There's only 13.5 around here.
13.0 is available under "Previous Versions of Enterprise Architect" at https://www.sparxsystems.com/registered/reg_ea_down.html (https://www.sparxsystems.com/registered/reg_ea_down.html)
-
As another fun fact: V13.0 never saw the light. There's only 13.5 around here.
13.0 is available under "Previous Versions of Enterprise Architect" at https://www.sparxsystems.com/registered/reg_ea_down.html (https://www.sparxsystems.com/registered/reg_ea_down.html)
Oh yes. Stupid me. It were just 13.1, 13.2, 13.3 and 13.4 which did not make it.
q.
-
Guys thanks a lot for your help !
I'm gonna try @rupertkiwi solution as soon as I get my logs
Many thanks again
-
Hi guys !
Sorry for the late reply.
Hope you're all doing fine in those crazy times.
I tried the sskslog approach.
I compiled the multiple files into one and put it into a powerbi file (wanted to have something user friendly to produce regular stats"
Filtered checkin light to get the AD user account.
Problem is that stats produced seem pretty far from reality (about 40 total connections to keystore since december while I have in a sole team 3 users using the software for sure everyday.)
I'm pretty lost at this point.
Isn't it possible to use a sql query either in EA or in SQL management studio to get stats like :
- Last user logged
- Last item modified
- Last item modified date
- Package numbers in the repo
- Security enabled yes / no
- Audit enabled yes / no
- Versioning enabled yes / no
If I would be able to get this result from every repo in the database this would be great.
Many thanks and god Bless you and keep you safe
-
You can get
- Last user logged => no
- Last item modified => (if it's an element or a diagram)
- Last item modified date => (for an element or a diagram)
- Package numbers in the repo => yes
- Security enabled yes / no => yes
- Audit enabled yes / no => yes
- Versioning enabled yes / no => yes
Geert
-
You can get
- Last user logged => no
- Last item modified => (if it's an element or a diagram)
- Last item modified date => (for an element or a diagram)
- Package numbers in the repo => yes
- Security enabled yes / no => yes
- Audit enabled yes / no => yes
- Versioning enabled yes / no => yes
Geert
Hi Geert thanks for your answer,
If you have queries for those purpose I'm in !
-
This 2 queries should give you the last modified elements and diagrams in the repository and the number of packages, including root nodes, in the repository (from t_package instead of t_element).
SELECT Object_ID AS ID, 'Element.' & Object_Type AS Type, Name, ModifiedDate
FROM t_object
WHERE ModifiedDate = (SELECT MAX(ModifiedDate) FROM t_object)
UNION ALL
SELECT Diagram_ID, 'Diagram.' & Diagram_Type, Name, ModifiedDate FROM t_diagram
WHERE ModifiedDate = (SELECT MAX(ModifiedDate) FROM t_diagram)
SELECT count(*) FROM t_package
-
This 2 queries should give you the last modified elements and diagrams in the repository and the number of packages, including root nodes, in the repository (from t_package instead of t_element).
SELECT Object_ID AS ID, 'Element.' & Object_Type AS Type, Name, ModifiedDate
FROM t_object
WHERE ModifiedDate = (SELECT MAX(ModifiedDate) FROM t_object)
UNION ALL
SELECT Diagram_ID, 'Diagram.' & Diagram_Type, Name, ModifiedDate FROM t_diagram
WHERE ModifiedDate = (SELECT MAX(ModifiedDate) FROM t_diagram)
SELECT count(*) FROM t_package
Hi Modesto,
thanks a lot for your answer.
When I run the first query, I have an error message : " The data types varchar and nvarchar are incompatible in the '&' operator. "
I run it from sql management studio directly connected to database.
-
https://stackoverflow.com/questions/14055400/sql-server-datatypes-nvarchar-and-varchar-are-incompatible-error (https://stackoverflow.com/questions/14055400/sql-server-datatypes-nvarchar-and-varchar-are-incompatible-error)
q.
-
https://stackoverflow.com/questions/14055400/sql-server-datatypes-nvarchar-and-varchar-are-incompatible-error (https://stackoverflow.com/questions/14055400/sql-server-datatypes-nvarchar-and-varchar-are-incompatible-error)
q.
thanks a lot !!
-
Google is always (well, in certain conditions) your friend. Strange error message? Just google and see if it hits StackOverflow :-)
q.