Need help specifying log path for MSSQL log files

grant2 · May 19, 2023, 1:30am

I am running Grafana Agent (static mode) successfully to send metrics of a MSSQL database running on Windows Server 2022. I got there by following this tutorial.

my data:

You can see from the above that the one thing that has not worked is the logs, which cannot apparently be found.

Here is the portion of my .yaml file that specifies the path where the log files are:

            labels:
              __path__: 'D:\SqlServerData\MSSQL16.MSSQL2022STD\MSSQL\Log'

But using the above gives this error in the Windows Event Viewer:
level=info component=logs logs_config=integrations msg="failed to tail file" error="file is a directory" filename=D:\SqlServerData\MSSQL16.MSSQL2022STD\MSSQL\Log

I changed the slashes in the above as follows:

            labels:
              __path__: 'D:/SqlServerData/MSSQL16.MSSQL2022STD/MSSQL/Log'

but the same exact error message appears in Windows Event Viewer.

Here is a screenshot of my log file in the designated location and an excerpt of my yaml file.

What’s the secret to specifying the location? Assuming I get it working, will I be able to see all of the log files shown above? Some are .txt files simply stating the backup was successful, while others are SQL system health, SQL errors, etc. I’d like to see everything in my cloud Loki.

yosiasz · May 19, 2023, 3:19am

