Get data from wmic/winmgmt/etc
-
I need to get data from wmic/winmgmt in SQL Server query.
Is there any way get tabular representation of that data?
For example, some analog of these commands in Powershell:
# data about processes
Get-WmiObject Win32_Process
# or this data
Get-CimInstance Win32_Process
# or this data
wmic process- This topic was modified 1 year, 2 months ago by us26.
- This topic was modified 1 year, 2 months ago by us26.
- This topic was modified 1 year, 2 months ago by us26.
- This topic was modified 1 year, 2 months ago by us26.
- This topic was modified 1 year, 2 months ago by us26.
- This topic was modified 1 year, 2 months ago by us26.
- This topic was modified 1 year, 2 months ago by us26.
-
If you know how to use xp_CmdShell properly and safely, here's how I do such things. Since you're not using SQL Server 2022, the STRING_SPLIT() function isn't guaranteed by MS to return things in proper ordinal order of the split elements. Instead, you can use the DelimitedSplit8K function found at the following link. It returns the enumerated ordinal for the line position of each split out element.
Here's the code I use to do similar things. You can adapt it to suit your needs for whatever CMD you need.
--===== Drop the temp tables just to make reruns in SSMS easier.
-- Comment this line out for "production"
DROP TABLE IF EXISTS #CmdResult, #ProcessList
;
--===== Create the table to receive the CSV output from xp_CmdShell.
CREATE TABLE #CmdResult
(
Line# INT IDENTITY(0,1) PRIMARY KEY CLUSTERED
,CmdResult VARCHAR(500) --Stores the original command result
,Cleaned AS (REPLACE(REPLACE(CmdResult,CHAR(13),''),CHAR(10),'')) PERSISTED --Removes Cr/Lf
)
;
--===== Execute the WMIC command through xp_CmdShell and store the results for splitting.
INSERT INTO #CmdResult WITH (TABLOCK)
(CmdResult)
EXEC xp_CmdShell 'wmic process list brief /format:CSV'
;
--===== In the following, uncomment the "Node" attribute if you want to see the machine name.
-- I just didn't want to publish mine.
SELECT
--Node = CONVERT(VARCHAR(100),MAX(IIF(split.ItemNumber = 1,split.Item,''))),
HandleCount = CONVERT(INT ,MAX(IIF(split.ItemNumber = 2,split.Item,0))),
Name = CONVERT(VARCHAR(100),MAX(IIF(split.ItemNumber = 3,split.Item,''))),
Priority = CONVERT(INT ,MAX(IIF(split.ItemNumber = 4,split.Item,0))),
ProcessId = CONVERT(INT ,MAX(IIF(split.ItemNumber = 5,split.Item,0))),
ThreadCount = CONVERT(INT ,MAX(IIF(split.ItemNumber = 6,split.Item,0))),
WorkingSetSize = CONVERT(BIGINT ,MAX(IIF(split.ItemNumber = 7,split.Item,0)))
INTO #ProcessList
FROM #CmdResult
CROSS APPLY dbo.DelimitedSplit8k(Cleaned,',') split
WHERE Line# >= 3
AND CmdResult > ''
GROUP BY Line#
;
--===== Display the final results.
SELECT * FROM #ProcessList
;And, here are the results...
If you need it, I have a PowerPoint presentation for how to use xp_CmdShell safely, why it's NOT the security risk everyone claims it is, and how to set it up for safe use (not need if only your trusted DBAs are going to use it).
Now, duck... here comes all the naysayers on that particular subject. 😉
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Here is another way.
If you do something like this in PoSh, to create a delimited text file, it is then very easy to import the results in tabular form.
Get-CimInstance Win32_Process | Export-Csv -Path .\CimInstance.csv -Delimiter ','
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin - Phil Parkin wrote:
Here is another way.
If you do something like this in PoSh, to create a delimited text file, it is then very easy to import the results in tabular form.
Get-CimInstance Win32_Process | Export-Csv -Path .\CimInstance.csv -Delimiter ','
True dat'.
In the early days, I used to tick people off by asking how you'd schedule something like that and be able get logging, etc, etc. Of course, they referred to the Windows Task scheduler and had to do a couple of extra things to get the logs into SQL Server for morning jobs reports, etc, etc. And, then, to add further injury (especially those that touted "Centralized Backups" as the latest "too cool for school" use (without ever considering what happens to all the servers if that system failed), I showed them how to create the PoSh command in T-SQL using xp_CmdShell so that the report of a downed system or server could be reported on by SQL Server so that failures could be reported instead of such reporting being on the system that failed. 😀
Along with the release of the undocumented but incredibly useful sys.dm_os_enumerate_filesystem function, it's amazing how easy it is to do some pretty wonderful things in SQL.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks for your solution!
Looks awesome.
The only one disadvantage is necessity to give names to columns manually.
Is there any way to do it automatically?
Preferably without dynamic-sql.
Jeff Moden wrote:If you need it, I have a PowerPoint presentation for how to use xp_CmdShell safely, why it's NOT the security risk everyone claims it is, and how to set it up for safe use (not need if only your trusted DBAs are going to use it).
Yes, I need it. I would much appreciate such information.
I avoided using it just because of the security risks.
- us26 wrote:
Thanks for your solution! Looks awesome. The only one disadvantage is necessity to give names to columns manually. Is there any way to do it automatically? Preferably without dynamic-sql.
Jeff Moden wrote:If you need it, I have a PowerPoint presentation for how to use xp_CmdShell safely, why it's NOT the security risk everyone claims it is, and how to set it up for safe use (not need if only your trusted DBAs are going to use it).
Yes, I need it. I would much appreciate such information. I avoided using it just because of the security risks.
See the attached. It's almost a decade old but still relevant today. If you don't have PowerPoint, let me know and I'll send it to you as a clickable slide presentation.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply