How can I edit my query to exclude partially matched results in the aggregate view?
I have a saved search that is returning results for URLs that I am expecting to see. I would like to exclude these results by using a partial match or whatever would be deemed best practice. Below the screenshot is the query.
_sourceCategory = symmetrylending/prod/network/meraki/* urls
| parse regex " (?<name>\S*?)\s(?<msg_type>urls|flows|events|ids-alerts|security_event|airmarshal_events?)\s+" nodrop
| kv regex "=(.*?)(?:\s|$)" "src", "dst", "mac" as src, dst, mac_address nodrop
| kv "agent" as user_agent nodrop
| parse "request: * *" as method, url nodrop
| parse field=src "*:*" as src_ip, src_port nodrop | parse field=dst "*:*" as dest_ip, dest_port nodrop
| where msg_type="urls" AND url !="https://dev-prod05.conferdeploy.net/..."
| count as Requests by url
| top 10 url by Requests, url asc
Hi admin.amartin :-)
The good news is that you were very close!
The equals (=) operator evaluates for a literal match. To do partial matches, use the matches operator (LINK) which will allow asterisked wildcards or regular expressions (which by the looks of things you're more than comfortable with :-)
The only other suggestion I'd make would be to optimise your initial parse regex to speed up processing based on the desired outcome.
| parse regex " (?<name>\S*?)\s(?<msg_type>urls)\s+"
Using a literal match for "urls" will provide the desired filtering, and removing nodrop will reduce unnecessary processing by dropping fields you're not ultimately interested in anyway (based on your where statement towards the end of the query).
I hope this helps!
Oh I absolutely did not mean to mislead you or anyone else reading this post.
This saved search was based on a pre-built Meraki panel. I am nowhere near the comfort level to be writing like this just yet but I am working on it! I'm only now getting comfortable with parse anchors so I've just been trying to reverse engineer the dashboard panel searches to fit my needs. I can only hope to someday write these queries from scratch myself.
So, given your reply I'm still not quite sure what I need to modify to exclude some of the URLs I'm seeing. How would I write this into the search query if I wanted to exclude the first three URLs I'm seeing or other results that include "office365"? These are known URLs that we expect to see users accessing so I don't need to see them in this saved search/e-mail notification.
With enough time and the training provided I'm determined to get there!
Thanks much for your reply!
Ahh, I realise I didn't actually give you a workable answer!
| where msg_type="urls" AND !(url matches "https://dev-prod05.conferdeploy.net/*")
Please feel free to keep asking questions in here, or even over in our public Slack channel (LINK)
Thanks, Ryan! I'm in the Slack channel as well! Hopefully I'm posting in the right channel but if I'm not, please do let me know. I posted this same question there.
Quick edit to my post here -- is there any way to have it exclude the result in the aggregate window based on a partial match or does it have to be exact?
In the answer you provided you have the full link but I'm hoping to exclude a result based on only part of the url. Not sure if this is possible.
Ninja edit: I ended up using "contains" instead of "matches" so I think that's working now.
I appreciate the assist!
Please sign in to leave a comment.