Splunk Search

Need help getting right timestamp from CSV

richgalloway
SplunkTrust
SplunkTrust

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?

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

richgalloway
SplunkTrust
SplunkTrust

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>[^"]+)"
---
If this reply helps you, Karma would be appreciated.

View solution in original post

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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>[^"]+)"
---
If this reply helps you, Karma would be appreciated.
0 Karma

somesoni2
SplunkTrust
SplunkTrust

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
0 Karma

richgalloway
SplunkTrust
SplunkTrust

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

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

sundareshr
Legend

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

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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
---
If this reply helps you, Karma would be appreciated.
0 Karma

sundareshr
Legend

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
0 Karma

richgalloway
SplunkTrust
SplunkTrust

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

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

sundareshr
Legend

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

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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.

---
If this reply helps you, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...