• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 769
  • Last Modified:

WQL DateTime

Hello,
   I'm working on implementing additional WMI monitoring configurations for use within our Monitoring system.  

  Ultimately, we can monitor anything that is compatible with WQL.  This has proven to be extensive, but I'm running into an issue where I want to know how old a file is.  

   The below WQL query has been constructed to list how many Windows Fax Queued jobs there are, but I'd like to add one more constraint.  The CIM_DataFile class offers CreationDate as part of it's dataset.

select * from CIM_DataFile where Path = '\\Documents and Settings\\All Users\\Application Data\\Microsoft\\Windows NT\\MSFax\\Queue\\' AND Extension = 'tif'

   Somehow, I'd like to add a clause to this to show me only files that were created more than 24 hours ago.  I only have access to WQL, I'm not going to be able to run VBS to process anything before reporting output back.

   Any ideas?
0
TransitionalData
Asked:
TransitionalData
  • 7
  • 4
1 Solution
 
RobSampsonCommented:
Hi, use this:
Select * from CIM_DataFile where Drive = 'C:' AND Path = '\\Documents and Settings\\All Users\\Application Data\\Microsoft\\Windows NT\\MSFax\\Queue\\' AND Extension = 'tif' AND CreationDate >= '20090417000000.000000+600'

Where the format is yyyymmhhhhmmss.000000 and the +600 is your time zone offset in minutes.

Regards,

Rob.
0
 
TransitionalDataAuthor Commented:
Hi Rob,
  Thanks for the prompt reply, how would the time stamp be generated automatically given the query will be handled by our automated monitoring system.

  Is there an analogous call to MSSQL's getdate() command that I failed to turn up?  Ultimately, I want to provide a time stamp that is 24 hours old to see if any of the files returned have a time stamp older than the reference.
0
 
RobSampsonCommented:
No, WQL doesn't have any intrinsic functions.  The best you could do is get the monitoring system to somehow generate the date, and then pass that into the query string.....not sure how that would work in your system.

Rob.
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
RobSampsonCommented:
Are there any environment variables or something that are accessible by the monitoring system, which you could roll over daily from an external script?
0
 
TransitionalDataAuthor Commented:
Thanks for the info Rob.  I couldn't locate any in the MS Technet or MSDN documentation, but then again I also didn't find any examples on how to use the datetime functions that you provided some info on.

I think I'm limited to using VBS where applicable since the WQL query gets embedded into a monitoring probe that can't be modified on the fly.
0
 
RobSampsonCommented:
No problem. If you do want to use VBS, this will work.

Regards,

Rob.
Const CONVERT_TO_LOCAL_TIME = True
Set strDateFrom = CreateObject("WbemScripting.SWbemDateTime")

strStartDate = DateAdd("d", -1, Now)
strComputer = "."
strDrive = "C:"
strPath = "\Documents and Settings\All Users\Application Data\Microsoft\Windows NT\MSFax\Queue\"
strExtension = "tif"

