topic Re: How to get splunk to ignore the second line of a log file in Getting Data In

How to get splunk to ignore the second line of a log file

capilarity — Mon, 28 Sep 2020 18:41:33 GMT

Our call manager spits out hundreds and hundreds of small log files, each containing a header line and a second line of garbage.

Line 1: Header
"cdrRecordType","globalCallID_callManagerId","globalCallID_callId","origLegCallIdentifier","dateTimeOrigination","origNodeId","origSpan","origIpAddr","callingPartyNumber","callingPartyUnicodeLoginUserID","origCause_location","origCause_value","origPrecedenceLevel","origMediaTransportAddress_IP","origMediaTransportAddress_Port","origMediaCap_payloadCapability","origMediaCap_maxFramesPerPacket","origMediaCap_g723BitRate","origVideoCap_Codec","origVideoCap_Bandwidth","origVideoCap_Resolution","origVideoTransportAddress_IP","origVideoTransportAddress_Port","origRSVPAudioStat","origRSVPVideoStat","destLegIdentifier","destNodeId","destSpan","destIpAddr","originalCalledPartyNumber","finalCalledPartyNumber","finalCalledPartyUnicodeLoginUserID","destCause_location","destCause_value","destPrecedenceLevel","destMediaTransportAddress_IP","destMediaTransportAddress_Port","destMediaCap_payloadCapability","destMediaCap_maxFramesPerPacket","destMediaCap_g723BitRate","destVideoCap_Codec","destVideoCap_Bandwidth","destVideoCap_Resolution","destVideoTransportAddress_IP","destVideoTransportAddress_Port","destRSVPAudioStat","destRSVPVideoStat","dateTimeConnect","dateTimeDisconnect","lastRedirectDn","pkid","originalCalledPartyNumberPartition","callingPartyNumberPartition","finalCalledPartyNumberPartition","lastRedirectDnPartition","duration","origDeviceName","destDeviceName","origCallTerminationOnBehalfOf","destCallTerminationOnBehalfOf","origCalledPartyRedirectOnBehalfOf","lastRedirectRedirectOnBehalfOf","origCalledPartyRedirectReason","lastRedirectRedirectReason","destConversationId","globalCallId_ClusterID","joinOnBehalfOf","comment","authCodeDescription","authorizationLevel","clientMatterCode","origDTMFMethod","destDTMFMethod","callSecuredStatus","origConversationId","origMediaCap_Bandwidth","destMediaCap_Bandwidth","authorizationCodeValue","outpulsedCallingPartyNumber","outpulsedCalledPartyNumber","origIpv4v6Addr","destIpv4v6Addr","origVideoCap_Codec_Channel2","origVideoCap_Bandwidth_Channel2","origVideoCap_Resolution_Channel2","origVideoTransportAddress_IP_Channel2","origVideoTransportAddress_Port_Channel2","origVideoChannel_Role_Channel2","destVideoCap_Codec_Channel2","destVideoCap_Bandwidth_Channel2","destVideoCap_Resolution_Channel2","destVideoTransportAddress_IP_Channel2","destVideoTransportAddress_Port_Channel2","destVideoChannel_Role_Channel2","IncomingProtocolID","IncomingProtocolCallRef","OutgoingProtocolID","OutgoingProtocolCallRef","currentRoutingReason","origRoutingReason","lastRedirectingRoutingReason","huntPilotPartition","huntPilotDN","calledPartyPatternUsage","IncomingICID","IncomingOrigIOI","IncomingTermIOI","OutgoingICID","OutgoingOrigIOI","OutgoingTermIOI","outpulsedOriginalCalledPartyNumber","outpulsedLastRedirectingNumber"

Line 2: Garbage

INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,VARCHAR(50),VARCHAR(128),INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,VARCHAR(64),VARCHAR(64),INTEGER,INTEGER,INTEGER,INTEGER,VARCHAR(50),VARCHAR(50),VARCHAR(128),INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,VARCHAR(64),VARCHAR(64),INTEGER,INTEGER,VARCHAR(50),UNIQUEIDENTIFIER,VARCHAR(50),VARCHAR(50),VARCHAR(50),VARCHAR(50),INTEGER,VARCHAR(129),VARCHAR(129),INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,VARCHAR(50),INTEGER,VARCHAR(2048),VARCHAR(50),INTEGER,VARCHAR(32),INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,VARCHAR(32),VARCHAR(50),VARCHAR(50),VARCHAR(64),VARCHAR(64),INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,VARCHAR(32),INTEGER,VARCHAR(32),INTEGER,INTEGER,INTEGER,VARCHAR(50),VARCHAR(50),INTEGER,VARCHAR(50),VARCHAR(50),VARCHAR(50),VARCHAR(50),VARCHAR(50),VARCHAR(50),VARCHAR(50),VARCHAR(50)

