- The Devo data analytics platform
- Getting started
- Domain administration
-
Sending data to Devo
-
The Devo In-House Relay
- Installing the Devo Relay
- Configuring the In-House Relay
- Relay migration
- Sending SSL/TLS encrypted events to the Devo relay
- Relay troubleshooting tips (v1.4.2)
-
Event sources
- Unix-like machines
- Windows
- MacOS X
- Cloud services
- Commercial products
- Custom apps
-
Universal Agent
- Deployment scenarios
- Pre-integrated query packs
- Data querying in Devo
-
Universal Agent Manager deployment
- Generic deployment guidelines
- Universal Agent Manager - CentOS 7 Deployment
- Universal Agent Manager - CentOS 8 Deployment
- Universal Agent Manager - Debian 9 Deployment
- Universal Agent Manager - Debian 10 Deployment
- Universal Agent Manager - RHEL 7 Deployment
- Universal Agent Manager - RHEL 8 Deployment
- Universal Agent Manager - Ubuntu 18 Deployment
- Universal Agent deployment
- Universal Agent Manager user manual
- Operational guidelines
- Performance considerations
- Universal Agent 1.0.1 upgrade procedure
- Other data collection methods
- Uploading log files
- Devo software
-
The Devo In-House Relay
-
Parsers and collectors
- About Devo tags
- Special Devo tags and data tables
-
List of Devo parsers
- Business & Consumer
- Cloud technologies
- Databases
- Host and Operating Systems
-
Network and application security
- auth.cisco
- auth.secureauth
- auth.securenvoy
- av.mcafee
- av.sophos
- box.iptables
- edr.carbonblack
- edr.crowdstrike
- edr.cylance
- edr.fireeye.alerts
- edr.minervalabs.events
- edr.paloalto
- endpoint.symantec
- firewall.checkpoint
- firewall.cisco firepower and vpn.cisco
- firewall.fortinet
- firewall.huawei
- firewall.juniper
- firewall.paloalto
- firewall.pfsense
- firewall.sonicwall
- firewall.sophos
- firewall.sophos.xgfirewall
- firewall.stonegate
- firewall.windows
- ids.extrahop
- mail.proofpoint
- nac.aruba
- network.meraki
- network.versa
- network.vmware
- proxy.bluecoat
- proxy.forcepoint
- proxy.squid
- proxy.zscaler
- uba.varonis
- vuln.beyondtrust
- vpn.pulsesecure.sa
- vpn.zscaler
- Network connectivity
- Web servers
- Technologies supported in CEF syslog format
- Collectors
-
Searching data
- Accessing data tables
-
Building a query
- Data types in Devo
- Build a query in the search window
- Build a query using LINQ
- Working with JSON objects in data tables
- Subqueries
-
Operations reference
-
Aggregation operations
- Average (avg)
- Count (count)
- First (first)
- First not null (nnfirst)
- HyperLogLog++ (hllpp)
- HyperLogLog++ Count Estimation (hllppcount)
- Last (last)
- Last not null (nnlast)
- Maximum (max)
- Median / 2nd quartile / Percentile 50 (median)
- Minimum (min)
- Non-null average (nnavg)
- Non-null standard deviation (biased) (nnstddev)
- Non-null standard deviation (unbiased) (nnustddev)
- Non-null variance (biased) (nnvar)
- Non-null variance (unbiased) (nnuvar)
- Percentile 10 (percentile10)
- Percentile 25 / 1st quartile (percentile25)
- Percentile 5 (percentile5)
- Percentile 75 / 3rd quartile (percentile75)
- Percentile 90 (percentile90)
- Percentile 95 (percentile95)
- Standard deviation (biased) (stddev)
- Standard deviation (unbiased) (ustddev)
- Sum (sum)
- Sum Square (sum2)
- Variance (biased) (var)
- Variance (unbiased) (uvar)
-
Arithmetic group
- Absolute value (abs)
- Addition, sum, plus / Concatenation (add, +)
- Ceiling (ceil)
- Cube root (cbrt)
- Division (div, \)
- Division remainder (rem, %)
- Floor (floor)
- Modulo (mod, %%)
- Multiplication, product (mul, *)
- Power (pow)
- Real division (rdiv, /)
- Rounding (round)
- Sign (signum)
- Square root (sqrt)
- Subtraction, minus / Additive inverse (sub, -)
-
Conversion group
- Duration (duration)
- Format date (formatdate)
- From base16, b16, hex (from16)
- From base64, b64 (from64)
- From UTF8 (fromutf8)
- From Z85, base85 (fromz85)
- Human size (humanSize)
- Make byte array (mkboxar)
- Parse date (parsedate)
- Regular expression, regexp (re)
- Template (template)
- Timestamp (timestamp)
- To base16, b16, hex (to16)
- To base64, b64, hex (to64)
- To BigInt (bigint)
- To boolean (bool)
- To Float (float)
- To image (image)
- To Int (int)
- To IPv4 (ip4)
- To IPv4 net (net4)
- To IPv6 (ip6)
- To IPv6 compatible (compatible)
- To IPv6 mapped (mapped)
- To IPv6 net (net6)
- To IPv6 translated (translated)
- To MAC address (mac)
- To string (str)
- To string (stringify)
- To UTF8 (toutf8)
- To Z85, base85 (toz85)
- Cryptography group
- Date group
- Flow group
- General group
-
Geolocation group
- Coordinates distance (distance)
- Geocoord (geocoord)
- Geographic coordinate system (coordsystem)
- Geohash (geohash)
- Geohash string (geohashstr)
- Geolocated Accuracy Radius with MaxMind GeoIP2 (mm2accuracyradius)
- Geolocated ASN (mmasn)
- Geolocated ASN with MaxMind GeoIP2 (mm2asn)
- Geolocated AS Organization Name with MaxMind GeoIP2 (mm2asorg)
- Geolocated AS owner (mmasowner)
- Geolocated City (mmcity)
- Geolocated City with MaxMind GeoIP2 (mm2city)
- Geolocated Connection Speed (mmspeed)
- Geolocated connection type with MaxMind GeoIP2 (mm2con)
- Geolocated Coordinates (mmcoordinates)
- Geolocated coordinates with MaxMind GeoIP2 (mm2coordinates)
- Geolocated Country (mmcountry)
- Geolocated Country with MaxMind GeoIP2 (mm2country)
- Geolocated ISP (mmisp)
- Geolocated ISP name with MaxMind GeoIP2 (mm2isp)
- Geolocated Latitude (mmlatitude)
- Geolocated Latitude with MaxMind GeoIP2 (mm2latitude)
- Geolocated Level 1 Subdivision with MaxMind GeoIP2 (mm2subdivision1)
- Geolocated Level 2 Subdivision with MaxMind GeoIP2 (mm2subdivision2)
- Geolocated Longitude (mmlongitude)
- Geolocated Longitude with MaxMind GeoIP2 (mm2longitude)
- Geolocated Organization (mmorg)
- Geolocated organization name with MaxMind GeoIP2 (mm2org)
- Geolocated Postal Code (mmpostalcode)
- Geolocated Postal Code with MaxMind GeoIP2 (mm2postalcode)
- Geolocated Region (mmregion)
- Geolocated Region Name (mmregionname)
- ISO-3166-1 Continent Alpha-2 Code (continentalpha2)
- ISO-3166-1 Continent Name (continentname)
- ISO-3166-1 Country Alpha-2 Code (countryalpha2)
- ISO-3166-1 Country Alpha-2 Continent (countrycontinent)
- ISO-3166-1 Country Alpha-3 Code (countryalpha3)
- ISO-3166-1 Country Latitude (countrylatitude)
- ISO-3166-1 Country Longitude (countrylongitude)
- ISO-3166-1 Country Name (countryname)
- Latitude (latitude)
- Latitude and longitude coordinates (latlon)
- Longitude (longitude)
- Parse geocoord format (parsegeo)
- Represent geocoord format (reprgeo)
- Round coordinates (gridlatlon)
- JSON group
- Logic group
-
Mathematical group
- Arc cosine (acos)
- Arc sine (asin)
- Arc tangent (atan)
- Bitwise AND (band, &)
- Bitwise left shift (lshift, <<)
- Bitwise NOT (bnot, ~)
- Bitwise OR (bor, |)
- Bitwise right shift (rshift, >>)
- Bitwise unsigned right shift (urshift, >>>)
- Bitwise XOR (bxor, ^)
- Cosine (cos)
- e (mathematical constant) (e)
- Exponential: base e (exp)
- Hyperbolic cosine (cosh)
- Hyperbolic sine (sinh)
- Hyperbolic tangent (tanh)
- Logarithm: base 2 (log2)
- Logarithm: base 10 (log10)
- Logarithm: natural / arbitrary base (log)
- Pi (mathematical constant) (pi)
- Sine (sin)
- Tangent (tan)
- Meta Analysis group
- Name group
-
Network group
- HTTP Status Description (httpstatusdescription)
- HTTP Status Type (httpstatustype)
- IP Protocol (ipprotocol)
- IP Reputation Score (reputationscore)
- IP Reputation Tags (reputation)
- IPv4 legal use (purpose)
- IPv6 host number (host)
- IPv6 routing number (routing)
- Is IPv4 (ipip4)
- Is Private IPv4 (isprivate)
- Is Public IPv4 (ispublic)
- Squid Black Lists Flags (sbl)
- Order group
-
Packet group
- Ethernet destination MAC address (etherdst)
- Ethernet payload (etherpayload)
- Ethernet source MAC address (ethersrc)
- Ethernet status (etherstatus)
- Ethernet tag (ethertag)
- EtherType (ethertype)
- Has Ethernet frame (hasether)
- Has IPv4 datagram (hasip4)
- Has TCP segment (hastcp)
- Has UDP datagram (hasudp)
- IPv4 destination address (ip4dst)
- IPv4 differentiated services (ip4ds)
- IPv4 explicit congestion notification (ip4ecn)
- IPv4 flags (ip4flags)
- IPv4 fragment offset (ip4fragment)
- IPv4 header checksum (ip4cs)
- IPv4 header length (ip4hl)
- IPv4 identification (ip4ident)
- IPv4 payload (ip4payload)
- IPv4 protocol (ip4proto)
- IPv4 source address (ip4src)
- IPv4 status (ip4status)
- IPv4 time to live (ip4ttl)
- IPv4 total length (ip4len)
- IPv4 type of service (ip4tos)
- TCP ACK (tcpack)
- TCP checksum (tcpcs)
- TCP destination port (tcpdst)
- TCP flags (tcpflags)
- TCP header length (tcphl)
- TCP payload (tcppayload)
- TCP sequence number (tcpseq)
- TCP source port (tcpsrc)
- TCP status (tcpstatus)
- TCP urgent pointer (tcpurg)
- TCP window size (tcpwin)
- UDP checksum (udpcs)
- UDP destination port (udpdst)
- UDP length (udplen)
- UDP payload (udppayload)
- UDP source port (udpsrc)
- UDP status (udpstatus)
- Statistical group
-
String group
- Contains (has, ->)
- Contains - case insensitive (weakhas)
- Contains tokens (toktains)
- Contains tokens - case insensitive (weaktoktains)
- Edit distance: Damerau (damerau)
- Edit distance: Hamming (hamming)
- Edit distance: Levenshtein (levenshtein)
- Edit distance: OSA (osa)
- Ends with (endswith)
- Format number (formatnumber)
- Hostname public suffix (publicsuffix)
- Hostname root domain (rootdomain)
- Hostname root prefix (rootprefix)
- Hostname root suffix (rootsuffix)
- Hostname subdomains (subdomain)
- Hostname top level domain (topleveldomain)
- Is empty (isempty)
- Is in (`in`, <-)
- Is in - case insensitive (weakin)
- Length (length)
- Locate (locate)
- Lower case (lower)
- Matches (matches, ~)
- Peek (peek)
- Replace all (replaceall)
- Replace first (replace)
- Shannon entropy (shannonentropy)
- Split (split)
- Split regexp (splitre)
- Starts with (startswith)
- Substitute (subs)
- Substitute all (subsall)
- Substring (substring)
- Trim both sides (trim)
- Trim the left side (ltrim)
- Trim the right side (rtrim)
- Upper case (upper)
-
Web group
- Absolute URI (absoluteuri)
- Opaque URI (opaqueuri)
- URI authority (uriauthority)
- URI fragment (urifragment)
- URI host (urihost)
- URI path (uripath)
- URI port (uriport)
- URI query (uriquery)
- URI scheme (urischeme)
- URI ssp (urissp)
- URI user (uriuser)
- URL decode (urldecode)
- User Agent Company (uacompany)
- User Agent Company URL (uacompanyurl)
- User Agent Device Icon (uadeviceicon)
- User Agent Device Information URL (uadeviceinfourl)
- User Agent Device Type (uadevicetype)
- User Agent Family (uafamily)
- User Agent Icon (uaicon)
- User Agent Information URL (uainfourl)
- User Agent is Robot (uaisrobot)
- User Agent Name (uaname)
- User Agent OS Company (uaoscompany)
- User Agent OS Company URL (uaoscompanyurl)
- User Agent OS Family (uaosfamily)
- User Agent OS Icon (uaosicon)
- User Agent OS Name (uaosname)
- User Agent OS URL (uaosurl)
- User Agent Type (uatype)
- User Agent URL (uaurl)
- User Agent Version (uaversion)
-
Aggregation operations
-
Working in the search window
-
Generate charts
- Affinity chord diagram
- Availability timeline
- Bipartite chord diagram
- Bubble chart
- Chart aggregation
- Custom date chart aggregation
- Flame graph
- Flat world map by coordinates
- Flat world map by country
- Google animated heat map
- Google area map
- Google heat map
- Graph diagram
- Histogram
- Pew Pew map
- Pie chart
- Pie layered chart
- Punch card
- Robust Random Cut Forest chart
- Sankey diagram
- Scatter plot
- Time heatmap
- Triple exponential chart
- Voronoi treemap
- Data enrichment
- Setting up a data table
- Advanced data operations
- Use case: eCommerce behavior analysis
-
Generate charts
- Managing your queries
- Best practices for data search
- Monitoring tables
- Activeboards
-
Dashboards
-
Working with dashboard widgets
- Availability timeline widget
- Chord diagram widget
- Circle world map widget
- Color key value widget
- Color world map widget
- Column chart widget
- Comparative chart widget
- Funnel widget
- Gauge meter widget
- Google heatmap widget
- Heat calendar widget
- Line chart widget
- Monitoring widget
- Pie chart widget
- Punch card widget
- Sectored pie chart widget
- Table widget
- Time heatmap widget
- Tree diagram widget
- Voronoi tree widget
- Configuring and sharing dashboards
-
Working with dashboard widgets
- Alerts and notifications
- Panels
- Applications
- Tools
- Flow
- Social Intelligence
- API reference
- Release notes
Working with JSON objects in data tables
When searching for data in a table that has columns containing JSON syntax (JSON data type), the search window UI offers some useful features to make working with this information fast and easy. You can:
Check JSON content using the pretty-print view
In this section, you will learn to use the pretty-print view to format and extract values from your JSON content. You can start checking the following video to see the process through an example:
You can open this view to check the content of a cell to better analyze it. Hover over any cell in your data table and click P on your keyboard to open this view in a separate window. For JSON content, this is especially useful because it is displayed in a reader-friendly way: name/value pairs are shown on separate rows, values are color-coded by data type and there are columns to show the data type icon, number of items and the level of depth.
If the content of the JSON is too large to handle you can use the following options to help you through:
- Click the expand/collapse arrows next to the nodes that contain subnodes to show the desired level of depth. You can quickly expand or collapse all of them by clicking the Expand all nodes/Collapse all nodes buttons at the top of the window, next to the search box.
- Use the search at the top left of the window to help you locate a node by its name or property value using a string. The content will be filtered as you type, showing the number of results next to the search and highlighting the matches. Use the back/forward arrows inside the search to navigate through the results.
- You can use the path selector at the bottom left of the window to filter by node and depth level:
- Clicking the data type icon or the three dots will open a selector for you to choose between the available nodes in the first level (level 1). Only the selected node and its content in the levels below, if any, will be shown.
- In case there is content below, the path selector will be updated with an additional icon and three dots for you to choose a node for the next level (level 2). Additional options will keep appearing in the path selector as you keep drilling down and until there is no further content in the level below. You can repeat the process until you reach the desired node and depth level.
- You can later change any of your selections by clicking on it and choosing another option. The path will be reconstructed accordingly, changing that level and removing the selections for the levels below. Clicking the name in blue at the beginning of the path (the name of the column) will show all the content of the JSON syntax including the top level (level 0).
Extract a property value into a new column
Using the pretty print view
The pretty-print view can be used with a JSON cell not only to check but also to extract information; you can put a specific property value into new columns in your table.
- Check the box(es) of the desired node(s) or use the master checkbox at the top left of the window to select/unselect all. Then, click the Extract button at the top right of the window, which is enabled after checking at least one box.
- The next window displays the selected nodes with the properties they will have when added as new columns so you can change them before confirming.
Give each column the desired name using the Column Name fields.
You can choose the data type for each new column or leave the JSON data type. The available options will be automatically determined for the kind of string recognized (for example, numbers could be integer or float). Click the data type icon of the desired row under the Type column to switch between the different options.
Click OK and the columns will be created in the table, each of them with the name and data type indicated and each cell displaying the corresponding value of the corresponding JSON.
This will apply to all the JSON in the same column as long as they present the exact same structure. For those that present a different structure, the cells of the newly created columns will display the value "null" and you would have to repeat the process to extract their values.
Performing operations in the search window
You can extract property values into new columns using the Jq evaluation (jqeval) and Jq filter compilation (jqcompile) operations. The new column is created in the JSON data type so if you want to use it in a subsequent operation, you will need to transform it into a different data type using the operations in the Conversion group.
Using LINQ syntax
If you want to perform the operations using LINQ syntax, you can stack the different operations into a single one to avoid unnecessary steps:
- To extract a property's value into a new column using LINQ, use the Jq evaluation (jqeval) operation to apply a jq filter to the JSON column. Use the Jq filter compilation (jqcompile) operation to identify the jq filter as an argument.
select jqeval(jqcompile(".email"), json) as emailUser
- In order to generate the new column in string data type, we apply the To string (str) operation on the entire operation.
select str(jqeval(jqcompile(".email"), json)) as emailUser
Related articles
- Data types in Devo
- Jq evaluation (jqeval)
- Jq filter compilation (jqcompile)
- Json value type (label)
- To json (jsonparse)
- Working with JSON objects in data tables