How to get repeated event for the same host and instance over the period?

[twh1]

I have some events on my server. I want to get events which are occurring repeatedly for same host and same instance for 1 or 2 weeks.

DB_HOST_NAME    DB_INSTANCE_NAME    EVENT   TIME_WAITED WAIT_CLASS  Timestamp
--------------   -----------------   -------  ---------   -------------  ----------
ABC xyz on CPU  8527.248862 Application 24/SEP/2018 16:00:26 UTC
ACD yxz lock contention 1572.800453 Application 24/SEP/2018 16:00:35 UTC
ABC xyz on CPU  880.093816  Application 24/SEP/2018 16:00:29 UTC
ASZ xzy on CPU  Concurrency 24/SEP/2018 16:00:33 UTC

My requirement is like: if we take set of host, instance & event(n3pvdo1001, cogcsprd, enq: TX - row lock contention ) and this combination is occurring every day for last 2 weeks or more, then status should be Red. If this combination is occurring every day for last 1 weeks, then status should be Amber, if this combination is occurring less than 7 days, then status as Green.

[msivill_splunk]

Not sure I've got what you are looking for, assuming the date range (1 week) is done via date picker. Everything up to the "| table" command is generating some test data.

This will initially do a host/event combinations count, filtering out host/event combination count above 1 (so at least one match), then count how many at least one matches per host/event combinations.

| makeresults count=7
| streamstats count 
| eval _time = _time - (count * 60 ) 
| eval host_name = CASE ( count==1,"host1",count==2,"host2",count==3,"host3",count==4,"host1",count==5,"host2",count==6,"host1",count==7,"host3") 
| eval event_msg = CASE ( count==1,"event a",count==2,"event b",count==3,"event c",count==4,"event a",count==5,"event b",count==6,"event a",count==7,"event a") 
| table _time, host_name, event_msg 
| stats count as events_per_host by host_name, event_msg 
| where events_per_host > 1 
| stats count as multiple_events_per_host_count 
| eval status = CASE ( multiple_events_per_host_count<=0, "Green", multiple_events_per_host_count==1, "Amber", multiple_events_per_host_count>=2, "Red" ) 
| fields status

[twh1]

@msivill_splunk ,
My requirement is like, if we take set of host, instance & event(n3pvdo1001, cogcsprd, enq: TX - row lock contention ) and this combination is occurring every day for last 2 weeks or more, then status should be Red. If this combination is occurring every day for last 1 weeks, then status should be Amber, if this combination is occurring less than 7 days, then status as Green.

[msivill_splunk]

I think this might get you closer. As I've had to generate the data in the SPL I'm not sure I have every edge case covered. So please test it thoroughly with your data set. I've also made my weeks 2 days to reduce the amount of data to create so this will need to be changed accordingly.

| makeresults count=12 
| streamstats count 
| eval _time = relative_time(now(), "+1d@d") - ( count * 60 * 60 * 8 ) + 1 
| eval host_name = CASE ( count==1,"1",count==2,"2",count==3,"3",count==4,"1",count==5,"2",count==6,"3",count==7,"1",count==8,"2",count==9,"3",count==10,"1",count==11,"2",count==12,"3") 
| eval event_msg = CASE ( count==1,"a",count==2,"b",count==3,"c",count==4,"a",count==5,"b",count==6,"x",count==7,"a",count==8,"b",count==9,"y",count==10,"a",count==11,"x",count==12,"z") 
| bin _time span=1d 
| stats count as daily_count by _time, host_name, event_msg 
| eval has_daily_count = if(daily_count >= 1, 1, 0 ) 
| bin _time span=2d 
| stats sum(has_daily_count) as weekly_count by _time, host_name, event_msg 
| eval has_full_weekly_count = if(weekly_count >= 2, 1, 0 ) 
| bin _time span=4d 
| stats sum(has_full_weekly_count) as full_weekly_count by _time, host_name, event_msg 
| eval status = CASE (full_weekly_count==0, "Green", full_weekly_count==1, "Amber",full_weekly_count==2, "Red")

[twh1]

Hi @msivill_splunk ,
I have have a question on above query.

You have used below line for getting daily count.
| bin _time span=1d
| stats count as daily_count by _time, host_name, event_msg

Why you are using below lines.

| bin _time span=2d 

OR

| bin _time span=4d 

Is above query will print amber if same data is repeating for 2 day then status will be amber and if repeated for 4 days status will be Red. Am I correct?

[msivill_splunk]

Correct in order to reduce the generated test data I made the week 2 days long.

It always takes a bit of time to create the data if you are able to generate the data on the questions itself it is makes it easier for people to try queries against it.

[msivill_splunk]

Do you need help uploading the data to Splunk and/or the Splunk query?

[twh1]

Hi @msivill_splunk ,
I need Splunk query. This data is already in splunk.