April 21, 2026 at 12:00 am
Hi, I'm currently trying to implement policy based mgmt with a condition to query event viewer using 'ExecuteWql'. However, WMI uses a different time format, so in addition to my WQL query, I'm nesting concatenating DATEPART() segments to build WMI-time for my WHERE clause. I'm aware of the differences with the GETDATE() syntax ('Day' vs DAY), but the whole advanced conditions thing seems to be a bit of a black box.
My real issue is that the query's becoming quite complex and I don't really have a means of outputting any kind of decent error information. All I can do is evaluate the policy and rely on the engine telling me the query is invalid.
Can anyone think of a means of outputting/logging more valuable information somewhere, so that I can troubleshoot the query properly, please? Even just a way to sanity-check my own code, where the syntax/parsing would be the same? I'm really lost...
P.S. I'm new to the forum, so please let me know if my etiquette's off at all.
April 21, 2026 at 7:06 pm
My opinion on this would be to design this a bit differently. Let SQL handle the SQL stuff and use WQL to handle WQL stuff. IF the WQL needs to be in side the SQL, then handle your WQL outside of SQL and get "sample data" to use when building your SQL statement.
Then build your SQL in SSMS to "evaluate the policy" and see the output you are getting.
Now , just to add a little thing to this - I've never heard of "custom PBM conditions" so I tried googling it and got no good results with respect to SQL server, so not sure if it is a typo in your acronym, a specialized tool, or if my "google-fu" is lacking. But my general approach to things like this would be:
1- determine what output the application expects (true/false, 0/1, table, string, integer, etc.)
2- determine what is internal source (TSQL) and what is an external source (WQL)
3- I generally start with external sources as their input can be unpredictable, so work with external source to get the results you want in the format you want and copy a sample output (this to speed up testing)
4- at this point your external source is in a "known good" state, so work on internal source with the sample data (faster than querying WQL repeatedly with each test run)
5- once your internal source (TSQL) is known good, combine the two and do a test in underlying tool (PBM)
6- if it works, expand scope (if neccessary, I suspect since you are doing WQL you are wanting to run it against multiple machines in which case you are likely to find some flaws with the WQL such as region differences where dates may come in dd/mm/yy format or mm/dd/yy format or even mm/dd/yyyy format)
7- if errors occur expanding scope, go back to step 3
8- no errors, you are good to go
As for "testing" and "evaluating" it, it really depends on the tool you are using. I've not heard of or used PBM so it may be that it is a simple pass/fail on parsing, or it may have an error log you can review. But I like taking my approach as it allows for minimal scope and puts you into "known good" states. If you get to step 4, your WQL is known good for that one machine (maybe not all, but known good for 1 machine). If you get to step 5, you know your WQL and TSQL is in a known good state. BUT if you fail to do step 1 and your TSQL and WQL are giving back dates in date format (kind of like strings) and PBM expects excel style dates (integers), you are going to get failures.
One nice thing with my approach is that it works with MOST SQL development. If you know the expected output, data sources (internal and external), you can start at the easiest point (WQL in this case as WMIC is a pretty open standard and google/AI can be a huge help getting your syntax correct), then move onto the next challenging point (in this case I'd argue it's TSQL) and repeat until you are ready to combine things and pass it off to the application as long as the input and output match the spec, you should be in a good state.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
April 22, 2026 at 1:41 am
Hi Brian,
That's an awesome answer, thank you! Really appreciate you sharing the methodology you'd use to approach this. It gives me a lot to go on with 🙂
Sorry, PBM was referring to SQL Server Policy Based Management. The 'policies' will evaluate 'conditions', dependent on the state of their underlying 'facets'. The 'Advanced Edit' condition editor in SSMS allows you to write 'custom condition' strings and there's not much comprehensive information online regarding its unique syntax or writing complex strings which explain the kind of thing I'm trying to achieve. I'll take your advice, trying a broken-down approach and see how I go.
Thanks again, I'm going to give it another crack with all of that in mind.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply