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-th...
Thank you for your time helping me.
|inputlookup yourcsv |untable user_id week hours | eval _time=strptime(week,"%m/%d/%Y") |table _time user_id hours | collect index=yours
|inputlookup yourcsv |untable user_id week hours | eval _time=strptime(week,"%m/%d/%Y") |table _time user_id hours | collect index=yours
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.
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 |
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.