Splunk Search

Why is outputlookup not updating the csv file as intended?

esmonder
Path Finder

I have a lookup file in the form of test.csv
in the test.csv there are two columns with date fields(date_first and date_last), of which i want to convert date_last to a splunk recognizable date field and appendcols to the test.csv

my code:

| inputlookup append=true test.csv 
| appendcols 
    [| inputlookup test.csv 
    | eval n = strftime((strptime(date_last, "%Y-%m-%dT%H:%M:%S.000Z")), "%m/%d/%Y %H:%M:%S") 
    | eval _time = n ] 
| outputlookup append=true test.csv

results are as follows in the statistics view of the same search page:

date_first                date_last                   n
2017-06-02T15:13:10.000Z    2017-06-02T15:14:42.000Z    06/02/2017 15:14:42
2017-06-02T15:13:10.000Z    2017-06-02T15:14:42.000Z    06/02/2017 15:14:42
2017-06-02T15:13:10.000Z    2017-06-02T15:14:42.000Z    06/02/2017 15:14:42
2017-06-02T15:13:10.000Z    2017-06-02T15:14:42.000Z    06/02/2017 15:14:42
2017-06-02T15:13:10.000Z    2017-06-02T15:14:42.000Z    06/02/2017 15:14:42
2017-06-02T15:13:10.000Z    2017-06-02T15:14:42.000Z    06/02/2017 15:14:42
2017-06-02T15:13:11.000Z    2017-06-02T15:14:42.000Z    06/02/2017 15:14:42
2017-06-02T15:13:11.000Z    2017-06-02T15:14:42.000Z    06/02/2017 15:14:42

The intent is to save the above results in test.csv (correct my SPL if i'm wrong and somehow i can't save the column as _time)
but when i do a subsequent |inputlookup test.csv, this is my results(rows just doubled with the initial data):

date_first                date_last
2017-06-02T15:13:10.000Z    2017-06-02T15:14:42.000Z
2017-06-02T15:13:10.000Z    2017-06-02T15:14:42.000Z
2017-06-02T15:13:10.000Z    2017-06-02T15:14:42.000Z
2017-06-02T15:13:10.000Z    2017-06-02T15:14:42.000Z
2017-06-02T15:13:10.000Z    2017-06-02T15:14:42.000Z
2017-06-02T15:13:10.000Z    2017-06-02T15:14:42.000Z
2017-06-02T15:13:11.000Z    2017-06-02T15:14:42.000Z
2017-06-02T15:13:11.000Z    2017-06-02T15:14:42.000Z
2017-06-02T15:13:10.000Z    2017-06-02T15:14:42.000Z
2017-06-02T15:13:10.000Z    2017-06-02T15:14:42.000Z
2017-06-02T15:13:10.000Z    2017-06-02T15:14:42.000Z
2017-06-02T15:13:10.000Z    2017-06-02T15:14:42.000Z
2017-06-02T15:13:10.000Z    2017-06-02T15:14:42.000Z
2017-06-02T15:13:10.000Z    2017-06-02T15:14:42.000Z
2017-06-02T15:13:11.000Z    2017-06-02T15:14:42.000Z
2017-06-02T15:13:11.000Z    2017-06-02T15:14:42.000Z

So just wondering what is going on here?

0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

The rows are doubled in the lookup file because of append=true. If you remove that option, the existing lookup file will be replaced and the third column should be added.
You say you want a "splunk recognizable date field", but the existing date fields are already readily recognizable by Splunk. What is the purpose of this excercise?
The appendcols command seems unnecessary. Have you tried | inputlookup append=true test.csv
| eval n = strftime((strptime(date_last, "%Y-%m-%dT%H:%M:%S.000Z")), "%m/%d/%Y %H:%M:%S")
| fields date_first date_last n
| outputlookup append=true test.csv
?

---
If this reply helps you, Karma would be appreciated.

View solution in original post

richgalloway
SplunkTrust
SplunkTrust

The rows are doubled in the lookup file because of append=true. If you remove that option, the existing lookup file will be replaced and the third column should be added.
You say you want a "splunk recognizable date field", but the existing date fields are already readily recognizable by Splunk. What is the purpose of this excercise?
The appendcols command seems unnecessary. Have you tried | inputlookup append=true test.csv
| eval n = strftime((strptime(date_last, "%Y-%m-%dT%H:%M:%S.000Z")), "%m/%d/%Y %H:%M:%S")
| fields date_first date_last n
| outputlookup append=true test.csv
?

---
If this reply helps you, Karma would be appreciated.

esmonder
Path Finder

I tried what you suggested SPL, and it returned the same result.
But i tried your first recommendation by removing append=true to everything and it worked!

| inputlookup test.csv 
| eval n = strftime((strptime(date_last, "%Y-%m-%dT%H:%M:%S.000Z")), "%m/%d/%Y %H:%M:%S") 
| fields date_first date_last n
| outputlookup e test.csv

Thanks!

0 Karma
Get Updates on the Splunk Community!

Dashboards: Hiding charts while search is being executed and other uses for tokens

There are a couple of features of SimpleXML / Classic dashboards that can be used to enhance the user ...

Splunk Observability Cloud's AI Assistant in Action Series: Explaining Metrics and ...

This is the fourth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how ...

Brains, Bytes, and Boston: Learn from the Best at .conf25

When you think of Boston, you might picture colonial charm, world-class universities, or even the crack of a ...