Lines 3 onwards contain the useful data:
1,3,1675985,58571830,1421194778,3,0,1210359306,"7435220","USERNAME",0,16,4,1210359306,20952,4,20,0,0,0,0,0,0,"0","0",58571831,2,22,-399533046,"926011605","926011605","",0,0,4,-399533046,18484,4,20,0,0,0,0,0,0,"0","0",1421194792,1421195072,"926011605","a7220bcb-ff5c-4910-8c2a-ac653d828890","P_LOC_PSTN","P_AS_INTERNAL_MIGRATED","P_LOC_PSTN","P_LOC_PSTN",280,"IPPHONE","S0/SU0/DS1-1@LOC_GW_SRST_2",12,0,0,0,0,0,0,"Region",0,"","",0,"",3,1,0,0,64,64,"","29015220","26011605","ipaddress","ipaddress",0,0,0,0,0,0,0,0,0,0,0,0,3,"00000000001992D1037DBC3600000000",4,"6044-29015220-26011605",0,0,0,"","",5,"","","","","","","",""
1,3,1675987,58571834,1421195075,3,0,1210097162,"7435904","USERNAME",0,16,4,0,0,0,0,0,0,0,0,0,0,"0","0",58571835,0,0,0,"","","",0,0,4,0,0,0,0,0,0,0,0,0,0,"0","0",0,1421195075,"","209334c9-b9a6-4ad8-b592-1c2072ee874f","","P_AS_INTERNAL_MIGRATED","","",0,"IPPHONE","",12,0,0,0,0,0,0,"Region",0,"","",0,"",3,0,0,0,0,0,"","","","ipaddress","",0,0,0,0,0,0,0,0,0,0,0,0,0,"",0,"",0,0,0,"","",2,"","","","","","","",""

Problem is that I can't get splunk to ignore Line 2 and this is causing a problem where is doesn't extract the headers properly and at the moment I get all lines added to the index with no KV pairs.

I've tried numerous ways of excluding line 2, even excluding the headers all together but all avail.

Any ideas?? Running 6.2.1 on windows

(editing each file to delete is not an option as there are too many and the same files are used else where)

Re: How to get splunk to ignore the second line of a log file

somesoni2 — Fri, 16 Jan 2015 15:55:31 GMT

Have a look at SEDCMD option in props.conf

http://docs.splunk.com/Documentation/Splunk/latest/admin/Propsconf

Using this your can delete the unwanted line/events before indexing (actual raw file is not updated, just the data that will get stored in SPlunk will be updated).

Re: How to get splunk to ignore the second line of a log file

chanfoli — Mon, 28 Sep 2020 18:41:35 GMT

I have not experimented with PREAMBLE_REGEX so I am not sure it will work. Have you tried something like:

INDEXED_EXTRACTIONS = CSV
HEADER_FIELD_LINE_NUMBER = 1
PREAMBLE_REGEX = ^INTEGER

Failing this you could try a null-queue transform:

In props.conf:

[your source or sourcetype spec]
TRANSFORMS-null = discardit

and in transforms.conf:

[discardit]
REGEX=^INTEGER
DEST_KEY = queue
FORMAT = nullQueue

Re: How to get splunk to ignore the second line of a log file

capilarity — Fri, 16 Jan 2015 16:04:16 GMT

not sure how this could be used to delete a line, profsconf page talks about using it to replace or substitute data - could replace the data but the line will still exist - need to play with this but thanks for the tip.

Re: How to get splunk to ignore the second line of a log file

capilarity — Fri, 16 Jan 2015 16:16:25 GMT

Tried the null-queue transformation, had no effect. Thought it might have been my regex but your version didn't work either.

Have also tired PREAMBLE-REGEX to delete both lines 1 and 2 but nothing gets indexed at all if use this.

thanks for the help though

Re: How to get splunk to ignore the second line of a log file

chanfoli — Fri, 16 Jan 2015 16:48:26 GMT

Sorry it did not work out. If you do not find an answer using the "automatic" CSV index time extractions, you might consider using a different approach, namely a search-time extraction using KV_MODE=none, and the DELIMS = "," and FIELDS= "field1, field2, field3..." with a REPORT-class extraction as described here:

http://docs.splunk.com/Documentation/Splunk/6.2.1/Knowledge/Createandmaintainsearch-timefieldextractionsthroughconfigurationfiles

