topic Re: Query for Consecutive count for missed time range in Splunk Search

Query for Consecutive count for missed time range

harishalipaka — Tue, 24 Aug 2021 14:18:16 GMT

[Updated]

HI All,

Please help me on this

I have data like below -

HostName	LastConnected
ABC	23/08/2021 10:04
ABC	23/08/2021 10:34
AAA	23/08/2021 12:01
AAA	23/08/2021 12:32
AAA	23/08/2021 13:03
AAA	23/08/2021 13:34
ABC	23/08/2021 17:03
AAA	23/08/2021 15:01
AAA	23/08/2021 15:35
ABC	23/08/2021 14:00
AAA	23/08/2021 21:02
AAA	23/08/2021 22:03
AAA	23/08/2021 20:02
ABC	23/08/2021 11:02
ABC	23/08/2021 11:34
ABC	23/08/2021 12:02
ABC	23/08/2021 13:34
AAA	23/08/2021 14:02
AAA	23/08/2021 14:34
ABC	23/08/2021 15:04
ABC	23/08/2021 16:34
ABC	23/08/2021 16:05
ABC	23/08/2021 22:02
ABC	23/08/2021 23:36
AAA	23/08/2021 11:03
ABC	24/08/2021 11:36
AAA	24/08/2021 12:03
ABC	24/08/2021 11:00
AAA	24/08/2021 12:36
ABC	23/08/2021 17:36
AAA	23/08/2021 20:32
AAA	23/08/2021 21:32

Now, i want output like this

HostName

TotalHours

Max_Consecutive

23/08/2021 10

23/08/2021 11

23/08/2021 12

23/08/2021 13

23/08/2021 14

23/08/2021 15

23/08/2021 16

23/08/2021 17

23/08/2021 18

23/08/2021 19

23/08/2021 20

23/08/2021 21

23/08/2021 22

23/08/2021 23

24/08/2021 11

24/08/2021 12

24/08/2021 13

24/08/2021 14

24/08/2021 15

ABC

23/08/2021 10:04
23/08/2021 10:34

offline

23/08/2021 12:02

23/08/2021 13:34

23/08/2021 14:00

23/08/2021 15:04

23/08/2021 16:34
23/08/2021 16:05

23/08/2021 17:03
23/08/2021 17:34

offline

23/08/2021 22:02

23/08/2021 23:36

24/08/2021 11:36
24/08/2021 11:00

offline

AAA

offline

23/08/2021 11:02
23/08/2021 11:34

23/08/2021 12:01
23/08/2021 12:32

23/08/2021 13:03
23/08/2021 13:34

23/08/2021 14:02
23/08/2021 14:34

23/08/2021 15:01
23/08/2021 15:35

offline

23/08/2021 20:02
23/08/2021 20:32

23/08/2021 21:02
23/08/2021 21:32

23/08/2021 22:03

offline

24/08/2021 12:03
24/08/2021 12:36

offline

Note:- I have more than 2 lakhs records, and if user select one week data it should be work for a week

If it is connected complete hour ,then it is online - means two times in hour

We if mvcount >=2 then it is online , we need to count

If it is 1 - no need count keep as it is

0 - offlilne

Thank you in advance

Re: Query for Consecutive count for missed time range

ITWhisperer — Mon, 23 Aug 2021 13:03:49 GMT

Try something like this

