topic Re: Need help getting right timestamp from CSV in Splunk Search

Need help getting right timestamp from CSV

richgalloway — Mon, 16 May 2016 20:54:13 GMT

I have a CSV file I'm trying to index, but the wrong timestamp field is getting selected.

UTC,LOCAL,HOSTNAME,SEVERITY,CATEGORY,PNAME,PID,MTNAME,MTID,METHOD,SRCFILE,SRCLINE,INDENT,MESSAGE
2016-05-10 12:40:00.887,2016-05-10 07:40:00.887,SYMCCS,Error,Data Reader,SymConsole,8316,,1,HandleException,,0,2,"ListBaselineNamed() Exception occured on the server side: 742|System.IO.DirectoryNotFoundException: Could not find a part of the path 'C:\Program Files (x86)\Symantec\CCS\Reporting and Analytics\Application Server\Console_Sync'.
   at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
   at System.IO.FileSystemEnumerableIterator`1.CommonInit()
   at System.IO.FileSystemEnumerableIterator`1..ctor(String path, String originalUserPath, String searchPattern, SearchOption searchOption, SearchResultHandler`1 resultHandler, Boolean checkHost)
   at System.IO.DirectoryInfo.InternalGetFiles(String searchPattern, SearchOption searchOption)
   at Symantec.CCS.DataReaderServer.FileSync.GetAllSCUDllList()
   at Symantec.CCS.DataReaderServer.Server.GetSCUFileList(DispatchObject input)|36|System.IO.DirectoryNotFoundException"
2016-05-10 12:40:00.890,2016-05-10 07:40:00.890,SYMCCS,Error,PreLaunchActivityProvider,SymConsole,8316,,1,DownloadBinaries,,0,1,"System.IO.DirectoryNotFoundException: Could not find a part of the path 'C:\Program Files (x86)\Symantec\CCS\Reporting and Analytics\Application Server\Console_Sync'.
   at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
   at System.IO.FileSystemEnumerableIterator`1.CommonInit()
   at System.IO.FileSystemEnumerableIterator`1..ctor(String path, String originalUserPath, String searchPattern, SearchOption searchOption, SearchResultHandler`1 resultHandler, Boolean checkHost)
   at System.IO.DirectoryInfo.InternalGetFiles(String searchPattern, SearchOption searchOption)
   at Symantec.CCS.DataReaderServer.FileSync.GetAllSCUDllList()
   at Symantec.CCS.DataReaderServer.Server.GetSCUFileList(DispatchObject input)"

Using the default settings parses the file well except the UTC column is used for _time, meaning times are 5 hours ahead of the system clock. I can't change the log format so I've been experimenting with other settings to get the right time.

I've tried:

CHECK_FOR_HEADER = true
TIMESTAMP_FIELDS = LOCAL

which correctly sets _time to the LOCAL field, but the remaining fields are not extracted.

I also tried

TIME_PREFIX = ,

which yields the same results.

Any suggestions for settings that will extract all fields and set _time to LOCAL?

Re: Need help getting right timestamp from CSV

sundareshr — Mon, 16 May 2016 21:10:36 GMT

Have you considered setting theTZ to UTC and extracting the UTC field for _time?

Re: Need help getting right timestamp from CSV

richgalloway — Mon, 16 May 2016 21:22:42 GMT

That also gets me halfway there. Times display correctly, but fields are not extracted.

Here is my props.conf stanza:

[CCScsv]
DATETIME_CONFIG =
INDEXED_EXTRACTIONS = csv
#CHECK_FOR_HEADER = true
KV_MODE = none
NO_BINARY_CHECK = true
SHOULD_LINEMERGE = false
TZ = UTC
#TIMESTAMP_FIELDS = LOCAL
category = Structured
description = Comma-separated value format. Set header and other settings in "Delimited Settings"
disabled = false
pulldown_type = true

Re: Need help getting right timestamp from CSV

sundareshr — Mon, 16 May 2016 21:49:15 GMT

Here's what i did. Copied the data from your post. Created a .csv (verified). Imported the data with TZ=UTC and everything looked right. Extracted all the cols, took time from UTC col and I got two events. Here's the props from my test

[ csv ]
SHOULD_LINEMERGE=false
NO_BINARY_CHECK=true
CHARSET=UTF-8
INDEXED_EXTRACTIONS=csv
KV_MODE=none
category=Structured
description=Comma-separated value format. Set header and other settings in "Delimited Settings"
disabled=false
pulldown_type=true
TZ=UTC

Then I tried this for props and this worked too. Extracted all the cols, took time from LOCAL col and I got two events.

[ csv ]
SHOULD_LINEMERGE=false
NO_BINARY_CHECK=true
CHARSET=UTF-8
INDEXED_EXTRACTIONS=csv
KV_MODE=none
category=Structured
description=Comma-separated value format. Set header and other settings in "Delimited Settings"
disabled=false
pulldown_type=true
TIME_FORMAT=%Y-%m-%d %H:%M:%S
TIMESTAMP_FIELDS=LOCAL

Re: Need help getting right timestamp from CSV

richgalloway — Mon, 16 May 2016 22:05:39 GMT

My data is coming from a Universal Forwarder. Would that make a difference? The forwarder's inputs.conf stanza is

[monitor://C:\ProgramData\Symantec.CSM\Logs]
disabled = false
index = ccs
sourcetype = CCScsv

Re: Need help getting right timestamp from CSV

sundareshr — Tue, 17 May 2016 20:29:21 GMT

Wonder if the sourcetypy is throwing a loop. Can you try changing it to csv?

Re: Need help getting right timestamp from CSV

richgalloway — Tue, 17 May 2016 20:46:53 GMT

Changing the sourcetype to csv puts me back where I started - fields are extracted, but times are 5 hours in the future. I don't want to props for all CSVs as they don't all have this problem.

Re: Need help getting right timestamp from CSV

somesoni2 — Tue, 17 May 2016 21:26:58 GMT

Give this a try

[CCScsv]
INDEXED_EXTRACTIONS = csv
CHECK_FOR_HEADER = true
KV_MODE = none
SHOULD_LINEMERGE = false
TIME_PREFIX=^\d+-\d+-\d+\s+\d+:\d+:\d+\.\d+,
TIME_FORMAT=%Y-%m-%d %H:%M:%S

Re: Need help getting right timestamp from CSV

richgalloway — Tue, 17 May 2016 21:43:10 GMT

Thanks for the suggestion, somesoni2. That fixes the time, but no fields are extracted.

Re: Need help getting right timestamp from CSV

richgalloway — Fri, 20 May 2016 16:09:39 GMT

I ended up putting a manual regex string into the field extractor. This is what my props.conf looks like on the SH.

[CCScsv]
EXTRACT-CCSlog = (?<UTC>[^,]+),(?<LOCAL>[^,]+),(?<HOSTNAME>[^,]+),(?<SEVERITY>[^,]+),(?<CATEGORY>[^,]+),(?<PNAME>[^,]+),(?<PID>[^,]+),(?<MTNAME>[^,]*),(?<MTID>[^,]+),(?<METHOD>[^,]+),(?<SRCFILE>[^,]*),(?<SRCLINE>[^,]+),(?<INDEX>[^,]+),"(?<MESSAGE>[^"]+)"