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!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

February 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...