| makeresults | eval _raw="HostName,LastConnected AAA,23/08/2021 11 AAA,23/08/2021 12 AAA,23/08/2021 13 AAA,23/08/2021 14 AAA,23/08/2021 15 AAA,23/08/2021 20 AAA,23/08/2021 21 AAA,23/08/2021 22 ABC,23/08/2021 10 ABC,23/08/2021 11 ABC,23/08/2021 12 ABC,23/08/2021 13 ABC,23/08/2021 14 ABC,23/08/2021 15 ABC,23/08/2021 16 ABC,23/08/2021 17 ABC,23/08/2021 22 ABC,23/08/2021 23" | multikv forceheader=1 | table HostName LastConnected | eval LastConnected=strptime(LastConnected,"%d/%m/%Y %H") | streamstats values(LastConnected) as previousConnected by HostName window=1 current=f | eval concurrent=if(LastConnected-previousConnected = 60*60,1,0) | eval span=if(concurrent=0,1,0) | streamstats sum(span) as span by HostName | eventstats count as Total by HostName | eventstats count by HostName span | eventstats max(count) as Highest by HostName | eval combined=HostName."!".Total."!".Highest | eval _time=LastConnected | timechart span=1h count by combined | foreach * [| eval <<FIELD>>=if('<<FIELD>>'=0,"Offline","connected")] | eval time=strftime(_time,"%d/%m/%Y %H") | fields - _span _time | transpose 0 header_field=time | eval column=split(column,"!") | eval HostName=mvindex(column,0) | eval Total=mvindex(column,1) | eval Highest=mvindex(column,2) | fields - column | table HostName Total Highest *

Re: Query for Consecutive count for missed time range

harishalipaka — Mon, 23 Aug 2021 14:04:05 GMT

Hi @ITWhisperer

Am getting wrong consecutive count with this query

Re: Query for Consecutive count for missed time range

ITWhisperer — Mon, 23 Aug 2021 14:17:29 GMT

How does your data differ from the sample you gave?

Re: Query for Consecutive count for missed time range

harishalipaka — Tue, 24 Aug 2021 08:00:32 GMT

@ITWhisperer I have different dates like below ----

------------------

| makeresults
| eval _raw="HostName,LastConnected
AAA,23/08/2021 11
ABC,23/08/2021 12
AAA,23/08/2021 12
AAA,23/08/2021 13
ABC,23/08/2021 11
AAA,23/08/2021 14
AAA,23/08/2021 21
AAA,24/08/2021 22
ABC,23/08/2021 10
AAA,23/08/2021 20
ABC,23/08/2021 13
ABC,23/08/2021 14
ABC,23/08/2021 15
ABC,23/08/2021 16
ABC,24/08/2021 17
AAA,23/08/2021 15
ABC,24/08/2021 22
ABC,23/08/2021 23"
| multikv forceheader=1
| table HostName LastConnected

Re: Query for Consecutive count for missed time range

ITWhisperer — Tue, 24 Aug 2021 08:21:05 GMT

Add a sort line after the first eval

| eval LastConnected=strptime(LastConnected,"%d/%m/%Y %H") | sort 0 HostName LastConnected

Re: Query for Consecutive count for missed time range

harishalipaka — Tue, 24 Aug 2021 10:07:46 GMT

Updated - @ITWhisperer Sorry, I have missed one condition, here if hours count is grater then or equal 2 , then it is online else we wont count----

------------------

| eval LastConnected1=strptime(LastConnected,"%d/%m/%Y %H") |stats list(LastConnected) as LastConnected by HostName LastConnected1
| streamstats values(LastConnected1) as previousConnected by HostName window=1 current=f
| eval concurrent=if(LastConnected1-previousConnected = 60*60,1,0)
| eval span=if(concurrent=0 AND mvcount(LastConnected)>=2,1,0)

Re: Query for Consecutive count for missed time range

ITWhisperer — Tue, 24 Aug 2021 12:29:33 GMT

So only hours where there are at least two entries are to be considered?

