Getting Data In

How can I get my inputlookup to ignore the time within the timestamp?

tkwaller_2
Communicator

Here's What I have to fix but haven't yet figred out how.
In this search

 index=dev_tsv "BO Type"="assessments"  
  |dedup "Workflow Name" "Workflow Phase" "Workflow Process Due Date" "Workflow Process Name" "Workflow Process Sort Order" "Workflow Started Date" "Workflow Step Name" "Step Due Date" "Workflow Step Sort Order"
  | table "Workflow Name" "Workflow Phase" "Workflow Process Due Date" "Workflow Process Name" "Workflow Process Sort Order" "Workflow Started Date" "Workflow Step Name" "Step Due Date"  "Workflow Step Sort Order" 
  | convert timeformat="%Y-%m-%d %H:%M:%S.%6N"  mktime("Step Due Date") AS cumulativeDueDate 
  | eval dueDateRange=mvrange(cumulativeDueDate,now(),86400)
  | mvexpand dueDateRange
  | convert ctime(dueDateRange) timeformat="%+" 
  | where  NOT match(dueDateRange,"(Sun|Sat).*") AND  NOT [ | inputlookup exclude_holidays  | eval holiday_date=strptime(holiday_date, "%Y-%m-%d %H:%M:%S") | rename holiday_date as dueDateRange | eval dueDateRange=strftime(dueDateRange, "%+") ]

The initial value of dueDateRange here is formatted like
"%Y-%m-%d %H:%M:%S.%6N"
after input lookup it is
"%Y-%m-%d %H:%M:%S"
but the "%H:%M:%S" portion is always
"00:00:00" in the input lookup values, so I don't think the inputlookup portion is working to exclude as the fields in the actual data have valid "%H:%M:%S"
for example:
"2018-07-04 07:51:25.966000"

this is the value of input lookup file:

holiday_date
 2018-01-01 00:00:00
 2018-01-15 00:00:00
 2018-02-19 00:00:00
 2018-05-28 00:00:00
 2018-07-04 00:00:00
 2018-09-03 00:00:00
 2018-10-08 00:00:00

Any thoughts on how I can get this to work by not using the time inside the inputlookup value timestamp? So it would use %Y-%m-%d?

0 Karma
1 Solution

DalJeanis
Legend

instead of

    | eval holiday_date=strptime(holiday_date, "%Y-%m-%d %H:%M:%S")

use

    | eval holiday_date=relative_time(strptime(holiday_date, "%Y-%m-%d %H:%M:%S"),"@d")

View solution in original post

DalJeanis
Legend

instead of

    | eval holiday_date=strptime(holiday_date, "%Y-%m-%d %H:%M:%S")

use

    | eval holiday_date=relative_time(strptime(holiday_date, "%Y-%m-%d %H:%M:%S"),"@d")

tkwaller_2
Communicator

Hello @DalJeanis my updated search like:

index=dev_tsv "BO Type"="assessments"  
|dedup "Workflow Name" "Workflow Phase" "Workflow Process Due Date" "Workflow Process Name" "Workflow Process Sort Order" "Workflow Started Date" "Workflow Step Name" "Step Due Date" "Workflow Step Sort Order"
 | table "Workflow Name" "Workflow Phase" "Workflow Process Due Date" "Workflow Process Name" "Workflow Process Sort Order" "Workflow Started Date" "Workflow Step Name" "Step Due Date"  "Workflow Step Sort Order" 
 | convert timeformat="%Y-%m-%d %H:%M:%S.%6N"  mktime("Step Due Date") AS cumulativeDueDate 
 | eval dueDateRange=mvrange(cumulativeDueDate,now(),86400)
 | mvexpand dueDateRange
 | convert ctime(dueDateRange) timeformat="%+" 
 | where  NOT match(dueDateRange,"(Sun|Sat).*") AND  NOT [ | inputlookup exclude_holidays  | eval holiday_date=relative_time(strptime(holiday_date, "%Y-%m-%d %H:%M:%S"),"@d") | rename holiday_date as dueDateRange | eval dueDateRange=strftime(dueDateRange, "%+") ]

but it still returns the same results, I still get records with dueDateRange = Wed Jul 4 07:35:35 GMT 2018 when it should be excluded since holiday_date = Wed Jul 4 00:00:00 GMT 2018

I just cant get it to ignore the time within the timestamp

0 Karma

DalJeanis
Legend

You need to do the relative_time(datefield,"@d") on both sides of the lookup.

Also, there's a basic issue you might run into with "it's the Fourth of July holiday WHERE"?

The records are stored in UTC, (which we'll pretend for the moment is London's time zone.) If a user is viewing the search in, say, San Francisco, then the interface may choose to determine what date/time the record occurred in San Francisco, or might not, depending on how the search and the system are set up.

So, if converting the other dat field doesn't do the trick, then before you do any more the conversion and testing in the above search, you need to determine precisely how the data is stored and precisely how the system is interpreting relative_time( sometimefield,"@d").

0 Karma
Get Updates on the Splunk Community!

App Building 101 - Build Your First App!

WATCH RECORDING NOW   Tech Talk: App Dev Edition Splunk has tons of out-of-the-box functionality, and you’ve ...

Introducing support for Amazon Data Firehose in Splunk Edge Processor

We’re excited to announce a powerful update to Splunk Data Management with added support for Amazon Data ...

The Observability Round-Up: September 2024

What’s up Splunk Community! Welcome to the latest edition of the Observability Round-Up, a monthly series in ...