1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
|
# --
# Copyright (C) 2001-2021 OTRS AG, https://otrs.com/
# --
# This software comes with ABSOLUTELY NO WARRANTY. For details, see
# the enclosed file COPYING for license information (GPL). If you
# did not receive this file, see https://www.gnu.org/licenses/gpl-3.0.txt.
# --
package Kernel::System::SupportDataCollector::Plugin::OTRS::DatabaseRecords;
use strict;
use warnings;
use parent qw(Kernel::System::SupportDataCollector::PluginBase);
use Kernel::Language qw(Translatable);
our @ObjectDependencies = (
'Kernel::Config',
'Kernel::System::DB',
'Kernel::System::Ticket',
);
sub GetDisplayPath {
return Translatable('OTRS') . '/' . Translatable('Database Records');
}
sub Run {
my $Self = shift;
my @Checks = (
{
SQL => "SELECT count(*) FROM ticket",
Identifier => 'TicketCount',
Label => Translatable("Tickets"),
},
{
SQL => "SELECT count(*) FROM ticket_history",
Identifier => 'TicketHistoryCount',
Label => Translatable("Ticket History Entries"),
},
{
SQL => "SELECT count(*) FROM article",
Identifier => 'ArticleCount',
Label => Translatable("Articles"),
},
{
SQL =>
"SELECT count(*) from article_data_mime_attachment WHERE content_type NOT LIKE 'text/html%'",
Identifier => 'AttachmentCountDBNonHTML',
Label => Translatable("Attachments (DB, Without HTML)"),
},
{
SQL => "SELECT count(DISTINCT(customer_user_id)) FROM ticket",
Identifier => 'DistinctTicketCustomerCount',
Label => Translatable("Customers With At Least One Ticket"),
},
{
SQL => "SELECT count(*) FROM queue",
Identifier => 'QueueCount',
Label => Translatable("Queues"),
},
{
SQL => "SELECT count(*) FROM service",
Identifier => 'ServiceCount',
Label => Translatable("Services"),
},
{
SQL => "SELECT count(*) FROM users",
Identifier => 'AgentCount',
Label => Translatable("Agents"),
},
{
SQL => "SELECT count(*) FROM roles",
Identifier => 'RoleCount',
Label => Translatable("Roles"),
},
{
SQL => "SELECT count(*) FROM groups",
Identifier => 'GroupCount',
Label => Translatable("Groups"),
},
{
SQL => "SELECT count(*) FROM dynamic_field",
Identifier => 'DynamicFieldCount',
Label => Translatable("Dynamic Fields"),
},
{
SQL => "SELECT count(*) FROM dynamic_field_value",
Identifier => 'DynamicFieldValueCount',
Label => Translatable("Dynamic Field Values"),
},
{
SQL => "SELECT count(*) FROM dynamic_field WHERE valid_id > 1",
Identifier => 'InvalidDynamicFieldCount',
Label => Translatable("Invalid Dynamic Fields"),
},
{
SQL => "
SELECT count(*)
FROM dynamic_field_value
JOIN dynamic_field ON dynamic_field.id = dynamic_field_value.field_id
WHERE dynamic_field.valid_id > 1",
Identifier => 'InvalidDynamicFieldValueCount',
Label => Translatable("Invalid Dynamic Field Values"),
},
{
SQL => "SELECT count(*) FROM gi_webservice_config",
Identifier => 'WebserviceCount',
Label => Translatable("GenericInterface Webservices"),
},
{
SQL => "SELECT count(*) FROM pm_process",
Identifier => 'ProcessCount',
Label => Translatable("Processes"),
},
{
SQL => "
SELECT count(*)
FROM dynamic_field df
LEFT JOIN dynamic_field_value dfv ON df.id = dfv.field_id
RIGHT JOIN ticket t ON t.id = dfv.object_id
WHERE df.name = '"
. $Kernel::OM->Get('Kernel::Config')->Get("Process::DynamicFieldProcessManagementProcessID") . "'",
Identifier => 'ProcessTickets',
Label => Translatable("Process Tickets"),
},
);
# get database object
my $DBObject = $Kernel::OM->Get('Kernel::System::DB');
my %Counts;
CHECK_RECORDS:
for my $Check (@Checks) {
$DBObject->Prepare( SQL => $Check->{SQL} );
while ( my @Row = $DBObject->FetchrowArray() ) {
$Counts{ $Check->{Identifier} } = $Row[0];
}
if ( defined $Counts{ $Check->{Identifier} } ) {
$Self->AddResultInformation(
Identifier => $Check->{Identifier},
Label => $Check->{Label},
Value => $Counts{ $Check->{Identifier} },
);
}
else {
$Self->AddResultProblem(
Identifier => $Check->{Identifier},
Label => $Check->{Label},
Value => $Counts{ $Check->{Identifier} },
Message => Translatable('Could not determine value.'),
);
}
}
$DBObject->Prepare(
SQL => "SELECT max(create_time), min(create_time) FROM ticket WHERE id > 1 ",
);
my $TicketWindowTime = 1;
while ( my @Row = $DBObject->FetchrowArray() ) {
if ( $Row[0] && $Row[1] ) {
my $OldestCreateTimeObject = $Kernel::OM->Create(
'Kernel::System::DateTime',
ObjectParams => {
String => $Row[0],
},
);
my $NewestCreateTimeObject = $Kernel::OM->Create(
'Kernel::System::DateTime',
ObjectParams => {
String => $Row[1],
},
);
my $Delta = $NewestCreateTimeObject->Delta( DateTimeObject => $OldestCreateTimeObject );
$TicketWindowTime = ( $Delta->{Years} * 12 ) + $Delta->{Months};
}
}
$TicketWindowTime = 1 if $TicketWindowTime < 1;
$Self->AddResultInformation(
Identifier => 'TicketWindowTime',
Label => Translatable('Months Between First And Last Ticket'),
Value => $TicketWindowTime,
);
$Self->AddResultInformation(
Identifier => 'TicketsPerMonth',
Label => Translatable('Tickets Per Month (avg)'),
Value => sprintf( "%d", $Counts{TicketCount} / $TicketWindowTime ),
);
my $OpenTickets = $Kernel::OM->Get('Kernel::System::Ticket')->TicketSearch(
Result => 'COUNT',
StateType => 'Open',
UserID => 1,
) || 0;
$Self->AddResultInformation(
Identifier => 'TicketOpenCount',
Label => Translatable('Open Tickets'),
Value => $OpenTickets,
);
return $Self->GetResults();
}
1;
|
