{"type":"doc","content":[{"type":"extension","attrs":{"layout":"default","extensionType":"com.atlassian.confluence.macro.core","extensionKey":"toc","parameters":{"macroParams":{"maxLevel":{"value":"2"},"type":{"value":"flat"}},"macroMetadata":{"macroId":{"value":"2da7238d-2505-4200-8798-fba9a64b0910"},"schemaVersion":{"value":"1"},"title":"Table of Contents"}},"localId":"7ee4da20-cdca-42bf-bd3b-cf046099efba"}},{"type":"heading","attrs":{"level":2},"content":[{"text":"Overview","type":"text"}]},{"type":"panel","attrs":{"panelType":"warning"},"content":[{"type":"paragraph","content":[{"text":"Note that this feature is still not available in the ","type":"text"},{"text":"Data Search","type":"text","marks":[{"type":"strong"}]},{"text":" area of the application. You can use it in your ","type":"text"},{"text":"Activeboards","type":"text","marks":[{"type":"link","attrs":{"href":"https://devodocs.atlassian.net/wiki/pages/createpage.action?spaceKey=NDT5&title=Activeboards"}}]},{"text":" and through the ","type":"text"},{"text":"Devo API","type":"text","marks":[{"type":"link","attrs":{"href":"https://devodocs.atlassian.net/wiki/pages/createpage.action?spaceKey=NDT5&title=Query%20API"}}]},{"text":".","type":"text"}]}]},{"type":"paragraph","content":[{"text":"Devo provides a functionality that suppresses the boundaries between searches and removes certain limitations of a single search: subqueries. It consists of using the result of a query to act as a filter in another query, thus making it possible to use information from different sources in a single consultation.","type":"text"}]},{"type":"paragraph","content":[{"text":"For example, let's assume we have 2 different queries (","type":"text"},{"text":"query","type":"text","marks":[{"type":"em"}]},{"text":" and ","type":"text"},{"text":"subquery","type":"text","marks":[{"type":"em"}]},{"text":" in the picture below) and we want to use the values in column 2 of the ","type":"text"},{"text":"subquery","type":"text","marks":[{"type":"em"}]},{"text":" to filter the data in the ","type":"text"},{"text":"query. ","type":"text","marks":[{"type":"em"}]},{"text":"Matching the columns 2 in both queries, whose data belongs to the same type, we can filter the data in our ","type":"text"},{"text":"query","type":"text","marks":[{"type":"em"}]},{"text":" and get only those events that contain the values in the column 2 of the ","type":"text"},{"text":"subquery","type":"text","marks":[{"type":"em"}]},{"text":".","type":"text"}]},{"type":"mediaSingle","attrs":{"layout":"center"},"content":[{"type":"media","attrs":{"width":1260,"id":"1296182a-5b4a-4d43-ade6-8c0ce563cb62","collection":"contentId-94743378","type":"file","height":700}}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"How does it work in LINQ?","type":"text"}]},{"type":"paragraph","content":[{"text":"You have to insert the full subquery enclosed in brackets ","type":"text"},{"text":"()","type":"text","marks":[{"type":"strong"}]},{"text":" and preceded by the clause ","type":"text"},{"text":"where in","type":"text","marks":[{"type":"code"}]},{"text":", functioning as the second argument of a filtering operation in the main query. You have to get rid of any unrelated field in the subquery by grouping your data or creating the required columns. The remaining fields must be only those whose data type will match the fields indicated in the main query.","type":"text"}]},{"type":"codeBlock","content":[{"text":"from \n\n[clauses]\n\n where in (\n\n from \n\n [clauses] \n\n where time expression < eventdate < time expression \n\n group every - by / select )\n\nselect ","type":"text"}]},{"type":"paragraph","content":[{"text":"Be careful with the allocation of the brackets and always ensure to finish the clause, especially when the subquery is very long and contains a variety of operations.","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Guidelines","type":"text"}]},{"type":"paragraph","content":[{"text":"There are some considerations we need to take into account when working with subqueries:","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"The data types of the resulting fields in the subquery and the fields indicated in the main query must match","type":"text","marks":[{"type":"underline"}]},{"text":". You can do this by grouping your data or creating the required columns. For example, if we want to filter a string column in the main query, we need to use a subquery that results in a string column. However, the names of the columns in the subquery and the main query can be different.","type":"text"},{"type":"hardBreak"},{"text":"from ","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"where in (","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"from ","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"where now() - 5m < eventdate < now()","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"group every - by )","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"select *","type":"text","marks":[{"type":"code"}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"You can match the data of the main query and subquery through more than one field, as long as their data types match and are indicated in the proper order (the first one matches the first one, and so on).","type":"text"},{"type":"hardBreak"},{"text":"from ","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"where () in (","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"from ","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"where now() - 5m < eventdate < now()","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"group every - by )","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"select *","type":"text","marks":[{"type":"code"}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"The subquery may contain additional fields, as long as they are added after the ones that match the main query.","type":"text"},{"type":"hardBreak"},{"text":"from ","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"where in (","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"from ","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"where now() - 5m < eventdate < now()","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"group every - by )","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"select *","type":"text","marks":[{"type":"code"}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"You must add the fields you want to see in the final query results using the operator ","type":"text"},{"text":"select","type":"text","marks":[{"type":"code"}]},{"text":". Include ","type":"text"},{"text":"select * ","type":"text","marks":[{"type":"code"}]},{"text":"to add all the columns in the table, or specify the required columns after the ","type":"text"},{"text":"select","type":"text","marks":[{"type":"code"}]},{"text":" operator.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"The subquery may come from the same table as the main query, but also from a different one.","type":"text"}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Time range selection","type":"text"}]},{"type":"paragraph","content":[{"text":"In case you want to explicitly specify a time range for your subquery data, you must include it using the following syntax:","type":"text"}]},{"type":"paragraph","content":[{"text":" ","type":"text"},{"text":"where time expression < eventdate < time expression","type":"text","marks":[{"type":"code"}]},{"text":".","type":"text"}]},{"type":"paragraph","content":[{"text":"Following this format, you can specify either times relative to now or absolute dates.","type":"text"}]},{"type":"paragraph","content":[{"text":"This is an example with relative dates:","type":"text"}]},{"type":"codeBlock","content":[{"text":"from demo.ecommerce.data\nwhere statusCode in (\nfrom demo.ecommerce.data \nwhere statusCode = \"404\" \nwhere now()- 5m < eventdate < now()\ngroup every - by statusCode) \nselect method, statusCode, eventdate","type":"text"}]},{"type":"paragraph","content":[{"text":"And the following is an example with absolute dates. As you can see, it is necessary to use the ","type":"text"},{"text":"Parse date (parsedate)","type":"text","marks":[{"type":"link","attrs":{"href":"/wiki/spaces/v7100/pages/94746036"}}]},{"text":" operation to transform a given string representing a date into a timestamp. Check the operation article for more details and valid formats.","type":"text"}]},{"type":"codeBlock","content":[{"text":"from siem.logtrust.web.activity\nwhere userid in(\nfrom siem.logtrust.web.error\nwhere not userid = \"\"\nwhere parsedate(\"21/04/2020 21:00\",\"DD/MM/YYYY HH:mm\") < eventdate < parsedate(\"21/04/2020 21:40\",\"DD/MM/YYYY HH:mm\")\ngroup every - by userid)\nselect *","type":"text"}]},{"type":"paragraph","content":[{"text":"It is possible to specify a time range for the subquery different than the one specified for the main query. Also, note that If you do NOT include a time range in your subquery. it will default to the same time range as the main query.","type":"text"}]},{"type":"panel","attrs":{"panelType":"warning"},"content":[{"type":"paragraph","content":[{"text":"Including a temporal grouping in your subquery","type":"text","marks":[{"type":"strong"}]}]},{"type":"paragraph","content":[{"text":"Note that if you include a temporal grouping in your subquery (for example, ","type":"text"},{"text":"group every 30m by ","type":"text","marks":[{"type":"code"}]},{"text":") you may not get any data. This is because a temporal grouping will return an eventdate prior to the time specified (in the example before,","type":"text"},{"text":" 30m","type":"text","marks":[{"type":"em"}]},{"text":"). That will be compared to the main query time range and will likely return no results.","type":"text"}]},{"type":"paragraph","content":[{"text":"For this reason, it is recommended to add no-time based groupings (","type":"text"},{"text":"group every -","type":"text","marks":[{"type":"code"}]},{"text":") to your subqueries.","type":"text"}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Query examples","type":"text"}]},{"type":"paragraph","content":[{"text":"Check below examples of subqueries that cover the different cases explained in the guidelines above. After each query, there is a capture that shows the result of adding them as source queries in an Activeboard widget. Go ","type":"text"},{"text":"here","type":"text","marks":[{"type":"link","attrs":{"href":"https://devodocs.atlassian.net/wiki/pages/createpage.action?spaceKey=NDT5&title=Activeboards"}}]},{"text":" to learn more about Activeboards and how to use them.","type":"text"}]},{"type":"heading","attrs":{"level":4},"content":[{"text":"Subquery from the same table as the main query","type":"text"}]},{"type":"codeBlock","content":[{"text":"from demo.ecommerce.data\nwhere statusCode in (\nfrom demo.ecommerce.data \nwhere statusCode = \"404\" \nwhere now()- 5m < eventdate < now()\ngroup every - by statusCode) \nselect method, statusCode, eventdate","type":"text"}]},{"type":"mediaSingle","attrs":{"layout":"center"},"content":[{"type":"media","attrs":{"width":1119,"id":"006a9a8d-e0f8-49a4-b3f9-1102b44195c9","collection":"contentId-94743378","type":"file","height":598}}]},{"type":"heading","attrs":{"level":4},"content":[{"text":"Subquery from a table different than the one in the main query","type":"text"}]},{"type":"codeBlock","content":[{"text":"from demo.ecommerce.data\nselect mmcountry(clientIpAddress) as Country\nwhere Country in (\nfrom siem.logtrust.web.activity\nwhere now() - 12h < eventdate < now()\ngroup every - by country)\nwhere isnotnull(Country)\nselect bytesTransferred, timeTaken","type":"text"}]},{"type":"mediaSingle","attrs":{"layout":"center"},"content":[{"type":"media","attrs":{"width":962,"id":"8172648b-8acb-43ea-87c8-153a16e4deb3","collection":"contentId-94743378","type":"file","height":629}}]},{"type":"heading","attrs":{"level":4},"content":[{"text":"Subquery using two columns to filter","type":"text"}]},{"type":"codeBlock","content":[{"text":"from siem.logtrust.web.activity \nwhere (username, domain) in (\nfrom siem.logtrust.web.navigation \nwhere origin = \"redada\" \nwhere now()-2h

Browser not supported