| makeresults | eval _raw="HostName,LastConnected AAA,23/08/2021 11 AAA,23/08/2021 11 ABC,23/08/2021 12 AAA,23/08/2021 11 AAA,23/08/2021 12 AAA,23/08/2021 12 AAA,23/08/2021 13 AAA,23/08/2021 13 ABC,23/08/2021 11 AAA,23/08/2021 14 AAA,23/08/2021 21 AAA,24/08/2021 22 AAA,24/08/2021 22 ABC,23/08/2021 10 ABC,23/08/2021 10 AAA,23/08/2021 20 ABC,23/08/2021 13 ABC,23/08/2021 13 ABC,23/08/2021 14 ABC,23/08/2021 14 ABC,23/08/2021 15 ABC,23/08/2021 15 ABC,23/08/2021 16 ABC,24/08/2021 17 AAA,23/08/2021 15 ABC,24/08/2021 22 ABC,23/08/2021 23" | multikv forceheader=1 | table HostName LastConnected | eval LastConnected1=strptime(LastConnected,"%d/%m/%Y %H") | stats list(LastConnected) as LastConnected by HostName LastConnected1 | where mvcount(LastConnected)>=2 | sort 0 HostName LastConnected1 | streamstats values(LastConnected1) as previousConnected by HostName window=1 current=f | eval concurrent=if(LastConnected1-previousConnected = 60*60,1,0) | eval span=if(concurrent=0,1,0) | streamstats sum(span) as span by HostName | eventstats count as Total by HostName | eventstats count by HostName span | eventstats max(count) as Highest by HostName | eval combined=HostName."!".Total."!".Highest | eval _time=LastConnected1 | timechart span=1h count by combined | foreach * [| eval <<FIELD>>=if('<<FIELD>>'=0,"Offline","connected")] | eval time=strftime(_time,"%d/%m/%Y %H") | fields - _span _time | transpose 0 header_field=time | eval column=split(column,"!") | eval HostName=mvindex(column,0) | eval Total=mvindex(column,1) | eval Highest=mvindex(column,2) | fields - column | table HostName Total Highest *

Re: Query for Consecutive count for missed time range

harishalipaka — Tue, 24 Aug 2021 14:24:15 GMT

@ITWhisperer

I have updated my question, Please help me on this

Re: Query for Consecutive count for missed time range

ITWhisperer — Tue, 24 Aug 2021 14:43:18 GMT

| makeresults | eval _raw="HostName,LastConnected ABC,23/08/2021 10:04 ABC,23/08/2021 10:34 AAA,23/08/2021 12:01 AAA,23/08/2021 12:32 AAA,23/08/2021 13:03 AAA,23/08/2021 13:34 ABC,23/08/2021 17:03 AAA,23/08/2021 15:01 AAA,23/08/2021 15:35 ABC,23/08/2021 14:00 AAA,23/08/2021 21:02 AAA,23/08/2021 22:03 AAA,23/08/2021 20:02 ABC,23/08/2021 11:02 ABC,23/08/2021 11:34 ABC,23/08/2021 12:02 ABC,23/08/2021 13:34 AAA,23/08/2021 14:02 AAA,23/08/2021 14:34 ABC,23/08/2021 15:04 ABC,23/08/2021 16:34 ABC,23/08/2021 16:05 ABC,23/08/2021 22:02 ABC,23/08/2021 23:36 AAA,23/08/2021 11:03 ABC,24/08/2021 11:36 AAA,24/08/2021 12:03 ABC,24/08/2021 11:00 AAA,24/08/2021 12:36 ABC,23/08/2021 17:36 AAA,23/08/2021 20:32 AAA,23/08/2021 21:32" | multikv forceheader=1 | table HostName LastConnected | eval LastConnected1=strptime(LastConnected,"%d/%m/%Y %H") | stats list(LastConnected) as LastConnected by HostName LastConnected1 | where mvcount(LastConnected)>=2 | sort 0 HostName LastConnected1 | streamstats values(LastConnected1) as previousConnected by HostName window=1 current=f | eval concurrent=if(LastConnected1-previousConnected = 60*60,1,0) | eval span=if(concurrent=0,1,0) | streamstats sum(span) as span by HostName | eventstats count as Total by HostName | eventstats count by HostName span | eventstats max(count) as Highest by HostName | eval combined=HostName."!".Total."!".Highest | eval _time=LastConnected1 | timechart span=1h list(LastConnected) as LastConnected by combined | foreach * [| eval <<FIELD>>=if(isnull('<<FIELD>>'),"Offline",'<<FIELD>>')] | eval time=strftime(_time,"%d/%m/%Y %H") | fields - _span _time | transpose 0 header_field=time | eval column=split(column,"!") | eval HostName=mvindex(column,0) | eval Total=mvindex(column,1) | eval Highest=mvindex(column,2) | fields - column | table HostName Total Highest *

