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.
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.
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.
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 ?
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")
...
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.
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 ?
| stats values(*) as * by title