and in this old-ish example question:
http://answers.splunk.com/answers/3006/best-way-to-have-the-splunk-indexers-handle-a-csv-log-file.html

Re: How to get splunk to ignore the second line of a log file

asaste — Tue, 20 Sep 2016 07:23:57 GMT

Hi Matt,
I have exactly same requirement as yours and I am also not able to ignore 2 line. Did you get solution for this ? IF yes, Can you please share it.
Thanks in Advance.

Re: How to get splunk to ignore the second line of a log file

gjanders — Tue, 20 Sep 2016 08:16:38 GMT

Sending data to the null queue as per one of the previous answers should work, you would need to ensure this configuration is on the first heavy forwarder or indexer that works with the data.

The null queue is offically documented here http://docs.splunk.com/Documentation/Splunk/6.4.3/Forwarding/Routeandfilterdatad and there are some examples on Splunk Answers...

Re: How to get splunk to ignore the second line of a log file

krusty — Mon, 20 Feb 2017 11:40:19 GMT

Hi,

I'm in the same situation. I have to "remove"/"delete"/"ignore" the second line of the csv file.
My configuration looks like this:

inputs.conf

[Monitor://splunk/ftp/cisco/cdr_*]
disabled = false
followTail = 0
host = <hostname>
sourcetype = csv
index = voice
recursive = false
followSymlink = false

transforms.conf

[eliminate_line_cdr]
REGEX=^INTEGER.*
DEST_KEY = Queue
FORMAT = nullQueue

props.conf

[source::/splunk/ftp/cisco/cdr_*]
TRANSFORMS-cisco_voice_cdr = eliminate_line_cdr

Could somebody tell me, where I do a mistake?
For me it looks good, but it will not work.

FYI: the configuration files are located on the splunk indexer. The same for the cdr_* files.

Any idea would be helpful.

Thanks

Re: How to get splunk to ignore the second line of a log file

DalJeanis — Mon, 20 Feb 2017 21:04:58 GMT

That looks good to me. Have you restarted the indexer since you made the change?

You could also try this -

 REGEX=^(INTEGER|UNIQUEIDENTIFIER|VARCHAR(?>\(\d+\))?|(?>,)?)+

That assumes that your second header has only INTEGER, VARCHAR and UNIQUEIDENTIFIER field types.

Re: How to get splunk to ignore the second line of a log file

krusty — Tue, 21 Feb 2017 13:08:53 GMT

Hi DaIJeanis,
thanks for you answer. Unfortunately with your REGEX it will also not work.

If I manually remove the line which starts with INTEGER,... the file will be indexed. But this is not the goal for me.

Re: How to get splunk to ignore the second line of a log file

somesoni2 — Tue, 21 Feb 2017 15:50:57 GMT

Few question- the props.conf and transforms.conf are placed on Indexers/Heavyforwarders right (and it was restarted after making the change)? Also, it seems there is typo in the DEST_KEY attribute, it should all small case letters queue. Also, can you try this

[eliminate_line_cdr]
REGEX=,INTEGER,INTEGER
DEST_KEY = queue
FORMAT = nullQueue

Re: How to get splunk to ignore the second line of a log file

krusty — Wed, 22 Feb 2017 06:42:09 GMT

Hi somesoni2,

yes I place the props.conf and transforms.conf on the indexer. We only have a universal forwarder running for windows events but in this case it is not used. For the specific cdr logs I configured our voice environment to send the data by sftp through our splunk indexer.

Yes I always restart the splunk service by typing service splunk restart on the command line. 😉 I didn't see any errors, regarding misconfiguration of *.conf files. So I gues that my configuration is fine.

I tried your REGEX and a DEST_KEY with lower letters, but it didn't solf the problem. If I place a new file into the folder which is monitored by splunk, splunk will do nothing.
Do you have any idea how I can debug it?

Thanks

Re: How to get splunk to ignore the second line of a log file

krusty — Wed, 22 Feb 2017 09:17:53 GMT

It's me again.

I'm able to find the problem and solve it.
First I just did a search on the _internal index for my configured source.
There I found this entry:

ERROR TailingProcessor - Ignoring path due to: File will not be read, seekptr checksum did not match (file=/myfile/test.out). Last time we saw this initcrc, the filename was different. You may wish to use a CRC salt on this source. Consult the documentation or file a support case online at http://www.splunk.com/page/submit_issue">http://www.splunk.com/page/submit_issue for more info.

After I changed the Inputs.conf to this,

[monitor:///splunk/ftp/cisco/cdr_*]
disabled = false
followTail = 0
host = cucm
sourcetype = csv
index = voice
recursive = false
followSymlink = false
crcSalt = <SOURCE>

I was able to index the files into splunk. yeeeeha!!!
So the important thing which was missing is crcSalt = <SOURCE>.

By the way, I changed the REGEX back to INTEGER. This works too.

Thanks everybody for help.

Re: How to get splunk to ignore the second line of a log file

charlescabico — Tue, 29 Sep 2020 13:20:30 GMT

Hi Krusty,

I followed your recommended solution but it doesn't seem to work in my case.
Logs seem to indicate the configured transforms are processed as expected, bu the 2nd line still shows up on the cucm index search.

inputs.conf
[monitor://C:\FTP]
disabled = false
index = cucm
sourcetype = csv
recursive = false
followTail = 0
followSymlink = false
crcSalt =

transforms.conf
[source::...\cdr_*]
TRANSFORMS-cdr_discard = eliminate_line_cdr

[source::...\cmr_*]
TRANSFORMS-cmr_discard = eliminate_line_cdr

props.conf
[source::...\cdr_*]
TRANSFORMS-cdr_discard = eliminate_line_cdr

[source::...\cmr_*]
TRANSFORMS-cmr_discard = eliminate_line_cdr

splunkd.log
03-24-2017 11:11:06.206 +1000 DEBUG PropertiesMapConfig - Performing pattern matching for: source::C:\FTP\cmr_StandAloneCluster_01_201703240110_466484|host::AU-BNE-SVR-SPK01|csv|2884
03-24-2017 11:11:06.206 +1000 DEBUG PropertiesMapConfig - Pattern 'source::...\cmr_' matches with lowest priority
03-24-2017 11:11:06.206 +1000 DEBUG PropertiesMapConfig - Pattern 'csv' matches with priority 100
03-24-2017 11:11:06.206 +1000 DEBUG PropertiesMapConfig - Performing pattern matching for: source::C:\FTP\cmr_StandAloneCluster_01_201703240110_466484|host::AU-BNE-SVR-SPK01|csv|2884
03-24-2017 11:11:06.207 +1000 DEBUG PropertiesMapConfig - Pattern 'source::...\cmr_' matches with lowest priority
03-24-2017 11:11:06.207 +1000 DEBUG PropertiesMapConfig - Pattern 'csv' matches with priority 100
03-24-2017 11:11:06.207 +1000 DEBUG regexExtractionProcessor - RegexExtractor: Instance found for eliminate_line_cdr
03-24-2017 11:11:06.207 +1000 DEBUG regexExtractionProcessor - RegexExtractor: Interpolated to nullQueue
03-24-2017 11:11:06.207 +1000 DEBUG regexExtractionProcessor - RegexExtractor: Extracted nullQueue

Re: How to get splunk to ignore the second line of a log file

andrewtrobec — Thu, 03 Aug 2017 15:29:09 GMT

This has just worked for me, thanks!

Re: How to get splunk to ignore the second line of a log file

claudio_manig — Wed, 27 Jun 2018 08:09:45 GMT

hi folks

I have exactly the same situation as capilarity had and i this should be a perfect scenario to use PREAMBLE_REGEX but it does not work.

Did actually ever someone get this scenario to work with PREAMBLE_REGEX? I have the feeling that this never works as documented. I'm not a big fan of nullqueues as the indexer has to process this and we neither want to put any unnecessary load on them, nor send the data from the uf as we obviously don't need those events. I know this can be done on hf as well, but there's not always one in place.

Re: How to get splunk to ignore the second line of a log file

gjanders — Wed, 27 Jun 2018 08:25:18 GMT

Just to confirm your following this rule?

Splunk props.conf

This feature and all of its settings apply at input time, when data is
first read by Splunk. The setting is used on a Splunk system that has
configured inputs acquiring the data.

In other words the PREAMBLE_REGEX must be on the universal forwarder where the inputs.conf is...

Also it might be worth posting a new question since this one goes back to 2015...also refer to the caveats here (Caveats to extracting fields from structured data files) if you do use this property...

Re: How to get splunk to ignore the second line of a log file

claudio_manig — Wed, 27 Jun 2018 08:33:35 GMT

yes i did as i test this out on a standalone machine-

Re: How to get splunk to ignore the second line of a log file

gjanders — Tue, 29 Sep 2020 20:09:59 GMT

While in theory you could have PREAMBLE_REGEX ignore the first two lines (I haven't tested it but you could make a regex that matches both), my personal opinion is that unless you need structured data extraction, use a LINE_BREAKER to drop the non-required lines (if they are at the start) and split the events using this option...

You might need a new question if you need help with that...