How to generate a query for the devices who is having public address.

[Ajinkya Kove1]

How to generate a query for the devices who is having public address.

[Peter Bodelier]

Hi @Ajinkya Kove1 

Where exactly are you stuck? 
Building the query, or creating the report for example?

[Ajinkya Kove1]

Query would also work.

[Peter Bodelier · Accepted solution]

Hi @Ajinkya Kove1 

Unfortunately you can't do this with a simple query, so you'll need to use script for this:

Create a script include: 

var getPublicIP = Class.create();
getPublicIP.prototype = {
	
    getIP: function() {
        var ips = [];
        var rr = new GlideRecord('cmdb_ci_server');
        rr.query();
        while (rr.next()) {
            if (rr.ip_address.match(/\b(?!(?:10\.|172\.(?:1[6-9]|2[0-9]|3[0-2])\.|192\.168\.))((?:(?:2(?:[0-4][0-9]|5[0-5])|[0-1]?[0-9]?[0-9])\.){3}(?:(?:2(?:[0-4][0-9]|5[0-5])|[0-1]?[0-9]?[0-9])))\b/)) {
                ips.push(rr.getUniqueValue());
            }
        }
        return ips.toString();
    },

    type: 'getPublicIP'
};

And call it in your report or filter by using:

Sys id | Is one of | javascript: new getPublicIP().getIP()