Splunk Search
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Ask a Question
Solved! Jump to solution

Query for Consecutive count for missed time range

harishalipaka
Motivator
‎08-23-2021 04:42 AM

[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 

Thanks
Harish
Labels (3)
Labels
0 Karma
Reply
1 Solution
Solved! Jump to solution
Solution

ITWhisperer
SplunkTrust
SplunkTrust
‎08-24-2021 02:17 PM

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
Reply
Solved! Jump to solution

ITWhisperer
SplunkTrust
SplunkTrust
‎08-23-2021 06:03 AM

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
Reply
Solved! Jump to solution

harishalipaka
Motivator
‎08-23-2021 07:04 AM

Hi @ITWhisperer

 

Am getting wrong consecutive count with this query

Thanks
Harish
0 Karma
Reply
Solved! Jump to solution

ITWhisperer
SplunkTrust
SplunkTrust
‎08-23-2021 07:17 AM

How does your data differ from the sample you gave?

0 Karma
Reply
Solved! Jump to solution

harishalipaka
Motivator
‎08-24-2021 12:00 AM

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

Thanks
Harish
0 Karma
Reply
Solved! Jump to solution

ITWhisperer
SplunkTrust
SplunkTrust
‎08-24-2021 01:21 AM

Add a sort line after the first eval

| eval LastConnected=strptime(LastConnected,"%d/%m/%Y %H")
| sort 0 HostName LastConnected
0 Karma
Reply
Solved! Jump to solution

harishalipaka
Motivator
‎08-24-2021 03:07 AM

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)

Thanks
Harish
0 Karma
Reply
Solved! Jump to solution

ITWhisperer
SplunkTrust
SplunkTrust
‎08-24-2021 05:29 AM

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
Reply
Solved! Jump to solution

harishalipaka
Motivator
‎08-24-2021 07:24 AM

@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

Thanks
Harish
0 Karma
Reply
Solved! Jump to solution

ITWhisperer
SplunkTrust
SplunkTrust
‎08-24-2021 07:43 AM 
| 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
Reply
Solved! Jump to solution

harishalipaka
Motivator
‎08-24-2021 07:51 AM

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

Thanks
Harish
0 Karma
Reply
Solved! Jump to solution
Solution

ITWhisperer
SplunkTrust
SplunkTrust
‎08-24-2021 02:17 PM

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 *
0 Karma
Reply
Solved! Jump to solution

harishalipaka
Motivator
‎08-25-2021 07:19 AM

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

Thanks
Harish
0 Karma
Reply
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, ...