Need help in creating alert when new QID is published from qualys

[martin61]

I would like to create an alert when new QID from qualys is published.  For that I'm using FIRST_FOUND_DATETIME field and comparing it with today's date. The date format for that field result is in GMT. I want in PST. Also whenever the FIRST_FOUND_DATETIME  is current date it should trigger the alert or list QIDs associated to today's date. 

[yuanliu · Accepted solution]

I think you are asking about time conversion and comparison.  These are good reads: Date and time format variables, Date and Time functions, especially strftime(X,Y), and strptime(X,Y).

I am not sure in what format your are receiving FIRST_FOUND_DATETIME; in my reports, it comes in a really bad one like "11/22/21 2:05".  You'll first need to convert the string to numeric time with strptime(FIRST_FOUND_DATETIME, "%m/%d/%y %H:%M"). (Luckily, %H handles the horrible hour format that  I receive.   If your data format is different, you'll need to adjust the format string.)  Then, compare it to today's date, which can be accessed from time() function.   After this, add 8 hour to this numeric time. (I assume PST is for display only.  My Splunk server uses UTC, so date comparison won't need timezone shift.)

To put this together, something like this will work in my setup.

| eval first_found = strptime(FIRST_FOUND_DATETIME, "%y/%m/%y %H:%M")
| where now() - first_found < 86400
| eval first_found = strftime(first_found - 28800, "%Y-%m-%d %H:%M PST")
| table QID first_found

If your server uses PST internally, you can perform the -8h shift before comparison.