strDateFrom.SetVarDate CDate(strStartDate), CONVERT_TO_LOCAL_TIME
If Left(strPath, 1) <> "\" Then strPath = "\" & strPath
If Right(strPath, 1) <> "\" Then strPath = strPath & "\"
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
strQuery = "Select * from CIM_DataFile where Drive = '" & strDrive & "' AND Path = '" & Replace(strPath, "\", "\\") & "' AND Extension = '" & strExtension & "' AND CreationDate >= '" & strDateFrom & "'"
MsgBox strQuery
Set colFiles = objWMIService.ExecQuery(strQuery)
For Each objFile In colFiles
    Wscript.Echo objFile.Name & ": " & objFile.CreationDate
Next
MsgBox "Done"

Open in new window

0
 
RobSampsonCommented:
Oh, you can remove the two MsgBox statements if you want.

Rob.
0
 
TransitionalDataAuthor Commented:
This is what close to what we were doing.

The below is a VBS that was in the task scheduler to analyse the files in the given directory, then pushed the information we wanted into WMI.  The monitoring system then went and pulled the calculated WMI information.

This works in practice and in production, but depends on Scheduled tasks that use credentials that are subject to change, adds dependencies, requires additional configuration and is ultimately a step I was looking to remove.

' ******************************************************************************
' Script: TDS-FaxQueueCount.vbs
' Version: 1.0
' Author: Tony Baker
' Description: This script checks how many documents are queued in the MSFax Outbox
'			   by counting the number of files in the file system folder and publishes to the
'              FaxQueueCount WMI Class in the root\TDSNCentralMonitoring WMI namespace.
' Date: Jan. 22nd, 2011
' ******************************************************************************



' Version History

' 1.0 - Initial Release (2011-03-17)





' Define the  variables used in the script
Option Explicit
dim HKEY_LOCAL_MACHINE, strComputer, objReg, strTrendKeyPath, InputRegistryKey1, InputRegistryKey2
dim InputRegistryKey3, InstalledAV, NamespacePresense, objClassCreator, objGetClass, objWMIObject
dim RegstrValue, ReturneddwValue1, objWMIService, objItem, objNameSpace, ReturneddwValue2
dim wbemCimtypeString, wbemCimtypeUint32, colNamespaces, objNewNamespace
dim RawAVVersion, FormattedAVVersion, objNewInstance, strWMINamespace, ParentWMINamespace
dim ReturnedstrValue1, strValue, FormattedPatternAge, CalculatedPatternAge, CurrentDate
dim WMINamespace, strWMIClassWithQuotes, strWMIClassNoQuotes, colClasses, objClass 
dim strTestKeyPath, Registry, arrValueNames, strSymantecESKeyPath, WshShell
dim ReturnedBinaryArray1(7), bytevalue, i,SymantecAvPatternDate
dim wbemCimtypeBoolean, ReturneddwValue3, InputRegistryKey4
Dim fso, TotalCountQueuedFaxJobs, src, folder, file, filehandle

' Specify values for some of the variables
HKEY_LOCAL_MACHINE = &H80000002
strComputer = "."
wbemCimtypeString = 8
wbemCimtypeUint32 = 19
wbemCimtypeBoolean = 11
Const wbemFlagReturnImmediately = &h10
Const wbemFlagForwardOnly = &h20 
Set objReg=GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputer & "\root\default:StdRegProv")
strWMIClassWithQuotes = chr(34) & "FaxQueueCount24HoursOld" & chr(34)
strWMIClassNoQuotes = "FaxQueueCount24HoursOld"
strWMINamespace = "TDSNCentralMonitoring"
Set ParentWMINamespace = GetObject("winmgmts:\\" & strComputer & "\root")
Set WshShell = WScript.CreateObject("WScript.Shell")






'This is a meat of the script - where all of the functions are called. 

  CountQueuedFaxJobs
 
  'Check to see if an instance of the WMI namespace exists; if it does, 
  'check to see if the WMI class exists. If the class exists, delete it, recreate it, and populate it
  If WMINamespaceExists(ParentWMINamespace) Then
'      wscript.echo "The Namespace already exists."
      If WMIClassExists(strComputer,strWMIClassWithQuotes) Then
'          wscript.echo "The WMI Class exists"
          WMINamespace.Delete strWMIClassNoQuotes
          CreateWMIClass
          PopulateWMIClass
      Else
'          wscript.echo "The Namespace exists, but the WMI class does not. Curious." 
          CreateWMIClass
          PopulateWMIClass      
      End If
  Else
      'Create the WMI Namespace (if it doesn't already exist), the WMI Class, and populate the class with data.              
'      wscript.echo "The WMI Namespace and Class does not exist"
      CreateWMINamespace
      CreateWMIClass
      PopulateWMIClass 
  End If    
                
                

  

 
' *****************************  
' Function: CountQueuedFaxJobs
' 		Returns "TotalCountQueuedFaxJobs" for use in writing to WMI
'		Adapted from http://www.visualbasicscript.com/How-to-count-files-in-directory-m445.aspx
' *****************************
Sub CountQueuedFaxJobs
	'' Count the number TIF of files in C:\Documents and Settings\All Users\Application Data\Microsoft\Windows NT\MSFax\Queue 
		Set fso = CreateObject("Scripting.FileSystemObject") 
		src = "C:\Documents and Settings\All Users\Application Data\Microsoft\Windows NT\MSFax\Queue" 
		Set folder = fso.GetFolder(src) 
		TotalCountQueuedFaxJobs = 0 
		For Each file In folder.files 
			If LCase(fso.GetExtensionName(file)) = "tif" Then
				Set filehandle = fso.GetFile(file)
				If DateDiff("d", filehandle.DateLastModified, Date) > 1 Then
					TotalCountQueuedFaxJobs = TotalCountQueuedFaxJobs + 1
				End If
				
			End If 
		Next 
'		WScript.Echo "Count: " & TotalCountQueuedFaxJobs 

		

   

End Sub



' *****************************  
' Sub: CreateWMINamespace
' *****************************
Sub CreateWMINamespace
    Set objItem = ParentWMINamespace.Get("__Namespace")
    Set objNewNamespace = objItem.SpawnInstance_    
    objNewNamespace.Name = strWMINamespace
    objNewNamespace.Put_
End Sub



