16.4. Database functions

This section contains the reference information for database functions. Database functions are used within the select and sort (for value functions) statements in advanced reports, and take either database fields or other database functions as arguments. They allow the values from database fields to be modified before they are added to the result of running a query.

Database functions are divided into three types: key functions, which return a database key, value functions, which return a database value, and time functions, which operate on time. Each function can be used wherever the corresponding field can be used. They can each be used in a database select, to add a column to the resulting table. Value functions only can also be used in a sort statement, to sort the resulting table on that column.

16.4.1. Labels in database functions

Each function takes an optional string argument as the first parameter. If provided, this string is called the function label, and is used within charts and tables within the report, to display the name of the function. For example, to resolve the source address within a chart, you might use resolve("Source name", sourceAddress). This specifies that when the name of the function is to be displayed, for example within the legend of a chart or the heading of a table, then the label "Source name" should be used. If this first parameter is not specified, then the function itself will be displayed (resolve(sourceAddress) in the example).

Using labels for functions is a way to make charts and tables more legible and understandable.

16.4.2. Key functions

16.4.2.1. countryCode

Synopsis

countryCode([label,] ipAddress)

Description

In addition to the optional label argument, described in Section 16.4.1, “Labels in database functions”, countryCode takes a single argument which must be an IPv4 address. The address can either be an address field (eg sourceAddress), or a literal address (eg "64.151.76.40"). It returns the ISO 3166 country code of the country in which the address is located, if known. If the country is not known, or the parameter is not an IPv4 address, then the empty string is returned.

Example

To select the sourceAddress and country code, use these fields in the select statement: sourceAddress, countryCode(sourceAddress)

16.4.2.2. countryName

Synopsis

countryName([label,] ipAddress)

Description

countryName takes a single argument in addition to the optional label, which must be an IPv4 address. The address can either be an address field (eg sourceAddress), or a literal address (eg "64.151.76.40"). It returns the name of the country in which the address is located, if known. If the country is not known, or the parameter is not an IPv4 address, then the empty string is returned.

Example

To select the country of the clientAddress, use this field in the select statement: countryCode("Country", clientAddress) This will also use the label Country in a table heading or chart legend.

16.4.2.3. hostname

Synopsis

hostname([label,] uuid) hostname([label,] macAddress)

Description

hostname takes a single argument, in addition to the optional label, which can be a UUID or a MAC address. The argument can either be a key field (eg macSource or uuid), or a literal address or UUID (eg "00248C70AB58" or "6ba7b811-9dad-11d1-80b4-00c04fd430c8"). It returns the hostname associated with the UUID or MAC address, if know through host sFlow. If the address is not a UUID or MAC address, or the hostname cannot be determined, then the empty string is returned.

Example

To select the hostname of the hosts in a host counters query, use this field in the select statement: hostname(uuid)

16.4.2.4. ifAlias

Synopsis

ifAlias([label,] interface)

Description

ifAlias takes a single argument, in addition to the optional label, which must be an interface field (note that ifAlias does not take an ifIndex parameter). It returns the ifAlias of the interface, if known. If the ifAlias is not known, or the parameter is not an interface, then the empty string is returned.

Example

To select the inputInterface and ifAlias from a switch, use these fields in the select statement: inputInterface, ifAlias(inputInterface)

16.4.2.5. ifAliasOrVMName

Synopsis

ifAliasOrVMName([label,] interface)

Description

ifAliasOrVMName takes a single argument, in addition to the optional label, which must be an interface field (note that ifAliasOrVMName does not take an ifIndex parameter). It returns the ifAlias of the interface, if known. If the ifAlias is not known, then it returns the name of the virtual machine attached to the interface. If there is no ifAlias, and no VM attached to the interface, or the parameter is not an interface, then the empty string is returned.

Example

To select the inputInterface and ifAlias or VM name from a switch, use these fields in the select statement: inputInterface, ifAliasOrVMName(inputInterface)

16.4.2.6. ifName

Synopsis

ifName([label,] interface)

Description

ifName takes a single argument, in addition to the optional label, which must be an interface field (note that ifName does not take an ifIndex parameter). It returns the ifName of the interface, if known. If the ifName is not known, or the parameter is not an interface, then the empty string is returned.

Example

To select the ifName of the outputInterface from a switch, use this field in the select statement: ifName(outputInterface)

16.4.2.7. ifOrVMName

Synopsis

ifOrVMName([label,] interface)

Description

ifOrVMName takes a single argument, in addition to the optional label, which must be an interface field (note that ifOrVMName does not take an ifIndex parameter). It returns the ifName of the interface, if known. If the ifName is not known, then it returns the name of the virtual machine attached to the interface. If there is no ifName, and no VM attached to the interface, or the parameter is not an interface, then the empty string is returned.

Example

To select the ifOrVMName of the outputInterface from a switch, use this field in the select statement: ifOrVMName(outputInterface)

16.4.2.8. locate

Synopsis

locate([label,] address)

Description

locate takes a single argument, in addition to the optional label, which must be an address. The address can either be an address field (eg sourceAddress), or a literal address (eg "64.151.76.40"). It returns the interface where the address is most likely to be located in the monitored network, if it can be determined. If the location cannot be determined, then the empty string is returned. Note that the address of an external system would most likely be located on a router interface. The location of an address can only be determined if network traffic from that address has been observed.

Example

To select the sourceAddress and location of the source address, use these fields in the select statement: sourceAddress, locate(sourceAddress)

16.4.2.9. locateSwitch

Synopsis

locateSwitch([label,] address)

Description

locateSwitch takes a single argument, in addition to the optional label, which must be an address. The address can either be an address field (eg sourceAddress), or a literal address (eg "64.151.76.40"). It works in the same way as Section 16.4.2.8, “locate”, but returns only the switch where the address is most likely to be located, rather than the complete interface. If the location cannot be determined, then the empty string is returned.

Example

To select the sourceAddress and switch where the source address is located, use these fields in the select statement: sourceAddress, locateSwitch(sourceAddress)

16.4.2.10. mac

Synopsis

mac([label,] address)

Description

mac takes a single argument, in addition to the optional label, which must be an address. The address can either be an address field (eg sourceAddress), or a literal address (eg "64.151.76.40"). It returns the most likely MAC address associated with the address if can be determined. If the address is already a MAC address, then it will be returned. If the MAC cannot be determined, then the empty string is returned. The MAC address associated with a layer 3 address can only be determined if network traffic from that address has been observed.

Example

To select the sourceAddress and MAC address associated with the source address, use these fields in the select statement: sourceAddress, mac(sourceAddress)

16.4.2.11. vendor

Synopsis

vendor([label,] macAddress)

Description

vendor takes a single argument, in addition to the optional label, which must be a MAC address. The address can either be an address field (eg sourceAddress), or a literal address (eg "00248C70AB58"). It returns the vendor associated with the address. If the address is not a MAC address or the vendor cannot be determined, then the empty string is returned.

Example

To select the source MAC address and vendor, use these fields in the select statement: macSource, vendor(macSource)

16.4.2.12. getParent

Synopsis

getParent([label,] hostname) getParent([label,] macAddress) getParent([label,] uuid)

Description

getParent takes a single argument, in addition to the optional label, which can be a hostname, a MAC address or a UUID. The argument can either be a key field (eg hostname, macSource, or uuid), or a literal hostname, address or UUID (eg "google.com", "00248C70AB58" or "6ba7b811-9dad-11d1-80b4-00c04fd430c8"). It returns the hostname, MAC address or UUID (respectively) associated with the parent of the host identified by the hostname, MAC address or UUID specified.

getParent is used to find system containment. For example, the parent of a virtual host is the physical host that is running the virtual one.

Example

To select the hostname of parent, of the host with hostname server.inmon.com in a host counters query, use this field in the select statement: getParent("server.inmon.com")

To select the UUID of the parent, of the host with UUID 6ba7b811-9dad-11d1-80b4-00c04fd430c8 in a host counters query, use this field in the select statement: getParent("6ba7b811-9dad-11d1-80b4-00c04fd430c8")

16.4.2.13. resolve

Synopsis

resolve([label,] address)

resolve([label,] port)

Description

resolve takes a single argument, in addition to the optional label, which must be an address or port field. It returns the resolved name of the address or port, if known. If the resolved name is not known, or the parameter is not an address or port, then the empty string is returned. Note that resolve only works with the highest layer port fields (eg sourcePort), not with the numeric ports (such as tcpSourcePort).

Example

To select the sourceAddress, resolved name of the source address, and the resolved name of the sourcePort, use these fields in the select statement: sourceAddress, resolve(sourceAddress), resolve(sourcePort)

16.4.2.14. subnet

Synopsis

subnet([label,] ipAddress)

Description

subnet takes a single argument, in addition to the optional label, which must be an IP address field. It returns the name of the smallest defined subnet that the address belongs to. If the address does not belong to a subnet, then the name of the external subnet is returned. If the parameter is not an IP address, then the empty string is returned. For this function to be useful, subnets must have first been defined in sFlowTrend-Pro; see Section 13.3, “Configuring subnets in sFlowTrend-Pro for more information.

Example

To select the sourceAddress and the subnet that sourceAddress belongs to, use these fields in the select statement: sourceAddress, subnet(sourceAddress)

16.4.2.15. uuid

Synopsis

uuid([label,] hostname) uuid([label,] macAddress)

Description

uuid takes a single argument, in addition to the optional label, which can be a hostname or a MAC address. The argument can either be a key field (eg macSource or hostname), or a literal address or hostname (eg "00248C70AB58" or "google.com"). It returns the UUID associated with the MAC address or hostname, if know through host sFlow. If the hostname or address is not valid, or the UUID cannot be determined, then the empty string is returned.

