How to create a report that appends the data of every past month in the excel before sending

[ak9092]

Hi,

I want to create a report through splunk that will send out an email consisting data of each months stats by auto appending the excel file with the past months data before sending.

Following is my query which gives me the required data. Now i don't know how to distribute that data by month and then append in some excel file and send it through over email.

my search..

| fillnull value=1000 response_code

| eval success=case(response_code>=400, 0, timed_out == "True", 0, response_code="",0)

| fillnull value=1 success 

|stats count as total, sum(success) as successes by title

| eval availability=round(100*(successes/total),2)

|stats count by title availability

I want the data in excel file to look something like below -

I want this to be done automatically through Splunk Schedule reports at the beginning of each month.

Can someone please help me figure out a way if its possible through Splunk ?

Thanks in advance.

[thambisetty]

you can split date by month using date_month auto extracted field and they look like below:

column value

date_hour	10
date_mday	7
date_minute	25
date_month	september
date_second	23
date_wday	monday
date_year	2020
date_zone	local

and coming to append your results in a format you presented in the screenshot is not possible. but you can send results in csv format.

[ak9092]

Hi @thambisetty , thank you.

I am now able to get the data in below format in splunk -

title 07 Jul 2020 08 Aug 2020

title	07 Jul  2020	08 Aug 2020
Title1	99.998	99.561
Title2	99.700	99.760
Title3	99.989	99.973

is there a way to just append it to a csv file every month ?

For example

i will store this file on search head and on 1st of October i will like to append data for month of September in the csv before sending.

If so ,then what changes i would need to make in following search so that it just adds the latest months data -

my search .. | fillnull value=1000 response_code
| eval success=case(response_code>=400, 0, timed_out == "True", 0, response_code="",0)
| fillnull value=1 success
| eval month=strftime(_time,"%m %b %Y")
| stats count as total, sum(success) as successes by title month
| eval availability=round(100*(successes/total),3)
| chart list(availability) over title by month
| eval month=replace(month, "\d+ (.*)","\1")

Also I had added the month_num(%m) to sort the data by month but when i am trying to remove it using replace its not working.  what am i doing wrong there any idea  ?

[ITWhisperer]

You can set a bin/bucket for the stats so that aggregates over the time period e.g.

...
| eval month=strftime(_time,"%m %b %Y")
| stats count as total, sum(success) as successes by title month
...
Becomes:
...
| bin span=1mon _time
| stats count as total, sum(success) as successes by title _time
| eval month=strftime(_time,"%b %Y")
...

[thambisetty · Accepted solution]

you can use either outputcsv or outputlookup commands to store events in Splunk search head.

for example: ending of your search you could say 

| outputlookup september_results.csv

you can append these results when you are generating report in October:

yoursearch 
| append [|inputlookup september_results.csv]

Also I had added the month_num(%m) to sort the data by month but when i am trying to remove it using replace its not working.  what am i doing wrong there any idea  ?

what exactly you want ?

 ---------------------------------

up vote, if it solves your problem.

[ak9092]

Thank You @thambisetty for your response , but its not appending the results as i want.

Its creating whole new row of results.

So for e.g - i currently i have following data present in my csv file

title	07 Jul  2020	08 Aug 2020
Title1	99.998	99.561
Title2	99.700	99.760
Title3	99.989	99.973

 now when i append the new data for the month of September, then I want it to append just the Months column like below -

title	07 Jul  2020	08 Aug 2020	09 Sep 2020
Title1	99.998	99.561	99.577
Title2	99.700	99.760	99.389
Title3	99.989	99.973	99.123

but its appending the results like below.

title	07 Jul  2020	08 Aug 2020	09 Sep 2020
Title1	99.998	99.561
Title2	99.700	99.760
Title3	99.989	99.973
Title1			99.577
Title2			99.389
Title3			99.123

How do i fix this ?

[ITWhisperer]

| stats values(*) as * by title