How to combine two different time source fields into _time

[kaungset]

Dears;

how can I combine Date/Time of two different source as follow;

CSV-01(pic-1) and CSV-02(pic-2) input in splunk
Query from splunk search and output as Splunk Query Output (pic-3)

pls kindly help the way;

[to4kawa · Accepted solution]

UPDATED:

index="CSV-01" OR index="CSV-02"
| eval Date=coalesce(Date, mvindex(split('Start Time'," "),0))
| eval Date=strptime(Date,"%d/%m/%Y")
| fieldformat Date=strftime(Date,"%d/%m/%Y")
| table Date City "Traffic(GB)" 
| rename "Traffic(GB)" as Traffic
| sort Traffic

---

Previous Answer:

| inputlookup csv_01
| append [ | inputlookup csv_02
| eval Date = mvindex(split('Start Time'," "),0)
| table Date City "Traffic(GB)" ]
| eval _time = strptime(Date,"%d/%m/%Y")
| rename "Traffic(GB)" as Traffic
| table _time City Traffic

Hi, @kaungset
How about this?

P.S. It is better not to use a field name with spaces (ex. Start Time)in CSV.
if my query doesn't work, maybe field name problem occurs.

[kaungset]

hi @to4kawa
Thank You!

when i try first SPL command line

1.| eval Date=coalesce(Date, mvindex(split('Start Time'," "),0)) was work

but 2nd & 3rd doesn't work well.

I try like this first line;
1. index="main"
| eval d=coalesce(Date,mvindex(split('Start Time'," "),0))
| starts count by d

but when I put 2nd & 3rd line it's show us No results found;
1. index="main"
| eval d=coalesce(Date,mvindex(split('Start Time'," "),0))
| eval Date=strptime(d,"%d/%m/%Y")
| fieldformat Date=strftime(Date,"%d/%m/%Y")
| starts count by Date

[to4kawa]

before stats, if Date is, the problem is stats.
if Date isn't, the problem is strptime.
but, I think no need strptime

[kaungset]

Hi,
Thank You for your reply!

Both source CSV format file 01 & 02 was already input to splunk and indexed.
It was indexed as 3 time values as follow;

_time(only system default), Date(1/30/2020), Start Time(1/30/2020 12:00:00 AM)

I want to combine values of Date & Start Time as only Date field

---

Date City Traffic
1/30/2020 A 102039

[to4kawa]

@kaungset
I see. my answer is updated. please confirm.

[jarizeloyola]

index = blah sourcetype = blah source IN ("*CSV-01","*CSV-02") |stats count by _time City "Traffic(GB)"|rename "Traffic(GB)" as Traffic| fields - count