SOLVED: SPLIT_PART in FAZ 5.2.0 GA

[nathan_emerson]

In another post AtiT posted a query for summing bandwidth by subnets. This query does not function for me returning the following error: ERROR: function split_part(inet, unknown, integer) does not exist LINE 1: select * from (SELECT (' 192.168.' ||SPLIT_PART(ip,' .' ,3)||' .0... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. Does anyone know if the supported functions have changed in 5.2.0? Is there a way to allow the use of SPLIT_PART or is there another equivalent function now available? Cheers, Nathan

[AtiT]

Hi I do not have FAZ on 5.2 available but probably you need a function to convert the IP address to STRING according to the argument type error. Try the IP address put into the HOST() function like this: select * from (SELECT (' 192.168.' ||SPLIT_PART(HOST(ip),' .' ,3)||' .0... probably other function exist something like ip_str() ?? I saw it somewhere here in some post.

[AtiT]

I found some functions for IP to String conversion: http://www.postgresql.org/docs/9.3/static/functions-net.html

[hzhao_FTNT]

split_part is a standard string function. From FAZ5.0.7, ip datatype is changed to inet. We have function ipstr to convert ip to string, then you can other standard string functions like split_part, trim, substring ... for example: split_part(ipstr(`srcip`), ' .' , 3); Regards, hz

[nathan_emerson]

Thanks AtiT and hzhao_FTNT for your assistance, I' ve tried a few versions using ipstr but keep coming back with: SELECT (' 10.' ||SPLIT_PART(ipstr(' ip' ),' .' ,2)||SPLIT_PART(ipstr(' ip' ),' .' ,3)||' .0/24' ) AS network ERROR: invalid input syntax for type inet: " ip" LINE 1: select * from (SELECT (' 10.' ||SPLIT_PART(ipstr(' ip' ),' .' ,2)|... I' ve tried ' ip' and ' srcip' with the same result, trying variations using different quotes returns an ERROR indicating the function does not work with TEXT as one would expect. I even tried changing the function to HOST(' ip' ), the function does no exist. I will continue working on it but if you have any suggestions they would be appreciated. Cheers, Nathan

[nathan_emerson]

Thanks one again chaps, I have it sorted out now, I began from the inner select and worked outwards. I now have two working data sets that SUM the total bandwidth by class B and class C sub nets for my internal WAN range 10.0.0.0/8. Total Bandwidth by Class B subnet SELECT (' 10.' ||SPLIT_PART(ipstr(srcip),' .' ,2)||' .0.0/16' ) AS network, SUM(bandwidth) AS bw FROM( SELECT srcip, SUM(COALESCE(sentbyte, 0)+COALESCE(rcvdbyte, 0)) AS bandwidth FROM $log WHERE action IN (' accept' ,' close' ) AND srcintf=' port3' AND NULLIFNA(ipstr(srcip)) IS NOT NULL AND ipstr(srcip) LIKE ' 10.%' GROUP BY srcip HAVING SUM(COALESCE(sentbyte, 0)+COALESCE(rcvdbyte, 0))>0 ORDER BY bandwidth DESC ) t GROUP BY network ORDER BY bw DESC Total Bandwidth by Class C subnet SELECT (' 10.' ||SPLIT_PART(ipstr(srcip),' .' ,2)||' .' ||SPLIT_PART(ipstr(srcip),' .' ,3)||' .0/24' ) AS network, SUM(bandwidth) AS bw FROM( SELECT srcip, SUM(COALESCE(sentbyte, 0)+COALESCE(rcvdbyte, 0)) AS bandwidth FROM $log WHERE action IN (' accept' ,' close' ) AND srcintf=' port3' AND NULLIFNA(ipstr(srcip)) IS NOT NULL AND ipstr(srcip) LIKE ' 10.%' GROUP BY srcip HAVING SUM(COALESCE(sentbyte, 0)+COALESCE(rcvdbyte, 0))>0 ORDER BY bandwidth DESC ) t GROUP BY network ORDER BY bw DESC The primary issues seemed to be in syntax, it did not like using the AS value of ip for srcip, use no ' marks around column names in the filters and the status filter is now action. Hope this helps others. Cheers, Nathan

[hzhao_FTNT]

Hi Nathan, it is good to know your dataset is working now. What I mentioned in my last post is `srcip`, not single quotes ' srcip' . :-) Regards, hz

[nathan_emerson]

hzhao_FTNT, I tried every type of quote, tick and mark and only the non enclosed version I ended up with worked. it failed using ' srcip' " srcip" `srcip` Thanks again for your assistance.

[AtiT]

Hi, I am using everywhere `srcip` the sign ` and is working.