Splunk Search

Why is outputlookup not updating the csv file as intended?

Path Finder

I have a lookup file in the form of test.csv
in the test.csv there are two columns with date fields(datefirst and datelast), 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

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, an upvote would be appreciated.

View solution in original post

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, an upvote would be appreciated.

View solution in original post

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