1 /* Copyright (C) 2018-2021 Greenbone Networks GmbH
2 *
3 * SPDX-License-Identifier: AGPL-3.0-or-later
4 *
5 * This program is free software: you can redistribute it and/or modify
6 * it under the terms of the GNU Affero General Public License as
7 * published by the Free Software Foundation, either version 3 of the
8 * License, or (at your option) any later version.
9 *
10 * This program is distributed in the hope that it will be useful,
11 * but WITHOUT ANY WARRANTY; without even the implied warranty of
12 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13 * GNU Affero General Public License for more details.
14 *
15 * You should have received a copy of the GNU Affero General Public License
16 * along with this program. If not, see <http://www.gnu.org/licenses/>.
17 */
18
19 /**
20 * @file manage_sql_tickets.c
21 * @brief GVM management layer: Ticket SQL
22 *
23 * The Ticket SQL for the GVM management layer.
24 */
25
26 #include "manage_tickets.h"
27 #include "manage_acl.h"
28 #include "manage_sql_tickets.h"
29 #include "manage_sql.h"
30 #include "sql.h"
31
32 #include <stdlib.h>
33 #include <string.h>
34
35 #undef G_LOG_DOMAIN
36 /**
37 * @brief GLib log domain.
38 */
39 #define G_LOG_DOMAIN "md manage"
40
41 /**
42 * @brief Ticket statuses.
43 */
44 typedef enum
45 {
46 TICKET_STATUS_OPEN = 0,
47 TICKET_STATUS_FIXED = 1,
48 TICKET_STATUS_FIX_VERIFIED = 2,
49 TICKET_STATUS_CLOSED = 3,
50 TICKET_STATUS_MAX,
51 TICKET_STATUS_ERROR = 100
52 } ticket_status_t;
53
54 /**
55 * @brief Get ticket status DB identifier from string.
56 *
57 * @param[in] status Status name.
58 *
59 * @return Status integer.
60 */
61 static ticket_status_t
ticket_status_integer(const char * status)62 ticket_status_integer (const char *status)
63 {
64 if (strcasecmp (status, "open") == 0)
65 return TICKET_STATUS_OPEN;
66 if (strcasecmp (status, "fixed") == 0)
67 return TICKET_STATUS_FIXED;
68 if (strcasecmp (status, "fix verified") == 0)
69 return TICKET_STATUS_FIX_VERIFIED;
70 if (strcasecmp (status, "closed") == 0)
71 return TICKET_STATUS_CLOSED;
72 return TICKET_STATUS_ERROR;
73 }
74
75 /**
76 * @brief Filter columns for ticket iterator.
77 */
78 #define TICKET_ITERATOR_FILTER_COLUMNS \
79 { GET_ITERATOR_FILTER_COLUMNS, "severity", "host", "location", \
80 "solution_type", "status", "opened", "fixed", "closed", "orphan", \
81 "result_id", "username", NULL }
82
83 /**
84 * @brief Ticket iterator columns.
85 */
86 #define TICKET_ITERATOR_COLUMNS \
87 { \
88 GET_ITERATOR_COLUMNS (tickets), \
89 { \
90 "(SELECT uuid FROM users WHERE id = assigned_to)", \
91 NULL, \
92 KEYWORD_TYPE_STRING \
93 }, \
94 { \
95 "(SELECT uuid FROM tasks WHERE id = task)", \
96 NULL, \
97 KEYWORD_TYPE_STRING \
98 }, \
99 { \
100 "(SELECT uuid FROM reports WHERE id = report)", \
101 NULL, \
102 KEYWORD_TYPE_STRING \
103 }, \
104 { \
105 "(CASE" \
106 " WHEN (SELECT EXISTS (SELECT * FROM ticket_results" \
107 " WHERE ticket = tickets.id))" \
108 " THEN (SELECT new_severity FROM result_new_severities" \
109 " WHERE result_new_severities.result" \
110 " = (SELECT result FROM ticket_results" \
111 " WHERE ticket = tickets.id" \
112 " LIMIT 1)" \
113 " AND result_new_severities.user" \
114 " = gvmd_user ()" \
115 " AND result_new_severities.dynamic = 0" \
116 " LIMIT 1)" \
117 " ELSE severity" \
118 " END)", \
119 "severity", \
120 KEYWORD_TYPE_DOUBLE \
121 }, \
122 { "host", NULL, KEYWORD_TYPE_STRING }, \
123 { "location", NULL, KEYWORD_TYPE_STRING }, \
124 { "solution_type", NULL, KEYWORD_TYPE_STRING }, \
125 { "(CASE status" \
126 " WHEN 0 THEN 'Open'" \
127 " WHEN 1 THEN 'Fixed'" \
128 " WHEN 2 THEN 'Fix Verified'" \
129 " WHEN 3 THEN 'Closed'" \
130 " ELSE 'Error' END)", \
131 "status", \
132 KEYWORD_TYPE_STRING }, \
133 { "iso_time (open_time)", NULL, KEYWORD_TYPE_STRING }, \
134 { "open_time", "opened", KEYWORD_TYPE_INTEGER }, \
135 { "iso_time (fixed_time)", NULL, KEYWORD_TYPE_STRING }, \
136 { "fixed_time", "fixed", KEYWORD_TYPE_INTEGER }, \
137 { "iso_time (closed_time)", NULL, KEYWORD_TYPE_STRING }, \
138 { "closed_time", "closed", KEYWORD_TYPE_INTEGER }, \
139 { "iso_time (fix_verified_time)", NULL, KEYWORD_TYPE_STRING }, \
140 { "fix_verified_time", "fix_verified", KEYWORD_TYPE_INTEGER }, \
141 { \
142 "(task = -1 OR report = -1)", \
143 "orphan", \
144 KEYWORD_TYPE_INTEGER \
145 }, \
146 { "open_note", NULL, KEYWORD_TYPE_STRING }, \
147 { "fixed_note", NULL, KEYWORD_TYPE_STRING }, \
148 { "closed_note", NULL, KEYWORD_TYPE_STRING }, \
149 { \
150 "(SELECT uuid FROM reports WHERE id = fix_verified_report)", \
151 NULL, \
152 KEYWORD_TYPE_STRING \
153 }, \
154 { "nvt", NULL, KEYWORD_TYPE_STRING }, \
155 { \
156 "(SELECT name FROM users WHERE id = assigned_to)", \
157 "username", \
158 KEYWORD_TYPE_STRING \
159 }, \
160 { \
161 "(SELECT name FROM tasks WHERE id = task)", \
162 NULL, \
163 KEYWORD_TYPE_STRING \
164 }, \
165 { \
166 "(SELECT result_uuid FROM ticket_results" \
167 " WHERE ticket = tickets.id" \
168 " AND result_location = " G_STRINGIFY (LOCATION_TABLE) \
169 " LIMIT 1)", \
170 "result_id", \
171 KEYWORD_TYPE_STRING \
172 }, \
173 { NULL, NULL, KEYWORD_TYPE_UNKNOWN } \
174 }
175
176 /**
177 * @brief Ticket iterator columns for trash case.
178 */
179 #define TICKET_ITERATOR_TRASH_COLUMNS \
180 { \
181 GET_ITERATOR_COLUMNS (tickets_trash), \
182 { \
183 "(SELECT uuid FROM users WHERE id = assigned_to)", \
184 NULL, \
185 KEYWORD_TYPE_STRING \
186 }, \
187 { \
188 "(SELECT uuid FROM tasks WHERE id = task)", \
189 NULL, \
190 KEYWORD_TYPE_STRING \
191 }, \
192 { \
193 "(SELECT uuid FROM reports WHERE id = report)", \
194 NULL, \
195 KEYWORD_TYPE_STRING \
196 }, \
197 { "severity", NULL, KEYWORD_TYPE_DOUBLE }, \
198 { "host", NULL, KEYWORD_TYPE_STRING }, \
199 { "location", NULL, KEYWORD_TYPE_STRING }, \
200 { "solution_type", NULL, KEYWORD_TYPE_STRING }, \
201 { "(CASE status" \
202 " WHEN 0 THEN 'Open'" \
203 " WHEN 1 THEN 'Fixed'" \
204 " WHEN 2 THEN 'Fix Verified'" \
205 " WHEN 3 THEN 'Closed'" \
206 " ELSE 'Error' END)", \
207 "status", \
208 KEYWORD_TYPE_STRING }, \
209 { "iso_time (open_time)", NULL, KEYWORD_TYPE_STRING }, \
210 { "open_time", "opened", KEYWORD_TYPE_INTEGER }, \
211 { "iso_time (fixed_time)", NULL, KEYWORD_TYPE_STRING }, \
212 { "fixed_time", "fixed", KEYWORD_TYPE_INTEGER }, \
213 { "iso_time (closed_time)", NULL, KEYWORD_TYPE_STRING }, \
214 { "closed_time", "closed", KEYWORD_TYPE_INTEGER }, \
215 { "iso_time (fix_verified_time)", NULL, KEYWORD_TYPE_STRING }, \
216 { "fix_verified_time", "fix_verified", KEYWORD_TYPE_INTEGER }, \
217 { \
218 "(task = -1 OR report = -1)", \
219 "orphan", \
220 KEYWORD_TYPE_INTEGER \
221 }, \
222 { "open_note", NULL, KEYWORD_TYPE_STRING }, \
223 { "fixed_note", NULL, KEYWORD_TYPE_STRING }, \
224 { "closed_note", NULL, KEYWORD_TYPE_STRING }, \
225 { \
226 "(SELECT uuid FROM reports WHERE id = fix_verified_report)", \
227 NULL, \
228 KEYWORD_TYPE_STRING \
229 }, \
230 { "nvt", NULL, KEYWORD_TYPE_STRING }, \
231 { \
232 "(SELECT name FROM users WHERE id = assigned_to)", \
233 NULL, \
234 KEYWORD_TYPE_STRING \
235 }, \
236 { \
237 "(SELECT name FROM tasks WHERE id = task)", \
238 NULL, \
239 KEYWORD_TYPE_STRING \
240 }, \
241 { \
242 "(SELECT result_uuid FROM ticket_results_trash" \
243 " WHERE ticket = tickets_trash.id" \
244 " AND result_location = " G_STRINGIFY (LOCATION_TABLE) \
245 " LIMIT 1)", \
246 "result_id", \
247 KEYWORD_TYPE_STRING \
248 }, \
249 { NULL, NULL, KEYWORD_TYPE_UNKNOWN } \
250 }
251
252 /**
253 * @brief Count number of tickets.
254 *
255 * @param[in] get GET params.
256 *
257 * @return Total number of tickets in filtered set.
258 */
259 int
ticket_count(const get_data_t * get)260 ticket_count (const get_data_t *get)
261 {
262 static const char *extra_columns[] = TICKET_ITERATOR_FILTER_COLUMNS;
263 static column_t columns[] = TICKET_ITERATOR_COLUMNS;
264 static column_t trash_columns[] = TICKET_ITERATOR_TRASH_COLUMNS;
265
266 return count ("ticket", get, columns, trash_columns, extra_columns, 0, 0, 0,
267 TRUE);
268 }
269
270 /**
271 * @brief Initialise a ticket iterator.
272 *
273 * @param[in] iterator Iterator.
274 * @param[in] get GET data.
275 *
276 * @return 0 success, 1 failed to find ticket, 2 failed to find filter,
277 * -1 error.
278 */
279 int
init_ticket_iterator(iterator_t * iterator,const get_data_t * get)280 init_ticket_iterator (iterator_t *iterator, const get_data_t *get)
281 {
282 static const char *filter_columns[] = TICKET_ITERATOR_FILTER_COLUMNS;
283 static column_t columns[] = TICKET_ITERATOR_COLUMNS;
284 static column_t trash_columns[] = TICKET_ITERATOR_TRASH_COLUMNS;
285
286 return init_get_iterator (iterator,
287 "ticket",
288 get,
289 columns,
290 trash_columns,
291 filter_columns,
292 0,
293 NULL,
294 NULL,
295 TRUE);
296 }
297
298 /**
299 * @brief Get a column value from a ticket iterator.
300 *
301 * @param[in] iterator Iterator.
302 *
303 * @return Value of the column or NULL if iteration is complete.
304 */
305 DEF_ACCESS (ticket_iterator_user_id, GET_ITERATOR_COLUMN_COUNT);
306
307 /**
308 * @brief Get a column value from a ticket iterator.
309 *
310 * @param[in] iterator Iterator.
311 *
312 * @return Value of the column or NULL if iteration is complete.
313 */
314 DEF_ACCESS (ticket_iterator_task_id, GET_ITERATOR_COLUMN_COUNT + 1);
315
316 /**
317 * @brief Get a column value from a ticket iterator.
318 *
319 * @param[in] iterator Iterator.
320 *
321 * @return Value of the column or NULL if iteration is complete.
322 */
323 DEF_ACCESS (ticket_iterator_report_id, GET_ITERATOR_COLUMN_COUNT + 2);
324
325 /**
326 * @brief Get a column value from a ticket iterator.
327 *
328 * @param[in] iterator Iterator.
329 *
330 * @return Value of the column, or SEVERITY_MISSING if iteration is complete.
331 */
332 double
ticket_iterator_severity(iterator_t * iterator)333 ticket_iterator_severity (iterator_t* iterator)
334 {
335 if (iterator->done) return SEVERITY_MISSING;
336 return iterator_double (iterator, GET_ITERATOR_COLUMN_COUNT + 3);
337 }
338
339 /**
340 * @brief Get a column value from a ticket iterator.
341 *
342 * @param[in] iterator Iterator.
343 *
344 * @return Value of the column or NULL if iteration is complete.
345 */
346 DEF_ACCESS (ticket_iterator_host, GET_ITERATOR_COLUMN_COUNT + 4);
347
348 /**
349 * @brief Get a column value from a ticket iterator.
350 *
351 * @param[in] iterator Iterator.
352 *
353 * @return Value of the column or NULL if iteration is complete.
354 */
355 DEF_ACCESS (ticket_iterator_location, GET_ITERATOR_COLUMN_COUNT + 5);
356
357 /**
358 * @brief Get a column value from a ticket iterator.
359 *
360 * @param[in] iterator Iterator.
361 *
362 * @return Value of the column or NULL if iteration is complete.
363 */
364 DEF_ACCESS (ticket_iterator_solution_type, GET_ITERATOR_COLUMN_COUNT + 6);
365
366 /**
367 * @brief Get the status from a ticket iterator.
368 *
369 * @param[in] iterator Iterator.
370 *
371 * @return Status of the ticket or NULL if iteration is complete.
372 */
373 DEF_ACCESS (ticket_iterator_status, GET_ITERATOR_COLUMN_COUNT + 7);
374
375 /**
376 * @brief Get column value from a ticket iterator.
377 *
378 * @param[in] iterator Iterator.
379 *
380 * @return Iterator column value or NULL if iteration is complete.
381 */
382 DEF_ACCESS (ticket_iterator_open_time, GET_ITERATOR_COLUMN_COUNT + 8);
383
384 /**
385 * @brief Get column value from a ticket iterator.
386 *
387 * @param[in] iterator Iterator.
388 *
389 * @return Iterator column value or NULL if iteration is complete.
390 */
391 DEF_ACCESS (ticket_iterator_fixed_time, GET_ITERATOR_COLUMN_COUNT + 10);
392
393 /**
394 * @brief Get column value from a ticket iterator.
395 *
396 * @param[in] iterator Iterator.
397 *
398 * @return Iterator column value or NULL if iteration is complete.
399 */
400 DEF_ACCESS (ticket_iterator_closed_time, GET_ITERATOR_COLUMN_COUNT + 12);
401
402 /**
403 * @brief Get column value from a ticket iterator.
404 *
405 * @param[in] iterator Iterator.
406 *
407 * @return Iterator column value or NULL if iteration is complete.
408 */
409 DEF_ACCESS (ticket_iterator_fix_verified_time, GET_ITERATOR_COLUMN_COUNT + 14);
410
411 /**
412 * @brief Get column value from a ticket iterator.
413 *
414 * @param[in] iterator Iterator.
415 *
416 * @return Value of the column, or -1 if iteration is complete.
417 */
418 int
ticket_iterator_orphan(iterator_t * iterator)419 ticket_iterator_orphan (iterator_t* iterator)
420 {
421 if (iterator->done) return -1;
422 return iterator_int (iterator, GET_ITERATOR_COLUMN_COUNT + 16);
423 }
424
425 /**
426 * @brief Get column value from a ticket iterator.
427 *
428 * @param[in] iterator Iterator.
429 *
430 * @return Iterator column value or NULL if iteration is complete.
431 */
432 DEF_ACCESS (ticket_iterator_open_note, GET_ITERATOR_COLUMN_COUNT + 17);
433
434 /**
435 * @brief Get column value from a ticket iterator.
436 *
437 * @param[in] iterator Iterator.
438 *
439 * @return Iterator column value or NULL if iteration is complete.
440 */
441 DEF_ACCESS (ticket_iterator_fixed_note, GET_ITERATOR_COLUMN_COUNT + 18);
442
443 /**
444 * @brief Get column value from a ticket iterator.
445 *
446 * @param[in] iterator Iterator.
447 *
448 * @return Iterator column value or NULL if iteration is complete.
449 */
450 DEF_ACCESS (ticket_iterator_closed_note, GET_ITERATOR_COLUMN_COUNT + 19);
451
452 /**
453 * @brief Get column value from a ticket iterator.
454 *
455 * @param[in] iterator Iterator.
456 *
457 * @return Iterator column value or NULL if iteration is complete.
458 */
459 DEF_ACCESS (ticket_iterator_fix_verified_report_id,
460 GET_ITERATOR_COLUMN_COUNT + 20);
461
462 /**
463 * @brief Get column value from a ticket iterator.
464 *
465 * @param[in] iterator Iterator.
466 *
467 * @return Iterator column value or NULL if iteration is complete.
468 */
469 DEF_ACCESS (ticket_iterator_nvt_oid, GET_ITERATOR_COLUMN_COUNT + 21);
470
471 /**
472 * @brief Get a column value from a ticket iterator.
473 *
474 * @param[in] iterator Iterator.
475 *
476 * @return Value of the column or NULL if iteration is complete.
477 */
478 DEF_ACCESS (ticket_iterator_user_name, GET_ITERATOR_COLUMN_COUNT + 22);
479
480 /**
481 * @brief Get a column value from a ticket iterator.
482 *
483 * @param[in] iterator Iterator.
484 *
485 * @return Value of the column or NULL if iteration is complete.
486 */
487 DEF_ACCESS (ticket_iterator_task_name, GET_ITERATOR_COLUMN_COUNT + 23);
488
489 /**
490 * @brief Initialise a ticket result iterator.
491 *
492 * Will iterate over all the results assigned to the ticket.
493 *
494 * @param[in] iterator Iterator.
495 * @param[in] ticket_id UUID of ticket.
496 * @param[in] trash Whether ticket is in trash.
497 *
498 * @return 0 success, 1 failed to find ticket, -1 error.
499 */
500 int
init_ticket_result_iterator(iterator_t * iterator,const gchar * ticket_id,int trash)501 init_ticket_result_iterator (iterator_t *iterator, const gchar *ticket_id,
502 int trash)
503 {
504 ticket_t ticket;
505
506 if (find_resource_with_permission ("ticket", ticket_id, &ticket, NULL, trash))
507 return -1;
508
509 if (ticket == 0)
510 return 1;
511
512 init_iterator (iterator,
513 "SELECT result,"
514 " ticket,"
515 " result_uuid"
516 " FROM ticket_results%s"
517 " WHERE ticket = %llu"
518 " AND report > 0"
519 " ORDER BY id;",
520 trash ? "_trash" : "",
521 ticket);
522 return 0;
523 }
524
525 /**
526 * @brief Get column value from a ticket result iterator.
527 *
528 * @param[in] iterator Iterator.
529 *
530 * @return Iterator column value or NULL if iteration is complete.
531 */
532 DEF_ACCESS (ticket_result_iterator_result_id, 2);
533
534 /**
535 * @brief Initialise a result ticket iterator.
536 *
537 * Will iterate over all the tickets that apply to the result's NVT.
538 *
539 * @param[in] iterator Iterator.
540 * @param[in] result Result.
541 *
542 * @return 0 success, -1 error.
543 */
544 int
init_result_ticket_iterator(iterator_t * iterator,result_t result)545 init_result_ticket_iterator (iterator_t *iterator, result_t result)
546 {
547 get_data_t get;
548 gchar *owned_clause, *with_clause;
549
550 if (result == 0)
551 return -1;
552
553 memset (&get, 0, sizeof (get));
554 owned_clause = acl_where_owned ("ticket", &get, 1, "any", 0, NULL, 0,
555 &with_clause);
556
557 init_iterator (iterator,
558 "%s"
559 "SELECT id, uuid"
560 " FROM tickets"
561 " WHERE id IN (SELECT ticket FROM ticket_results"
562 " WHERE result = %llu"
563 " AND result_location = %i)"
564 " AND %s"
565 " ORDER BY id;",
566 with_clause ? with_clause : "",
567 result,
568 LOCATION_TABLE,
569 owned_clause);
570
571 g_free (with_clause);
572 g_free (owned_clause);
573 return 0;
574 }
575
576 /**
577 * @brief Get column value from a ticket result iterator.
578 *
579 * @param[in] iterator Iterator.
580 *
581 * @return Iterator column value or NULL if iteration is complete.
582 */
583 DEF_ACCESS (result_ticket_iterator_ticket_id, 1);
584
585 /**
586 * @brief Return owner of ticket.
587 *
588 * @param[in] ticket Ticket.
589 *
590 * @return Owner.
591 */
592 user_t
ticket_owner(ticket_t ticket)593 ticket_owner (ticket_t ticket)
594 {
595 return sql_int64_0 ("SELECT owner FROM tickets WHERE id = %llu;",
596 ticket);
597 }
598
599 /**
600 * @brief Return user that ticket is assigned to.
601 *
602 * @param[in] ticket Ticket.
603 *
604 * @return User.
605 */
606 user_t
ticket_assigned_to(ticket_t ticket)607 ticket_assigned_to (ticket_t ticket)
608 {
609 return sql_int64_0 ("SELECT assigned_to FROM tickets WHERE id = %llu;",
610 ticket);
611 }
612
613 /**
614 * @brief Return NVT name of ticket.
615 *
616 * @param[in] ticket Ticket.
617 *
618 * @return NVT name.
619 */
620 gchar *
ticket_nvt_name(ticket_t ticket)621 ticket_nvt_name (ticket_t ticket)
622 {
623 return sql_string ("SELECT name FROM tickets WHERE id = %llu;",
624 ticket);
625 }
626
627 /**
628 * @brief Return task of ticket.
629 *
630 * @param[in] ticket Ticket.
631 *
632 * @return Task if there is one, else 0.
633 */
634 static task_t
ticket_task(ticket_t ticket)635 ticket_task (ticket_t ticket)
636 {
637 return sql_int64_0 ("SELECT task FROM tickets WHERE id = %llu;",
638 ticket);
639 }
640
641 /**
642 * @brief Return whether a ticket is in use.
643 *
644 * @param[in] ticket Ticket.
645 *
646 * @return 1 if in use, else 0.
647 */
648 int
ticket_in_use(ticket_t ticket)649 ticket_in_use (ticket_t ticket)
650 {
651 return 0;
652 }
653
654 /**
655 * @brief Return whether a trashcan ticket is in use.
656 *
657 * @param[in] ticket Ticket.
658 *
659 * @return 1 if in use, else 0.
660 */
661 int
trash_ticket_in_use(ticket_t ticket)662 trash_ticket_in_use (ticket_t ticket)
663 {
664 return 0;
665 }
666
667 /**
668 * @brief Return whether a ticket is writable.
669 *
670 * @param[in] ticket Ticket.
671 *
672 * @return 1 if writable, else 0.
673 */
674 int
ticket_writable(ticket_t ticket)675 ticket_writable (ticket_t ticket)
676 {
677 return 1;
678 }
679
680 /**
681 * @brief Return whether a trashcan ticket is writable.
682 *
683 * @param[in] ticket Ticket.
684 *
685 * @return 1 if writable, else 0.
686 */
687 int
trash_ticket_writable(ticket_t ticket)688 trash_ticket_writable (ticket_t ticket)
689 {
690 return trash_ticket_in_use (ticket) == 0;
691 }
692
693 /**
694 * @brief Delete a ticket.
695 *
696 * @param[in] ticket_id UUID of ticket.
697 * @param[in] ultimate Whether to remove entirely, or to trashcan.
698 *
699 * @return 0 success, 1 fail because ticket is in use, 2 failed to find ticket,
700 * 99 permission denied, -1 error.
701 */
702 int
delete_ticket(const char * ticket_id,int ultimate)703 delete_ticket (const char *ticket_id, int ultimate)
704 {
705 ticket_t ticket = 0;
706
707 sql_begin_immediate ();
708
709 if (acl_user_may ("delete_ticket") == 0)
710 {
711 sql_rollback ();
712 return 99;
713 }
714
715 /* Search in the regular table. */
716
717 if (find_resource_with_permission ("ticket", ticket_id, &ticket,
718 "delete_ticket", 0))
719 {
720 sql_rollback ();
721 return -1;
722 }
723
724 if (ticket == 0)
725 {
726 /* No such ticket, check the trashcan. */
727
728 if (find_trash ("ticket", ticket_id, &ticket))
729 {
730 sql_rollback ();
731 return -1;
732 }
733 if (ticket == 0)
734 {
735 sql_rollback ();
736 return 2;
737 }
738 if (ultimate == 0)
739 {
740 /* It's already in the trashcan. */
741 sql_commit ();
742 return 0;
743 }
744
745 sql ("DELETE FROM permissions"
746 " WHERE resource_type = 'ticket'"
747 " AND resource_location = %i"
748 " AND resource = %llu;",
749 LOCATION_TRASH,
750 ticket);
751
752 sql ("DELETE FROM permissions"
753 " WHERE resource_type = 'task'"
754 " AND comment = 'Automatically created for ticket'"
755 " AND resource = (SELECT task FROM tickets_trash"
756 " WHERE id = %llu);",
757 ticket);
758
759 tags_remove_resource ("ticket", ticket, LOCATION_TRASH);
760
761 sql ("DELETE FROM ticket_results_trash WHERE ticket = %llu;", ticket);
762 sql ("DELETE FROM tickets_trash WHERE id = %llu;", ticket);
763
764 sql_commit ();
765 return 0;
766 }
767
768 /* Ticket was found in regular table. */
769
770 if (ultimate == 0)
771 {
772 ticket_t trash_ticket;
773
774 /* Move to trash. */
775
776 sql ("INSERT INTO tickets_trash"
777 " (uuid, owner, name, comment, nvt, task, report, severity, host,"
778 " location, solution_type, assigned_to, status, open_time,"
779 " open_note, fixed_time, fixed_note, fix_verified_time,"
780 " fix_verified_report, closed_time, closed_note, creation_time,"
781 " modification_time)"
782 " SELECT uuid, owner, name, comment, nvt, task, report, severity,"
783 " host, location, solution_type, assigned_to, status,"
784 " open_time, open_note, fixed_time, fixed_note,"
785 " fix_verified_time, fix_verified_report, closed_time,"
786 " closed_note, creation_time, modification_time"
787 " FROM tickets WHERE id = %llu;",
788 ticket);
789
790 trash_ticket = sql_last_insert_id ();
791
792 sql ("INSERT INTO ticket_results_trash"
793 " (ticket, result, result_location, result_uuid, report)"
794 " SELECT %llu, result, result_location, result_uuid, report"
795 " FROM ticket_results"
796 " WHERE ticket = %llu;",
797 trash_ticket,
798 ticket);
799
800 permissions_set_locations ("ticket", ticket, trash_ticket,
801 LOCATION_TRASH);
802 tags_set_locations ("ticket", ticket, trash_ticket,
803 LOCATION_TRASH);
804 }
805 else
806 {
807 /* Delete entirely. */
808
809 sql ("DELETE FROM permissions"
810 " WHERE resource_type = 'ticket'"
811 " AND resource_location = %i"
812 " AND resource = %llu;",
813 LOCATION_TABLE,
814 ticket);
815
816 sql ("DELETE FROM permissions"
817 " WHERE resource_type = 'task'"
818 " AND comment = 'Automatically created for ticket'"
819 " AND resource = (SELECT task FROM tickets"
820 " WHERE id = %llu);",
821 ticket);
822
823 tags_remove_resource ("ticket", ticket, LOCATION_TABLE);
824 }
825
826 sql ("DELETE FROM ticket_results WHERE ticket = %llu;", ticket);
827 sql ("DELETE FROM tickets WHERE id = %llu;", ticket);
828
829 sql_commit ();
830 return 0;
831 }
832
833 /**
834 * @brief Try restore a ticket.
835 *
836 * If success, ends transaction for caller before exiting.
837 *
838 * @param[in] ticket_id UUID of resource.
839 *
840 * @return 0 success, 1 fail because ticket is in use, 2 failed to find ticket,
841 * -1 error.
842 */
843 int
restore_ticket(const char * ticket_id)844 restore_ticket (const char *ticket_id)
845 {
846 ticket_t trash_ticket, ticket;
847
848 if (find_trash ("ticket", ticket_id, &trash_ticket))
849 {
850 sql_rollback ();
851 return -1;
852 }
853
854 if (trash_ticket == 0)
855 return 2;
856
857 /* Move the ticket back to the regular table. */
858
859 sql ("INSERT INTO tickets"
860 " (uuid, owner, name, comment, nvt, task, report, severity, host,"
861 " location, solution_type, assigned_to, status, open_time,"
862 " open_note, fixed_time, fixed_note, fix_verified_time,"
863 " fix_verified_report, closed_time, closed_note, creation_time,"
864 " modification_time)"
865 " SELECT uuid, owner, name, comment, nvt, task, report, severity,"
866 " host, location, solution_type, assigned_to, status,"
867 " open_time, open_note, fixed_time, fixed_note,"
868 " fix_verified_time, fix_verified_report, closed_time,"
869 " closed_note, creation_time, modification_time"
870 " FROM tickets_trash WHERE id = %llu;",
871 trash_ticket);
872
873 ticket = sql_last_insert_id ();
874
875 sql ("INSERT INTO ticket_results"
876 " (ticket, result, result_location, result_uuid, report)"
877 " SELECT %llu, result, result_location, result_uuid, report"
878 " FROM ticket_results_trash"
879 " WHERE ticket = %llu;",
880 ticket,
881 trash_ticket);
882
883 /* Adjust references to the ticket. */
884
885 permissions_set_locations ("ticket", trash_ticket, ticket, LOCATION_TABLE);
886 tags_set_locations ("ticket", trash_ticket, ticket, LOCATION_TABLE);
887
888 /* Clear out the trashcan ticket. */
889
890 sql ("DELETE FROM ticket_results_trash WHERE ticket = %llu;", trash_ticket);
891 sql ("DELETE FROM tickets_trash WHERE id = %llu;", trash_ticket);
892
893 sql_commit ();
894 return 0;
895 }
896
897 /**
898 * @brief Create a ticket.
899 *
900 * @param[in] comment Comment on ticket.
901 * @param[in] result_id Result that the ticket is on.
902 * @param[in] user_id User the ticket is assigned to.
903 * @param[in] open_note Note on open status.
904 * @param[out] ticket Created ticket.
905 *
906 * @return 0 success, 1 failed to find user, 2 failed to find result,
907 * 99 permission to create ticket denied, 98 permission to
908 * create permission denied, -1 error.
909 */
910 int
create_ticket(const char * comment,const char * result_id,const char * user_id,const char * open_note,ticket_t * ticket)911 create_ticket (const char *comment, const char *result_id,
912 const char *user_id, const char *open_note,
913 ticket_t *ticket)
914 {
915 ticket_t new_ticket;
916 permission_t permission;
917 user_t user;
918 iterator_t results;
919 get_data_t get;
920 gchar *quoted_name, *quoted_comment, *quoted_oid, *quoted_host;
921 gchar *quoted_location, *quoted_solution, *quoted_uuid, *quoted_open_note;
922 char *new_ticket_id, *task_id;
923 task_t task;
924 int ret;
925
926 assert (current_credentials.uuid);
927 assert (result_id);
928 assert (user_id);
929
930 sql_begin_immediate ();
931
932 if (acl_user_may ("create_ticket") == 0)
933 {
934 sql_rollback ();
935 return 99;
936 }
937
938 /* Ensure the current user can access the assigned user and the result. */
939
940 if (find_resource_with_permission ("user", user_id, &user, NULL, 0))
941 {
942 sql_rollback ();
943 return -1;
944 }
945
946 if (user == 0)
947 {
948 sql_rollback ();
949 return 1;
950 }
951
952 memset (&get, 0, sizeof (get));
953 get.id = g_strdup (result_id);
954 switch (init_result_get_iterator (&results, &get, 0, NULL, NULL))
955 {
956 case 0:
957 break;
958 case 1:
959 g_free (get.id);
960 sql_rollback ();
961 return 2;
962 default:
963 g_free (get.id);
964 sql_rollback ();
965 return -1;
966 }
967 g_free (get.id);
968
969 if (next (&results) == 0)
970 {
971 sql_rollback ();
972 return -1;
973 }
974
975 /* Create the ticket. */
976
977 if (comment)
978 quoted_comment = sql_quote (comment);
979 else
980 quoted_comment = sql_quote ("");
981
982 quoted_name = sql_quote (result_iterator_nvt_name (&results) ?: "");
983 quoted_oid = sql_quote (result_iterator_nvt_oid (&results) ?: "");
984 quoted_host = sql_quote (result_iterator_host (&results) ?: "");
985 quoted_location = sql_quote (result_iterator_port (&results) ?: "");
986 quoted_solution = sql_quote (result_iterator_solution_type (&results) ?: "");
987
988 quoted_open_note = sql_quote (open_note ? open_note : "");
989
990 task = result_iterator_task (&results);
991
992 sql ("INSERT INTO tickets"
993 " (uuid, name, owner, comment, nvt, task, report, severity, host,"
994 " location, solution_type, assigned_to, status, open_time,"
995 " open_note, creation_time, modification_time)"
996 " VALUES"
997 " (make_uuid (), '%s',"
998 " (SELECT id FROM users WHERE users.uuid = '%s'),"
999 " '%s', '%s', %llu, %llu, %0.1f, '%s', '%s', '%s',"
1000 " %llu, %i, m_now (), '%s', m_now (), m_now ());",
1001 quoted_name,
1002 current_credentials.uuid,
1003 quoted_comment,
1004 quoted_oid,
1005 task,
1006 result_iterator_report (&results),
1007 result_iterator_severity_double (&results),
1008 quoted_host,
1009 quoted_location,
1010 quoted_solution,
1011 user,
1012 TICKET_STATUS_OPEN,
1013 quoted_open_note);
1014
1015 g_free (quoted_open_note);
1016 g_free (quoted_location);
1017 g_free (quoted_host);
1018 g_free (quoted_oid);
1019 g_free (quoted_comment);
1020 g_free (quoted_name);
1021
1022 new_ticket = sql_last_insert_id ();
1023 if (ticket)
1024 *ticket = new_ticket;
1025
1026 /* Link the ticket to the result. */
1027
1028 quoted_uuid = sql_quote (get_iterator_uuid (&results));
1029
1030 sql ("INSERT INTO ticket_results"
1031 " (ticket, result, result_location, result_uuid, report)"
1032 " VALUES (%llu, %llu, %i, '%s', %llu)",
1033 new_ticket,
1034 result_iterator_result (&results),
1035 LOCATION_TABLE,
1036 quoted_uuid,
1037 result_iterator_report (&results));
1038
1039 g_free (quoted_uuid);
1040 cleanup_iterator (&results);
1041
1042 new_ticket_id = ticket_uuid (new_ticket);
1043
1044 /* Give assigned user permission to access ticket and ticket's task. */
1045
1046 if ((ret = create_permission_internal (1,
1047 "modify_ticket",
1048 "Automatically created for ticket",
1049 NULL,
1050 new_ticket_id,
1051 "user",
1052 user_id,
1053 &permission)))
1054 {
1055 sql_rollback ();
1056 return (ret == 99 ? 98 : -1);
1057 }
1058
1059 task_uuid (task, &task_id);
1060 if ((ret = create_permission_internal (1,
1061 "get_tasks",
1062 "Automatically created for ticket",
1063 NULL,
1064 task_id,
1065 "user",
1066 user_id,
1067 &permission)))
1068 {
1069 free (task_id);
1070 sql_rollback ();
1071 return (ret == 99 ? 98 : -1);
1072 }
1073 free (task_id);
1074
1075 event (EVENT_TICKET_RECEIVED, NULL, new_ticket, 0);
1076
1077 /* Cleanup. */
1078
1079 free (new_ticket_id);
1080
1081 sql_commit ();
1082
1083 return 0;
1084 }
1085
1086 /**
1087 * @brief Create a ticket from an existing ticket.
1088 *
1089 * @param[in] comment Comment on new ticket. NULL to copy from existing.
1090 * @param[in] ticket_id UUID of existing ticket.
1091 * @param[out] new_ticket New ticket.
1092 *
1093 * @return 0 success, 1 ticket exists already, 2 failed to find existing
1094 * ticket, 99 permission denied, -1 error.
1095 */
1096 int
copy_ticket(const char * comment,const char * ticket_id,ticket_t * new_ticket)1097 copy_ticket (const char *comment, const char *ticket_id, ticket_t *new_ticket)
1098 {
1099 int ret;
1100 ticket_t old_ticket;
1101
1102 assert (new_ticket);
1103
1104 ret = copy_resource ("ticket", NULL, comment, ticket_id,
1105 "nvt, task, report, severity, host, location,"
1106 " solution_type, assigned_to, status, open_time,"
1107 " open_note, fixed_time, fixed_note,"
1108 " fix_verified_time, fix_verified_report, closed_time,"
1109 " closed_note",
1110 0, new_ticket, &old_ticket);
1111 if (ret)
1112 return ret;
1113
1114 sql ("INSERT INTO ticket_results"
1115 " (ticket, result, result_location, result_uuid, report)"
1116 " SELECT %llu, result, result_location, result_uuid, report"
1117 " FROM ticket_results"
1118 " WHERE ticket = %llu",
1119 *new_ticket,
1120 old_ticket);
1121
1122 return 0;
1123 }
1124
1125 /**
1126 * @brief Return the UUID of a ticket.
1127 *
1128 * @param[in] ticket Ticket.
1129 *
1130 * @return Newly allocated UUID if available, else NULL.
1131 */
1132 char*
ticket_uuid(ticket_t ticket)1133 ticket_uuid (ticket_t ticket)
1134 {
1135 return sql_string ("SELECT uuid FROM tickets WHERE id = %llu;",
1136 ticket);
1137 }
1138
1139 /**
1140 * @brief Set a note on a ticket.
1141 *
1142 * @param[in] ticket Ticket.
1143 * @param[in] name Name of note column.
1144 * @param[in] note Note text.
1145 *
1146 * @return 1 if ticket modified, else 0.
1147 */
1148 static int
set_note(ticket_t ticket,const gchar * name,const gchar * note)1149 set_note (ticket_t ticket, const gchar *name, const gchar *note)
1150 {
1151 if (note)
1152 {
1153 gchar *quoted_note;
1154
1155 quoted_note = sql_quote (note);
1156 sql ("UPDATE tickets SET %s = '%s'"
1157 " WHERE id = %llu;",
1158 name,
1159 quoted_note,
1160 ticket);
1161 g_free (quoted_note);
1162
1163 return 1;
1164 }
1165
1166 return 0;
1167 }
1168
1169 /**
1170 * @brief Modify a ticket.
1171 *
1172 * @param[in] ticket_id UUID of ticket.
1173 * @param[in] comment Comment on ticket.
1174 * @param[in] status_name Status of ticket.
1175 * @param[in] open_note Note if status is 'Open'.
1176 * @param[in] fixed_note Note if status is 'Fixed'.
1177 * @param[in] closed_note Note if status is 'Closed'.
1178 * @param[in] user_id UUID of user that ticket is assigned to.
1179 *
1180 * @return 0 success, 1 ticket exists already, 2 failed to find ticket,
1181 * 3 failed to find user, 4 error in status,
1182 * 5 Fixed status requires a fixed_note,
1183 * 6 Closed status requires a closed_note,
1184 * 7 Open status requires an open_note,
1185 * 99 permission denied, -1 error.
1186 */
1187 int
modify_ticket(const gchar * ticket_id,const gchar * comment,const gchar * status_name,const gchar * open_note,const gchar * fixed_note,const gchar * closed_note,const gchar * user_id)1188 modify_ticket (const gchar *ticket_id, const gchar *comment,
1189 const gchar *status_name, const gchar *open_note,
1190 const gchar *fixed_note, const gchar *closed_note,
1191 const gchar *user_id)
1192 {
1193 ticket_t ticket;
1194 user_t assigned_to;
1195 int updated;
1196
1197 assert (ticket_id);
1198 assert (current_credentials.uuid);
1199
1200 sql_begin_immediate ();
1201
1202 updated = 0;
1203
1204 /* Check permissions and get a handle on the ticket. */
1205
1206 if (acl_user_may ("modify_ticket") == 0)
1207 {
1208 sql_rollback ();
1209 return 99;
1210 }
1211
1212 ticket = 0;
1213 if (find_resource_with_permission ("ticket", ticket_id, &ticket,
1214 "modify_ticket", 0))
1215 {
1216 sql_rollback ();
1217 return -1;
1218 }
1219
1220 if (ticket == 0)
1221 {
1222 sql_rollback ();
1223 return 2;
1224 }
1225
1226 /* Update comment if requested. */
1227
1228 if (comment)
1229 {
1230 gchar *quoted_comment;
1231
1232 quoted_comment = sql_quote (comment);
1233 sql ("UPDATE tickets SET"
1234 " comment = '%s',"
1235 " modification_time = m_now ()"
1236 " WHERE id = %llu;",
1237 quoted_comment,
1238 ticket);
1239 g_free (quoted_comment);
1240
1241 updated = 1;
1242 }
1243
1244 /* Update status if requested. */
1245
1246 if (status_name)
1247 {
1248 ticket_status_t status;
1249 const gchar *time_column;
1250
1251 /* Check the status. */
1252
1253 status = ticket_status_integer (status_name);
1254 switch (status)
1255 {
1256 case TICKET_STATUS_OPEN:
1257 {
1258 if ((open_note == NULL) || (strlen (open_note) == 0))
1259 {
1260 /* Open status must always be accompanied by a note. */
1261 sql_rollback ();
1262 return 7;
1263 }
1264
1265 time_column = "open_time";
1266
1267 break;
1268 }
1269 case TICKET_STATUS_FIXED:
1270 {
1271 if ((fixed_note == NULL) || (strlen (fixed_note) == 0))
1272 {
1273 /* Fixed status must always be accompanied by a note. */
1274 sql_rollback ();
1275 return 5;
1276 }
1277
1278 time_column = "fixed_time";
1279
1280 break;
1281 }
1282 case TICKET_STATUS_CLOSED:
1283 {
1284 if ((closed_note == NULL) || (strlen (closed_note) == 0))
1285 {
1286 /* Closed status must always be accompanied by a note. */
1287 sql_rollback ();
1288 return 6;
1289 }
1290
1291 time_column = "closed_time";
1292 }
1293 break;
1294 default:
1295 /* Ticket may only be manually set to Open, Fixed or Closed. */
1296 sql_rollback ();
1297 return 4;
1298 }
1299
1300 /* Update the status. */
1301
1302 sql ("UPDATE tickets SET"
1303 " status = %i,"
1304 " modification_time = m_now (),"
1305 " %s = m_now ()"
1306 " WHERE id = %llu;",
1307 status,
1308 time_column,
1309 ticket);
1310
1311 updated = 1;
1312 }
1313
1314 if (set_note (ticket, "open_note", open_note))
1315 updated = 1;
1316
1317 if (set_note (ticket, "fixed_note", fixed_note))
1318 updated = 1;
1319
1320 if (set_note (ticket, "closed_note", closed_note))
1321 updated = 1;
1322
1323 /* Get assignee for update check, before updating assignee. */
1324
1325 assigned_to = ticket_assigned_to (ticket);
1326
1327 /* Update assigned user if requested. */
1328
1329 if (user_id)
1330 {
1331 user_t user;
1332 permission_t permission;
1333
1334 if (find_resource_with_permission ("user", user_id, &user, NULL, 0))
1335 {
1336 sql_rollback ();
1337 return -1;
1338 }
1339
1340 if (user == 0)
1341 {
1342 sql_rollback ();
1343 return 3;
1344 }
1345
1346 if (assigned_to != user)
1347 {
1348 task_t task;
1349
1350 sql ("UPDATE tickets SET"
1351 " assigned_to = %llu,"
1352 " modification_time = m_now ()"
1353 " WHERE id = %llu;",
1354 user,
1355 ticket);
1356
1357 updated = 1;
1358
1359 /* Ensure that the user can access the ticket and task. */
1360
1361 if (create_permission_internal (1,
1362 "modify_ticket",
1363 "Automatically created for ticket",
1364 NULL,
1365 ticket_id,
1366 "user",
1367 user_id,
1368 &permission))
1369 {
1370 sql_rollback ();
1371 return -1;
1372 }
1373
1374 task = ticket_task (ticket);
1375 if (task && (task > 0))
1376 {
1377 char *task_id;
1378
1379 task_uuid (task, &task_id);
1380 if (create_permission_internal (1,
1381 "get_tasks",
1382 "Automatically created for"
1383 " ticket",
1384 NULL,
1385 task_id,
1386 "user",
1387 user_id,
1388 &permission))
1389 {
1390 free (task_id);
1391 sql_rollback ();
1392 return -1;
1393 }
1394 free (task_id);
1395 }
1396
1397 event (EVENT_TICKET_RECEIVED, NULL, ticket, 0);
1398 }
1399 }
1400
1401 if (updated)
1402 {
1403 /* An Assigned Ticket Changed event is not generated if the ticket
1404 * assignee modifies the ticket. */
1405 if (sql_int ("SELECT id != %llu FROM users WHERE uuid = '%s';",
1406 assigned_to,
1407 current_credentials.uuid))
1408 event (EVENT_ASSIGNED_TICKET_CHANGED, NULL, ticket, 0);
1409
1410 /* An Owned Ticket Changed event is not generated if the ticket owner
1411 * modifies the ticket. */
1412 if (sql_int ("SELECT owner != (SELECT id FROM users WHERE uuid = '%s')"
1413 " FROM tickets"
1414 " WHERE id = %llu;",
1415 current_credentials.uuid,
1416 ticket))
1417 event (EVENT_OWNED_TICKET_CHANGED, NULL, ticket, 0);
1418 }
1419
1420 sql_commit ();
1421
1422 return 0;
1423 }
1424
1425 /**
1426 * @brief Empty ticket trashcans.
1427 */
1428 void
empty_trashcan_tickets()1429 empty_trashcan_tickets ()
1430 {
1431 sql ("DELETE FROM permissions"
1432 " WHERE resource_type = 'ticket'"
1433 " AND resource_location = %i"
1434 " AND resource IN (SELECT id FROM tickets_trash"
1435 " WHERE owner = (SELECT id FROM users"
1436 " WHERE uuid = '%s'));",
1437 LOCATION_TRASH,
1438 current_credentials.uuid);
1439
1440 sql ("DELETE FROM permissions"
1441 " WHERE resource_type = 'task'"
1442 " AND comment = 'Automatically created for ticket'"
1443 " AND resource IN (SELECT task FROM tickets_trash"
1444 " WHERE owner = (SELECT id FROM users"
1445 " WHERE uuid = '%s'));",
1446 current_credentials.uuid);
1447
1448 sql ("DELETE FROM ticket_results_trash"
1449 " WHERE ticket in (SELECT id FROM tickets_trash"
1450 " WHERE owner = (SELECT id FROM users"
1451 " WHERE uuid = '%s'));",
1452 current_credentials.uuid);
1453 sql ("DELETE FROM tickets_trash"
1454 " WHERE owner = (SELECT id FROM users WHERE uuid = '%s');",
1455 current_credentials.uuid);
1456 }
1457
1458 /**
1459 * @brief Check if tickets have been resolved.
1460 *
1461 * @param[in] task Task.
1462 */
1463 void
check_tickets(task_t task)1464 check_tickets (task_t task)
1465 {
1466 report_t report;
1467 iterator_t tickets;
1468
1469 if (task_last_report (task, &report))
1470 {
1471 g_warning ("%s: failed to get last report of task %llu,"
1472 " skipping ticket check",
1473 __func__,
1474 task);
1475 return;
1476 }
1477
1478 init_iterator (&tickets,
1479 "SELECT id FROM tickets"
1480 " WHERE task = %llu"
1481 " AND (status = %i"
1482 " OR status = %i)"
1483 /* Only if the same host was scanned. */
1484 " AND EXISTS (SELECT * FROM report_hosts"
1485 " WHERE report = %llu"
1486 " AND report_hosts.host = tickets.host)"
1487 /* Only if the problem result is gone. */
1488 " AND NOT EXISTS (SELECT * FROM results"
1489 " WHERE report = %llu"
1490 " AND nvt = (SELECT nvt FROM results"
1491 " WHERE id = (SELECT result"
1492 " FROM ticket_results"
1493 " WHERE ticket = tickets.id"
1494 " AND result_location = %i"
1495 " LIMIT 1)))"
1496 /* Only if there were no login failures on the host. */
1497 " AND NOT EXISTS (SELECT * FROM report_host_details"
1498 " WHERE report_host = (SELECT id"
1499 " FROM report_hosts"
1500 " WHERE report = %llu"
1501 " AND report_hosts.host"
1502 " = tickets.host"
1503 " LIMIT 1)"
1504 " AND (name = 'Auth-SSH-Failure'"
1505 " OR name = 'Auth-SMB-Failure'"
1506 " OR name = 'Auth-SNMP-Failure'"
1507 " OR name = 'Auth-ESXi-Failure'));",
1508 task,
1509 TICKET_STATUS_OPEN,
1510 TICKET_STATUS_FIXED,
1511 report,
1512 report,
1513 LOCATION_TABLE,
1514 report);
1515 while (next (&tickets))
1516 {
1517 ticket_t ticket;
1518
1519 ticket = iterator_int64 (&tickets, 0);
1520
1521 sql ("UPDATE tickets"
1522 " SET status = %i,"
1523 " fix_verified_time = m_now (),"
1524 " fix_verified_report = %llu"
1525 " WHERE id = %llu;",
1526 TICKET_STATUS_FIX_VERIFIED,
1527 report,
1528 ticket);
1529
1530 event (EVENT_OWNED_TICKET_CHANGED, NULL, ticket, 0);
1531 event (EVENT_ASSIGNED_TICKET_CHANGED, NULL, ticket, 0);
1532 }
1533 cleanup_iterator (&tickets);
1534 }
1535
1536 /**
1537 * @brief Delete all tickets owner by a user.
1538 *
1539 * Also delete trash tickets and assign any tickets that were assigned to
1540 * the user back to the owner.
1541 *
1542 * @param[in] user The user.
1543 */
1544 void
delete_tickets_user(user_t user)1545 delete_tickets_user (user_t user)
1546 {
1547 /* Regular tickets. */
1548
1549 sql ("DELETE FROM ticket_results"
1550 " WHERE ticket IN (SELECT id FROM tickets WHERE owner = %llu);",
1551 user);
1552 sql ("DELETE FROM tickets WHERE owner = %llu;", user);
1553
1554 sql ("UPDATE tickets SET assigned_to = owner WHERE assigned_to = %llu;",
1555 user);
1556
1557 /* Trash tickets. */
1558
1559 sql ("DELETE FROM ticket_results_trash"
1560 " WHERE ticket IN (SELECT id FROM tickets_trash WHERE owner = %llu);",
1561 user);
1562 sql ("DELETE FROM tickets_trash WHERE owner = %llu;", user);
1563
1564 sql ("UPDATE tickets_trash SET assigned_to = owner WHERE assigned_to = %llu;",
1565 user);
1566 }
1567
1568 /**
1569 * @brief Change ownership of tickets, for user deletion.
1570 *
1571 * Also assign tickets that are assigned to the user to the inheritor.
1572 *
1573 * @param[in] user Current owner.
1574 * @param[in] inheritor New owner.
1575 */
1576 void
inherit_tickets(user_t user,user_t inheritor)1577 inherit_tickets (user_t user, user_t inheritor)
1578 {
1579 /* Regular tickets. */
1580
1581 sql ("UPDATE tickets SET owner = %llu WHERE owner = %llu;",
1582 inheritor, user);
1583 sql ("UPDATE tickets SET assigned_to = %llu WHERE assigned_to = %llu;",
1584 inheritor, user);
1585
1586 /* Trash tickets. */
1587
1588 sql ("UPDATE tickets_trash SET owner = %llu WHERE owner = %llu;",
1589 inheritor, user);
1590 sql ("UPDATE tickets_trash SET assigned_to = %llu WHERE assigned_to = %llu;",
1591 inheritor, user);
1592 }
1593
1594 /**
1595 * @brief Remove a task from all tickets.
1596 *
1597 * @param[in] task Task.
1598 */
1599 void
tickets_remove_task(task_t task)1600 tickets_remove_task (task_t task)
1601 {
1602 sql ("UPDATE tickets SET task = -1 WHERE task = %llu;", task);
1603 sql ("UPDATE tickets_trash SET task = -1 WHERE task = %llu;", task);
1604 }
1605
1606 /**
1607 * @brief Remove a report from all tickets.
1608 *
1609 * @param[in] report Report.
1610 */
1611 void
tickets_remove_report(report_t report)1612 tickets_remove_report (report_t report)
1613 {
1614 sql ("UPDATE tickets SET report = -1 WHERE report = %llu;", report);
1615 sql ("UPDATE tickets_trash SET report = -1 WHERE report = %llu;", report);
1616
1617 sql ("UPDATE ticket_results"
1618 " SET report = -1, result = -1"
1619 " WHERE report = %llu;",
1620 report);
1621 sql ("UPDATE ticket_results_trash"
1622 " SET report = -1, result = -1"
1623 " WHERE report = %llu;",
1624 report);
1625 }
1626
1627 /**
1628 * @brief Remove all of a user's tasks from all tickets.
1629 *
1630 * @param[in] user User.
1631 */
1632 void
tickets_remove_tasks_user(user_t user)1633 tickets_remove_tasks_user (user_t user)
1634 {
1635 sql ("UPDATE tickets SET task = -1"
1636 " WHERE task IN (SELECT id FROM tasks WHERE owner = %llu);",
1637 user);
1638 sql ("UPDATE tickets_trash SET task = -1"
1639 " WHERE task IN (SELECT id FROM tasks WHERE owner = %llu);",
1640 user);
1641 }
1642
1643 /**
1644 * @brief Adjust tickets for task being moved to trash.
1645 *
1646 * This must be called while the old and new results still exist.
1647 *
1648 * @param[in] task Task.
1649 */
1650 void
tickets_trash_task(task_t task)1651 tickets_trash_task (task_t task)
1652 {
1653 sql ("UPDATE ticket_results"
1654 " SET result_location = %i,"
1655 " result = (SELECT id FROM results_trash"
1656 " WHERE task = %llu"
1657 " AND uuid = ticket_results.result_uuid)"
1658 " WHERE result IN (SELECT id FROM results WHERE task = %llu);",
1659 LOCATION_TRASH,
1660 task,
1661 task);
1662 sql ("UPDATE ticket_results_trash"
1663 " SET result_location = %i,"
1664 " result = (SELECT id FROM results_trash"
1665 " WHERE task = %llu"
1666 " AND uuid = ticket_results_trash.result_uuid)"
1667 " WHERE result IN (SELECT id FROM results WHERE task = %llu);",
1668 LOCATION_TRASH,
1669 task,
1670 task);
1671 }
1672
1673 /**
1674 * @brief Adjust tickets for task being restored.
1675 *
1676 * This must be called while the old and new results still exist.
1677 *
1678 * @param[in] task Task.
1679 */
1680 void
tickets_restore_task(task_t task)1681 tickets_restore_task (task_t task)
1682 {
1683 sql ("UPDATE ticket_results"
1684 " SET result_location = %i,"
1685 " result = (SELECT id FROM results"
1686 " WHERE task = %llu"
1687 " AND uuid = ticket_results.result_uuid)"
1688 " WHERE result IN (SELECT id FROM results_trash WHERE task = %llu);",
1689 LOCATION_TABLE,
1690 task,
1691 task);
1692 sql ("UPDATE ticket_results_trash"
1693 " SET result_location = %i,"
1694 " result = (SELECT id FROM results"
1695 " WHERE task = %llu"
1696 " AND uuid = ticket_results_trash.result_uuid)"
1697 " WHERE result IN (SELECT id FROM results_trash WHERE task = %llu);",
1698 LOCATION_TABLE,
1699 task,
1700 task);
1701 }
1702