<div id="readability-page-1" class="page"><div id="post-body-8213715016677190079" itemprop="articleBody">
<div><pre><code>[2023-06-23T13:19:13,487][INFO ][logstash.agent&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;] Pipelines running {:count=&gt;1, :running_pipelines=&gt;[:main], :non_running_pipelines=&gt;[]}[2023-06-23T13:20:00,593][INFO ][logstash.inputs.jdbc&nbsp; &nbsp; &nbsp;][main][02f2fe91bcdc2cef22f19291d544a8551a0d1ce4c1b2aabf7a101153deed4fe1] (0.002507s)select a.timestamp, a.cid, b.sig_name, unhex(c.data_payload)from event a, signature b, data cwhere a.signature = b.sig_idand a.sid = c.sid and a.cid = c.cidand a.cid &gt; 0<a name="more"></a>{&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;"sig_name" =&gt; "icmp-request",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;"@timestamp" =&gt; 2023-06-23T04:21:00.772026400Z,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;"@version" =&gt; "1",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "cid" =&gt; 5316,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "timestamp" =&gt; 2023-06-23T04:20:29.000Z,&nbsp; &nbsp; "unhex(c.data_payload)" =&gt; #&lt;Sequel::SQL::Blob:0x1494 bytes=56 start="\xFD\xD4\x8Bd\x00\x00\x00\x00*\x1C" end="./01234567"&gt;}{&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;"sig_name" =&gt; "web-request",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;"@timestamp" =&gt; 2023-06-23T04:21:00.772026400Z,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;"@version" =&gt; "1",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "cid" =&gt; 5317,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "timestamp" =&gt; 2023-06-23T04:20:31.000Z,&nbsp; &nbsp; "unhex(c.data_payload)" =&gt; #&lt;Sequel::SQL::Blob:0x14c4 bytes=77 start="GET / HTTP" end="t: */*\r\n\r\n"&gt;}</code></pre></div>Splunk DB Connect 앱은&nbsp;unhex 함수를 이용한 쿼리문 실행 시 값을 가져오지 못한다.<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEguc20_OtEu6pOxQAdYBwUQDjD1P9mdaZmDUIoUi9hBjcHl6m7sabVzirKWeu1XjGgXOdqpjxxAY6UiymDtL9-L-j-lJn-2zE36FJQ_lNg_6QobMxtHkRhpyBBHhixQ-Ure-63GHnh_FXnJ5JXBFYu038pame4SegMOmD-8mfz8BjGGA_mulYDr69aHG0zh/s1164/splunk_db_connect.png"><img data-original-height="720" data-original-width="1164" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEguc20_OtEu6pOxQAdYBwUQDjD1P9mdaZmDUIoUi9hBjcHl6m7sabVzirKWeu1XjGgXOdqpjxxAY6UiymDtL9-L-j-lJn-2zE36FJQ_lNg_6QobMxtHkRhpyBBHhixQ-Ure-63GHnh_FXnJ5JXBFYu038pame4SegMOmD-8mfz8BjGGA_mulYDr69aHG0zh/s280/splunk_db_connect.png" width="280"></a>물론 unhex 함수를 사용하지 않으면 필드값을 가져올 수 있다.<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEglULgGQQkR9rt0xdgGDrEOSfrkdlM6hwcbCQSwsMOrAs4L_tP-eMR6USF3-B_OcwPWjqeutz533hRNLZ_qQsifIVt1g0smRhnf1w8Ax3lmjPclRFbcp3nuyhfFr_gj76V5RvkMWLXyj7vOrTkR9XEckoxtgy32Zx7ENSVAyCc6eciFSU66fm-d_sGnrSGi/s1242/splunk_db_connect2.png"><img data-original-height="720" data-original-width="1242" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEglULgGQQkR9rt0xdgGDrEOSfrkdlM6hwcbCQSwsMOrAs4L_tP-eMR6USF3-B_OcwPWjqeutz533hRNLZ_qQsifIVt1g0smRhnf1w8Ax3lmjPclRFbcp3nuyhfFr_gj76V5RvkMWLXyj7vOrTkR9XEckoxtgy32Zx7ENSVAyCc6eciFSU66fm-d_sGnrSGi/s280/splunk_db_connect2.png" width="280"></a>하지만 제일 중요한 data_payload 필드값을 읽을 수 없음.<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi_E_T1N1m2CXtJNXYVpeBIGzvhJGFIFo70hFBP-f8UOHdpZEdWF9Qbe6dRczOFJ_X2JOXF_9CnvLdxS8PdP8gOumnczatj500RZSJE0Zs2aKdF7tyupjO3xPBHlrqIg12X694HuXdsSKRslExRxe-dTIsnNpWD0sR0D7ToL4UGSpySFVxvEtajT1dSYwBH/s1778/splunk_db_connect3.png"><img data-original-height="1125" data-original-width="1778" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi_E_T1N1m2CXtJNXYVpeBIGzvhJGFIFo70hFBP-f8UOHdpZEdWF9Qbe6dRczOFJ_X2JOXF_9CnvLdxS8PdP8gOumnczatj500RZSJE0Zs2aKdF7tyupjO3xPBHlrqIg12X694HuXdsSKRslExRxe-dTIsnNpWD0sR0D7ToL4UGSpySFVxvEtajT1dSYwBH/s280/splunk_db_connect3.png" width="280"></a>16진수 디코딩우선 2개 단위로 % 기호를 붙여서 url 인코딩.<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjPFzV6cmPqHpXFQblcGZUThkemujohG4lGGr4koUhE4no8Rb16iBHS2TFxQhtYNModa4_Q6zKAXyBT3fh3XhsDRE6_I90pHcBBj0Q0B_uNG41RW9CiPtqp63XFov73vlMClxyV16Ljf0guQbYyloiCt9sNx-32h5ibdLQ_1xnnUb2sTT1d6BzjmU2K8ak7/s1778/splunk_eval_decrypt.png"><img data-original-height="1125" data-original-width="1778" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjPFzV6cmPqHpXFQblcGZUThkemujohG4lGGr4koUhE4no8Rb16iBHS2TFxQhtYNModa4_Q6zKAXyBT3fh3XhsDRE6_I90pHcBBj0Q0B_uNG41RW9CiPtqp63XFov73vlMClxyV16Ljf0guQbYyloiCt9sNx-32h5ibdLQ_1xnnUb2sTT1d6BzjmU2K8ak7/s280/splunk_eval_decrypt.png" width="280"></a><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgHgY7X96EYukmg-zppKgtEdq0hnkx4WfUj4SU7BFcJ7kG-YTuoRRIdt59Yqq-6nayC6-ojaKfe5fpXZC-bIvW61ZaY8X0HvpzGQmPold0RszGbmevaFQi1xRiNeKOaaQXGCqux1lDbfSiaIUVA71ctdhdFOJQF0wSpGblp7N9JKODhIiY7DgyC-epsDE6x/s1692/splunk_eval_decrypt2.png"><img data-original-height="1125" data-original-width="1692" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgHgY7X96EYukmg-zppKgtEdq0hnkx4WfUj4SU7BFcJ7kG-YTuoRRIdt59Yqq-6nayC6-ojaKfe5fpXZC-bIvW61ZaY8X0HvpzGQmPold0RszGbmevaFQi1xRiNeKOaaQXGCqux1lDbfSiaIUVA71ctdhdFOJQF0wSpGblp7N9JKODhIiY7DgyC-epsDE6x/s280/splunk_eval_decrypt2.png" width="280"></a>다행히 스플렁크는 다양한 문자열 인/디코딩을 지원하는 <a href="https://apps.splunk.com/app/5565/" target="_blank">decrypt2</a>라는 앱을 제공한다. icmp 트래픽 데이터 디코딩 성공.<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhN4MA-7h6xqvwKGtZiO_r2rpY02XNTQvtyyLBS6RTUGoo2VzH2uVPq2Fol9rpqCk3wANg_InvtvfgEgRqhIzva_bX1THjSZgnJ2IpsUeYATQVQjmYVKX52UYscV3cV9s-aMfNvkkqWhvUVum7pmWAW9wItpbipSdKEr5aX0f4HjLv4MI9jNXH7qlmI566P/s1754/splunk_decrypt_app.png"><img data-original-height="1125" data-original-width="1754" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhN4MA-7h6xqvwKGtZiO_r2rpY02XNTQvtyyLBS6RTUGoo2VzH2uVPq2Fol9rpqCk3wANg_InvtvfgEgRqhIzva_bX1THjSZgnJ2IpsUeYATQVQjmYVKX52UYscV3cV9s-aMfNvkkqWhvUVum7pmWAW9wItpbipSdKEr5aX0f4HjLv4MI9jNXH7qlmI566P/s280/splunk_decrypt_app.png" width="280"></a>관련 글<div><ul><li><a href="https://kangmyounghun.blogspot.com/2023/04/splunk-db-connect.html">Splunk DB Connect</a></li><li><a href="https://kangmyounghun.blogspot.com/2023/06/splunk-db-connect-2nd.html">Splunk DB Connect - 2nd</a></li><li><a href="https://kangmyounghun.blogspot.com/2017/08/elasticsearch-snort.html" target="_blank">Elasticsearch 활용(Snort 데이터베이스 연동)</a></li></ul></div>

</div></div>

케세라세라

다음은 로그스태시 jdbc 인풋 연동 결과. unhex 함수를 이용한 쿼리문이 정상 동작함을 알 수 있다.
[2023-06-23T13:19:13,487][INFO ][logstash.agent           ] Pipelines running {:count=>1, :running_pipelines=>[:main], :non_running_pipelines=>[]}
[2023-06-23T13:20:00,593][INFO ][logstash.inputs.jdbc     ][main][02f2fe91bcdc2cef22f19291d544a8551a0d1ce4c1b2aabf7a101153deed4fe1] (0.002507s)
select a.timestamp, a.cid, b.sig_name, unhex(c.data_payload)
from event a, signature b, data c
where a.signature = b.sig_id
and a.sid = c.sid and a.cid = c.cid
and a.cid > 0
{
                 "sig_name" => "icmp-request",
               "@timestamp" => 2023-06-23T04:21:00.772026400Z,
                 "@version" => "1",
                      "cid" => 5316,
                "timestamp" => 2023-06-23T04:20:29.000Z,
    "unhex(c.data_payload)" => #<Sequel::SQL::Blob:0x1494 bytes=56 start="\xFD\xD4\x8Bd\x00\x00\x00\x00*\x1C" end="./01234567">
}
{
                 "sig_name" => "web-request",
               "@timestamp" => 2023-06-23T04:21:00.772026400Z,
                 "@version" => "1",
                      "cid" => 5317,
                "timestamp" => 2023-06-23T04:20:31.000Z,
    "unhex(c.data_payload)" => #<Sequel::SQL::Blob:0x14c4 bytes=77 start="GET / HTTP" end="t: */*\r\n\r\n">
}
Splunk DB Connect 앱은 
unhex 함수를 이용한 쿼리문 실행 시 값을 가져오지 못한다.
물론 unhex 함수를 사용하지 않으면 필드값을 가져올 수 있다.
하지만 제일 중요한 data_payload 필드값을 읽을 수 없음.
16진수 디코딩
우선 2개 단위로 % 기호를 붙여서 url 인코딩.
이후 urldecode 함수를 이용해서 디코딩. 그런데 icmp 트래픽 데이터는 디코딩 실패. 트래픽에 포함된 non-printable characters 때문인가?
다행히 스플렁크는 다양한 문자열 인/디코딩을 지원하는 decrypt2라는 앱을 제공한다. icmp 트래픽 데이터 디코딩 성공.
관련 글
Splunk DB Connect
Splunk DB Connect - 2nd
Elasticsearch 활용(Snort 데이터베이스 연동)

Splunk DB Connect - 3rd

댓글