Re: Query for Consecutive count for missed time range

harishalipaka — Tue, 24 Aug 2021 14:51:43 GMT

@ITWhisperer

I need all data where its connected once also.

Can you pls check my question again.

We need to remove this condition - | where mvcount(LastConnected)>=2

I think we need apply here - | eval span=if(concurrent=0 AND mvcount(LastConnected)>=2,1,0)

Re: Query for Consecutive count for missed time range

ITWhisperer — Tue, 24 Aug 2021 21:17:06 GMT

It's not quite that simple

| makeresults | eval _raw="HostName,LastConnected ABC,23/08/2021 10:04 ABC,23/08/2021 10:34 AAA,23/08/2021 12:01 AAA,23/08/2021 12:32 AAA,23/08/2021 13:03 AAA,23/08/2021 13:34 ABC,23/08/2021 17:03 AAA,23/08/2021 15:01 AAA,23/08/2021 15:35 ABC,23/08/2021 14:00 AAA,23/08/2021 21:02 AAA,23/08/2021 22:03 AAA,23/08/2021 20:02 ABC,23/08/2021 11:02 ABC,23/08/2021 11:34 ABC,23/08/2021 12:02 ABC,23/08/2021 13:34 AAA,23/08/2021 14:02 AAA,23/08/2021 14:34 ABC,23/08/2021 15:04 ABC,23/08/2021 16:34 ABC,23/08/2021 16:05 ABC,23/08/2021 22:02 ABC,23/08/2021 23:36 AAA,23/08/2021 11:03 ABC,24/08/2021 11:36 AAA,24/08/2021 12:03 ABC,24/08/2021 11:00 AAA,24/08/2021 12:36 ABC,23/08/2021 17:36 AAA,23/08/2021 20:32 AAA,23/08/2021 21:32" | multikv forceheader=1 | table HostName LastConnected | eval LastConnected1=strptime(LastConnected,"%d/%m/%Y %H") | stats list(LastConnected) as LastConnected by HostName LastConnected1 | eval online=if(mvcount(LastConnected)>=2,1,0) | sort 0 HostName LastConnected1 | streamstats values(LastConnected1) as previousConnected values(online) as previousOnline by HostName window=1 current=f | eval concurrent=if(LastConnected1-previousConnected = 60*60 AND previousOnline=1,1,0) | eval span=if(concurrent + online=2,0,1) | streamstats sum(span) as span by HostName | eventstats sum(online) as Total by HostName | eventstats count by HostName span | eventstats max(count) as Highest by HostName | eval combined=HostName."!".Total."!".Highest | eval _time=LastConnected1 | timechart span=1h list(LastConnected) as LastConnected by combined | foreach * [| eval <<FIELD>>=if(isnull('<<FIELD>>'),"Offline",'<<FIELD>>')] | eval time=strftime(_time,"%d/%m/%Y %H") | fields - _span _time | transpose 0 header_field=time | eval column=split(column,"!") | eval HostName=mvindex(column,0) | eval Total=mvindex(column,1) | eval Highest=mvindex(column,2) | fields - column | table HostName Total Highest *

Re: Query for Consecutive count for missed time range

harishalipaka — Thu, 02 Sep 2021 06:51:52 GMT

Thanks for your help @ITWhisperer

Here am facing one limit issue with list command.

I did few changes from my side and achieved it.

Question :- Can help me to get the count of how many times connected more than 8 hours.?

@ITWhisperer pls help me on this