Splunk Search

match dates between an appencol and inputlookup

Communicator

I have a search from an input looup and i have appended search results from an index so i can overlay some results but the dates are not matching up.

| inputlookup user | where stat1=1 OR stat2=1 AND tonumber(strftime(_time,"%Y")) > 2019
| eval Date = strftime(_time,"%d") 
| timechart span=1d max(Date) as days, count as Registrations 
| eval DayRate = round(Registrations /days, 1) 
| fields - days
| appendcols  [search index="index1" | stats values(Confirmed) by Date]

This shows me results as per the below where the overlay red line should match the dates of the rest of the graph

alt text

As you can see the dates are not aligning as per the below

alt text

if you can advise on how i can correlate the dates to the same row.

I tried append and join with no luck

Tags (3)
0 Karma
1 Solution

Ultra Champion
| appendcols  [search index="index1" | stats values(Confirmed) by Date]

| append [search index="index1" | stats values(Confirmed) as Confirmed by Date | eval _time= strptime(Date."+0000", "%FT%TZ%z") | fields - Date]
| stats values(*) as * by _time

View solution in original post

0 Karma

Ultra Champion
| appendcols  [search index="index1" | stats values(Confirmed) by Date]

| append [search index="index1" | stats values(Confirmed) as Confirmed by Date | eval _time= strptime(Date."+0000", "%FT%TZ%z") | fields - Date]
| stats values(*) as * by _time

View solution in original post

0 Karma

Communicator

@to4kawa

I cant seem to convert your comment to an answer. Can you post as an answer again and ill accept

0 Karma

Ultra Champion

I see,
It was nice to run.

0 Karma

Communicator

Sorry, my mistake as i still had appendcols, your answer is correct and will convert

0 Karma

Communicator

Thanks but this still hasnt worked and date still stops at 12th april.

With my _time field in index=index1 this is data imported via HEC so each field has the same _time and i can only go by the Date field

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!