1

I have this table getting populated with top10 destination ports every ten minutes along with a "total" entry that records the total number of bytes and packets transmitted for each port for the time range.

Following is the sample table:

SELECT * FROM topdestport WHERE timestamp > 1569026690;  

  id   | timestamp  |    agentid     | destport | totalbytes | totalpkts
-------+------------+----------------+----------+------------+-----------
 17783 | 1569026693 | 192.168.5.38   | 1194     |   79115496 |    180719
 17790 | 1569026693 | 192.168.5.38   | 161      |    6138371 |     31449
 17785 | 1569026693 | 192.168.5.38   | 16100    |   18182357 |    111251
 17788 | 1569026693 | 192.168.5.38   | 20946    |    8235024 |     36704
 17789 | 1569026693 | 192.168.5.38   | 39885    |    7253228 |     16796
 17784 | 1569026693 | 192.168.5.38   | 443      |   32862884 |    129279
 17787 | 1569026693 | 192.168.5.38   | 50914    |    8586242 |     38459
 17792 | 1569026693 | 192.168.5.38   | 514      |    3911742 |     15096
 17786 | 1569026693 | 192.168.5.38   | 8        |    9380959 |     86096
 17791 | 1569026693 | 192.168.5.38   | 9991     |    4196072 |      4432
 17793 | 1569026693 | 192.168.5.38   | total    |  411467748 |   1057104
 17797 | 1569026693 | 198.211.162.66 | 135      |    1065921 |     61472
 17800 | 1569026693 | 198.211.162.66 | 161      |     864736 |      8949
 17799 | 1569026693 | 198.211.162.66 | 28909    |     896027 |       612
 17796 | 1569026693 | 198.211.162.66 | 443      |    1829175 |      5763
 17803 | 1569026693 | 198.211.162.66 | 49154    |     540961 |      2117
 17794 | 1569026693 | 198.211.162.66 | 63028    |   29299322 |     20209
 17798 | 1569026693 | 198.211.162.66 | 65223    |     896128 |       613
 17795 | 1569026693 | 198.211.162.66 | 8        |    3871066 |     67627
 17801 | 1569026693 | 198.211.162.66 | 80       |     779706 |     13515
 17802 | 1569026693 | 198.211.162.66 | 9605     |     732976 |      7853
 17804 | 1569026693 | 198.211.162.66 | total    |   52337162 |    239945
 17805 | 1569027293 | 192.168.5.38   | 1194     |   64900551 |    132184
 17812 | 1569027293 | 192.168.5.38   | 161      |    4837816 |     33042
 17807 | 1569027293 | 192.168.5.38   | 16100    |    9894703 |     64514
 17814 | 1569027293 | 192.168.5.38   | 20946    |    3625487 |     18181
 17806 | 1569027293 | 192.168.5.38   | 443      |   22534140 |     90898
 17809 | 1569027293 | 192.168.5.38   | 50914    |    6503324 |     28394
 17813 | 1569027293 | 192.168.5.38   | 514      |    4062611 |     12100
 17808 | 1569027293 | 192.168.5.38   | 53271    |    7676109 |      8518
 17811 | 1569027293 | 192.168.5.38   | 60056    |    4866841 |     29548
 17810 | 1569027293 | 192.168.5.38   | 8        |    6373754 |     54248
 17815 | 1569027293 | 192.168.5.38   | total    |  288440521 |    737546
 17818 | 1569027293 | 198.211.162.66 | 135      |     680178 |      3413
 17817 | 1569027293 | 198.211.162.66 | 161      |     801128 |      5784
 17816 | 1569027293 | 198.211.162.66 | 443      |    1123139 |      3567
 17825 | 1569027293 | 198.211.162.66 | 445      |     178228 |       941
 17821 | 1569027293 | 198.211.162.66 | 48447    |     273634 |       520
 17820 | 1569027293 | 198.211.162.66 | 49154    |     290261 |       963
 17819 | 1569027293 | 198.211.162.66 | 49155    |     344997 |      1167
 17822 | 1569027293 | 198.211.162.66 | 53       |     222161 |      2019
 17823 | 1569027293 | 198.211.162.66 | 56713    |     190333 |         1
 17824 | 1569027293 | 198.211.162.66 | 80       |     184392 |      1961
 17826 | 1569027293 | 198.211.162.66 | total    |   10320551 |     50808
 17827 | 1569027893 | 192.168.5.38   | 1194     |   65364115 |    140788
 17834 | 1569027893 | 192.168.5.38   | 161      |    4555358 |     24267
 17829 | 1569027893 | 192.168.5.38   | 16100    |   14795520 |     91161
 17835 | 1569027893 | 192.168.5.38   | 20946    |    4520208 |     22759
 17832 | 1569027893 | 192.168.5.38   | 33319    |    7210596 |      8344
 17828 | 1569027893 | 192.168.5.38   | 443      |   23856143 |     95467
 17833 | 1569027893 | 192.168.5.38   | 50914    |    6604621 |     28643
 17830 | 1569027893 | 192.168.5.38   | 60056    |    8208623 |     31333
 17831 | 1569027893 | 192.168.5.38   | 8        |    7966467 |     63532
 17836 | 1569027893 | 192.168.5.38   | 9991     |    3950939 |      3959
 17837 | 1569027893 | 192.168.5.38   | total    |  336832333 |    840397
 17844 | 1569027893 | 198.211.162.66 | 135      |     793398 |      4144
 17841 | 1569027893 | 198.211.162.66 | 161      |     870284 |      7162
 17839 | 1569027893 | 198.211.162.66 | 3422     |    2274015 |      1573
 17842 | 1569027893 | 198.211.162.66 | 43965    |     830536 |      9596
 17840 | 1569027893 | 198.211.162.66 | 443      |    1085678 |      4203
 17847 | 1569027893 | 198.211.162.66 | 445      |     350938 |      1815
 17845 | 1569027893 | 198.211.162.66 | 4459     |     733168 |      7856
 17846 | 1569027893 | 198.211.162.66 | 49155    |     441978 |      1615
 17838 | 1569027893 | 198.211.162.66 | 8        |    7568689 |     57893
 17843 | 1569027893 | 198.211.162.66 | 80       |     821480 |      3024
 17848 | 1569027893 | 198.211.162.66 | total    |   25117950 |    139873

I would like to create a view that I can use to gather the following data just by pushing a time range:

timestamp
agentid
destport
totalbytes
totalpkts
totalbytes(%) >> calculated based on total bytes
totalpkts(%) >> calculated based on total packets

Is this possible to achieve with views

  • 1
    Why can't you do this with a SELECT? – Vérace Sep 21 at 2:26
  • percentage of total, or percentage of top ten? – Jasen Sep 21 at 4:09
  • percentage of total. – Arda Savran Sep 21 at 21:47
  • The question should disclose your Postgres version, table definition (CREATE TABLE statement showing data types and constraints), cardinality, what you tried (even if it's not working) and the desired result matching the sample data. – Erwin Brandstetter Sep 23 at 16:26
0
CREATE VIEW topdestportView    
SELECT
  t1.timestamp,
  t1.agentid,
  t1.totalbytes,
  t1.totalpkts,
  t1.totalbytes / (SELECT sum(totalbytes) FROM topdestport t2 WHERE t2.timestamp=t1.timestamp) as perctotalbytes,
  t1.totalpkts / (SELECT sum(totalpkts) FROM topdestport t2 WHERE t2.timestamp=t1.timestamp) as perctotalpkts
FROM topdestport t1
WHERE t1.timestamp > (SELECT MAX(timestamp) - 10000 FROM topdestport)
  • The value '10000' (last line) of query should be chosen to get the correct time range result from your table.

After this a SELECT * FROM topdestportView should return the results of this query.

  • Thank you Luuk. Can I change the last WHERE clause to be WHERE t1.timestamp BETWEEN XXX AND YYYY That’s how I was planning on using it on my BI tool. The expected result is to see different percentage values for different time range selections. – Arda Savran Sep 21 at 11:31
  • 1
    Of course, you can, as long as YOU are satisfied with the output! 😉 – Luuk Sep 21 at 12:34
  • database=> CREATE VIEW topdestportView AS (SELECT t1.timestamp,t1.agentid,t1.totalbytes,t1.totalpkts,t1.totalbytes/(SELECT sum(totalbytes) FROM topdestport t2 on t2.timestamp=t1.timestamp),t1.totalpkts/(SELECT sum(totalpkts) FROM topdestport t2 on t2.timestamp=t1.timestamp) FROM topdestport t1 WHERE t1.timestamp > (SELECT MAX(timestamp) - 10000 FROM topdestport)); ERROR: syntax error at or near "on" LINE 1: ...bytes/(SELECT sum(totalbytes) FROM topdestport t2 on t2.time... It is complaining about the ON statement. Is this syntax supposed to work for psql? – Arda Savran Sep 21 at 21:41
  • I did not test good enough, I should have requested a DM to create topdestport, with some data, to have an opportunity to test... I did correct two errors in my answer. – Luuk Sep 22 at 7:36
  • thanks. now it works but I was wondering if this is actually calculating the individual percentage of each entry compare to the total of all "total" labeled rows across the given time. When I add up all calculated percentages except the "total" lines in return it is more than 1000%. – Arda Savran Sep 22 at 12:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.