Is the D drive an actual disk on the server or some remote share/nas virtual disk? Or drive letter mapping to a remote file share
For test purposes move a couple of those logs to a simple path such as
D:/lokilogs/ and see what happens
__path__: D:/lokilogs/* i think is the way to go
Folder name length
If you open cmd line on the server and type dir /x. What do you see for SqlServerData as short folder name Then cd into SqlServerData and again do dir /x what do you see as short folder name for the parenr folder for log

D:\SqlServerData\MSSQL16.MSSQL2022STD\MSSQL\Log

grant2 · May 19, 2023, 3:28pm

Thanks for the tips @yosiasz

D drive is an actual disk.
Creating the new simple path of D:/lokilogs and placing some log files into that new folder worked! (but don’t open the champagne just yet…keep reading)
__path__: D:/lokilogs/* does indeed work
In cmd line, I typed fsutil 8dot3name query and it says:
The registry state is: 2 (Per volume setting - the default)
and the dir /x indicates to me that I do not have short folder names working

There are 3 remaining issues:

It’s puzzling to me that the Grafana Agent accepts short paths like D:/lokilogs but will not accept D:/SqlServerData/MSSQL16.MSSQL2022STD/MSSQL/Log Is this indeed intended behavior?
The SQL log is encoded as UTF-16LE acc. to Edit Pad, which it seems acc. to this thread is still an unresolved issue. Per this, I put an encoding line into my yaml file and it resulted in Chinese characters

    scrape_configs:
      - job_name: integrations/mssql-windows
        encoding: utf-16le
        static_configs:
          - targets:
              - localhost
            labels:
              __path__: 'D:/lokilogs/*'

The dates/times in the raw logs use the time at which the log file was last updated (2023-05-19 07:48:05) for every log entry, even though each of those log entries has a different day/time. Is this preventable / correctable, and if so, how?

image1281×369 70.4 KB

yosiasz · May 19, 2023, 4:50pm

As best practice we put data in D folder logs in L folder on all our sql servers for decades. Any chance of such a change

grant2 · May 19, 2023, 5:13pm

OK, some small progress to report on issue #1. Changing the path to this:

            labels:
              __path__: 'D:/SqlServerData/MSSQL16.MSSQL2022STD/MSSQL/Log/*'

works to display the ERRORLOG file, but the other 2 issues are still unresolved.

yosiasz · May 19, 2023, 5:51pm

I think this was merged only to a certain branch?

github.com/grafana/loki

Comment by grafanabot to Promtail: Add text encoding conversion to file targets

grafana:main ← DylanGuedes:promtail-text-encoding

The backport to `release-2.6.x` failed: ``` The process '/usr/bin/git' failed wi…th exit code 1 ``` To backport manually, run these commands in your terminal: ```bash # Fetch latest updates from GitHub git fetch # Create a new branch git switch --create backport-6395-to-release-2.6.x origin/release-2.6.x # Cherry-pick the merged commit of this pull request and resolve the conflicts git cherry-pick -x f17d3d768cb915b656af20a2ef7973efae335d51 # Push it to GitHub git push --set-upstream origin backport-6395-to-release-2.6.x git switch main # Remove the local backport branch git branch -D backport-6395-to-release-2.6.x ``` Then, create a pull request where the `base` branch is `release-2.6.x` and the `compare`/`head` branch is `backport-6395-to-release-2.6.x`.

If it was up to me, I would use that branch

Maybe @tonyswumac could shed a light on the encoding issue?

tonyswumac · May 19, 2023, 6:07pm

I haven’t personally run into this before since I haven’t had to deal with utf-16le encoding. May be a good idea to try with a newer version of promtail. I can also test it too, if @grant2 can share a sample log file.

yosiasz · May 19, 2023, 6:23pm

in notepad++

2023-05-10 08:36:55.98 Server      Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) 
	Oct  8 2022 05:58:25 
	Copyright (C) 2022 Microsoft Corporation
	Developer Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 19045: ) (Hypervisor)

2023-05-10 08:36:55.99 Server      UTC adjustment: -7:00
2023-05-10 08:36:55.99 Server      (c) Microsoft Corporation.
2023-05-10 08:36:55.99 Server      All rights reserved.
2023-05-10 08:36:55.99 Server      Server process ID is 7276.
2023-05-10 08:36:55.99 Server      System Manufacturer: 'Dell Inc.', System Model: 'Latitude 7490'.
2023-05-10 08:36:55.99 Server      Authentication mode is MIXED.
2023-05-10 08:36:55.99 Server      Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Log\ERRORLOG'.
2023-05-10 08:36:55.99 Server      The service account is 'NT Service\MSSQLSERVER'. This is an informational message; no user action is required.
2023-05-10 08:36:55.99 Server      Registry startup parameters: 
	 -d C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\master.mdf
	 -e C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Log\ERRORLOG
	 -l C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
2023-05-10 08:36:55.99 Server      Command Line Startup Parameters:
	 -s "MSSQLSERVER"

grant2 · May 19, 2023, 6:40pm

Hi @yosiasz

In Notepad++, I see this:

while in Edit Pad, I see this:

Regardless, Loki does not like the format.

@tonyswumac I am using Grafana Agent version v0.33.2 (static mode) which I believe is the latest and greatest. My understanding is that Grafana Agent combines Prometheus for metrics, Loki for logs, and Grafana Tempo for traces. But maybe Promtail by itself would avoid this whole encoding issue? I would be grateful if you could run my error log through your Promtail setup. I will send to you via direct message.

yosiasz · May 19, 2023, 7:18pm

same difference. bom is just defining file’s “endianness”

under the hood what language does loki use for parsing files? any idea

grant2 · May 19, 2023, 7:21pm

Acc. to bard.google.com

Loki uses Go’s regular expression library to parse log files. Regular expressions are a powerful tool for matching patterns in text. Loki uses regular expressions to extract the timestamp, source, and message from each log line.

tonyswumac · May 19, 2023, 11:05pm

Ran your ERROR log through our cluster and did not see any issue.

OS: Amazon Linux 2
Promtail version: 2.8.2
Loki version: 2.8.0

Here is the configuration I used ( note that I did not have to specify encoding, and I added a block to parse the timestamp:

---
server:
  disable: true
positions:
  filename: /tmp/positions.yml
clients:
- url: <LOKI_URL>
  basic_auth:
    username: <USERNAME>
    password: <PASSWORD>
  external_labels:
    app: tonytest
scrape_configs:
  - job_name: tonytest
    static_configs:
    - targets:
        - localhost
      labels:
        __path__: /tmp/test/ERRORLOG
        job: tonytest
    pipeline_stages:
      - regex:
          expression: '^(?P<timestamp>\S* \S*)'
      - timestamp:
          source: timestamp
          format: '2006-01-02 15:04:05.00'

I did not manipulate the file at all. It was downloaded and unzipped directly on the agent host. File output from server:

[root@node0 test]# file ./ERRORLOG
./ERRORLOG: Little-endian UTF-16 Unicode text, with very long lines, with CRLF line terminators

Screenshot:

tonyswumac · May 19, 2023, 11:18pm

Actually now that I look at it again the timestamp didn’t quite work, but should be something close to that.

grant2 · May 19, 2023, 11:33pm

Thank you Tony. I owe you a coffee or a beer!

It seems Promtail does not have any encoding issues while Grafana Agent does.

tonyswumac · May 19, 2023, 11:57pm

I may have spoken too soon. I think the encoding does indeed mess up the parsing, and it’s why my timestamp configuration did not work. I am testing sending with encoding and see if it works or not.

tonyswumac · May 20, 2023, 12:09am

Looks like the following configuration works:

---
server:
  disable: true
positions:
  filename: /tmp/positions.yml
clients:
- url: <URL>
  basic_auth:
    username: <USERNAME>
    password: <PASSWORD>
  external_labels:
    app: tonytest3
scrape_configs:
  - job_name: tonytest1
    encoding: utf-16
    static_configs:
    - targets:
        - localhost
      labels:
        __path__: /tmp/test/ERRORLOG
        job: tonytest
    pipeline_stages:
      - regex:
          expression: '^(?P<timestamp>\S* \S*)'
      - timestamp:
          source: timestamp
          format: '2006-01-02 15:04:05.00'

Screenshot:

The line break is still a weird character, but at least the time stamp is good now and the logs are parsable.

yosiasz · May 20, 2023, 11:44am

@grant2 are you going to file a bug about this?

grant2 · May 20, 2023, 11:58am

Yes, will do next week. Will link here (and on the other thread re: UTF encoding).

Thanks again for your help in figuring out the path and thanks to @tonyswumac for taking time to test my log through Promtail.

erobichaux · May 20, 2023, 3:32pm

I am really looking forward to a fix for the UTF-16 encoding for the MSSQL logs. We have been ingesting them for 6 months and there are pretty much useless as is.

grant2 · May 24, 2023, 3:13pm

Issue raised in Github by Grafana staff member on 22-May:

github.com/grafana/agent

loki.source.file: allow tailing non-UTF8 encoded files

opened 08:05AM - 22 May 23 UTC

tpaschalis

enhancement type/signals flow/feature-parity

### Request We should change `loki.source.file` to allow the files encoding to …be set to something other than UTF-8. I'm not sure if this should be a target-specific option that can be defined via a label or a component-level option, although I'm leaning towards the latter. ### Use case This is something that is supported on Promtail and should be brought over for feature-parity. Some applications output their logs in non-UTF8 encodings by default, and changing that might be challenging for end-users in restricted environments.

Topic		Replies	Views
MSSQL servers logs extracted to loki Grafana dashboard with space in between character's Grafana Loki mssql	15	2397	May 23, 2024
Grafana Agent Windows logs help Grafana Loki loki , windows , grafana	8	2317	December 20, 2023
Windows Logs Push Grafana Loki	9	1577	May 16, 2024
Issue with Grafana agent-config.yml for Windows VM Grafana Loki loki	4	967	April 10, 2024
How to filter sql server log Grafana Loki loki	2	572	October 24, 2023

Need help specifying log path for MSSQL log files

Related topics