Splunk Search

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

henry_ty_leung
Explorer

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!

0 Karma

henry_ty_leung
Explorer

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

0 Karma

vasanthmss
Motivator

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
Explorer

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!

0 Karma

vasanthmss
Motivator

check the above answer

0 Karma

vasanthmss
Motivator

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
Builder

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,

0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!