topic How do I create new columns with the foreach command? in Splunk Search

How do I create new columns with the foreach command?

kiamco — Tue, 09 Oct 2018 16:26:52 GMT

Hello, I have a question about the use of the foreach command. I have a good idea what the foreach command can do for example:

index=summary source="summary_events_2" 
orig_source=$source$
ms_region=$region$
ms_level=$level$
| timechart span=5m  partial=f sum(count) as count
| timewrap d series=short
| rename s0 as Today
| foreach s*
     [eval d<<MATCHSTR>> = Today - <<FIELD>>]

I know that this will create new columns like d1,d2,d3,d4,... that will contain the difference of Today and s* .

Here is my dilemma: what if I do something like this:

   index=summary source="summary_events_2" 
     orig_source=$source$
     ms_region=$region$
     ms_level=$level$
     | timechart span=5m  partial=f sum(count) as count by ms_level        (//note that there are 4 levels)
     | timewrap d series=short
     | rename *_s0 as *_Today

This would give me columns like ERROR_Today, WARNING_TODAY, ERROR_s1, ERROR_s2,WARNING_s1....

Is there a way to achieve the same result as the first query using the foreach command. I want to be able to create new columns like ERROR_d1, ERROR_d2, WARNING_d1, WARNING_d2

Re: How do I create new columns with the foreach command?

DalJeanis — Wed, 10 Oct 2018 15:44:37 GMT

You can use the <<MATCHSEGn>> options to break up the names

  | rename *_s0 as *_Today
  | foreach *_s*  [eval <<MATCHSEG1>>_d<<MATCHSEG2>> = <<MATCHSEG1>>_Today - <<FIELD>>]

If you wanted, you could flip the field name order at the same time.

  | rename *_s0 as Today_*
  | foreach *_s*  [eval d<<MATCHSEG2>>_<<MATCHSEG1>> = Today_<<MATCHSEG1>> - <<FIELD>> | rename <<FIELD>> as  s<<MATCHSEG2>>_<<MATCHSEG1>> ]

Here's some arbitrary run-anywhere test code for people to play with. Run this for the preceding 6 minutes. You do need access to the _internal index.

index=_internal group=*  name=* | eval myfan=mvrange(0,31,3) | mvexpand myfan | eval _time = _time + myfan + ( ( ( random() %1475 ) % 341 ) %60)
| where _time >= relative_time(now(),"-5m@m") AND  _time < relative_time(now(),"@m")
| timechart span=10s partial=f limit=10 count by name useother=f 
| where _time >= relative_time(now(),"-2m@m") AND  _time < relative_time(now(),"@m")
| fields - NULL
| timewrap min series=short 
| rename *_s0 as *_Today
| foreach *_s*  [eval <<MATCHSEG1>>_d<<MATCHSEG2>> = <<MATCHSEG1>>_Today - <<FIELD>>]

Re: How do I create new columns with the foreach command?

woodcock — Mon, 11 Feb 2019 10:20:53 GMT

Sure, like this:

index=summary source="summary_events_2" 
      orig_source=$source$
      ms_region=$region$
      ms_level=$level$
| timechart span=5m partial=f sum(count) AS count BY sourcetype
| timewrap d series=short 
| rename *s0 AS *Today 
| foreach *s* [ eval <<MATCHSEG1>>d<<MATCHSEG2>> = <<MATCHSEG1>>Today - <<FIELD>>]