Getting Data In

CSV multipul time events in header

kphillipson
Path Finder

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_ID6/24/20196/17/20196/10/2019
340.3440.544.53
436.9938.6442.86
5000

 

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-th...

Thank you for your time helping me.

Labels (3)
0 Karma
1 Solution

to4kawa
Ultra Champion

|inputlookup yourcsv |untable user_id week hours | eval _time=strptime(week,"%m/%d/%Y") |table _time user_id hours | collect index=yours

View solution in original post

to4kawa
Ultra Champion

|inputlookup yourcsv |untable user_id week hours | eval _time=strptime(week,"%m/%d/%Y") |table _time user_id hours | collect index=yours

View solution in original post

kphillipson
Path Finder

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.

0 Karma

Nisha18789
Builder

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

 

week user_idhours
6/24/2019340.34
6/24/2019436.99
6/17/2019340.5
6/17/2019438.64
Tags (1)

kphillipson
Path Finder

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. 

Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!