How to find the count of events between the 2 Events in a day ?

[Real_captain]

Hi 

Can you please help me to find out how we can find the count of events between the 2 events in SPLUNK. 

Example , i have to find the count of events (RPWARDA , SPWARAA , SPWARRA ) between events IDJO20P and PIDZJEA. 

IDJO20P to PIDZJEA will be considered a day and i have to find count of events (RPWARDA , SPWARAA , SPWARRA ) in a day. 

SPLUNk Query to find the events:

index=events_prod_cdp_penalty_esa source="SYSLOG"
(TERM(NIDF=RPWARDA) OR TERM(NIDF=SPWARAA) OR TERM(NIDF=SPWARRA) OR PIDZJEA OR IDJO20P)

[Real_captain]

Thanks gcusello. 

This solution really works when we have to extract the data of previous days. 

Is it possible to have the stats of the current date when the startswith="IDJO20P" arrived but endswith="PIDZJEA" is still not received ??? 

[gcusello]

Hi @Real_captain ,

try adding keeporphans =true option to the transaction command (as you can see at https://docs.splunk.com/Documentation/SplunkCloud/9.1.2312/SearchReference/Transaction), it should run,

index=events_prod_cdp_penalty_esa source="SYSLOG"
(TERM(NIDF=RPWARDA) OR TERM(NIDF=SPWARAA) OR TERM(NIDF=SPWARRA) OR PIDZJEA OR IDJO20P)
| transaction startswith="IDJO20P" endswith="PIDZJEA" keeporphans=True
| bin span=1d _time
| chart sum(eventcount) AS eventcount OVER _time BY NIDF

otherwise use only startswith option and not also endswith option.

Ciao.

Giuseppe

[Real_captain]

Hi

Thanks for the update. 

But we cannot use the query without endswith because without endswith it will give all the events of the day which was created after the event PIDZJEA. 

1. is it possible to use both startswith and endswith and get the records of the current day ? 

2. also is it possible to get the count of events which are generated after the PIDZJEA (endswith) on the same day for every day?? 

Expected result.

Current query : 

index=events_prod_cdp_penalty_esa source="SYSLOG"
(TERM(NIDF=RPWARDA) OR TERM(NIDF=SPWARAA) OR TERM(NIDF=SPWARRA) OR PIDZJEA OR IDJO20P)
| rex field=TEXT "NIDF=(?<file>[^\\s]+)"
| transaction startswith="IDJO20P" endswith="PIDZJEA" keeporphans=True
| bin span=1d _time
| chart sum(eventcount) AS eventcount OVER _time BY file

Result: 

[gcusello]

Hi @Real_captain,

the only way is the append command, with another transaction, but you'll have a very slow search:

index=events_prod_cdp_penalty_esa source="SYSLOG"
(TERM(NIDF=RPWARDA) OR TERM(NIDF=SPWARAA) OR TERM(NIDF=SPWARRA) OR PIDZJEA OR IDJO20P)
| rex field=TEXT "NIDF=(?<file>[^\\s]+)"
| transaction startswith="IDJO20P" endswith="PIDZJEA" keeporphans=True
| bin span=1d _time
| chart sum(eventcount) AS eventcount OVER _time BY file
| append [
     index=events_prod_cdp_penalty_esa source="SYSLOG"
     (TERM(NIDF=RPWARDA) OR TERM(NIDF=SPWARAA) OR TERM(NIDF=SPWARRA) OR PIDZJEA OR IDJO20P)
     | rex field=TEXT "NIDF=(?<file>[^\\s]+)"
     | transaction startswith=PIDZJEA" keeporphans=True
     | bin span=1d _time
     | chart sum(eventcount) AS "count after PIDZJEA" BY _time ]

Ciao.

Giuseppe

[Real_captain]

Hi @gcusello 

I am not able to use the append command as suggested by you. Facing the below error: 

[Real_captain]

Hi @gcusello 

I have corrected the search query but the results are like below: 

Possible to have records for the date in the same line. 

Query : 

index=events_prod_cdp_penalty_esa source="SYSLOG" (TERM(NIDF=RPWARDA) OR TERM(NIDF=SPWARAA) OR TERM(NIDF=SPWARRA) OR PIDZJEA OR IDJO20P)
| rex field=TEXT "NIDF=(?<file>[^\\s]+)"
| transaction startswith="IDJO20P" endswith="PIDZJEA" keeporphans=True
| bin span=1d _time
| chart sum(eventcount) AS eventcount OVER _time BY file
| append [ search index=events_prod_cdp_penalty_esa source="SYSLOG" (TERM(NIDF=RPWARDA) OR TERM(NIDF=SPWARAA) OR TERM(NIDF=SPWARRA) OR PIDZJEA OR IDJO20P)
| rex field=TEXT "NIDF=(?<file>[^\\s]+)"
| transaction startswith="PIDZJEA" keeporphans=True
| bin span=1d _time
| chart sum(eventcount) AS "count after PIDZJEA" BY _time ]

[gcusello]

Hi @Real_captain,

sorry for the previous message: I forgot the search command!

,anyway, please try this:

index=events_prod_cdp_penalty_esa source="SYSLOG" (TERM(NIDF=RPWARDA) OR TERM(NIDF=SPWARAA) OR TERM(NIDF=SPWARRA) OR PIDZJEA OR IDJO20P)
| rex field=TEXT "NIDF=(?<file>[^\\s]+)"
| transaction startswith="IDJO20P" endswith="PIDZJEA" keeporphans=True
| bin span=1d _time
| stats sum(eventcount) AS eventcount BY _time file
| append [ search index=events_prod_cdp_penalty_esa source="SYSLOG" (TERM(NIDF=RPWARDA) OR TERM(NIDF=SPWARAA) OR TERM(NIDF=SPWARRA) OR PIDZJEA OR IDJO20P)
| rex field=TEXT "NIDF=(?<file>[^\\s]+)"
| transaction startswith="PIDZJEA" keeporphans=True
| bin span=1d _time
| stats sum(eventcount) AS eventcount BY _time 
| eval file="count after PIDZJEA"
| table file eventcount _time]
| chart sum(eventcount) AS eventcount OVER _time BY file

Ciao.

Giuseppe

[Real_captain]

Hi @gcusello 

With the updated query , i am not able to fetch the data of the current date. 

Can you please help me to add the data of the current date too. 

Query: 
index=events_prod_cdp_penalty_esa source="SYSLOG" (TERM(NIDF=RPWARDA) OR TERM(NIDF=SPWARAA) OR TERM(NIDF=SPWARRA) OR PIDZJEA OR IDJO20P)
| rex field=TEXT "NIDF=(?<file>[^\\s]+)"
| transaction startswith="IDJO20P" endswith="PIDZJEA" keeporphans=True
| bin span=1d _time
| stats sum(eventcount) AS eventcount BY _time file
| append [ search index=events_prod_cdp_penalty_esa source="SYSLOG" (TERM(NIDF=RPWARDA) OR TERM(NIDF=SPWARAA) OR TERM(NIDF=SPWARRA) OR PIDZJEA OR IDJO20P)
| rex field=TEXT "NIDF=(?<file>[^\\s]+)"
| transaction startswith="PIDZJEA" endswith="IDJO20P" keeporphans=True
| bin span=1d _time
| stats sum(eventcount) AS eventcount BY _time
| eval file="count after PIDZJEA"
| table file eventcount _time]
| chart sum(eventcount) AS eventcount OVER _time BY file

Extract :

Also , is it possible to have a visual graph like below to show the details in the graph : 

IN_per_24h = count of RPWARDA between IDJO20P and PIDZJEA of the day. 

Out_per_24h =  count of SPWARAA + SPWARRA between IDJO20P and PIDZJEA of the day. 

Backlog = count after PIDZJEA  of the day. 

[Real_captain]

Hi @gcusello 

With the updated query , i am not able to fetch the data of the current date. 

Can you please help me to add the data of the current date too. 

Query: 

index=events_prod_cdp_penalty_esa source="SYSLOG" (TERM(NIDF=RPWARDA) OR TERM(NIDF=SPWARAA) OR TERM(NIDF=SPWARRA) OR PIDZJEA OR IDJO20P)
| rex field=TEXT "NIDF=(?<file>[^\\s]+)"
| transaction startswith="IDJO20P" endswith="PIDZJEA" keeporphans=True
| bin span=1d _time
| stats sum(eventcount) AS eventcount BY _time file
| append [ search index=events_prod_cdp_penalty_esa source="SYSLOG" (TERM(NIDF=RPWARDA) OR TERM(NIDF=SPWARAA) OR TERM(NIDF=SPWARRA) OR PIDZJEA OR IDJO20P)
| rex field=TEXT "NIDF=(?<file>[^\\s]+)"
| transaction startswith="PIDZJEA" endswith="IDJO20P" keeporphans=True
| bin span=1d _time
| stats sum(eventcount) AS eventcount BY _time
| eval file="count after PIDZJEA"
| table file eventcount _time]
| chart sum(eventcount) AS eventcount OVER _time BY file

Extract :

Also , is it possible to have a visual graph like below to show the details in the graph : 

IN_per_24h = count of RPWARDA between IDJO20P and PIDZJEA of the day. 

Out_per_24h =  count of SPWARAA + SPWARRA between IDJO20P and PIDZJEA of the day. 

Backlog = count after PIDZJEA  of the day. 

[gcusello]

Hi @Real_captain ,

the search seems to be correct and you should have results also for the present time, are you sure that you have data for the last day that match the conditions?

Anyway, your solution with append is subjected to the limit of 50,000 results because it's a subsearch.

About the graph, you should be ableto plot a graph with your search, see in the Visualization tab or in a panel.

Ciao.

Giuseppe

[Real_captain]

Hi 

Thanks for the response. But it gives me the result like below : 

I want to have the results as below : 

[gcusello]

Hi @Real_captain,

this is one the few cases to use transaction command:

index=events_prod_cdp_penalty_esa source="SYSLOG"
(TERM(NIDF=RPWARDA) OR TERM(NIDF=SPWARAA) OR TERM(NIDF=SPWARRA) OR PIDZJEA OR IDJO20P)
| transaction startswith="IDJO20P" endswith="PIDZJEA"
| table _time eventcount

Ciao.

Giuseppe

[gcusello]

Hi @Real_captain ,

please try something like this:

index=events_prod_cdp_penalty_esa source="SYSLOG"
(TERM(NIDF=RPWARDA) OR TERM(NIDF=SPWARAA) OR TERM(NIDF=SPWARRA) OR PIDZJEA OR IDJO20P)
| transaction startswith="IDJO20P" endswith="PIDZJEA"
| bin span=1d _time
| chart sum(eventcount) AS eventcount OVER _time BY NIDF

Ciao.

Giuseppe