SQL Alerts and WMI

Question

Post reply

SQL Alerts and WMI

shindle 17293

Ten Centuries

Points: 1374
More actions
September 16, 2013 at 1:22 am

#282534

I'm trying to create an alert disk % free space left using:
SELECT * from __InstanceModificationEvent
WITHIN 30
WHERE
TargetInstance ISA 'Win32_LogicalDisk'
AND
--this line of code
((TargetInstance.freespace\TargetInstance.size) *100) < 10
--
AND TargetInstance.DeviceID = 'Y:'
but this line of code above seems to be an issue?
I've used the .freespace property without an issue but the calculation seems to throw an error.
Has anyone done this successfully?

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

lkennedy76 SSChampion Points: 13683 More actions · Answer 1

Can you paste the error?

MCSE SQL Server 2012\2014\2016

shindle 17293 Ten Centuries Points: 1374 More actions · Answer 2

The error is:

SQLServerAgent Error: WMI Error: 0x80041058

The @wmi_query could not be executed in the @wmi_namespace provided. Varify that an event class selected in the query exists in the namespace and that the query has the correct syntax.

This is the exact statement:

SELECT * from __InstanceModificationEvent

WITHIN 30

WHERE

TargetInstance ISA 'Win32_LogicalDisk'

AND

((TargetInstance.freespace/TargetInstance.freespace.size) * 100) < 15

AND

TargetInstance.DeviceID = 'Y:'

shindle 17293 Ten Centuries Points: 1374 More actions · Answer 3

Apologies - typo - the exact statement is:

SELECT * from __InstanceModificationEvent

WITHIN 30

WHERE

TargetInstance ISA 'Win32_LogicalDisk'

AND

((TargetInstance.freespace/TargetInstance.size) * 100) < 10

AND

TargetInstance.DeviceID = 'Y:'

jasona.work SSC Guru Points: 50117 More actions · Answer 4

I'm using the following WMI to trigger alerts based on disk free space (the size is set per disk):

select * from __instancemodificationevent within 1 where targetinstance isa 'CIM_LogicalDisk' and targetinstance.freespace < 53687091200 and targetinstance.name = 'D:'

Based on the error you mentioned, you might need to change your namespace from 'Win32_LogicalDisk' to 'CIM_LogicalDisk'

Give it a try!

Jason

shindle 17293 Ten Centuries Points: 1374 More actions · Answer 5

Unfortunately I get the same error:

I think this is the line it doesn't like:

((TargetInstance.freespace/TargetInstance.size) * 100) < 10

but you can use a similar syntax in SSIS / WMI Connections?

lkennedy76 SSChampion Points: 13683 More actions · Answer 6

The problem is the syntax at TargetInstance. I am looking how to fix it for you.

MCSE SQL Server 2012\2014\2016

michael bourgon SSC Eights! Points: 823 More actions · Answer 7

Anybody ever figure this out? I'm stuck where I have the alert firing, but I can't find the right token for WMI to expose the DeviceID from TargetInstance. I know it's in targetinstance, but can't figure out what the right token replacement would be. wmi(targetinstance) doesn't work, nor does wmi(deviceID) or the like. Thanks.

lkennedy76 SSChampion Points: 13683 More actions · Answer 8

Hey Michael,

Are you just trying to get an alert going for disk space?

MCSE SQL Server 2012\2014\2016

michael bourgon SSC Eights! Points: 823 More actions · Answer 9

Not really - I could just use xp_fixeddrives for that. What I'm trying to do is see what else I can do with WMI/WQL alerting. I've already dug through the EventNotifications aspect, but some of the stuff you can do via WQL alerting (CPU is one example) is interesting to me. I've got 3/4 of a SQLSat presentation, but want a "whoa" demo. My initial thought was a drive space alert, but I'll probably look at CPU, since that's harder to get from within SQL Server. But now my interest is piqued, and if I can get info from TargetInstance, I have far more options on what I can do with it.

Thanks,

Mbourgon

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 10

Wouldn't a simple WMI windows alert be easier and more fruitful since that's what they were designed for?

http://www.opennms.org/wiki/Enable_monitoring_of_CPU_utilization_on_Windows_systems

--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)

michael bourgon SSC Eights! Points: 823 More actions · Answer 11

michael bourgon

SSC Eights!

Points: 823

August 4, 2015 at 7:31 pm

#1817372

Spoilsport. :-b

lkennedy76 SSChampion Points: 13683 More actions · Answer 12

This is what I found, which I am sure you have read...

https://msdn.microsoft.com/en-us/library/aa394584(v=vs.85).aspx

and Jeff is right... 😉

MCSE SQL Server 2012\2014\2016