topic Re: CSV multipul time events in header in Getting Data In

CSV multipul time events in header

kphillipson — Tue, 01 Sep 2020 02:29:27 GMT

I have a CSV file where the header contains the time of each subset of data. I need Splunk to split the columns into different event times, to be referenced as _time.

user_ID	6/24/2019	6/17/2019	6/10/2019
3	40.34	40.5	44.53
4	36.99	38.64	42.86
5	0	0	0

For instance user_ID 3 has logged in for 40.34 hours for week 6/24/2019, 40.5 hours for week 6/17/2019 etc...

The only thing that comes to mind is creating separate csv files for each week, but I believe there is a better way. I have search but nothing has lined up with what I'm running into. The closest was this one but didn't help. https://community.splunk.com/t5/All-Apps-and-Add-ons/How-can-I-use-the-time-column-name-of-CSV-as-the-index/td-p/456189

Thank you for your time helping me.

Re: CSV multipul time events in header

Nisha18789 — Tue, 01 Sep 2020 07:21:46 GMT

Hi @kphillipson , is it possible to update the csv to contain data like below?

week	user_id	hours
6/24/2019	3	40.34
6/24/2019	4	36.99
6/17/2019	3	40.5
6/17/2019	4	38.64

Re: CSV multipul time events in header

kphillipson — Tue, 01 Sep 2020 17:28:47 GMT

Hello@Nisha18789 ,

Unfortunately I can't export it that way. That would be a lot of entries to hand jam but I see where you are going with this. I think I'll have to try my hand at a python script to change it. I can easily flip the column A with row 1 using paste special > transpose. From there maybe python can group the users to the hours, as you have in your example.

Re: CSV multipul time events in header

to4kawa — Tue, 01 Sep 2020 20:34:04 GMT

Re: CSV multipul time events in header

kphillipson — Wed, 02 Sep 2020 00:44:26 GMT

Thank you for your experience. A kind friend was able to generate a python script to reorder the csv for me. Loading the csv as a lookup and having Splunk generate the desired output works too! Hope this helps someone with the same issue having time referenced in the row.