# Integration with Analysis Tools

> Getting the converted CSV into Excel, Power BI, SQL Server, a SIEM, or a Python notebook — including the delimiter and date settings that make it work on the first attempt.

---

LLMS index: [llms.txt](/llms.txt)

---

The point of converting a DNS debug log is what happens next. CSV was chosen because everything reads it — but "everything reads CSV" hides two settings that decide whether an import is painless or an afternoon of fiddling.

## Two settings that decide everything

**Delimiter.** Default is `;`. Keep it for Excel in locales that use the comma as decimal separator. Switch to `,` for most databases and data-science tooling. Whatever you choose, tell the importing side the same thing.

**Date format.** `-OutputCulture` controls how timestamps are written. For anything read by a machine, use `sv-SE` (or invariant culture) so `DateTime` comes out as `2026-01-20 23:00:16`:

```powershell
Convert-DNSDebugLogFile -InputFile "C:\Logs\dns.log" -OutputCulture 'sv-SE'
```

Every consumer below understands that format without a hint. Locale-specific output such as `20.01.2026` will import as text or, worse, get misread as month/day.

## Microsoft Excel

The quickest way to look at a single converted log.

- With the default `;` delimiter and a locale that expects it, a double-click opens the file with proper columns.
- If everything lands in column A, the delimiter does not match your Excel setting. Either re-run with `-Delimiter ","` or import via **Data → From Text/CSV** and pick the delimiter there.
- Turn the range into a table (`Ctrl+T`) and build a PivotTable on `ClientIP`, `QuestionName` or `QuestionType`. Top-talkers and top-domains take about a minute.
- For anything over a few hundred thousand rows, use Power Query or one of the other options below instead.

The `*_Statistic.csv` and `*_PacketStatistic.csv` files are usually the better Excel targets — they are pre-aggregated and stay small. See [Output Formats](../02-output-formats/).

## Power BI

Import the CSV files and build dashboards for DNS activity, top clients, query trends, and error rates.

- Point Power Query at the *folder* holding your converted files rather than a single file. The layout is identical across servers and days, so files simply append.
- The `ComputerName` column is what makes a multi-server report possible — set `-ComputerName` during conversion or you will not be able to slice by server.
- `ResponseCode` and `QuestionType` are natural slicers; `DateTime` becomes your time axis.
- Feeding Power BI with `*_PacketStatistic.csv` instead of the full data file keeps the model small when you only need daily trends.

## SQL databases

Bulk-load the CSV into SQL Server, PostgreSQL, or anything else that speaks SQL, for long-term retention and repeatable queries.

A ready-to-run walkthrough — table definition, conversion, `SqlBulkCopy` import, and a detection query for suspicious `TXT` activity — is documented in [Security Analysis Workflow with SQL Server](../examples/security-analysis-sql/).

Points worth planning before the first load:

- Use `-OutputCulture 'sv-SE'` so timestamps land in a `datetime2` column without conversion tricks.
- `Information` and `Details` can be long; give them `nvarchar(max)`.
- Index what you actually query — typically `DateTime`, `ClientIP` and `QuestionName`.
- Keep `-ComputerName` populated so rows stay attributable after they are merged.

## SIEM systems

Splunk, Elastic, Sentinel, and similar platforms ingest the CSV for correlation with other security telemetry and for alerting.

- Ship the compressed output (`-CompressOutput`) — it is roughly a tenth of the size and most collectors unpack it themselves.
- Define the field mapping once; the column layout never changes between runs or servers, including the always-present trailing `ComputerName` column.
- Make your collector deduplicate, or archive processed `.log` files. A conversion job that reprocesses the same rotated logs will otherwise re-send identical rows.
- `-ContextFilter Packet` keeps ingest volume (and licence cost) down when server notes are not part of your use cases.

## Python, R and data science tooling

The structured output is ready for anomaly detection, baselining, and custom analytics.

```python
import pandas as pd

df = pd.read_csv(
    r"C:\Administration\Logs\DNSServer\dns.csv",
    sep=";",
    parse_dates=["DateTime"],
)

# top 20 queried names, queries only
top = (
    df[df["Direction"] == "Rcv"]
    .groupby("QuestionName")
    .size()
    .sort_values(ascending=False)
    .head(20)
)
print(top)
```

`parse_dates` works out of the box when you exported with `-OutputCulture 'sv-SE'`. From here, pandas, scikit-learn, or R handle clustering, seasonality and outlier detection as usual.

## Staying in PowerShell

You do not have to leave PowerShell for a quick answer:

```powershell
$dns = Import-Csv "C:\Administration\Logs\DNSServer\dns.csv" -Delimiter ';'

# noisiest clients
$dns | Where-Object Direction -eq 'Rcv' |
    Group-Object ClientIP |
    Sort-Object Count -Descending |
    Select-Object -First 10 Count, Name

# failed lookups
$dns | Where-Object ResponseCode -eq 'NXDOMAIN' |
    Group-Object QuestionName |
    Sort-Object Count -Descending |
    Select-Object -First 10 Count, Name
```

`Import-Csv` reads the whole file into memory, so this is fine for a rotated chunk and unsuitable for a multi-gigabyte export — that is what the database and SIEM routes are for.