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

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

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

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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...