topic Re: How to combine two different time source fields into _time in Splunk Search

How to combine two different time source fields into _time

kaungset — Fri, 10 Jan 2020 06:27:31 GMT

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;

Re: How to combine two different time source fields into _time

jarizeloyola — Wed, 30 Sep 2020 03:38:05 GMT

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

Re: How to combine two different time source fields into _time

to4kawa — Fri, 10 Jan 2020 12:44:50 GMT

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.

Re: How to combine two different time source fields into _time

kaungset — Tue, 14 Jan 2020 04:06:23 GMT

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

Re: How to combine two different time source fields into _time

to4kawa — Tue, 14 Jan 2020 11:24:53 GMT

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

Re: How to combine two different time source fields into _time

kaungset — Thu, 16 Jan 2020 02:22:32 GMT

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

Re: How to combine two different time source fields into _time

to4kawa — Thu, 16 Jan 2020 03:56:06 GMT

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