postgresql count

Question

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.

Pablo Santa Cruz · Accepted Answer · 2010-06-15 22:31:04Z

3

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

answered Jun 15, 2010 at 22:31

Pablo Santa Cruz

182k33 gold badges250 silver badges300 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

dars Over a year ago

Thank you for the reply. The problem I have is I need the count for every blade in the switch. For each address, and each blade, how many = 2. I believe I will need some sort of regex count(substring(ifdesc, 'Ethernet1/*)

Andrew · Accepted Answer · 2012-02-22 02:30:24Z

0

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

answered Feb 22, 2012 at 2:30

Andrew

1,1972 gold badges12 silver badges18 bronze badges

Collectives™ on Stack Overflow

postgresql count

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related