Splunk Search

Query for Consecutive count for missed time range

harishalipaka
Motivator

[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)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

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

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
Motivator

Hi @ITWhisperer

 

Am getting wrong consecutive count with this query

Thanks
Harish
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

How does your data differ from the sample you gave?

0 Karma

harishalipaka
Motivator

@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

ITWhisperer
SplunkTrust
SplunkTrust

Add a sort line after the first eval

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

harishalipaka
Motivator

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

ITWhisperer
SplunkTrust
SplunkTrust

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
Motivator

@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

ITWhisperer
SplunkTrust
SplunkTrust
| 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
Motivator

@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

ITWhisperer
SplunkTrust
SplunkTrust

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

harishalipaka
Motivator

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
Get Updates on the Splunk Community!

Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...