topic Re: How to extract values from field and use it as column header in Splunk Search

How to extract values from field and use it as column header

egonstep — Thu, 29 Aug 2019 19:33:21 GMT

Hello all, how do I retrieve the values from my search and insert in the same row, extracting the values from the field Services, like:

current search:

<search>
| stats sum(FAIL) as Fails, sum(PASS) as Passes, sum(TOTAL_VOLUME) as Total, values(SERVICE) as 
Services by CLIENT
| table CLIENT Fails Passes Total Services

Results:

CLIENT   Fails  Passes  Total   Services
ClientA    10      1       11    A
                                 B
                                 C
ClientB     0      1        1    D

And I need to set the results like:

CLIENT   Fails  Passes  Total   A  B  C  D
ClientA   10      1       11    5  5  1  0
ClientB    0      1        1    0  0  0  1

Example of events:

DATE_TIME="2019-08-08 00:00:00" CLIENT="ClientA", SERVICE="A", FAIL="0", PASS="1", TOTAL_VOLUME="1"

DATE_TIME="2019-08-08 00:00:00" CLIENT="ClientA", SERVICE="C", FAIL="3", PASS="0", TOTAL_VOLUME="3"

DATE_TIME="2019-08-08 00:00:00" CLIENT="ClientB", SERVICE="D", FAIL="0", PASS="1", TOTAL_VOLUME="1"

Thanks!

Re: How to extract values from field and use it as column header

Sukisen1981 — Thu, 29 Aug 2019 20:23:12 GMT

| stats sum(fail) as Fails, sum(pass) as Passes, sum(Total_Volume) as Total, count(SERVICE) as
Services by CLIENT,SERVICE |table

Re: How to extract values from field and use it as column header

egonstep — Fri, 30 Aug 2019 11:50:31 GMT

Hey Sukisen, thanks for the quick response.

But didn't worked the way it supposed.

CLIENT   SERVICE   Services
ClientA       A         5

So the count is hiding the other services like (B,C, D..etc).

Re: How to extract values from field and use it as column header

kamlesh_vaghela — Fri, 30 Aug 2019 12:03:00 GMT

@egonstep

Can you please sample events? As per the question what I understood, there are fields A ,B, C, D
in your events but optional and depends on Services fields. Please correct me if I'm wrong.

Re: How to extract values from field and use it as column header

egonstep — Fri, 30 Aug 2019 12:43:47 GMT

Hey Kamlesh, I updated the question with some sample events.

Re: How to extract values from field and use it as column header

Sukisen1981 — Fri, 30 Aug 2019 13:57:17 GMT

hi @egonstep
try this

  | stats sum(FAIL) as Fails, sum(PASS) as Passes, sum(TOTAL_VOLUME) as Total  by CLIENT,SERVICE
    | fields CLIENT,Fails,Passes,SERVICE,Total| eval {SERVICE}=Total
    | fillnull value=0
    | fields - SERVICE| stats sum(*) by CLIENT
    | rename sum(* as ""*
    | rename *) as *""
    | fields CLIENT,Fails,Passes,Total,*

Re: How to extract values from field and use it as column header

egonstep — Fri, 30 Aug 2019 15:05:19 GMT

Wow Sukisen, you're the Splunk wizard lol. That's is exactly what I wanted.

Would you mind explaining how does {} works?

| eval {SERVICE}=Total

Or if you can suggest any reading material.

Thanks!!

Re: How to extract values from field and use it as column header

Sukisen1981 — Wed, 30 Sep 2020 02:01:40 GMT

hi @egonstep
It is mentioned here but very briefly - looks like the old motto in life of 'mention the really important stuff as sparsely as you can' is being adhered to 🙂
ref - https://docs.splunk.com/Documentation/Splunk/7.3.1/SearchReference/Eval
Section :
Field names
To specify a field name with multiple words, you can either concatenate the words, or use single quotation marks when you specify the name. For example, to specify the field name Account ID you can specify AccountID or 'Account ID'.

To specify a field name with special characters, such as a period, use single quotation marks. For example, to specify the field name Last.Name use 'Last.Name'.

You can use the value of another field as the name of the destination field by using curly brackets, { }. For example, if you have an event with the following fields, aName=counter and aValue=1234. Use | eval {aName}=aValue to return counter=1234.

Basically , what this does is transfer the rows(values) of a column(field) into column headers.
so if I run this and you can run this as well as it is since it is on the delivered _audit index
index="_audit"
| table action,date_minute
I will receive a table with 2 fields action and the date_minute. For some weird reason (and well, this is a bad example) if I want to see the action fields or the date_minute as headers AND have the corresponding values as a sort of pivot as the cells, I can use something like
index="_audit"
| table action,date_minute
| eval {date_minute}=action
OR vice veras that is see my action row values as headers
index="_audit"
| table action,date_minute
| eval {date_minute}=action
So any time you see something which talks about transforming row values into column headers , try the curly eval !
Lastly, apologize for my first answer which was way beyond wrong, I only got to realize what you wanted after seeing your event snapshot. I believe @kamlesh_vaghela (who is one of our most valued members would have come up with a better answer and in a shorter span of time ,if he had a bit of time to devote to this) deserves a 'pat on the back' award 🙂

Re: How to extract values from field and use it as column header

egonstep — Fri, 30 Aug 2019 17:09:21 GMT

Oh, I see, I think I got it, just need to practice more lol. But thanks Sukisen for the responses and explanations. It helped a lot.

Just to let you know or maybe help someone in the future. My current code is like this, (showing all the values as a percentage by time as well):

<search>
| bin _time span=1h
| stats sum(FAIL) as "Total Fails" sum(PASS) as "Total Passes" sum(TOTAL_VOLUME) as Total by 
CLIENT, _time, SERVICE
| fields CLIENT, "Total Fails", "Total Passes", SERVICE, Total,  _time
| eval {SERVICE}=Total
| fillnull value=0
| fields - SERVICE
| stats sum(*) by CLIENT, _time
| rename sum(* as ""*
| rename *) as *%""
| rename "Total %" as Total
| eval "Total Fails"='Total Fails%', "Total Passes"='Total Passes%'
| fields _time, CLIENT, *, "Total Passes", "Total Fails", Total
| rename _time AS "Date:Hour"
| convert timeformat="%m/%d:%H" ctime("Date:Hour")
| sort -"Total Passes"
| head 5
| foreach *%
    [eval <<FIELD>>=round(('<<FIELD>>'/Total)*100,2)]

Thank you again!

Re: How to extract values from field and use it as column header

Naa_Win — Fri, 01 Mar 2024 17:59:11 GMT

Hello @Sukisen1981 ,

Your Query helped me !! Thank you.

But I had a small question. I have a similar Query which I wanted to show By Month

CLIENT   Fails  Passes  Total   MonthClientA   10      1       11     jan
ClientB    0      1        1     mar
CliectC     11    9        20    feb

So I had tried your query and i got the below results...

I used this for month | eval Month=strftime(_time, "%B")
But I wanted to get the latest month first so on like mar feb jan

CLIENT   Fails  Passes  Total   MAR    JAN   FEBClientA   10      1       11     0       5    0
ClientB    0      1        1     2      11    0
CliectC     11    9        20    0       0    19