inputlookup convert columns to rows

[middlemiddle]

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

[kamlesh_vaghela · Accepted solution]

@middlemiddle 

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.

[middlemiddle]

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