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!

Built-in Service Level Objectives Management to Bridge the Gap Between Service & ...

Wednesday, May 29, 2024  |  11AM PST / 2PM ESTRegister now and join us to learn more about how you can ...

Get Your Exclusive Splunk Certified Cybersecurity Defense Engineer at Splunk .conf24 ...

We’re excited to announce a new Splunk certification exam being released at .conf24! If you’re headed to Vegas ...

Share Your Ideas & Meet the Lantern team at .Conf! Plus All of This Month’s New ...

Splunk Lantern is Splunk’s customer success center that provides advice from Splunk experts on valuable data ...