Splunk Search

Query for Consecutive count for missed time range

harishalipaka
Builder

[Updated]

HI All,

@ITWhisperer 

Please help me on this

I have data like below - 

HostNameLastConnected
ABC23/08/2021 10:04
ABC23/08/2021 10:34
AAA23/08/2021 12:01
AAA23/08/2021 12:32
AAA23/08/2021 13:03
AAA23/08/2021 13:34
ABC23/08/2021 17:03
AAA23/08/2021 15:01
AAA23/08/2021 15:35
ABC23/08/2021 14:00
AAA23/08/2021 21:02
AAA23/08/2021 22:03
AAA23/08/2021 20:02
ABC23/08/2021 11:02
ABC23/08/2021 11:34
ABC23/08/2021 12:02
ABC23/08/2021 13:34
AAA23/08/2021 14:02
AAA23/08/2021 14:34
ABC23/08/2021 15:04
ABC23/08/2021 16:34
ABC23/08/2021 16:05
ABC23/08/2021 22:02
ABC23/08/2021 23:36
AAA23/08/2021 11:03
ABC24/08/2021 11:36
AAA24/08/2021 12:03
ABC24/08/2021 11:00
AAA24/08/2021 12:36
ABC23/08/2021 17:36
AAA23/08/2021 20:32
AAA23/08/2021 21:32

 

Now, i want output like this 

 

HostNameTotalHoursMax_Consecutive 23/08/2021 1023/08/2021 1123/08/2021 1223/08/2021 1323/08/2021 1423/08/2021 1523/08/2021 1623/08/2021 1723/08/2021 1823/08/2021 1923/08/2021 2023/08/2021 2123/08/2021 2223/08/2021 2324/08/2021 1124/08/2021 1224/08/2021 1324/08/2021 1424/08/2021 15
ABC4223/08/2021 10:04
23/08/2021 10:34
offline23/08/2021 12:0223/08/2021 13:3423/08/2021 14:0023/08/2021 15:0423/08/2021 16:34
23/08/2021 16:05
23/08/2021 17:03
23/08/2021 17:34
offlineofflineofflineoffline23/08/2021 22:0223/08/2021 23:3624/08/2021 11:36
24/08/2021 11:00
offlineofflineofflineoffline
AAA85offline23/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
offlineofflineofflineoffline23/08/2021 20:02
23/08/2021 20:32
23/08/2021 21:02
23/08/2021 21:32
23/08/2021 22:03offlineoffline24/08/2021 12:03
24/08/2021 12:36
offlineofflineoffline

 

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 

Labels (3)
0 Karma
1 Solution

ITWhisperer
Legend

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 *

View solution in original post

0 Karma

ITWhisperer
Legend

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 *
0 Karma

harishalipaka
Builder

Hi @ITWhisperer

 

Am getting wrong consecutive count with this query

0 Karma

ITWhisperer
Legend

How does your data differ from the sample you gave?

0 Karma

harishalipaka
Builder

@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

 

| 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 *

0 Karma

ITWhisperer
Legend

Add a sort line after the first eval

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

harishalipaka
Builder

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----

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

| 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
| 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)

0 Karma

ITWhisperer
Legend

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 *
0 Karma

harishalipaka
Builder

@ITWhisperer 

 

I have updated my question, Please help me on this

| 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

0 Karma

ITWhisperer
Legend
| 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 *
0 Karma

harishalipaka
Builder

@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)

0 Karma

ITWhisperer
Legend

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 *

View solution in original post

0 Karma

harishalipaka
Builder

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

0 Karma
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!