Let's Document How to Use Read-DbaXEFile Without a UNC Share

[ReeceGoding]

Summarize Functionality

Currently, the only documentation for Read-DbaXEFile that does not require knowing the path to the .xel file is Get-DbaXESession -SqlInstance sql2019 -Session deadlocks | Read-DbaXEFile. I've never seen this work, presumably because the documentation strongly suggests that it only works if you write your .xel file to a file share and I've never seen anyone do that.

I propose that we document how to use Read-DbaXEFile without knowing the file path and without needing a UNC share. The only trick that I know for this is by putting many extra steps in the pipeline. For example,

Get-DbaXESession -SqlInstance sql2019 -Session deadlocks |
Get-DbaXESessionTarget |
Get-DbaXESessionTargetFile |
Read-DbaXEFile

works consistently for me, but requires some permissions that I have yet to fully understand.

Is there a command that is similiar or close to what you are looking for?

Yes

Technical Details

As above. The only thing to add is that it helps to stop the session to force a rollover.

[ReeceGoding]

but requires some permissions that I have yet to fully understand.

I suspect that the part of the documentation that says "Note: this performs a Get-ChildItem on remote servers if the specified target SQL Server is remote." is key. It amazed me that I can get Get-DbaXESessionTargetFile to work on production servers but not in a docker container that is entirely under my control. Even if you're sa, the following will never work no matter where you add -SqlCredential.

$sa = Get-Credential -UserName 'sa'
$ins = Connect-DbaInstance -SqlInstance 'YourDockerContainerIP' -SqlCredential $sa -TrustServerCertificate

Get-DbaXESession -SqlInstance $ins -Session 'system_health' |
Get-DbaXESessionTarget | 
Get-DbaXESessionTargetFile

On a Windows box, the trick is to open your terminal as a user that has good permissions on both the SQL instance and its host.

Either I'm missing something or Get-DbaXESessionTargetFile needs a Credential parameter as well as a SqlCredential parameter, like Find-DbaInstance.

[niphlod]

Get-DbaXESessionTargetFile.ps1 is quite clear. All it does is a Get-ChildItem, so it'll work "out of the box" just on windows boxes. Get-DbaXESessionTarget.ps1 too, it just does an Join-AdminUnc to build the "remotefile" from the "file" attribute, which prepares the access via an admin share (i.e. \computername\disk$\folder\to\path\file.xel from disk:\folder\to\path\file.xel on computername). Again, it only works on Windows boxes, as there's no clear way to map both linux mounts or container volumes via an "universally valid" ... pointer? link? uri?

So, what's the missing point on dbatools's docs or examples ?

[ReeceGoding]

So, what's the missing point on dbatools's docs or examples ?

Three things:

1. Nothing in the dbatools docs shows how to go from Get-DbaXESession to Read-DbaXEFile if you don't know the path to the .xel file ahead of time. I would argue that this is a very common use case. People often only know the name of the session. What we currently have only works if you write your .xel files to a UNC share, which I've never seen in practice.
2. I don't recall anything that points out the Windows-only restriction that you have mentioned.
3. There is nothing that spells out what permissions Get-DbaXESessionTargetFile requires. For example, it took me a long time to figure out that I needed more than just a correct -SqlCredential. This could be a very easy change to make. We would just need to bolt "so you will need to be running PowerShell as an account with read access to the target files" on to the bit that already mentioned Get-ChildItem.

[niphlod]

okay, so

1. Get-DbaXESession -SqlInstance someinstance | Select Name, TargetFile, RemoteTargetFile and Get-DbaXESessionTarget -SqlInstance someinstance -Verbose | Select Session, File, RemoteTargetFile as examples are good ?
2. Using Get-DbaXESessionTargetFile without proper permission gives you back something like " Failure | Access to the path '\computername\C$\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Log' is denied." .... does it need to be more clear that it's trying to access an admin share you don't have access to, and with a "windows-style" share uri ?
3. See above. All it does is TRYING to read the xel file directly so, as it needs to reach the target files .

I guess we can reinstate in Get-DbaXESessionTargetFile that it works in windows boxes and when access to files listed by Get-DbaXeSession and Get-DbaXeSessionTarget are readable.

[ReeceGoding]

Yes. I think we entirely agree on all points. The examples and clarifications you have suggested are exactly what I had in mind, particularly around permissions. It's too easy for a novice to think that a correct -SqlCredential is all that you need.

The only thing that I'd think to add is that I like spelling out that the Get-DbaXESession -SqlInstance sql2019 -Session deadlocks | Get-DbaXESessionTarget | Get-DbaXESessionTargetFile | Read-DbaXEFile pipeline works. It's really handy to know how to go from a session to reading the file!

[ReeceGoding]

Just made a related Q&A. I wonder if I'm just not very good with these commands. https://github.com/dataplat/dbatools/discussions/9706