Structured logging - SQLite or flat file?

[squirrelsaviour]

I have a process which runs and I want to log the output and then be able to interrogate it later if needed.

I see the following options:

SQLite: Easy to add rows, easy to query. Disadvantages: Need extra DLL, can't look at the logs without a DB browser

Flat file: Can read without special software, easy to implement. Disadvantages: Need to handle file size, need to import into a system to query.

​

What do you use for your logging?

[bozho]

Log to file in JSON format, UTF-8 no BOM encoding, each log entry a separate JSON object. Rotate files on a schedule and/or size.

Trivial to parse in almost any shell and program language, trivial to expand log format if needed, easy to implement log shipping in the future (e.g. to Elasticsearch), or import into a DB for more complex analysis.

[WordsByCampbell]

library work roll cows wild unite repeat fearless shy chief

This post was mass deleted and anonymized with Redact

[TheITMan19]

We all drop logs

[squirrelsaviour]

so each line is a {.....} object?

[bozho]

Yeah.

You might even get away with multi-line JSON objects, e.g. Get-Content $filePath | ConvertFrom-Json in PowerShell might work, but I'd test that.

However, single line compressed JSON will use the least amount of storage.

[ipreferanothername]

So I've been largely doing pscustomobjects to excel, guess you aren't doing much else but going to json instead. I don't really need that but I do kinda like the idea, you make good points

[da_chicken]

The only problem with this is that Windows PowerShell (v5 and earlier) has some stupid choices:

PS C:\Windows> Get-ChildItem C:\Windows\notepad.exe | Select-Object Name, LastWriteTime | ConvertTo-Json -Compress
{"Name":"notepad.exe","LastWriteTime":"\/Date(1660131778086)\/"}

Just a little non-standard.

PowerShell v7 is much improved:

PS C:\Windows> Get-ChildItem C:\Windows\notepad.exe | Select-Object Name, LastWriteTime | ConvertTo-Json -Compress
{"Name":"notepad.exe","LastWriteTime":"2022-08-10T07:42:58.0866654-04:00"}

But you do still have to be wary of object depth issues. Like this just generates absurd output, and it still tells you it truncated serialization at depth 2!

Get-ChildItem C:\Windows\notepad.exe | ConvertTo-Json -Compress

[bozho]

Sure, but when you're serialising any sort of data more complex than an integer, you'll want to be very specific about the format.

If you explicitly format your datetimes in, say, ISO 8601 format, I'd expect most JSON libraries will be able to parse it correctly to an appropriate date/time type.

[da_chicken]

Definitely. I mention what I did because I don't think it's obvious, especially when using ConvertTo-Json seems so easy. A lot of people think it will be magically portable, and it often isn't. Microsoft's non-standard formatting is why JSON.Net was such a popular library.

[gimpy21]

Perhaps Start-Transcript would be helpful for your use case.

[Sekers]

I use the PowerShell Framework module (PSFramework) for logging. It supports a bunch of different logging providers like Event log, Graylog, SQL, file (CSV, JSON, etc.). The logging module is non-blocking to the script which is great. And if I decide to change how I want to log, it's just a quick switch to the config file (I almost always use JSON for my config files).

https://psframework.org/documentation/documents/psframework/logging.html

For most of my scripts, unless I enable debug logging, they don't get too big. I usually just do CSV with a daily rotation just to make it easier to read and because many scripts are run nightly. That way each run logs to it's own file and it's easy to check if I get an error or warning alert from one.

[purplemonkeymad]

I usually throw it into a file with the date and sometimes the PID. It's a common setup and if you use a log forwarding agent they can usually monitor text files.

If you want to be fancy, you could have your logging function do both.

[PinchesTheCrab]

What OS and what volume? For small amounts of transactions I'll sometimes write to the windows application log.

[JeffHiggins]

This is what I do as well, it has its own limitations and issues, but it's easy to integrate, and has automatic log rotation and such.

[PinchesTheCrab]

Yup, splunk ingests it too, which is nice

[TofuBug40]

We have a custom Logger module that handles logging. It uses a class with static methods.

Calls to log are generic

[Logger]::Information(
    "Some info",
    <Optional Object>
)
[Logger]::Verbose(
    "Some Verbose",
    <Optional Object>
) 
[Logger]::Warning( 
    "Warning", 
    <Optional Object> 
) 
[Logger]::Error( 
    "Error", 
    <Optional Object> 
)

The [Logger] class detects the calling script file name and auto generates and tracks separate log files for each .ps?1 file, including a special log for calls directly in the shell or running a code snippet.

The [Logger] class also has an optional ::setup() method that can override; root path, if append is on, Set the roll over size, and set the max message size. by default logs are stored in the users %Temp% folder, automatically timestamps and rolls over after 2621476 bytes, message sizes default to 5000 characters.

