Splunk Search

search pattern sum count if it repeated more than a day

harsush
Path Finder

Hi Team,

index=AA source=*XXX.log
| rex field=_raw "- (?<uc>U(\d{7}|\d{8})) "
| rex field=uc "(?<ul5>\d{5})$"
| rex "[^\w](?<JOB>(?<env>[A-Z0-9@_#]+)\.[A-Z0-9@_#]+\.[A-Z0-9@_#]+\.(?<app>[A-Z0-9@_#]+\.[A-Z0-9@_#]+)\.[A-Z0-9@_#]+)"
| search env=* app=* JOB=*DEV.* ul5=*11007*
| stats count as "Alert Count" by JOB
| sort - "Alert Count"

with abv search i can get count of jobs which has ul5=*11007*  for a given period of time

example for 7 days i got below output from abv search 

JOB Alert Count
DEV.JOBS.Temp1 18
DEV.JOBS.Temp2 11
DEV.JOBS.Temp3 7

from abv i know DEV.JOBS.Temp1 has count 18, But this job has repeated only on 1 day not all days in 7 days

How can i find count of a Job if it repeated only for multiple days 
example 

14-dec-2020 DEV.JOBS.Temp1 ul5=*11007* count 2
14-dec-2020 DEV.JOBS.Temp2 ul5=*11007* count 11
15-dec-2020 DEV.JOBS.Temp1 ul5=*11007* count 10
15-dec-2020 DEV.JOBS.Temp2 ul5=*11007* count 21
16-dec-2020 DEV.JOBS.Temp1 ul5=*11007* count 3
16-dec-2020 DEV.JOBS.Temp2 ul5=*11007* count 6
17-dec-2020 DEV.JOBS.Temp1 ul5=*11007* count 2
17-dec-2020 DEV.JOBS.Temp2 ul5=*11007* count 11
18-dec-2020 DEV.JOBS.Temp1 ul5=*11007* count 10
18-dec-2020 DEV.JOBS.Temp2 ul5=*11007* count 21
19-dec-2020 DEV.JOBS.Temp1 ul5=*11007* count 3
19-dec-2020 DEV.JOBS.Temp2 ul5=*11007* count 6
19-dec-2020 DEV.JOBS.Temp3 ul5=*11007* count 6

If i do search i should get out put as below  -- for the jobs it should show 5 why because its repeated for 5 days.
JOB Alert Count
DEV.JOBS.Temp1 5
DEV.JOBS.Temp2 5
DEV.JOBS.Temp3 1

Thanks

Labels (4)
0 Karma
1 Solution

harsush
Path Finder

It worked you are really Super fast @renjith_nair  Thanks -- One last help 

We can pull the day with date_wday -- Along with date is it possible to show the day also like shown in the below .

 

JOB11/12/2020(Friday)12/12/2020(Saturday)13-12-2020(Sunday)14-12-2020(Monday)15-12-2020(Tuesday)16-12-2020(Wednesday)17-12-2020(Thrusday)18-12-2020(Friday)Total
Job1 810     18
Job211 1223 10

View solution in original post

0 Karma

renjith_nair
Legend

Try

index=AA source=*XXX.log
| rex field=_raw "- (?<uc>U(\d{7}|\d{8})) "
| rex field=uc "(?<ul5>\d{5})$"
| rex "[^\w](?<JOB>(?<env>[A-Z0-9@_#]+)\.[A-Z0-9@_#]+\.[A-Z0-9@_#]+\.(?<app>[A-Z0-9@_#]+\.[A-Z0-9@_#]+)\.[A-Z0-9@_#]+)"
| search env=* app=* JOB=*DEV.* ul5=*11007*
| eval date=strftime(_time,"%d-%m-%Y")
| stats count by date,JOB
| stats count by JOB

First stats should give you a count by date and JOB and second stats should aggregate based on the JOB

 

Happy Splunking!
0 Karma

harsush
Path Finder

I never thought of putting another stats ur awesome -- Thanks a lot @renjith_nair . 

Is it possible to add a date as column and get the count for each date along with sum of all dates count @renjith_nair 

JobCount15-Dec-2016-Dec-2017-Dec-2018-Dec-2019-Dec-2020 
Job1612111
0 Karma

renjith_nair
Legend

Glad it worked. You may 👍 for the replies which helped you 🙂

Yes, its possible

Just add this after the stats command which gives you details per job and date

"Your search"
|xyseries Job,date,count| addtotals row=true
Happy Splunking!

harsush
Path Finder

It worked you are really Super fast @renjith_nair  Thanks -- One last help 

We can pull the day with date_wday -- Along with date is it possible to show the day also like shown in the below .

 

JOB11/12/2020(Friday)12/12/2020(Saturday)13-12-2020(Sunday)14-12-2020(Monday)15-12-2020(Tuesday)16-12-2020(Wednesday)17-12-2020(Thrusday)18-12-2020(Friday)Total
Job1 810     18
Job211 1223 10
0 Karma
Get Updates on the Splunk Community!

Introducing the 2024 SplunkTrust!

Hello, Splunk Community! We are beyond thrilled to announce our newest group of SplunkTrust members!  The ...

Introducing the 2024 Splunk MVPs!

We are excited to announce the 2024 cohort of the Splunk MVP program. Splunk MVPs are passionate members of ...

Splunk Custom Visualizations App End of Life

The Splunk Custom Visualizations apps End of Life for SimpleXML will reach end of support on Dec 21, 2024, ...