topic Re: Export search results into YYYYMM.csv dynamically based on log date in Splunk Search

Export search results into YYYYMM.csv dynamically based on log date

henry_ty_leung — Mon, 28 Sep 2020 18:21:25 GMT

As stated in subject line, i would like to split a huge log with past 12 months' log records and dynamically without hardcoding the dates and export search results into YYYYMM.csv accordingly based on their log date, how could i do that ?

E.g. From my search command
sourcetype=xmas_log ID begin.action date_month=January | table _time ID mcn begin_action Desc _raw |outputcsv 201401.csv

can i do something like:
sourcetype=x,as_log ID begin.action date_month=$YYYYMM| table _time ID mcn begin_action Desc _raw |outputcsv $YYYYMM.csv

Thank you very much!

Re: Export search results into YYYYMM.csv dynamically based on log date

musskopf — Thu, 11 Dec 2014 04:17:06 GMT

Have you tried using the Splunk CLI interface?

Have a look at the CLI documentation: http://docs.splunk.com/Documentation/Splunk/6.2.0/SearchReference/CLIsearchsyntax you might easily combine it with some scripting (PowerShell, Bash, etc) to produce what you need.

Cheers,

Re: Export search results into YYYYMM.csv dynamically based on log date

vasanthmss — Thu, 11 Dec 2014 04:22:46 GMT

Hi Henry,

try this,

sourcetype=x,as_log ID | table _time ID mcn begin_action Desc _raw | outputcsv [ | stats count | eval filename=strftime(now(), "%Y%m") | return $filename]

The above query will help you to get the today's date. your can change the sub search based on your requirement like instead of now() you can use any date param.

Cheersss!

Re: Export search results into YYYYMM.csv dynamically based on log date

henry_ty_leung — Mon, 28 Sep 2020 18:25:21 GMT

Hi,

Thanks for the prompted reply! Vasanthmss!

However, do you have any idea why the CSV generated via outputcsv is in a different format as
export to csv (via Web interface) :

I found _time column display as as 1417622399.196 in above script
however, it display correctly if manual export to csv from the browser!

Re: Export search results into YYYYMM.csv dynamically based on log date

vasanthmss — Thu, 11 Dec 2014 21:14:35 GMT

Hi,

if you are getting epoch time in your output csv you can convert the _time like this,

| eval time=strftime(_time,"%Y-%m-%d %H:%M:%S")

So your search would be like this,

sourcetype=csms_log date_month=December ID begin.action | join type=outer ID [ search sourcetype=tony ID detail.identity| stats values(detail_identity) as mcn by ID|fields ID mcn]  | eval time=strftime(_time,"%Y-%m-%d %H:%M:%S") | table time ID mcn begin_action _raw | outputcsv [ | stats count | eval filename=strftime(_time, "%Y%m") | return $filename]

Accept this answer if its solves your query.

Cheerrs!

Re: Export search results into YYYYMM.csv dynamically based on log date

vasanthmss — Thu, 11 Dec 2014 21:17:23 GMT

check the above answer

Re: Export search results into YYYYMM.csv dynamically based on log date

henry_ty_leung — Mon, 28 Sep 2020 18:22:40 GMT

Hi Vasanthmss

I have tried the sql that you have provided, however it stilll not able to return the right filename...any ideas ?
do i need to get an earliest time as the filename from below ?

Thanks

Henry