' *****************************  
' Sub: CreateWMIClass
' *****************************
Sub CreateWMIClass
    Set objWMIService = GetObject("Winmgmts:root\" & strWMINamespace)
    Set objClassCreator = objWMIService.Get() 'Load the Namespace           
    'Define the Properties of the WMI Class
    objClassCreator.Path_.Class = "" & strWMIClassNoQuotes
    
    objClassCreator.Properties_.add "Displayname", wbemCimtypeString
    objClassCreator.Properties_.add "QueuedFaxJobs", wbemCimtypeString
                
                
    ' Make the 'Displayname' property a 'key' (or index) property
    objClassCreator.Properties_("Displayname").Qualifiers_.add "key", true
                
    ' Write the new class to the 'root\TDSNCentralMonitoring' namespace in the repository
    objClassCreator.Put_

End Sub
    
    
' *****************************  
' Function: WMINamespaceExists
' Thanks to http://www.cruto.com/resources/vbscript/vbscript-examples/misc/wmi/List-All-WMI-Namespaces.asp for this code 
' *****************************
Function WMINamespaceExists(ParentWMINamespace)
                WMINamespaceExists = vbFalse
                Set colNamespaces = ParentWMINamespace.InstancesOf("__Namespace")
                For Each objNamespace In colNamespaces
                      If instr(objNamespace.Path_.Path,strWMINamespace) Then
                            WMINamespaceExists = vbTrue
                      End if
                Next
                Set colNamespaces = Nothing
End Function


  


' *****************************  
' Function: WMIClassExists
' Thanks to http://gallery.technet.microsoft.com/ScriptCenter/en-us/a1b23364-34cb-4b2c-9629-0770c1d22ff0 for this code 
' *****************************
Function WMIClassExists(strComputer, strWMIClassWithQuotes)
                WMIClassExists = vbFalse
                Set WMINamespace = GetObject("winmgmts:\\" & strComputer & "\root\" & strWMINamespace)
                Set colClasses = WMINamespace.SubclassesOf()
                For Each objClass In colClasses
                      If instr(objClass.Path_.Path,strWMIClassNoQuotes) Then
                            WMIClassExists = vbTrue
                      End if
                Next
                Set colClasses = Nothing
End Function  
  
     

    
' *****************************  
' Sub: PopulateWMIClass
' *****************************
Sub PopulateWMIClass    
    'Create an instance of the WMI class using SpawnInstance_
    Set WMINamespace = GetObject("winmgmts:\\" & strComputer & "\root\" & strWMINamespace)
    Set objGetClass = WMINamespace.Get(strWMIClassNoQuotes)
    Set objNewInstance = objGetClass.SpawnInstance_
    objNewInstance.QueuedFaxJobs = TotalCountQueuedFaxJobs
'    wscript.echo "Populating the WMI Class with the data." & TotalCountQueuedFaxJobs
    
    ' Write the instance into the WMI repository
    objNewInstance.Put_()
End Sub



' *****************************  
' Function: RegKeyExists
' Returns a value (true / false)
' Thanks to http://www.tek-tips.com/faqs.cfm?fid=5864 for this code
'************************************


Function RegKeyExists (RegistryKey)

    'If there isn't the key when we read it, it will return an error, so we need to resume
    On Error Resume Next

    'Try reading the key
    WshShell.RegRead(RegistryKey)

    'Catch the error
    Select Case Err
      'Error Code 0 = 'success'
      
      Case 0:
        RegKeyExists = true
      'Any other error code is a failure code
      
      Case Else:
        RegKeyExists = false
    End Select

    'Turn error reporting back on
    On Error Goto 0

End Function

Open in new window

0
 
RobSampsonCommented:
It doesn't sound like you'll be able to remove that mid-step.  WMI isn't capable of perfoming inline calculations, and unless your monitoring program can run script, I think you'll be stuck with it.  I do think though that it's an excellent idea to create a WMI class that your monitoring program can query!

Perhaps the only think that might make it slightly more streamlined would be to run the VBS as a service (using instsrv and srvany) and have it update the value on the hour.....I'm not sure what sort of overhead that would create though, since it would use WScript.Sleep alot.

Rob.
0
 
TransitionalDataAuthor Commented:
That's certainly a possibility.  

I also toyed with the idea of creating a separate domain user for the service purpose, but I think either way I slice it, it's going to have the overhead.

Trouble is that this configuration is not highly deployable...

Thanks for your help Rob, you completely earned the 250 :)
0
 
RobSampsonCommented:
No worries.  If it is worth your time though, you could wrap it in a script that would create the Scheduled Task, and the associated script for it to run, to have it all nice and neat.  Also, are you able to run your task as the local SYSTEM account?  If the script only needs local system access, then it should run as the SYSTEM account just fine, and remove any dependency on a specific account.

Rob.
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now