CODEWITHSUNDEEP
postgresql
dars
dars

Reputation: 21

postgresql count

Can this be done in PGSQL? I have a view which I created where hostname,ip, and datacenter are from one table, and ifdesc and if stats from another table. the view output looks like this:

hostname | ip     | datacenter | ifdesc           | ifadminstat | ifoperstat|
---------- ------------------------------------------------------------------
r1        1.1.1.1     dc       GigabitEthernet1/1      2             1
r1        1.1.1.1     dc       GigabitEthernet1/2      2             2
r1        1.1.1.1     dc       GigabitEthernet1/3      2             2
r1        1.1.1.1     dc       GigabitEthernet1/4      2             1
r1        1.1.1.1     dc       GigabitEthernet2/1      2             2
r1        1.1.1.1     dc       GigabitEthernet2/2      2             2
r2        2.2.2.2     dc       GigabitEthernet1/1      2             2
r2        2.2.2.2     dc       GigabitEthernet1/2      2             2

I need to get a count of "ifadminstat = 2" and "ifoperstat = 2" for all interfaces on each blade, for each router (for example... for r1, how many interfaces on blade 1 (GigabitEthernet1/1-48) have "ifadminstat = 2" and "ifoperstat = 2".

I am trying to do the counting in Postgresql then present the results on a website using PHP.

Upvotes: 2

Views: 729

Answers (2)

Andrew
Andrew

Reputation: 1147

You've already defined a view. Might as well make it more useful by plucking out the blades in the switch, etc.

CREATE VIEW ... AS
SELECT ... 
  substr(ifdesc, 1, strpos(ifdesc, '/') - 1) AS backplane_name,
  substr(ifdesc, strpos(ifdesc, '/') + 1) AS switch_port,
  ...

From there it becomes pretty trivial to write your query.

SELECT host, ip, backplane_name, count(1) AS active_ports
FROM table
WHERE ifadminstat = 2 AND ifoperstat = 2

Upvotes: 0

Pablo Santa Cruz
Pablo Santa Cruz

Reputation: 181280

select hostname, ip, count(1)
  from table
 where ifadminstat = 2 and ifoperstat = 2
 group by hostname, ip

Upvotes: 3

Related Questions