IIS - CSV parsing error

Elastic Stack Logstash
1

Hi,

I've been using Logstash for quite some time and over many of it's versions, we are shipping IIS logs via Filebeat to Logstash (currently 5.2.0) filtering of these logs is done mostly via CSV filter (we tried GROK it was way too CPU expensive), we have an error that has been with us trough all versions and I would love to get assistance for as it affects high percentage of our incoming events - <CSV::MalformedCSVError: Illegal quoting in line 1.>
my filter looks like this -

filter {
if [type] == 'iis-il2' {
csv {
separator => " "
columns => ["date","time","s-computername","s-ip","cs-method","cs-uri-stem","cs-uri-query","s-port","cs-username","c-ip","cs-version","cs(User-Agent)","cs(Cookie)","cs(Referer)","cs-host","sc-status","sc-substatus","sc-win32-status",
"sc-bytes","cs-bytes","time-taken","X-Forwarded-For","originalip","Incap-Client-Ip","Coolv-XIP","X-RDWR-IP"]
}
mutate {
remove_field => ["message","date-local"]
}
if "IL2" not in [s-computername] {
drop { }
}
}
}

and I receive many of these in my logstash log and the events never ship to ES -

[2017-03-22T08:05:38,747][WARN ][logstash.filters.csv ] Error parsing csv {:field=>"message", :source=>"2017-03-22 08:05:30 IL2WEBEQX10 66.6.20.5 GET /MobileTemplateV2.aspx p=-4769&pp=/&ln=&pa=ahmelive.com&ppp=/ 80 - 66.6.20.21 HTTP/1.1 Mozilla/5.0+(Linux;+Android+4.4.4;+SM-G318H+Build/KTU84P)+AppleWebKit/537.36+(KHTML,+like+Gecko)+Chrome/56.0.2924.87+Mobile+Safari/537.36 dtCookie=C77DC397FCCFCECAC5CD249D55E8D746|T3RoZXIrV0x8MQ;+ASP.NET_SessionId=vjienfdakc3yzm2e0xx3chpd;+theme=wlg_uni_wht_pur_mob;+rvh_gg=31b20a3b-3401-48ae-b8fe-1ca5e0a9af4d;+tid=0;+sgid=0;+7ORlydXrcRAMGhtb3k3wZw%3d%3d=7mN86WfQzgVQZg2uBm1UYOPBEQeCSQt3a5mPyij3kV8XgyeWwG2p2JyQYPLjSymRNXQ8Mi%2bzzbQVju8JIi%2bxmOf2vkRdW73cqD1ON4Q0WSXdPoCcsw73A5izlOwdtSp9;+BIGipServerStreamLiveSex.com=85198402.20480.0000;+__utmt=1;+adult=true;+Language=en;+nomobile=;+flcCount=2;+FOLLOW_ME=NaughtyGames4U;+last_visited_page=/free-videochat;+__utma=9651557.2016737814.1490169874.1490169874.1490169874.1;+__utmb=9651557.2.10.1490169874;+__utmc=9651557;+__utmz=9651557.1490169874.1.1.utmcsr=(direct)|utmccn=(direct)|utmcmd=(none);+bagsrv={i:"cqoglfwcwd7o0f2adj14uiey3",t:1,v:1} http://ahmelive.com/free-videochat/NaughtyGames4U ahmelive.com 200 0 0 10129 1414 95 49.224.86.197 198.143.38.41 49.224.86.197 49.224.86.197 -", :exception=>#<CSV::MalformedCSVError: Illegal quoting in line 1.>}
[2017-03-22T08:05:38,747][WARN ][logstash.filters.csv ] Error parsing csv {:field=>"message", :source=>"2017-03-22 08:05:31 IL2WEBEQX10 66.6.20.5 GET /services/mobile/BoshCommand.ashx cmd=closechat 80 - 66.6.20.21 HTTP/1.1 Mozilla/5.0+(Linux;+Android+6.0.1;+SM-G935F+Build/MMB29K)+AppleWebKit/537.36+(KHTML,+like+Gecko)+Chrome/56.0.2924.87+Mobile+Safari/537.36 dtCookie=E15217A51E0C9A63A207750F47E3F17A|T3RoZXIrV0x8MQ;+ASP.NET_SessionId=hztjoxti30iuftraoxdews3o;+theme=wlg_uni_gra_wht_mob;+rvh_gg=3ff7ce2e-79cf-403a-b4d6-38af9cf3469c;+adult=true;+tid=0;+sgid=0;+7ORlydXrcRAMGhtb3k3wZw%3d%3d=7mN86WfQzgVQZg2uBm1UYHyiolMl0aUF9npghwZuzOkCLsWJFdxPrJuJNIoAWenYdr4PY8Pw0wHYrh6BmZeXjkvkHAuglPmOGfn2mcbjoda9k3M244upSrrepbrbpypvwxkxXi4SnZW1nAr41JOITLDE0OtEjCemj18uABuctZI%3d;+BIGipServerStreamLiveSex.com=85198402.20480.0000;+Language=en;+nomobile=;+flcCount=2;+FOLLOW_ME=EAlicesweeteye;+last_visited_page=/free-videochat;+bagsrv={i:"ri612o63a04i9914x9h2yvzv6",t:1,v:1};+__utmt=1;+__utma=35078590.189748477.1490169930.1490169930.1490169930.1;+__utmb=35078590.1.10.1490169930;+__utmc=35078590;+__utmz=35078590.1490169930.1.1.utmcsr=uk.shagdates.com|utmccn=(referral)|utmcmd=referral|utmcct=/me/conversations http://www.naughtylivecamgirls.com/free-videochat/EAlicesweeteye/?queryid=1 www.naughtylivecamgirls.com 200 0 0 328 1476 2 176.27.126.111 149.126.76.9 176.27.126.111 176.27.126.111 -", :exception=>#<CSV::MalformedCSVError: Illegal quoting in line 1.>}

Thanks,

Ron

2

Please post your logs as preformatted text (use the </> toolbar button). I'm interested in what comes after ":exception=>#".

3 
[2017-03-22T08:05:38,747][WARN ][logstash.filters.csv     ] Error parsing csv {:field=>"message", :source=>"2017-03-22 08:05:30 IL2WEBEQX10 66.6.20.5 GET /MobileTemplateV2.aspx p=-4769&pp=/&ln=&pa=ahmelive.com&ppp=/ 80 - 66.6.20.21 HTTP/1.1 Mozilla/5.0+(Linux;+Android+4.4.4;+SM-G318H+Build/KTU84P)+AppleWebKit/537.36+(KHTML,+like+Gecko)+Chrome/56.0.2924.87+Mobile+Safari/537.36 dtCookie=C77DC397FCCFCECAC5CD249D55E8D746|T3RoZXIrV0x8MQ;+ASP.NET_SessionId=vjienfdakc3yzm2e0xx3chpd;+theme=wlg_uni_wht_pur_mob;+rvh_gg=31b20a3b-3401-48ae-b8fe-1ca5e0a9af4d;+tid=0;+sgid=0;+7ORlydXrcRAMGhtb3k3wZw%3d%3d=7mN86WfQzgVQZg2uBm1UYOPBEQeCSQt3a5mPyij3kV8XgyeWwG2p2JyQYPLjSymRNXQ8Mi%2bzzbQVju8JIi%2bxmOf2vkRdW73cqD1ON4Q0WSXdPoCcsw73A5izlOwdtSp9;+BIGipServerStreamLiveSex.com=85198402.20480.0000;+__utmt=1;+adult=true;+Language=en;+nomobile=;+flcCount=2;+FOLLOW_ME=NaughtyGames4U;+last_visited_page=/free-videochat;+__utma=9651557.2016737814.1490169874.1490169874.1490169874.1;+__utmb=9651557.2.10.1490169874;+__utmc=9651557;+__utmz=9651557.1490169874.1.1.utmcsr=(direct)|utmccn=(direct)|utmcmd=(none);+bagsrv={i:\"cqoglfwcwd7o0f2adj14uiey3\",t:1,v:1} http://ahmelive.com/free-videochat/NaughtyGames4U ahmelive.com 200 0 0 10129 1414 95 49.224.86.197 198.143.38.41 49.224.86.197 49.224.86.197 -", :exception=>#<CSV::MalformedCSVError: Illegal quoting in line 1.>}

[2017-03-22T08:05:38,747][WARN ][logstash.filters.csv ] Error parsing csv {:field=>"message", :source=>"2017-03-22 08:05:31 IL2WEBEQX10 66.6.20.5 GET /services/mobile/BoshCommand.ashx cmd=closechat 80 - 66.6.20.21 HTTP/1.1 Mozilla/5.0+(Linux;+Android+6.0.1;+SM-G935F+Build/MMB29K)+AppleWebKit/537.36+(KHTML,+like+Gecko)+Chrome/56.0.2924.87+Mobile+Safari/537.36 dtCookie=E15217A51E0C9A63A207750F47E3F17A|T3RoZXIrV0x8MQ;+ASP.NET_SessionId=hztjoxti30iuftraoxdews3o;+theme=wlg_uni_gra_wht_mob;+rvh_gg=3ff7ce2e-79cf-403a-b4d6-38af9cf3469c;+adult=true;+tid=0;+sgid=0;+7ORlydXrcRAMGhtb3k3wZw%3d%3d=7mN86WfQzgVQZg2uBm1UYHyiolMl0aUF9npghwZuzOkCLsWJFdxPrJuJNIoAWenYdr4PY8Pw0wHYrh6BmZeXjkvkHAuglPmOGfn2mcbjoda9k3M244upSrrepbrbpypvwxkxXi4SnZW1nAr41JOITLDE0OtEjCemj18uABuctZI%3d;+BIGipServerStreamLiveSex.com=85198402.20480.0000;+Language=en;+nomobile=;+flcCount=2;+FOLLOW_ME=EAlicesweeteye;+last_visited_page=/free-videochat;+bagsrv={i:"ri612o63a04i9914x9h2yvzv6",t:1,v:1};+__utmt=1;+__utma=35078590.189748477.1490169930.1490169930.1490169930.1;+__utmb=35078590.1.10.1490169930;+__utmc=35078590;+__utmz=35078590.1490169930.1.1.utmcsr=uk.shagdates.com|utmccn=(referral)|utmcmd=referral|utmcct=/me/conversations http://www.naughtylivecamgirls.com/free-videochat/EAlicesweeteye/?queryid=1 www.naughtylivecamgirls.com 200 0 0 328 1476 2 176.27.126.111 149.126.76.9 176.27.126.111 176.27.126.111 -", :exception=>#<CSV::MalformedCSVError: Illegal quoting in line 1.>}

4

CSV::MalformedCSVError: Illegal quoting in line 1

Perhaps it doesn't like the double quotes within at least one of the values?

5

So what would you suggest a replace mutate to replace double quotes with singe quotes perhaps ?

6

Only if you're okay with tampering with the evidence. Perhaps you should use a grok filter instead of csv?

7

I used GROK in the past... unfortunately LS kept crashing for cpu even thou we use strong instances, my guess is the size of each log entry plus the fact that grok is cpu intensive caused it.

8

My money is on an inefficient grok expression with too many DATA and/or GREEDYDATA.

1 Like
9

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.