Here in 6.1. I’m ingesting a ton of firewall logs and trying to do a little threat hunting. But I don’t understand how to do a query that just returns certain fields so I can quickly look at data and see what is going on. For instance right now I am tracking an IP address. So I want to query
interface: ext0 AND SourceIP: “w.x.y.z”
That returns the full message which makes it almost impossible to look at the data quickly. I want the SQL equivalent of:
SELECT SourcePort,DestinationPort WHERE interface: ext0 AND SourceIP: “w.x.y.z” SORT BY DestinationPort DESC
I want to just quickly see the data. How do I do that?
Are you parsing out the message into fields when your ingesting it with pipeline rules or extractors? What does an example message look like when its displayed in the search results.
Yes I parse the message fully to individual fields. When I do a query, and look for data, all it does is return every message that matches and returns the full message which doesn’t really help me. So, the basic query works correctly. But I want to just display certain fields in the return.
In fact even the limit command doesn’t do anything.
Oh, ya there is no way to limit the fields, it returns all the messages. You can customize the columns that show, or in aggregation widgets, but the main sesrch results of a message show all fields for any message.
yeah… ive read over that stuff. It doesn’t do anything to fix my problem. So let me ask this a different way. You are in a crunch and all your firewall logs are flowing into Graylog. You have an external IP that is setting off some notifications that it’s probably doing some xmas tree poodling. To verify that, all I need to do is query for the SourceIp = “the public IP i’m sus on”, and look at the fields DestinationPort, and if it’s a TCP connection attempt (ProtoID=6), then give me the TCPOptions field as well. Limit it to the last 100 entries ordered by time descending.
That will allow me to rapidly inspect what the source is doing. And apparently that is impossible in Graylog. However, since I started this, processing the messages and encapsulating them as json and loading them into MariaDB works like a charm and is so far way easier.
so your message is well parsed? then have a look at the edit-button on the top-right and add those fields in the edit-panel on the left. then it will look similar to this:
when you have already specific fields for every message, then you have to create a search with the correct displayed data, for example the right stream, the right table with the right columns. For this you have to create this at your own.
Attention Message Preview is not checked on left side in widget configuration.
And of course you can parse field data in pipelines for specific field usage.
SELECT SourcePort,DestinationPort WHERE interface: ext0 AND SourceIP: “w.x.y.z” SORT BY DestinationPort DESC
for your query you can then type something like this:
SourceIP:“w.x.y.z” AND interface:“ext0”
When you want to sort, you can click on the column DestinationPort. But i personally like the feature “show top values” when you click on the little dropdown in the column header for DestinationPort.
that shows a list of the Ports with count and Percent.
Every Query is depend for the choosen Time in the Time range search. The choosen time is valid in a search to all widgets. You can create a second widget that counts for you the hits for field messages. And the best is, this search is usable for every admin that has to deal with firewall logs. Save the search and share with your buddies and everyone is happy.
Example search, what you can use everyday and if shared other admins have also this nice tool.
Hope that helps for you.
When you have to deal with a large amount of logs, a MariaDB cannot handle this with good performance like graylog with a elasticsearch/opensearch backend.
