Hi,
I am a newbie to splunk and would like to know how to solve the following problem.
I have a SharePoint dump which consists of certain date columns and I need to find out the number of days between them. I export this dump into an excel file, convert it into CSV and then import it into Splunk. The problem I am facing is that when I use strptime/strftime on any one of the date columns and try to display that particular column using table command, I am not getting any results. So I am not able to move ahead and calculate the difference.
| inputlookup ontap4.csv | search "Complete
Target"!=TBD "Editorial Start Target"!="N/A" | eval time=strptime("Editorial Start Target","%m/%d/%Y") | table time
This is the input i give and get back no results found.
Anyone know how to solve this??
Thank you
Hi
Have you a lookup table in etc/system/lookups or etc/apps/*/lookups? Instead of a inputlookup command try one of these searches after adding your data into splunk. Setting>Data input>...
Source= ontap4.csv
| rename "Editorial Start Target" as editorial_start_target
|dedup editorial_start_target
| convert timeformat="%m/%d/%Y" mktime(editorial_start_target) as time
|table editorial_start_target time
or with eval command
Source= ontap4.csv
| rename "Editorial Start Target" as editorial_start_target
|dedup editorial_start_target
| eval epoch1=strptime(editorial_start_target,"%m/%d/%Y")// the exact format of the field editorial_start_target
|table editorial_start_target time
If you have to use spaces in field names you can use single quotes on the right hand side of an eval
statement:
| stats count | eval Editorial Start Target = now() | eval time = strftime('Editorial Start Target', "%+")
Note that this also applies to fields with period separators as one finds using the _json sourcetype.
Thanks for the tip about using single quotes on the right hand side of an eval statement when a field name has a space in it - I had the same issue and this fixed it for me.
Splunk tends to replace spaces in field names, but only if the field name was extracted automatically by Splunk. If you did setup any field extraction, Splunk will use the field name you did provide for the field extraction.
You can prepend this:
... | rename "Editorial Start Target" as Editorial_Start_Target | ...
Disregarding the time formatting, and the table, have you checked the basics. Are you getting search results at all?
Yes.I am getting the general search results. For instance:
| inputlookup ontap4.csv | search "Complete
Target"!=TBD"Editorial Start Target"!="N/A" | table "Editorial Start Target"
returns the correct answer.
I have extracted fields using the Headers of a CSV and I cannot simply replace whitespace with underscore...I am forced to respect the whitespace and use quotes in my searches...are you certain your advice is accurate? (i tried it hoping it was true...sadly no dice....)
just a hint, field names containing a space can be search as Complete_Target
or Editorial_Start_Target
as well. This will make it easier to create searches without quotes around fieldnames.
posting one line of you csv file would help