What is the best way to setup/configure/build a SQL database for Kiwi Syslog Server?

What is your requirements to store the logs, especially for 5 years?  SQL can certainly do it with the correct configuration but it's hard to make recommendations without knowing the scale required.  We average between 5 and 8 billion logs yearly, at the standard message size(1k) it's a fairly large datastore. Since we have a lot of Windows event logs that can be 2k or more our storage would be 2.5-3x that.

If the goal is to search the logs at some future date what would be the most likely time window used? Weekly, monthly?  I'd create my tables based on that, probably monthly.

Do you need SQL? Can the requirements be met with simple filesystem search? Or would it be better to use something like ElasticSearch?

kstone​​ Well, over the past 6 months, I have been able to trim down the incoming syslog messages significantly, from just under 600k mph, down to roughly 50k mph. So, while we still receive some junk, most of it is actual data that may need to be referenced in the future. I know I said 5+ years, but, in reality, and from past experiences, I think keeping the data for a minimum of 18-24 months should cover the majority. When I said 5+ years, in regards to storage, I simply meant the storage over time part of it would probably be the least of my worries, as we seem to have more than enough to go around.

In regards to time, I would also say that the monthly option would work, I just do not know how to make it happen. As far as a time frame for the requested data, it varies. Sometimes they only need to look a few days, or weeks, back. Other times, it could be more than 14 months old.

More specifically, I currently have a handful of tables that I need to report basic metrics and counts on. The remainder of the tables/data simply need to sit safely, in case they are needed later.

Ex.

Due to my tiny brain, I'll try to keep my example/use case as simple as I can.

Syslog currently dumps messages into 10 different tables. Each table pertains to a different service type, vendor, machine type, etc.

I need to do various, separate, counts on the data in some of those tables, while the other data, in the remaining tables, just needs to be stored for later.

Here are some of the values in that table for which I need counts.

• Table 1
  • Total of all messages (total)
  • Individual Totals
    • Arrivals (total)
    • Departures (total)
    • Battery Failures
      • Alarm (total)
      • Clear (total)
    • Upstream
      • Alarm (total)
      • Clear (total)
    • Downstream
      • Alarm (total)
      • Clear (total)
    • Rogue
      • Alarm (total)
      • Clear (total)
    • DDOS Attack
      • Alarm (total)
      • Clear (total)

SELECT TOP 1000
Totals.Caption
,Totals.Total
,Arrivals.Arrivals
,Departures.Departs
,AlarmBattFails.AlarmBattFail
,ClearBattFails.ClearBattFail
,AlarmUpSDBER.AlarmUpSDBER
,ClearUpSDBER.ClearUpSDBER
,AlarmDwnSDBER.AlarmDwnSDBER
,ClearDwnSDBER.ClearDwnSDBER
FROM dbo.Kiwi_E7_Totals_1_Hour AS Totals
JOIN dbo.Kiwi_E7_Departures_1_Hour AS Departures ON Totals.Caption=Departures.Caption
JOIN dbo.Kiwi_E7_Arrivals_1_Hour AS Arrivals ON Totals.Caption=Arrivals.Caption
JOIN dbo.Kiwi_E7_AlarmBattFail_1_Hour AS AlarmBattFails ON Totals.Caption=AlarmBattFails.Caption
JOIN dbo.Kiwi_E7_ClearBattFail_1_Hour AS ClearBattFails ON Totals.Caption=ClearBattFails.Caption
JOIN dbo.Kiwi_E7_AlarmUpSDBER_1_Hour AS AlarmUpSDBER ON Totals.Caption=AlarmUpSDBER.Caption
JOIN dbo.Kiwi_E7_ClearUpSDBER_1_Hour AS ClearUpSDBER ON Totals.Caption=ClearUpSDBER.Caption
JOIN dbo.Kiwi_E7_AlarmDwnSDBER_1_Hour AS AlarmDwnSDBER ON Totals.Caption=AlarmDwnSDBER.Caption
JOIN dbo.Kiwi_E7_ClearDwnSDBER_1_Hour AS ClearDwnSDBER ON Totals.Caption=ClearDwnSDBER.Caption
ORDER BY Caption ASC

Currently, I pretty much just have all of those messages pouring into the same table. I have that db linked to my main SolarWinds db, allowing me to join it to track things back to various node properties. I built views in my SolarWinds db to show/filter the different items for which I need to gather metrics. Then I use this query in a custom resource/.aspx page, which gives me a nicely formatted display of the hourly totals for each item. I also do the same thing for 24 hour counts, as well as monthly.  All of this currently works, however, it is extremely slow most of the time. I do it this way to be able to link the table data in both databases. Also, these were the options available to me, and they worked first, so I had to stop there, and move on to the next impossible task. However, I am certainly willing to use different tools and methods.

Recently, I manually moved all messages in the table, older than xx days, into a different table. That instantly, and significantly, improved the page load time, which pushed me to decide it was time to take the next step... which brings me here.

I realize I have probably done every step to the worst possible practice, however, I have only a very limited amount of experience with databases. And, that tiny amount of experience is basically how to do simple queries, which is why I am probably making a big mess.

Thank you,

-Will

It would seem that you have the basics working and probably just need to have a SQL task to automate your move of old records into new monthly tables.  Not a DBA or SQL person so I can't help much there.

kstone​ Yeah, same here... I'm in no way a DBA or SQL person either... While I'm surely doing it all wrong, it does work for now, so I suppose things could be worse.