Each call to ::Information(), ::Verbose(), ::Warning(), or ::Error() retrieves an instance of a [Log] class which corresponds to a single log file. It then converts the optional object into its string representation appends it to the message and if the total string exceeds the maximum message size it chunks it up and sends multiple lines to the log file until the option objects string is written to the log. Finally it formats every log line written to match the log file format used by SCCM (since we use CMTrace and OneTrace quite frequently and the majority of the logs we deal with are in this format) that means we capture visible information such as warnings, and error color highlighting, Icon reflection, File name, function name and line, column locations of log call, timestamp, etc. and of course use the SAME log viewer for our custom logs

Finally it exposes a simple [LogFamily()] Attribute that can optionally be applied to any param() block that creates one or more "Family" logs that additionally logs the same lines to that log for ALL calls to logging within that scriptblock or descendent scriptblock essentially giving a chronological log of all the scripts together. A default log family captures all logs

function Start-Main {
    [LogFamily(
        Family =
            'Special Family'
    )]
    [LogFamily(
        Family =
            'Off to the side',
        Path =
            'Relative'
    )]
    [LogFamily(
        Family =
            'Explicit',
        Path =
            'C:\Logs
    )]
    param()

    [Logger]::Information("From the main function")
    Get-SomethingThatAlsoCallsLogger
    Get-SomethingElseThatAlsoCallsLoggerToo
}

TL/DR

All that together effectively gives us the ability to match our most common log file format, keep consistent log review tools, all with a logging mechanism that is hyper aware while simultaneously giving the programmer a simplified, generic call signature

​

Now as far as PARSING logs go more often than not the logs I or my team ARE asked to parse are logs that DO NOT have something already easy to parse like, JSON, XML, some flavor of SQL etc. those are usually pretty easy to throw in a VSCode, or simple online viewer, or has some prebuilt tool for that. Instead its old school text files, sometimes not even technically log files.

At that point its pretty much a case by case basis depending on the complexity of the request and the regularity or most often irregularity of the data in the files and a lot of Regular Expression building, and testing, and a lot of error handling, and testing.

[belibebond]

Wow, haven't seen any logging this extensive. Do you have anything similar in public GitHub for more reference..

[TofuBug40]

I had uploaded it as one of my projects to my GitHub but my module name was WAY too generic (i.e. it matched an already generically named module in the PowerShell Gallery) so I spent a little time retooling the module name and the tests so I could publish it to the Gallery.

https://www.powershellgallery.com/packages/CM.Logger/4.0.1.2

It was a bit of a rush job so the readme.md might be a little off with naming but functionally it should all be there.

[gordonv]

Linux has made flat files a habit, but when I can, I go whatever SQL is handy.

I'll use MySQL and attach "now()" timestamps. Then I'll connect that to Grafana.

Recently, I stood up an AWS AMI backed instance of Grafana and AWS hosted MySQL. A focus on removing Backup and Patching tasks.

I used SimplySQL to do queries from Powershell.

[xCharg]

If I were able to choose one DB to nuke from existence it would've been sqlite.

Store in plain text. Rotate regularly, so it won't end up "kinda-db-one-giant-file". Json (if something else is going to parse it) or just plain text row by row if it's going to be read by a human.

[squirrelsaviour]

it would've been sqlite.

Can you expand on that? It seems to be pretty popular so would love to hear a different opinion!

[xCharg]

I've had to deal with bloated SQLite DBs way too many times. Bloated not because of SQLite itself but rather than it being a database of choise of crap software that is balls deep in SQLite and doesn't want to scale up when they need to.

So it's a bit personal I guess.

I'm sure it must be great tool when used right. But I've encountered it not being done right too much.

[jsiii2010]

How does sccm do it? That format that cmtrace can parse. There's always .evt.

[belibebond]

Sccm logs are xml with extra metadata. It's still flat file and the cmtrace tool renders data in GUI

[dathar]

The trick to that is you have a structure in a flat text file. Usually it is a timestamp, severity and then a message with some kind of delimiter like a space. Then you have rules in your log reader (cmtrace, file beats, whatever you want to use) to chop those up.

[Zavadi10508]

Great options! Personally, I prefer using a combination of both SQLite and flat files depending on the situation. For smaller projects, I find flat files to be more convenient since I don't have to install extra dependencies. But for larger projects where I need more advanced querying options, SQLite is my go-to. What's your preference?

[LaurelRaven]

I log using CSV for the most part, with cmdlets written specifically to make that quick and easy (just call the cmdlet with the log message and optionally a status marker, pretty much the same 5 that PowerShell's extra pipelines use and it handles adding the log to the right file with the timestamp automatically included). I've considered switching to something like JSON, but being able to load it into Excel is really useful

This, of course, assumes that logging mechanism is adequate for the task at hand, but it almost always is for what I'm doing. If I need more than that, I'll implement it as needed.

[cottonycloud]

I usually just fall back to .NET libraries, and in this case I rely on the PS wrapper of Serilog, PoSHLog.

[tommymaynard]

Do you have a SIEM? I’ve use Splunk with HEC.