Splunk Search

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

ak9092
Path Finder

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 -

ak9092_0-1599402401538.png

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.

Labels (1)
0 Karma
1 Solution

thambisetty
Super Champion

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.

————————————
If this helps, give a like below.

View solution in original post

0 Karma

thambisetty
Super Champion

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

column value

date_hour10
date_mday7
date_minute25
date_monthseptember
date_second23
date_wdaymonday
date_year2020
date_zonelocal

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.

————————————
If this helps, give a like below.
0 Karma

ak9092
Path Finder

Hi @thambisetty , thank you.

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

title 07 Jul 2020 08 Aug 2020

title07 Jul  202008 Aug 2020
Title199.99899.561
Title299.70099.760
Title399.98999.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  ?

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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")
...
0 Karma

thambisetty
Super Champion

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.

————————————
If this helps, give a like below.
0 Karma

ak9092
Path Finder

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

title07 Jul  202008 Aug 2020
Title199.99899.561
Title299.70099.760
Title399.98999.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 -

title07 Jul  202008 Aug 202009 Sep 2020
Title199.99899.56199.577
Title299.70099.76099.389
Title399.98999.97399.123

 

but its appending the results like below.

title07 Jul  202008 Aug 202009 Sep 2020
Title199.99899.561 
Title299.70099.760 
Title399.98999.973 
Title1  99.577
Title2  99.389
Title3  99.123

 

How do i fix this ?

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
| stats values(*) as * by title
Get Updates on the Splunk Community!

Is there an add-on for the Cisco Meraki devices?

We have many Cisco Meraki devices sending data via syslog to Splunk. Is there an add-on for ...

Should our Deployment Servers have the Search Head server role on them?

all of our stuff is on premcurrently our dedicated Deployment Servers also have the Search Head role on them, ...

Why am I unable to create dropdown static option that is "All" static options ...

Hi, I am trying to get a static option that is "All" the individual static options combined.  The mCode ...