· 8 years ago · Jan 10, 2018, 06:48 PM
1DROP TABLE IF EXISTS temp_user_app_list;
2CREATE TEMPORARY TABLE temp_user_app_list AS
3 SELECT DISTINCT
4 xcap.client_id,
5 xcap.application_id AS app_id
6 FROM x_client_app_parent xcap
7 WHERE (EXISTS(SELECT cu.application_id
8 FROM compliance_user cu
9 WHERE (xcap.application_id = cu.application_id)));
10
11CREATE INDEX ix_temp_user_app_list_app
12 ON temp_user_app_list (app_id);
13CREATE INDEX ix_temp_user_app_list_client
14 ON temp_user_app_list (client_id);
15
16DROP TABLE IF EXISTS temp_user_real_apps_list;
17CREATE TABLE temp_user_real_apps_list AS
18 WITH user_real_apps_list AS (
19 WITH RECURSIVE bundle_group_children AS (
20 SELECT
21 app_1.app_id,
22 xcap.application_id AS real_app_id,
23 xcap.client_id,
24 TRUE AS is_suite,
25 FALSE AS is_group
26 FROM ((xource.application appl
27 JOIN temp_user_app_list app_1 ON (((appl.id = app_1.app_id) AND appl.is_suite)))
28 JOIN x_client_app_parent xcap
29 ON (((xcap.application_id = app_1.app_id) AND (xcap.client_id = app_1.client_id))))
30 UNION ALL
31 SELECT
32 rec_q.app_id,
33 rec_q.real_app_id,
34 rec_q.client_id,
35 rec_q.is_suite,
36 rec_q.is_group
37 FROM (WITH helper AS (
38 SELECT
39 bundle_group_children.app_id,
40 bundle_group_children.real_app_id,
41 bundle_group_children.client_id,
42 bundle_group_children.is_suite,
43 bundle_group_children.is_group
44 FROM bundle_group_children
45 )
46 SELECT
47 h.app_id,
48 xcap.application_id AS real_app_id,
49 xcap.client_id,
50 appl.is_suite,
51 appl.is_group
52 FROM ((helper h
53 JOIN x_client_app_parent xcap
54 ON (((h.real_app_id = xcap.best_bundle) AND (h.client_id = xcap.client_id) AND h.is_suite)))
55 JOIN xource.application appl ON ((appl.id = xcap.application_id)))
56 UNION ALL
57 SELECT
58 h.app_id,
59 xag.application_id AS real_app_id,
60 h.client_id,
61 appl.is_suite,
62 appl.is_group
63 FROM ((helper h
64 JOIN xource.x_application_group xag ON (((h.real_app_id = xag.group_id) AND h.is_group)))
65 JOIN xource.application appl ON ((appl.id = xag.application_id)))) rec_q
66 )
67 SELECT
68 bundle_group_children.app_id,
69 bundle_group_children.real_app_id,
70 bundle_group_children.client_id
71 FROM bundle_group_children
72 WHERE ((NOT bundle_group_children.is_group) AND (NOT bundle_group_children.is_suite))
73 UNION
74 SELECT
75 user_group_apps.app_id,
76 user_group_apps.real_app_id,
77 user_group_apps.client_id
78 FROM (WITH RECURSIVE group_applications AS (
79 SELECT
80 app_1.app_id,
81 app_1.client_id,
82 xag.application_id AS real_app_id,
83 appl2.is_suite,
84 appl2.is_group
85 FROM (((xource.application appl
86 JOIN temp_user_app_list app_1 ON (((appl.id = app_1.app_id) AND appl.is_group)))
87 JOIN xource.x_application_group xag ON ((appl.id = xag.group_id)))
88 JOIN xource.application appl2 ON ((appl2.id = xag.application_id)))
89 UNION ALL
90 SELECT
91 ga.app_id,
92 ga.client_id,
93 xag.application_id AS real_app_id,
94 appl.is_group,
95 appl.is_suite
96 FROM ((group_applications ga
97 JOIN xource.x_application_group xag ON (((ga.real_app_id = xag.group_id) AND ga.is_group)))
98 JOIN xource.application appl ON ((appl.id = xag.application_id)))
99 )
100 SELECT
101 group_applications.app_id,
102 group_applications.client_id,
103 group_applications.real_app_id
104 FROM group_applications
105 WHERE ((NOT group_applications.is_group) AND (NOT group_applications.is_suite))) user_group_apps
106 ) SELECT *
107 FROM user_real_apps_list;
108
109CREATE INDEX ix_temp_user_real_apps_list_app_id
110 ON temp_user_real_apps_list (app_id);
111CREATE INDEX ix_temp_user_real_apps_list_client_id
112 ON temp_user_real_apps_list (client_id);
113
114EXPLAIN (ANALYZE ,BUFFERS )
115WITH device_app_list AS (
116 SELECT DISTINCT
117 cd.device_id AS client_id,
118 cd.application_id AS app_id
119 FROM compliance_device cd
120), device_group_apps AS (
121 WITH RECURSIVE group_applications AS (
122 SELECT
123 app_1.app_id,
124 app_1.client_id,
125 xag.application_id AS real_app_id,
126 appl2.is_suite,
127 appl2.is_group
128 FROM (((xource.application appl
129 JOIN device_app_list app_1 ON (((appl.id = app_1.app_id) AND appl.is_group)))
130 JOIN xource.x_application_group xag ON ((appl.id = xag.group_id)))
131 JOIN xource.application appl2 ON ((appl2.id = xag.application_id)))
132 UNION ALL
133 SELECT
134 ga.app_id,
135 ga.client_id,
136 xag.application_id AS real_app_id,
137 appl.is_group,
138 appl.is_suite
139 FROM ((group_applications ga
140 JOIN xource.x_application_group xag ON (((ga.real_app_id = xag.group_id) AND ga.is_group)))
141 JOIN xource.application appl ON ((appl.id = xag.application_id)))
142 )
143 SELECT
144 group_applications.app_id,
145 group_applications.client_id AS device_id,
146 group_applications.real_app_id
147 FROM group_applications
148 WHERE ((NOT group_applications.is_group) AND (NOT group_applications.is_suite))
149), device_real_apps_list AS (
150 WITH RECURSIVE bundle_group_children AS (
151 SELECT
152 app_1.app_id,
153 xcap.application_id AS real_app_id,
154 xcap.client_id,
155 app_1.client_id AS device_id,
156 TRUE AS is_suite,
157 FALSE AS is_group
158 FROM ((xource.application appl
159 JOIN device_app_list app_1 ON (((appl.id = app_1.app_id) AND appl.is_suite)))
160 JOIN x_client_app_parent xcap ON ((xcap.application_id = app_1.app_id)))
161 UNION ALL
162 SELECT
163 rec_q.app_id,
164 rec_q.real_app_id,
165 rec_q.client_id,
166 rec_q.device_id,
167 rec_q.is_suite,
168 rec_q.is_group
169 FROM (WITH helper AS (
170 SELECT
171 bundle_group_children.app_id,
172 bundle_group_children.real_app_id,
173 bundle_group_children.client_id,
174 bundle_group_children.device_id,
175 bundle_group_children.is_suite,
176 bundle_group_children.is_group
177 FROM bundle_group_children
178 )
179 SELECT
180 h.app_id,
181 xcap.application_id AS real_app_id,
182 xcap.client_id,
183 h.device_id,
184 appl.is_suite,
185 appl.is_group
186 FROM ((helper h
187 JOIN x_client_app_parent xcap
188 ON (((h.real_app_id = xcap.best_bundle) AND (h.client_id = xcap.client_id) AND h.is_suite)))
189 JOIN xource.application appl ON ((appl.id = xcap.application_id)))
190 UNION ALL
191 SELECT
192 h.app_id,
193 xag.application_id AS real_app_id,
194 h.client_id,
195 h.device_id,
196 appl.is_suite,
197 appl.is_group
198 FROM ((helper h
199 JOIN xource.x_application_group xag ON (((h.real_app_id = xag.group_id) AND h.is_group)))
200 JOIN xource.application appl ON ((appl.id = xag.application_id)))) rec_q
201 )
202 SELECT
203 bundle_group_children.app_id,
204 bundle_group_children.real_app_id,
205 bundle_group_children.client_id,
206 bundle_group_children.device_id
207 FROM bundle_group_children
208 WHERE ((NOT bundle_group_children.is_group) AND (NOT bundle_group_children.is_suite))
209 UNION
210 SELECT
211 device_group_apps.app_id,
212 device_group_apps.real_app_id,
213 NULL :: INTEGER AS int4,
214 device_group_apps.device_id
215 FROM device_group_apps
216
217
218), total_concurrent_users AS (
219 SELECT
220 concurent_users.application_id,
221 concurent_users.organization_id,
222 max(concurent_users."greatest") AS users_concurrent_unique,
223 max(concurent_users.greatest_ts) AS users_concurrent_ts
224 FROM (SELECT
225 unique_users.application_id,
226 unique_users.date,
227 GREATEST(COUNT(
228 CASE
229 WHEN unique_users.h0
230 THEN unique_users.h0
231 ELSE NULL :: BOOLEAN
232 END), COUNT(
233 CASE
234 WHEN unique_users.h1
235 THEN unique_users.h1
236 ELSE NULL :: BOOLEAN
237 END), COUNT(
238 CASE
239 WHEN unique_users.h2
240 THEN unique_users.h2
241 ELSE NULL :: BOOLEAN
242 END), COUNT(
243 CASE
244 WHEN unique_users.h3
245 THEN unique_users.h3
246 ELSE NULL :: BOOLEAN
247 END), COUNT(
248 CASE
249 WHEN unique_users.h4
250 THEN unique_users.h4
251 ELSE NULL :: BOOLEAN
252 END), COUNT(
253 CASE
254 WHEN unique_users.h5
255 THEN unique_users.h5
256 ELSE NULL :: BOOLEAN
257 END), COUNT(
258 CASE
259 WHEN unique_users.h6
260 THEN unique_users.h6
261 ELSE NULL :: BOOLEAN
262 END), COUNT(
263 CASE
264 WHEN unique_users.h7
265 THEN unique_users.h7
266 ELSE NULL :: BOOLEAN
267 END), COUNT(
268 CASE
269 WHEN unique_users.h8
270 THEN unique_users.h8
271 ELSE NULL :: BOOLEAN
272 END), COUNT(
273 CASE
274 WHEN unique_users.h9
275 THEN unique_users.h9
276 ELSE NULL :: BOOLEAN
277 END), COUNT(
278 CASE
279 WHEN unique_users.h10
280 THEN unique_users.h10
281 ELSE NULL :: BOOLEAN
282 END), COUNT(
283 CASE
284 WHEN unique_users.h11
285 THEN unique_users.h11
286 ELSE NULL :: BOOLEAN
287 END), COUNT(
288 CASE
289 WHEN unique_users.h12
290 THEN unique_users.h12
291 ELSE NULL :: BOOLEAN
292 END), COUNT(
293 CASE
294 WHEN unique_users.h13
295 THEN unique_users.h13
296 ELSE NULL :: BOOLEAN
297 END), COUNT(
298 CASE
299 WHEN unique_users.h14
300 THEN unique_users.h14
301 ELSE NULL :: BOOLEAN
302 END), COUNT(
303 CASE
304 WHEN unique_users.h15
305 THEN unique_users.h15
306 ELSE NULL :: BOOLEAN
307 END), COUNT(
308 CASE
309 WHEN unique_users.h16
310 THEN unique_users.h16
311 ELSE NULL :: BOOLEAN
312 END), COUNT(
313 CASE
314 WHEN unique_users.h17
315 THEN unique_users.h17
316 ELSE NULL :: BOOLEAN
317 END), COUNT(
318 CASE
319 WHEN unique_users.h18
320 THEN unique_users.h18
321 ELSE NULL :: BOOLEAN
322 END), COUNT(
323 CASE
324 WHEN unique_users.h19
325 THEN unique_users.h19
326 ELSE NULL :: BOOLEAN
327 END), COUNT(
328 CASE
329 WHEN unique_users.h20
330 THEN unique_users.h20
331 ELSE NULL :: BOOLEAN
332 END), COUNT(
333 CASE
334 WHEN unique_users.h21
335 THEN unique_users.h21
336 ELSE NULL :: BOOLEAN
337 END), COUNT(
338 CASE
339 WHEN unique_users.h22
340 THEN unique_users.h22
341 ELSE NULL :: BOOLEAN
342 END), COUNT(
343 CASE
344 WHEN unique_users.h23
345 THEN unique_users.h23
346 ELSE NULL :: BOOLEAN
347 END)) AS "greatest",
348 GREATEST(COUNT(
349 CASE
350 WHEN unique_users.h0_ts
351 THEN unique_users.h0_ts
352 ELSE NULL :: BOOLEAN
353 END), COUNT(
354 CASE
355 WHEN unique_users.h1_ts
356 THEN unique_users.h1_ts
357 ELSE NULL :: BOOLEAN
358 END), COUNT(
359 CASE
360 WHEN unique_users.h2_ts
361 THEN unique_users.h2_ts
362 ELSE NULL :: BOOLEAN
363 END), COUNT(
364 CASE
365 WHEN unique_users.h3_ts
366 THEN unique_users.h3_ts
367 ELSE NULL :: BOOLEAN
368 END), COUNT(
369 CASE
370 WHEN unique_users.h4_ts
371 THEN unique_users.h4_ts
372 ELSE NULL :: BOOLEAN
373 END), COUNT(
374 CASE
375 WHEN unique_users.h5_ts
376 THEN unique_users.h5_ts
377 ELSE NULL :: BOOLEAN
378 END), COUNT(
379 CASE
380 WHEN unique_users.h6_ts
381 THEN unique_users.h6_ts
382 ELSE NULL :: BOOLEAN
383 END), COUNT(
384 CASE
385 WHEN unique_users.h7_ts
386 THEN unique_users.h7_ts
387 ELSE NULL :: BOOLEAN
388 END), COUNT(
389 CASE
390 WHEN unique_users.h8_ts
391 THEN unique_users.h8_ts
392 ELSE NULL :: BOOLEAN
393 END), COUNT(
394 CASE
395 WHEN unique_users.h9_ts
396 THEN unique_users.h9_ts
397 ELSE NULL :: BOOLEAN
398 END), COUNT(
399 CASE
400 WHEN unique_users.h10_ts
401 THEN unique_users.h10_ts
402 ELSE NULL :: BOOLEAN
403 END), COUNT(
404 CASE
405 WHEN unique_users.h11_ts
406 THEN unique_users.h11_ts
407 ELSE NULL :: BOOLEAN
408 END), COUNT(
409 CASE
410 WHEN unique_users.h12_ts
411 THEN unique_users.h12_ts
412 ELSE NULL :: BOOLEAN
413 END), COUNT(
414 CASE
415 WHEN unique_users.h13_ts
416 THEN unique_users.h13_ts
417 ELSE NULL :: BOOLEAN
418 END), COUNT(
419 CASE
420 WHEN unique_users.h14_ts
421 THEN unique_users.h14_ts
422 ELSE NULL :: BOOLEAN
423 END), COUNT(
424 CASE
425 WHEN unique_users.h15_ts
426 THEN unique_users.h15_ts
427 ELSE NULL :: BOOLEAN
428 END), COUNT(
429 CASE
430 WHEN unique_users.h16_ts
431 THEN unique_users.h16_ts
432 ELSE NULL :: BOOLEAN
433 END), COUNT(
434 CASE
435 WHEN unique_users.h17_ts
436 THEN unique_users.h17_ts
437 ELSE NULL :: BOOLEAN
438 END), COUNT(
439 CASE
440 WHEN unique_users.h18_ts
441 THEN unique_users.h18_ts
442 ELSE NULL :: BOOLEAN
443 END), COUNT(
444 CASE
445 WHEN unique_users.h19_ts
446 THEN unique_users.h19_ts
447 ELSE NULL :: BOOLEAN
448 END), COUNT(
449 CASE
450 WHEN unique_users.h20_ts
451 THEN unique_users.h20_ts
452 ELSE NULL :: BOOLEAN
453 END), COUNT(
454 CASE
455 WHEN unique_users.h21_ts
456 THEN unique_users.h21_ts
457 ELSE NULL :: BOOLEAN
458 END), COUNT(
459 CASE
460 WHEN unique_users.h22_ts
461 THEN unique_users.h22_ts
462 ELSE NULL :: BOOLEAN
463 END), COUNT(
464 CASE
465 WHEN unique_users.h23_ts
466 THEN unique_users.h23_ts
467 ELSE NULL :: BOOLEAN
468 END)) AS greatest_ts,
469 unique_users.organization_id
470 FROM (SELECT
471 sub_concurent.application_id,
472 sub_concurent.organization_id,
473 sub_concurent.date,
474 sub_concurent.user_id,
475 bool_or(sub_concurent."H0") AS h0,
476 bool_or(sub_concurent.h0_ts) AS h0_ts,
477 bool_or(sub_concurent."H1") AS h1,
478 bool_or(sub_concurent.h1_ts) AS h1_ts,
479 bool_or(sub_concurent."H2") AS h2,
480 bool_or(sub_concurent.h2_ts) AS h2_ts,
481 bool_or(sub_concurent."H3") AS h3,
482 bool_or(sub_concurent.h3_ts) AS h3_ts,
483 bool_or(sub_concurent."H4") AS h4,
484 bool_or(sub_concurent.h4_ts) AS h4_ts,
485 bool_or(sub_concurent."H5") AS h5,
486 bool_or(sub_concurent.h5_ts) AS h5_ts,
487 bool_or(sub_concurent."H6") AS h6,
488 bool_or(sub_concurent.h6_ts) AS h6_ts,
489 bool_or(sub_concurent."H7") AS h7,
490 bool_or(sub_concurent.h7_ts) AS h7_ts,
491 bool_or(sub_concurent."H8") AS h8,
492 bool_or(sub_concurent.h8_ts) AS h8_ts,
493 bool_or(sub_concurent."H9") AS h9,
494 bool_or(sub_concurent.h9_ts) AS h9_ts,
495 bool_or(sub_concurent."H10") AS h10,
496 bool_or(sub_concurent.h10_ts) AS h10_ts,
497 bool_or(sub_concurent."H11") AS h11,
498 bool_or(sub_concurent.h11_ts) AS h11_ts,
499 bool_or(sub_concurent."H12") AS h12,
500 bool_or(sub_concurent.h12_ts) AS h12_ts,
501 bool_or(sub_concurent."H13") AS h13,
502 bool_or(sub_concurent.h13_ts) AS h13_ts,
503 bool_or(sub_concurent."H14") AS h14,
504 bool_or(sub_concurent.h14_ts) AS h14_ts,
505 bool_or(sub_concurent."H15") AS h15,
506 bool_or(sub_concurent.h15_ts) AS h15_ts,
507 bool_or(sub_concurent."H16") AS h16,
508 bool_or(sub_concurent.h16_ts) AS h16_ts,
509 bool_or(sub_concurent."H17") AS h17,
510 bool_or(sub_concurent.h17_ts) AS h17_ts,
511 bool_or(sub_concurent."H18") AS h18,
512 bool_or(sub_concurent.h18_ts) AS h18_ts,
513 bool_or(sub_concurent."H19") AS h19,
514 bool_or(sub_concurent.h19_ts) AS h19_ts,
515 bool_or(sub_concurent."H20") AS h20,
516 bool_or(sub_concurent.h20_ts) AS h20_ts,
517 bool_or(sub_concurent."H21") AS h21,
518 bool_or(sub_concurent.h21_ts) AS h21_ts,
519 bool_or(sub_concurent."H22") AS h22,
520 bool_or(sub_concurent.h22_ts) AS h22_ts,
521 bool_or(sub_concurent."H23") AS h23,
522 bool_or(sub_concurent.h23_ts) AS h23_ts
523 FROM (SELECT
524 apps.app_id AS application_id,
525 US.organization_id,
526 uc_1.date,
527 uca.user_id,
528 uc_1."H0",
529 CASE
530 WHEN (cl_terminal.id IS NOT NULL)
531 THEN uc_1."H0"
532 ELSE FALSE
533 END AS h0_ts,
534 uc_1."H1",
535 CASE
536 WHEN (cl_terminal.id IS NOT NULL)
537 THEN uc_1."H1"
538 ELSE FALSE
539 END AS h1_ts,
540 uc_1."H2",
541 CASE
542 WHEN (cl_terminal.id IS NOT NULL)
543 THEN uc_1."H2"
544 ELSE FALSE
545 END AS h2_ts,
546 uc_1."H3",
547 CASE
548 WHEN (cl_terminal.id IS NOT NULL)
549 THEN uc_1."H3"
550 ELSE FALSE
551 END AS h3_ts,
552 uc_1."H4",
553 CASE
554 WHEN (cl_terminal.id IS NOT NULL)
555 THEN uc_1."H4"
556 ELSE FALSE
557 END AS h4_ts,
558 uc_1."H5",
559 CASE
560 WHEN (cl_terminal.id IS NOT NULL)
561 THEN uc_1."H5"
562 ELSE FALSE
563 END AS h5_ts,
564 uc_1."H6",
565 CASE
566 WHEN (cl_terminal.id IS NOT NULL)
567 THEN uc_1."H6"
568 ELSE FALSE
569 END AS h6_ts,
570 uc_1."H7",
571 CASE
572 WHEN (cl_terminal.id IS NOT NULL)
573 THEN uc_1."H7"
574 ELSE FALSE
575 END AS h7_ts,
576 uc_1."H8",
577 CASE
578 WHEN (cl_terminal.id IS NOT NULL)
579 THEN uc_1."H8"
580 ELSE FALSE
581 END AS h8_ts,
582 uc_1."H9",
583 CASE
584 WHEN (cl_terminal.id IS NOT NULL)
585 THEN uc_1."H9"
586 ELSE FALSE
587 END AS h9_ts,
588 uc_1."H10",
589 CASE
590 WHEN (cl_terminal.id IS NOT NULL)
591 THEN uc_1."H10"
592 ELSE FALSE
593 END AS h10_ts,
594 uc_1."H11",
595 CASE
596 WHEN (cl_terminal.id IS NOT NULL)
597 THEN uc_1."H11"
598 ELSE FALSE
599 END AS h11_ts,
600 uc_1."H12",
601 CASE
602 WHEN (cl_terminal.id IS NOT NULL)
603 THEN uc_1."H12"
604 ELSE FALSE
605 END AS h12_ts,
606 uc_1."H13",
607 CASE
608 WHEN (cl_terminal.id IS NOT NULL)
609 THEN uc_1."H13"
610 ELSE FALSE
611 END AS h13_ts,
612 uc_1."H14",
613 CASE
614 WHEN (cl_terminal.id IS NOT NULL)
615 THEN uc_1."H14"
616 ELSE FALSE
617 END AS h14_ts,
618 uc_1."H15",
619 CASE
620 WHEN (cl_terminal.id IS NOT NULL)
621 THEN uc_1."H15"
622 ELSE FALSE
623 END AS h15_ts,
624 uc_1."H16",
625 CASE
626 WHEN (cl_terminal.id IS NOT NULL)
627 THEN uc_1."H16"
628 ELSE FALSE
629 END AS h16_ts,
630 uc_1."H17",
631 CASE
632 WHEN (cl_terminal.id IS NOT NULL)
633 THEN uc_1."H17"
634 ELSE FALSE
635 END AS h17_ts,
636 uc_1."H18",
637 CASE
638 WHEN (cl_terminal.id IS NOT NULL)
639 THEN uc_1."H18"
640 ELSE FALSE
641 END AS h18_ts,
642 uc_1."H19",
643 CASE
644 WHEN (cl_terminal.id IS NOT NULL)
645 THEN uc_1."H19"
646 ELSE FALSE
647 END AS h19_ts,
648 uc_1."H20",
649 CASE
650 WHEN (cl_terminal.id IS NOT NULL)
651 THEN uc_1."H20"
652 ELSE FALSE
653 END AS h20_ts,
654 uc_1."H21",
655 CASE
656 WHEN (cl_terminal.id IS NOT NULL)
657 THEN uc_1."H21"
658 ELSE FALSE
659 END AS h21_ts,
660 uc_1."H22",
661 CASE
662 WHEN (cl_terminal.id IS NOT NULL)
663 THEN uc_1."H22"
664 ELSE FALSE
665 END AS h22_ts,
666 uc_1."H23",
667 CASE
668 WHEN (cl_terminal.id IS NOT NULL)
669 THEN uc_1."H23"
670 ELSE FALSE
671 END AS h23_ts
672 FROM (((((usage_client_app uca
673 --CROSS JOIN countdown_timestamp ct)
674 JOIN usage_concurrent uc_1 ON (((uca.usage_id = uc_1.usage_id) AND (uc_1.date > (SELECT (
675 (now()) :: DATE
676 - (
677 (settings_global.metric_days) :: DOUBLE PRECISION
678 *
679 '1 day' :: INTERVAL)) AS from_date
680 FROM
681 settings_global
682 ORDER BY
683 settings_global.id
684 LIMIT 1)))))
685 JOIN compliance_user cu ON ((uca.user_id = cu.user_id)))
686 JOIN (SELECT
687 app_1.app_id,
688 COALESCE(ral.real_app_id, app_1.app_id) AS application_id,
689 COALESCE(ral.client_id, app_1.client_id) AS client_id
690 FROM (temp_user_app_list app_1
691 LEFT JOIN temp_user_real_apps_list ral
692 ON ((app_1.app_id = ral.app_id AND app_1.client_id = ral.client_id)))) apps
693 ON (((cu.application_id = apps.app_id) AND (uca.application_id = apps.application_id) AND
694 (uca.client_id = apps.client_id))))
695 JOIN "user" US ON ((cu.user_id = US.id)))
696 LEFT JOIN client cl_terminal ON (((cl_terminal.id = uca.terminal_server_id) AND
697 cl_terminal.is_terminal_server)))) sub_concurent
698 GROUP BY sub_concurent.application_id, sub_concurent.date, sub_concurent.user_id,
699 sub_concurent.organization_id) unique_users
700 GROUP BY unique_users.application_id, unique_users.date, unique_users.organization_id) concurent_users
701 GROUP BY concurent_users.application_id, concurent_users.organization_id
702), total_concurrent_devices AS (
703 SELECT
704 concurrent_devices.application_id,
705 concurrent_devices.organization_id,
706 max(concurrent_devices.greatest_device) AS devices_concurrent_unique
707 FROM (SELECT
708 unique_client.application_id,
709 unique_client.organization_id,
710 unique_client.date,
711 GREATEST(COUNT(
712 CASE
713 WHEN unique_client.h0_device
714 THEN unique_client.h0_device
715 ELSE NULL :: BOOLEAN
716 END), COUNT(
717 CASE
718 WHEN unique_client.h1_device
719 THEN unique_client.h1_device
720 ELSE NULL :: BOOLEAN
721 END), COUNT(
722 CASE
723 WHEN unique_client.h2_device
724 THEN unique_client.h2_device
725 ELSE NULL :: BOOLEAN
726 END), COUNT(
727 CASE
728 WHEN unique_client.h3_device
729 THEN unique_client.h3_device
730 ELSE NULL :: BOOLEAN
731 END), COUNT(
732 CASE
733 WHEN unique_client.h4_device
734 THEN unique_client.h4_device
735 ELSE NULL :: BOOLEAN
736 END), COUNT(
737 CASE
738 WHEN unique_client.h5_device
739 THEN unique_client.h5_device
740 ELSE NULL :: BOOLEAN
741 END), COUNT(
742 CASE
743 WHEN unique_client.h6_device
744 THEN unique_client.h6_device
745 ELSE NULL :: BOOLEAN
746 END), COUNT(
747 CASE
748 WHEN unique_client.h7_device
749 THEN unique_client.h7_device
750 ELSE NULL :: BOOLEAN
751 END), COUNT(
752 CASE
753 WHEN unique_client.h8_device
754 THEN unique_client.h8_device
755 ELSE NULL :: BOOLEAN
756 END), COUNT(
757 CASE
758 WHEN unique_client.h9_device
759 THEN unique_client.h9_device
760 ELSE NULL :: BOOLEAN
761 END), COUNT(
762 CASE
763 WHEN unique_client.h10_device
764 THEN unique_client.h10_device
765 ELSE NULL :: BOOLEAN
766 END), COUNT(
767 CASE
768 WHEN unique_client.h11_device
769 THEN unique_client.h11_device
770 ELSE NULL :: BOOLEAN
771 END), COUNT(
772 CASE
773 WHEN unique_client.h12_device
774 THEN unique_client.h12_device
775 ELSE NULL :: BOOLEAN
776 END), COUNT(
777 CASE
778 WHEN unique_client.h13_device
779 THEN unique_client.h13_device
780 ELSE NULL :: BOOLEAN
781 END), COUNT(
782 CASE
783 WHEN unique_client.h14_device
784 THEN unique_client.h14_device
785 ELSE NULL :: BOOLEAN
786 END), COUNT(
787 CASE
788 WHEN unique_client.h15_device
789 THEN unique_client.h15_device
790 ELSE NULL :: BOOLEAN
791 END), COUNT(
792 CASE
793 WHEN unique_client.h16_device
794 THEN unique_client.h16_device
795 ELSE NULL :: BOOLEAN
796 END), COUNT(
797 CASE
798 WHEN unique_client.h17_device
799 THEN unique_client.h17_device
800 ELSE NULL :: BOOLEAN
801 END), COUNT(
802 CASE
803 WHEN unique_client.h18_device
804 THEN unique_client.h18_device
805 ELSE NULL :: BOOLEAN
806 END), COUNT(
807 CASE
808 WHEN unique_client.h19_device
809 THEN unique_client.h19_device
810 ELSE NULL :: BOOLEAN
811 END), COUNT(
812 CASE
813 WHEN unique_client.h20_device
814 THEN unique_client.h20_device
815 ELSE NULL :: BOOLEAN
816 END), COUNT(
817 CASE
818 WHEN unique_client.h21_device
819 THEN unique_client.h21_device
820 ELSE NULL :: BOOLEAN
821 END), COUNT(
822 CASE
823 WHEN unique_client.h22_device
824 THEN unique_client.h22_device
825 ELSE NULL :: BOOLEAN
826 END), COUNT(
827 CASE
828 WHEN unique_client.h23_device
829 THEN unique_client.h23_device
830 ELSE NULL :: BOOLEAN
831 END)) AS greatest_device
832 FROM (SELECT
833 sub_concurent.application_id,
834 sub_concurent.date,
835 sub_concurent.client_id,
836 sub_concurent.organization_id,
837 bool_or(
838 CASE
839 WHEN (sub_concurent.terminal_server_id IS NOT NULL)
840 THEN sub_concurent."H0"
841 ELSE FALSE
842 END) AS h0_device,
843 bool_or(
844 CASE
845 WHEN (sub_concurent.terminal_server_id IS NOT NULL)
846 THEN sub_concurent."H1"
847 ELSE FALSE
848 END) AS h1_device,
849 bool_or(
850 CASE
851 WHEN (sub_concurent.terminal_server_id IS NOT NULL)
852 THEN sub_concurent."H2"
853 ELSE FALSE
854 END) AS h2_device,
855 bool_or(
856 CASE
857 WHEN (sub_concurent.terminal_server_id IS NOT NULL)
858 THEN sub_concurent."H3"
859 ELSE FALSE
860 END) AS h3_device,
861 bool_or(
862 CASE
863 WHEN (sub_concurent.terminal_server_id IS NOT NULL)
864 THEN sub_concurent."H4"
865 ELSE FALSE
866 END) AS h4_device,
867 bool_or(
868 CASE
869 WHEN (sub_concurent.terminal_server_id IS NOT NULL)
870 THEN sub_concurent."H5"
871 ELSE FALSE
872 END) AS h5_device,
873 bool_or(
874 CASE
875 WHEN (sub_concurent.terminal_server_id IS NOT NULL)
876 THEN sub_concurent."H6"
877 ELSE FALSE
878 END) AS h6_device,
879 bool_or(
880 CASE
881 WHEN (sub_concurent.terminal_server_id IS NOT NULL)
882 THEN sub_concurent."H7"
883 ELSE FALSE
884 END) AS h7_device,
885 bool_or(
886 CASE
887 WHEN (sub_concurent.terminal_server_id IS NOT NULL)
888 THEN sub_concurent."H8"
889 ELSE FALSE
890 END) AS h8_device,
891 bool_or(
892 CASE
893 WHEN (sub_concurent.terminal_server_id IS NOT NULL)
894 THEN sub_concurent."H9"
895 ELSE FALSE
896 END) AS h9_device,
897 bool_or(
898 CASE
899 WHEN (sub_concurent.terminal_server_id IS NOT NULL)
900 THEN sub_concurent."H10"
901 ELSE FALSE
902 END) AS h10_device,
903 bool_or(
904 CASE
905 WHEN (sub_concurent.terminal_server_id IS NOT NULL)
906 THEN sub_concurent."H11"
907 ELSE FALSE
908 END) AS h11_device,
909 bool_or(
910 CASE
911 WHEN (sub_concurent.terminal_server_id IS NOT NULL)
912 THEN sub_concurent."H12"
913 ELSE FALSE
914 END) AS h12_device,
915 bool_or(
916 CASE
917 WHEN (sub_concurent.terminal_server_id IS NOT NULL)
918 THEN sub_concurent."H13"
919 ELSE FALSE
920 END) AS h13_device,
921 bool_or(
922 CASE
923 WHEN (sub_concurent.terminal_server_id IS NOT NULL)
924 THEN sub_concurent."H14"
925 ELSE FALSE
926 END) AS h14_device,
927 bool_or(
928 CASE
929 WHEN (sub_concurent.terminal_server_id IS NOT NULL)
930 THEN sub_concurent."H15"
931 ELSE FALSE
932 END) AS h15_device,
933 bool_or(
934 CASE
935 WHEN (sub_concurent.terminal_server_id IS NOT NULL)
936 THEN sub_concurent."H16"
937 ELSE FALSE
938 END) AS h16_device,
939 bool_or(
940 CASE
941 WHEN (sub_concurent.terminal_server_id IS NOT NULL)
942 THEN sub_concurent."H17"
943 ELSE FALSE
944 END) AS h17_device,
945 bool_or(
946 CASE
947 WHEN (sub_concurent.terminal_server_id IS NOT NULL)
948 THEN sub_concurent."H18"
949 ELSE FALSE
950 END) AS h18_device,
951 bool_or(
952 CASE
953 WHEN (sub_concurent.terminal_server_id IS NOT NULL)
954 THEN sub_concurent."H19"
955 ELSE FALSE
956 END) AS h19_device,
957 bool_or(
958 CASE
959 WHEN (sub_concurent.terminal_server_id IS NOT NULL)
960 THEN sub_concurent."H20"
961 ELSE FALSE
962 END) AS h20_device,
963 bool_or(
964 CASE
965 WHEN (sub_concurent.terminal_server_id IS NOT NULL)
966 THEN sub_concurent."H21"
967 ELSE FALSE
968 END) AS h21_device,
969 bool_or(
970 CASE
971 WHEN (sub_concurent.terminal_server_id IS NOT NULL)
972 THEN sub_concurent."H22"
973 ELSE FALSE
974 END) AS h22_device,
975 bool_or(
976 CASE
977 WHEN (sub_concurent.terminal_server_id IS NOT NULL)
978 THEN sub_concurent."H23"
979 ELSE FALSE
980 END) AS h23_device
981 FROM (SELECT
982 apps.app_id AS application_id,
983 uc_1.date,
984 uca.client_id,
985 uca.terminal_server_id,
986 cl.organization_id,
987 uc_1."H0",
988 uc_1."H1",
989 uc_1."H2",
990 uc_1."H3",
991 uc_1."H4",
992 uc_1."H5",
993 uc_1."H6",
994 uc_1."H7",
995 uc_1."H8",
996 uc_1."H9",
997 uc_1."H10",
998 uc_1."H11",
999 uc_1."H12",
1000 uc_1."H13",
1001 uc_1."H14",
1002 uc_1."H15",
1003 uc_1."H16",
1004 uc_1."H17",
1005 uc_1."H18",
1006 uc_1."H19",
1007 uc_1."H20",
1008 uc_1."H21",
1009 uc_1."H22",
1010 uc_1."H23"
1011 FROM (((((usage_client_app uca
1012 --CROSS JOIN countdown_timestamp ct)
1013 JOIN usage_concurrent uc_1 ON (((uca.usage_id = uc_1.usage_id) AND (uc_1.date > (SELECT (
1014 (now()) :: DATE
1015 - (
1016 (settings_global.metric_days) :: DOUBLE PRECISION
1017 *
1018 '1 day' :: INTERVAL)) AS from_date
1019 FROM
1020 settings_global
1021 ORDER BY
1022 settings_global.id
1023 LIMIT 1)))))
1024 RIGHT JOIN compliance_device cd ON ((uca.client_id = cd.device_id)))
1025 JOIN (SELECT
1026 app_1.app_id,
1027 COALESCE(ral.real_app_id, app_1.app_id) AS application_id,
1028 ral.client_id,
1029 app_1.client_id AS device_id
1030 FROM (device_app_list app_1
1031 LEFT JOIN device_real_apps_list ral
1032 ON (((app_1.app_id = ral.app_id) AND (ral.device_id = app_1.client_id))))) apps ON ((
1033 (cd.application_id = apps.app_id) AND (uca.application_id = apps.application_id) AND
1034 (uca.client_id = apps.device_id) AND (uca.terminal_server_id IS NOT NULL) AND
1035 ((apps.client_id IS NULL) OR (uca.terminal_server_id = apps.client_id)))))
1036 JOIN client cl ON ((cd.device_id = cl.id)))
1037 JOIN client cl_terminal ON (((cl_terminal.id = uca.terminal_server_id) AND
1038 cl_terminal.is_terminal_server)))) sub_concurent
1039 GROUP BY sub_concurent.application_id, sub_concurent.date, sub_concurent.client_id,
1040 sub_concurent.organization_id) unique_client
1041 GROUP BY unique_client.application_id, unique_client.date, unique_client.organization_id) concurrent_devices
1042 GROUP BY concurrent_devices.application_id, concurrent_devices.organization_id
1043)
1044 ,
1045 concurrent_values AS (
1046 SELECT
1047 COALESCE(ui.application_id, di.application_id) AS application_id,
1048 COALESCE(ui.organization_id, di.organization_id) AS organization_id,
1049 COALESCE(ui.users_concurrent_ts, 0) AS users_concurrent_ts,
1050 COALESCE(ui.users_concurrent_unique, 0) AS users_concurrent,
1051 COALESCE(di.devices_concurrent_unique, 0) AS devices_concurrent
1052 FROM total_concurrent_users ui
1053 FULL OUTER JOIN total_concurrent_devices di
1054 ON ui.application_id = di.application_id AND ui.organization_id = di.organization_id
1055 )
1056
1057SELECT *
1058FROM concurrent_values;