[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 | 4 | 2 | 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 | offline | offline | offline | 23/08/2021 22:02 | 23/08/2021 23:36 | 24/08/2021 11:36 24/08/2021 11:00 | offline | offline | offline | offline |
AAA | 8 | 5 | 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 | offline | offline | 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 | offline | 24/08/2021 12:03 24/08/2021 12:36 | offline | offline | 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
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 *
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 *
How does your data differ from the sample you gave?
@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 *
Add a sort line after the first eval
| eval LastConnected=strptime(LastConnected,"%d/%m/%Y %H")
| sort 0 HostName LastConnected
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)
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 *
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
| 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 *
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)
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 *
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