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

[henry_ty_leung]

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!

[henry_ty_leung]

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

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]

Henry

[vasanthmss]

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!

[henry_ty_leung]

Hi,

Thanks for the prompted reply! Vasanthmss!

SQL used.
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] | table _time ID mcn begin_action _raw | outputcsv [ | stats count | eval filename=strftime(_time, "%Y%m") | return $filename]

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!

[vasanthmss]

check the above answer

[vasanthmss]

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!

[musskopf]

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,