Doing NSLookup on SQL Query Results
-
I have to scan a bunch of servers for failed logins. As part of this process, I want to get the incoming host names. However, the SQL log file only has IP addresses. I have two pieces of code. The first one is like below. It gets me the Server name, all errors I'm looking for and I break out the login name in question and the client IP in two extra columns so they can be filtered on later. I want to do a nslookup on the IP address and still have it associated with the login name / server / IP Address. Or at the very least, the IP address and server name.
$query = "Create table #TempLogCheck (LogDate DATETIME, ProcessInfo VARCHAR(50), ErrorMessage VARCHAR(2000))
INSERT INTO #TempLogCheck (LogDate, ProcessInfo, ErrorMessage)
EXEC sp_readerrorlog 0, 1, 'Login failed' -- current
INSERT INTO #TempLogCheck (LogDate, ProcessInfo, ErrorMessage)
EXEC sp_readerrorlog 1, 1, 'Login failed' -- .1 (previous)
INSERT INTO #TempLogCheck (LogDate, ProcessInfo, ErrorMessage)
EXEC sp_readerrorlog 2, 1, 'Login failed' -- .2 (the one before that)
INSERT INTO #TempLogCheck (LogDate, ProcessInfo, ErrorMessage)
EXEC sp_readerrorlog 3, 1, 'Login failed' -- .2 (the one before that)
INSERT INTO #TempLogCheck (LogDate, ProcessInfo, ErrorMessage)
EXEC sp_readerrorlog 4, 1, 'Login failed' -- .2 (the one before that)
INSERT INTO #TempLogCheck (LogDate, ProcessInfo, ErrorMessage)
EXEC sp_readerrorlog 5, 1, 'Login failed' -- .2 (the one before that)
INSERT INTO #TempLogCheck (LogDate, ProcessInfo, ErrorMessage)
EXEC sp_readerrorlog 6, 1, 'Login failed'; -- .2 (the one before that)
SELECT DISTINCT @@ServerName AS ServerName, LogDate, ErrorMessage,
SUBSTRING(ErrorMessage, /*First argument*/ CHARINDEX('''', ErrorMessage) + 1, /*Second argument*/
CHARINDEX('''', ErrorMessage, CHARINDEX('''', ErrorMessage) + 1) - 1 - CHARINDEX('''', ErrorMessage) /*Third argument*/) AS LoginName,
REPLACE(REPLACE(SUBSTRING(ErrorMessage, /*First argument*/ CHARINDEX('[CLIENT: ', ErrorMessage) + 1, /*Second argument*/
CHARINDEX('[CLIENT: ', ErrorMessage, 18) ), 'CLIENT: ',''),']','') AS HostName,
'nslookup ' + REPLACE(REPLACE(SUBSTRING(ErrorMessage, /*First argument*/ CHARINDEX('[CLIENT: ', ErrorMessage) + 1, /*Second argument*/
CHARINDEX('[CLIENT: ', ErrorMessage, 18) ), 'CLIENT: ',''),']','') AS CmdPromptSearchCommand
FROM #TempLogCheck
WHERE LogDate >= '04/01/2024'
AND (ErrorMessage LIKE '%Could not find a login matching the name provided%' OR ErrorMessage LIKE '%The account is disabled%'
OR ErrorMessage LIKE '%An error occurred while evaluating the password%')
ORDER BY @@ServerName DESC;"
#Path to the excel file to be saved.
$csvFilePath = "c:\Temp\FailedSQLLoginSearch_AllSvrs.csv"
# Run Query against multiple servers, combine results
# Replace "Server1", "Server2\INSTANCE1" with names of your SQL Server instances
$instanceNameList = get-content c:\Temp\Scripts\serverlist.txt
$results=@()
foreach($instanceName in $instanceNameList)
{
write-host "Executing query against server: " $instanceName
$results += Invoke-Sqlcmd -Query $query -ServerInstance $instanceName
}
# Output to CSV
write-host "Saving Query Results in CSV format..."
$results | export-csv $csvFilePath -NoTypeInformationThe second piece of code I got off Stack Overflow which is nice, but only produces the actual machine name in a single list. I can't figure out how to combine both pieces of code to get one file. That's okay. I could live with 2 files if I could get the server name and login name in the output of the nslookup on the same lines as the host name.
$location = "C:\TEMP\FailedSQLLoginSearch_AllSvrs.xlsx"
$Names = Import-Csv -Path $location
foreach($n in $Names)
{
try {
$Computer = [system.net.dns]::resolve($n.NAME) | Select HostName
$IP = ($Computer.AddressList).IPAddressToString
New-Object PSObject -Property @{IPAddress=$IP; Name=$Computer.HostName} | Export-Csv C:\TEMP\ClientWorking.csv -NoTypeInformation -Append
} catch {
Write-Host "$($n.NAME) is unreachable."
Write-Output $n | Export-Csv C:\TEMP\Unreachable.csv -Append -Encoding ASCII
}
}My Google-Fu fails me as all I seem to be getting is general commands for Resolve-DNSName and the one thing I could find for an export only exports the result of the IP address, not the associated IP address and server name.
Does anyone have any suggestions on either how to get the second to also output the additional columns from the original file? Or merge the two sets of code together so I have an additional column with the host name?
xp_cmdshell is disabled, so I can't run a command from the T-SQL query portion of the first script to grab that information.
-
try the following.
assuming that your csv file (you have it as xlsx which I believe may be a typo) contains 3 columns - SQLServerName, IPAddress and LoginName
foreach($n in $Names)
{
try {
[system.net.dns]::resolve($n.NAME)|Select-Object @{N='Name';E={$_.HostName}},@{N='IP';E={($_.AddressList).IPAddressToString}},@{N='LoginName';E={$n.LoginName}},,@{N='SQLServerName';E={$n.SQLServerName}}| Export-Csv C:\TEMP\ClientWorking.csv -NoTypeInformation -Append
} catch {
Write-Host "$($n.NAME) is unreachable."
Write-Output $n | Export-Csv C:\TEMP\Unreachable.csv -Append -Encoding ASCII
}
} -
Yes, I was messing around and forgot to change xlxs to csv.
Oddly, I'm not getting a file created with your posted suggestion. I'm going to noodle around a little more and see if I can get a result.
-
code had a "," too many.
below works
$location = "C:\TEMP\FailedSQLLoginSearch_AllSvrs.csv"
$Names = Import-Csv -Path $location
foreach($n in $Names)
{
try {
[system.net.dns]::resolve($n.NAME)|Select-Object @{N='Name';E={$_.HostName}},@{N='IP';E={($_.AddressList).IPAddressToString}},@{N='LoginName';E={$n.LoginName}},@{N='SQLServerName';E={$n.SQLServerName}}| Export-Csv C:\TEMP\ClientWorking.csv -NoTypeInformation -Append
} catch {
Write-Host "$($n.NAME) is unreachable."
Write-Output $n | Export-Csv C:\TEMP\Unreachable.csv -Append -Encoding ASCII
}
} - Brandie Tarvin wrote:
I have to scan a bunch of servers for failed logins. As part of this process, I want to get the incoming host names. However, the SQL log file only has IP addresses. I have two pieces of code. The first one is like below. It gets me the Server name, all errors I'm looking for and I break out the login name in question and the client IP in two extra columns so they can be filtered on later. I want to do a nslookup on the IP address and still have it associated with the login name / server / IP Address. Or at the very least, the IP address and server name.
$query = "Create table #TempLogCheck (LogDate DATETIME, ProcessInfo VARCHAR(50), ErrorMessage VARCHAR(2000))
INSERT INTO #TempLogCheck (LogDate, ProcessInfo, ErrorMessage)
EXEC sp_readerrorlog 0, 1, 'Login failed' -- current
INSERT INTO #TempLogCheck (LogDate, ProcessInfo, ErrorMessage)
EXEC sp_readerrorlog 1, 1, 'Login failed' -- .1 (previous)
INSERT INTO #TempLogCheck (LogDate, ProcessInfo, ErrorMessage)
EXEC sp_readerrorlog 2, 1, 'Login failed' -- .2 (the one before that)
INSERT INTO #TempLogCheck (LogDate, ProcessInfo, ErrorMessage)
EXEC sp_readerrorlog 3, 1, 'Login failed' -- .2 (the one before that)
INSERT INTO #TempLogCheck (LogDate, ProcessInfo, ErrorMessage)
EXEC sp_readerrorlog 4, 1, 'Login failed' -- .2 (the one before that)
INSERT INTO #TempLogCheck (LogDate, ProcessInfo, ErrorMessage)
EXEC sp_readerrorlog 5, 1, 'Login failed' -- .2 (the one before that)
INSERT INTO #TempLogCheck (LogDate, ProcessInfo, ErrorMessage)
EXEC sp_readerrorlog 6, 1, 'Login failed'; -- .2 (the one before that)
SELECT DISTINCT @@ServerName AS ServerName, LogDate, ErrorMessage,
SUBSTRING(ErrorMessage, /*First argument*/ CHARINDEX('''', ErrorMessage) + 1, /*Second argument*/
CHARINDEX('''', ErrorMessage, CHARINDEX('''', ErrorMessage) + 1) - 1 - CHARINDEX('''', ErrorMessage) /*Third argument*/) AS LoginName,
REPLACE(REPLACE(SUBSTRING(ErrorMessage, /*First argument*/ CHARINDEX('[CLIENT: ', ErrorMessage) + 1, /*Second argument*/
CHARINDEX('[CLIENT: ', ErrorMessage, 18) ), 'CLIENT: ',''),']','') AS HostName,
'nslookup ' + REPLACE(REPLACE(SUBSTRING(ErrorMessage, /*First argument*/ CHARINDEX('[CLIENT: ', ErrorMessage) + 1, /*Second argument*/
CHARINDEX('[CLIENT: ', ErrorMessage, 18) ), 'CLIENT: ',''),']','') AS CmdPromptSearchCommand
FROM #TempLogCheck
WHERE LogDate >= '04/01/2024'
AND (ErrorMessage LIKE '%Could not find a login matching the name provided%' OR ErrorMessage LIKE '%The account is disabled%'
OR ErrorMessage LIKE '%An error occurred while evaluating the password%')
ORDER BY @@ServerName DESC;"
#Path to the excel file to be saved.
$csvFilePath = "c:\Temp\FailedSQLLoginSearch_AllSvrs.csv"
# Run Query against multiple servers, combine results
# Replace "Server1", "Server2\INSTANCE1" with names of your SQL Server instances
$instanceNameList = get-content c:\Temp\Scripts\serverlist.txt
$results=@()
foreach($instanceName in $instanceNameList)
{
write-host "Executing query against server: " $instanceName
$results += Invoke-Sqlcmd -Query $query -ServerInstance $instanceName
}
# Output to CSV
write-host "Saving Query Results in CSV format..."
$results | export-csv $csvFilePath -NoTypeInformationThe second piece of code I got off Stack Overflow which is nice, but only produces the actual machine name in a single list. I can't figure out how to combine both pieces of code to get one file. That's okay. I could live with 2 files if I could get the server name and login name in the output of the nslookup on the same lines as the host name.
$location = "C:\TEMP\FailedSQLLoginSearch_AllSvrs.xlsx"
$Names = Import-Csv -Path $location
foreach($n in $Names)
{
try {
$Computer = [system.net.dns]::resolve($n.NAME) | Select HostName
$IP = ($Computer.AddressList).IPAddressToString
New-Object PSObject -Property @{IPAddress=$IP; Name=$Computer.HostName} | Export-Csv C:\TEMP\ClientWorking.csv -NoTypeInformation -Append
} catch {
Write-Host "$($n.NAME) is unreachable."
Write-Output $n | Export-Csv C:\TEMP\Unreachable.csv -Append -Encoding ASCII
}
}My Google-Fu fails me as all I seem to be getting is general commands for Resolve-DNSName and the one thing I could find for an export only exports the result of the IP address, not the associated IP address and server name.
Does anyone have any suggestions on either how to get the second to also output the additional columns from the original file? Or merge the two sets of code together so I have an additional column with the host name?
xp_cmdshell is disabled, so I can't run a command from the T-SQL query portion of the first script to grab that information.
Instead of "polluting" the errorlog, maybe just configure eXtended Events and monitor those.
ref: "Logon monitoring in SQL Server and Azure Managed Instances - Adopting Extended Events"
Have a look at the information XE's can hold for you !!
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
At the moment, configuring extended events is not something I can do. We need to jump through hoops to make changes to production servers and this is a high priority issue. Plus I'm only supporting a small number of servers compared to the hundreds we have and if I make this kind of change to that subset, I have to make it to all of them.
I'm not sure why the code frederico posted isn't working for me, but even with the tweaks I'm not getting anything. And I finally realized that the code I previously had is only giving me my own machine name. DOH!
So I'm working some options, teaching myself more powershell int he process. I'm trying GetHostEntry, which is succeeded on some items and failing on others with "no such host known." That's a little weird because I would think the host would be known considering it logged into the db server in the past few days...
This is what I've got so far (ignoring LogDate and ErrorMessage). It's still not showing ServerName and LoginName, but it is returning some host names that aren't my own box.
$IPAddys = Import-Csv C:\TEMP\FailedSQLLoginSearch_AllSvrs.csv | Select Servername, LoginName, HostName
#$IPAddys.ServerName
Foreach ($ips in $IPAddys)
{ [system.net.dns]::Resolve($ips.HostName) }EDIT: I changed GetHostEntry to Resolve and now it's working better. Now my only issue is trying to get Servername and LoginName on the same line as the DNS resolution. If I try to add a Write-Output or Write-Host within the foreach loop, it just writes the IP address instead of the resolved host name. So I get ServerA LoginMe 10.55.55.55 (for example) instead of ServerA LoginMe AppServerB.
-
are you querying cross domain ? ( add the domain extention ! )
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
regarding adding the details to the same line I gave you the way to do it - using Expressions @{N='Name'; E=Expression} you can add anything you wish to the output.
another attempt - this time trapping the errors you are getting (I assumed none)
Note that "dns.resolve" is obsolete and should not be used.
$location = "C:\TEMP\FailedSQLLoginSearch_AllSvrs.csv"
$Names = Import-Csv -Path $location
#Define in-memory table to hold all records - this will be the one used to output to CSV after its populated
$Table = [System.Collections.ArrayList]@()
#define logical record to add all required details
$record = @{
"Name"= ""
"IP" = ""
"LoginName" = ""
"SQLServerName" = ""
}
foreach($n in $Names)
{
#inicialize $record in each loop
$record."IP" = $n.NAME # input file "NAME" is the IP Address -- better change it!!!
$record."LoginName" = $n.LoginName
$record."SQLServerName" = $n.SQLServerName # this is the SQL Server instance the error is from
$record."Name"= "" # this will contain resolved name from IP
try
{
##### $host = ([system.net.dns]::resolve($n.NAME)).Hostname # resolve is obsolete and should not be used
$validrecord."Name" = ([system.net.dns]::GetHostEntry($n.NAME)).HostName
}
catch
{
$validrecord."Name" = "IP Address could not be resolved"
Write-Host "$($n.NAME) is unreachable."
Write-Output $n | Export-Csv C:\TEMP\Unreachable.csv -Append -Encoding ASCII
}
$objRecord = New-Object PSObject -property $record
[void]$Table.Add($objRecord)
}
if ($Table) # only if it has rows
{
$Table|select SQLServerName,LoginName,Name,IP| Export-Csv C:\TEMP\ClientWorking.csv -NoTypeInformation -Append
} -
Frederico, the expressions line you gave me isn't working for me. The information writing to the screen is " is unreachable." as if $n.NAME is null or empty and the only file I get is the unreachable one.
Yet when I use other code or do nslookup manually, I get results.
As a note, your code first given also uses the resolve keyword. So it works when I'm not using the Select-Object command. But not when I am. So, I'm really really confused.
EDIT: It's not just the pipe to Select-Object. Even the pipe to Export-Csv is causing this issue.
-
try the latest code - and resolve was on your own original code and I kept it before. Gone on latest one.
it does work on my pc with both valid and invalid IP's - while I am outputting the invalid ones on the valid file (as I believe its better for further analysis) that can be changed easily
-
WHOO HOO! Figured it out. Needed to throw a hash table in there...
Thank you, Frederico. Without your code I never would have reached this. Now I'm just fiddling with user-friendly file names, which is nothing.
$IPAddys = Import-Csv C:\TEMP\FailedSQLLoginSearch_AllSvrs.csv | Select-Object -Property ServerName, LoginName, HostName -Unique
$results=@()
Foreach ($ips in $IPAddys)
{
try {
write-host "Checking " $ips.ServerName " with login " $ips.LoginName " against IP address " $ips.HostName
$results += [system.net.dns]::Resolve($ips.HostName) | Select-Object @{N='HostServer';E={$_.HostName}},@{N='IpAddy';E={$ips.HostName}},@{N='ServerName';E={$ips.ServerName}},@{N='LoginName';E={$ips.LoginName}}
}
catch {
Write-Host "$($ips.HostName) is unreachable."
Write-Output $ips | Export-Csv C:\TEMP\Unreachable.csv -Append -Encoding ASCII
}
}
$results | Export-Csv C:\TEMP\NslookupAttempt.csv -NoTypeInformation -
And now the non-obsolete version that appears to work as well (possibly better?).
$IPAddys = Import-Csv C:\TEMP\FailedSQLLoginSearch_AllSvrs.csv | Select-Object -Property ServerName, LoginName, HostName -Unique
$results=@()
Foreach ($ips in $IPAddys)
{
try {
write-host "Checking " $ips.ServerName " with login " $ips.LoginName " against IP address " $ips.HostName
$results += [system.net.dns]::GetHostEntry($ips.HostName) | Select-Object @{N='HostServer';E={$_.HostName}},@{N='IpAddy';E={$ips.HostName}},@{N='ServerName';E={$ips.ServerName}},@{N='LoginName';E={$ips.LoginName}}
}
catch {
Write-Host "$($ips.HostName) is unreachable."
Write-Output $ips | Export-Csv C:\TEMP\Unreachable.csv -Append -Encoding ASCII
}
}
$results | Export-Csv C:\TEMP\NslookupAttempt.csv -NoTypeInformation
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply