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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

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

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...