Example

To select the uuid of the host server.inmon.com in a host counters query, use this field in the select statement: uuid("server.inmon.com")

16.4.2.16. vmName

Synopsis

vmName([label,] interface)

Description

vmName takes a single argument, in addition to the optional label, which must be an interface field (note that vmName does not take an ifIndex parameter). It returns the name of the virtual machine attached to the interface, if there is one. If there is no VM attached to the interface, or if the parameter is not an interface, then the empty string is returned.

Example

To select the virtual machine name for a VM attached to virtual switch interface inputInterface, use this field in the select statement: vmName(inputInterface)

16.4.3. Value functions

16.4.3.1. count

Synopsis

count([label,] keyField [, keyField, ...]))

Description

count is an unusual function, which takes any number of key fields as arguments, in addition to the optional label, and returns a value field. It counts the number of unique combinations of all the key fields observed. This is useful if you are trying to understand for example, how many different connections a host made, rather than the specifics of each connection.

Example

To select each source, and the total number of destinations that each source connected to, you could use the following fields in a select: sourceAddress, count(sourceAddress, destinationAddress)

16.4.3.2. max

Synopsis

max([label,] valueField1, valueField2)

Description

max returns the larger of the values of two value fields. Either or both of the fields can also be other value functions, in addition to constant fields, to allow more complex expressions to be created. max is most useful when both the arguments are of similar type - eg frames, or bytes, etc.

Example

To select the larger of ingress frames and egress frames, use this field in the select statement: max(framesIn, framesOut)

To select the larger of the ingress frames per second and egress frames per second, you would use a combination of the max function and the rate function (see Section 16.4.3.5, “rate”): max("Frame rate", rate(framesIn), rate(framesOut)) . This example uses a label ("Frame rate") which means that the function will be displayed as Frame rate in table headings and chart legends.

16.4.3.3. min

Synopsis

min([label,] valueField1, valueField2)

Description

min returns the smaller of the values of two value fields. Either or both of the fields can also be other value functions, in addition to constant fields, to allow more complex expressions to be created. min is most useful when both the arguments are of similar type - eg frames, or bytes, etc.

Example

To select the smaller of ingress bytes and egress bytes, use this field in the select statement: min(bytesIn, bytesOut)

16.4.3.4. percent

Synopsis

percent([label], valueField)

Description

percent takes a single value field as a parameter, in addition to the optional label, and converts it to a percent of the value over the interval. For example, it would convert a frames field into percent of frames of the relevant interval.

Example

To select the percent of total frames in the interval, use this field in the select statement: percent(framesTotal)

16.4.3.5. rate

Synopsis

rate([label,] valueField)

Description

rate takes a single value field as a parameter, in addition to the optional label, and converts it to a value per second (ie a rate). For example, it would convert a frames field into frames per second.

Example

To select the total frames per second, use this field in the select statement: rate(framesTotal)

16.4.3.6. scale

Synopsis

scale([label,] valueField, factor)

Description

scale takes a single value field as a parameter, in addition to the optional label, and scales it by a constant factor, given as the second argument. The factor can be a real number. This is very useful to convert a bytes field into bits - use a scale factor of 8.

Example

To select the total bits per second, first of all scale total bytes to get bits, then convert it into a rate: rate(scale(bytesTotal, 8))

16.4.3.7. sum

Synopsis

sum([label,] valueField1, valueField2)

Description

sum adds the values of two values, and returns the result. Either or both of the fields can be other value functions, in addition to constant fields, to allow more complex expressions to be created.

Example

To select the sum of ingress multicasts and broadcasts, use this field in the select statement: sum(ifInMulticasts, ifInBroadcasts)

16.4.4. Time functions

16.4.4.1. format

Synopsis

format([label,] time, [[todayFormat], otherDayFormat])

Description

format takes up to three arguments, in addition to the optional label: the first must be the time field (time). The second, optionally, is a Java time and date format string that will be used to format time which is in today. The third argument is a format string that will be used to format time that is not in today. If the second argument is missing, the same format string will be used to format all time, regardless of whether it is in today or not. If both the second and third arguments are missing, then default formats are used, which formats time in today with a short time (no date), and formats time in other days in with a short time and date.

For documentation on how to construct a format string, please see the Java data format documentation

Example

To select time formatted with the default formatter, use this field in the select statement: format(time)

To select time formatted as 24 hour time, without the date, for today, and including a short date for other days, use this field in the select statement: format(time, "HH:mm", "dd/MM/yy HH:mm")

16.4.4.2. timestamp

Synopsis

timestamp([label,] time)

Description

timestamp takes a single argument, in addition to the optional label, which must be the time field (time), and returns the timestamp corresponding to the time. The timestamp is the number of milliseconds since January 1st, 1970.

Example

To select the timestamp of data, use this field in the select statement: timestamp(time)