Splunk Search

inputlookup convert columns to rows

middlemiddle
Explorer

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

Labels (1)
0 Karma
1 Solution

kamlesh_vaghela
SplunkTrust
SplunkTrust

@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.

View solution in original post

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@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.

0 Karma

middlemiddle
Explorer

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

Tags (3)
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...