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

[egonstep]

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!

[Sukisen1981 · Accepted solution]

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,*

[Naa_Win]

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

[egonstep]

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!!

[Sukisen1981]

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 🙂

[egonstep]

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!

[kamlesh_vaghela]

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

[egonstep]

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

[Sukisen1981]

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

[egonstep]

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