I have a lookup with file_type name and a threshold per hour as the headers, like below. I would like the hour headers converted to a new column "time_interval"
Current lookup:
file_type,0:00,1:00,2:00,3:00,4:00,5:00,6:00,7:00,8:00,9:00,10:00,11:00,12:00,13:00,14:00,15:00,16:00,17:00,18:00,19:00,20:00,21:00,22:00,23:00
TYPE1,0,0,0,0,0,0,0,0,8,8,8,8,8,8,8,8,8,8,0,0,0,0,0,0
TYPE2,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1
TYPE3,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1
TYPE4,0,0,0,0,0,0,0,0,8,8,8,8,8,8,8,8,8,8,0,0,0,0,0,0
TYPE5,0,0,0,0,0,0,0,0,8,8,8,8,8,8,8,8,8,8,0,0,0,0,0,0
TYPE6,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1
New lookup:
file_type time_interval file_threshold
TYPE1 0:00 0
TYPE1 1:00 0
TYPE1 2:00 0
TYPE1 3:00 0
TYPE1 4:00 0
TYPE1 5:00 0
TYPE1 6:00 0
TYPE1 7:00 0
TYPE1 8:00 8
TYPE1 9:00 8
TYPE1 10:00 8
TYPE1 11:00 8
TYPE1 12:00 8
TYPE1 13:00 8
TYPE1 14:00 8
TYPE1 15:00 8
TYPE1 16:00 8
TYPE1 17:00 8
TYPE1 18:00 0
TYPE1 19:00 0
TYPE1 20:00 0
TYPE1 21:00 0
TYPE1 22:00 0
TYPE1 23:00 0
Can you please try this?
| inputlookup YOUR_LOOKUP
| eval time_interval="",file_threshold=""
| foreach *
[
eval time_interval=if("<<FIELD>>"!="file_type" AND "<<FIELD>>"!="time_interval" AND "<<FIELD>>"!="file_threshold",time_interval+","+"<<FIELD>>",time_interval),
file_threshold=if("<<FIELD>>"!="file_type" AND "<<FIELD>>"!="time_interval" AND "<<FIELD>>"!="file_threshold",file_threshold+","+'<<FIELD>>',file_threshold)
]
| eval time_interval=substr(time_interval,2),time_interval=split(time_interval,","), file_threshold =substr(file_threshold,2),file_threshold=split(file_threshold,",")
| eval t=mvzip(time_interval,file_threshold)
| table file_type t
| stats count by file_type t | eval time_interval = mvindex(split(t,","),0),file_threshold= mvindex(split(t,","),1)
| table file_type time_interval,file_threshold
My Sample Search :
| makeresults
| eval _raw="file_type,0:00,1:00,2:00,3:00,4:00,5:00,6:00,7:00,8:00,9:00,10:00,11:00,12:00,13:00,14:00,15:00,16:00,17:00,18:00,19:00,20:00,21:00,22:00,23:00
TYPE1,0,0,0,0,0,0,0,0,8,8,8,8,8,8,8,8,8,8,0,0,0,0,0,0
TYPE2,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1
TYPE3,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1
TYPE4,0,0,0,0,0,0,0,0,8,8,8,8,8,8,8,8,8,8,0,0,0,0,0,0
TYPE5,0,0,0,0,0,0,0,0,8,8,8,8,8,8,8,8,8,8,0,0,0,0,0,0
TYPE6,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1
"
| multikv forceheader=1
| eval time_interval="",file_threshold=""
| table file_type *_*
| foreach *
[
eval time_interval=if("<<FIELD>>"!="file_type" AND "<<FIELD>>"!="time_interval" AND "<<FIELD>>"!="file_threshold",time_interval+","+"<<FIELD>>",time_interval),
file_threshold=if("<<FIELD>>"!="file_type" AND "<<FIELD>>"!="time_interval" AND "<<FIELD>>"!="file_threshold",file_threshold+","+'<<FIELD>>',file_threshold)
]
| eval time_interval=substr(time_interval,2), time_interval=replace(time_interval,"_",":"),time_interval=split(time_interval,","), file_threshold =substr(file_threshold,2),file_threshold=split(file_threshold,",")
| eval t=mvzip(time_interval,file_threshold)
| table file_type t
| stats count by file_type t | eval time_interval = mvindex(split(t,","),0),file_threshold= mvindex(split(t,","),1)
| table file_type time_interval,file_threshold
Thanks
KV
▄︻̷̿┻̿═━一
If any of my reply helps you to solve the problem Or gain knowledge, an upvote would be appreciated.
Can you please try this?
| inputlookup YOUR_LOOKUP
| eval time_interval="",file_threshold=""
| foreach *
[
eval time_interval=if("<<FIELD>>"!="file_type" AND "<<FIELD>>"!="time_interval" AND "<<FIELD>>"!="file_threshold",time_interval+","+"<<FIELD>>",time_interval),
file_threshold=if("<<FIELD>>"!="file_type" AND "<<FIELD>>"!="time_interval" AND "<<FIELD>>"!="file_threshold",file_threshold+","+'<<FIELD>>',file_threshold)
]
| eval time_interval=substr(time_interval,2),time_interval=split(time_interval,","), file_threshold =substr(file_threshold,2),file_threshold=split(file_threshold,",")
| eval t=mvzip(time_interval,file_threshold)
| table file_type t
| stats count by file_type t | eval time_interval = mvindex(split(t,","),0),file_threshold= mvindex(split(t,","),1)
| table file_type time_interval,file_threshold
My Sample Search :
| makeresults
| eval _raw="file_type,0:00,1:00,2:00,3:00,4:00,5:00,6:00,7:00,8:00,9:00,10:00,11:00,12:00,13:00,14:00,15:00,16:00,17:00,18:00,19:00,20:00,21:00,22:00,23:00
TYPE1,0,0,0,0,0,0,0,0,8,8,8,8,8,8,8,8,8,8,0,0,0,0,0,0
TYPE2,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1
TYPE3,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1
TYPE4,0,0,0,0,0,0,0,0,8,8,8,8,8,8,8,8,8,8,0,0,0,0,0,0
TYPE5,0,0,0,0,0,0,0,0,8,8,8,8,8,8,8,8,8,8,0,0,0,0,0,0
TYPE6,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1
"
| multikv forceheader=1
| eval time_interval="",file_threshold=""
| table file_type *_*
| foreach *
[
eval time_interval=if("<<FIELD>>"!="file_type" AND "<<FIELD>>"!="time_interval" AND "<<FIELD>>"!="file_threshold",time_interval+","+"<<FIELD>>",time_interval),
file_threshold=if("<<FIELD>>"!="file_type" AND "<<FIELD>>"!="time_interval" AND "<<FIELD>>"!="file_threshold",file_threshold+","+'<<FIELD>>',file_threshold)
]
| eval time_interval=substr(time_interval,2), time_interval=replace(time_interval,"_",":"),time_interval=split(time_interval,","), file_threshold =substr(file_threshold,2),file_threshold=split(file_threshold,",")
| eval t=mvzip(time_interval,file_threshold)
| table file_type t
| stats count by file_type t | eval time_interval = mvindex(split(t,","),0),file_threshold= mvindex(split(t,","),1)
| table file_type time_interval,file_threshold
Thanks
KV
▄︻̷̿┻̿═━一
If any of my reply helps you to solve the problem Or gain knowledge, an upvote would be appreciated.
This works locally, thank you very much.
I ended up adding a column to the lookup called "monitor_windows" and using the following, not as elegant as your approach but I have other fields in the table I did not add to the initial question and this gets it down to fewer lines in the search.
monitor_windows
00:00,01:00,02:00,03:00,04:00,05:00,06:00,07:00,08:00,09:00,10:00,11:00,12:00,13:00,14:00,15:00,16:00,17:00,18:00,19:00,20:00,21:00,22:00,23:00
| eval event_hour=split(monitor_windows, ",")
| mvexpand event_hour
| foreach * [ eval event_hour_threshold=if("<<FIELD>>"=event_hour,'<<FIELD>>',event_hour_threshold)]