Splunk Search

Search Result Sorting as per values in a field

sdhawanx
Path Finder

I have a search query that gives the supposed following results.

Name

WW

Name2

Result

Type

Value

Abc

50.5

Prod

Pass

A

1280

Xyz

47.2

Prod

Pass

Dr

Sound

Abc

51.3

Test

Fail

 

 

Def

8.2

Test

Fail

Td

Wifi

Def

44.2

Prod2

Pass

Gf

Printer

Xyz

6.2

Test1

Fail

Fr

Audio

Abc

451

Prod1

Pass

Cs

Audio

 

The values in the name column do not have a fixed value. There can be 10-12 different names. I want the above table to be shown as below

Name

WW

Name2

Result

Type

Value

Name

WW

Name2

Result

Type

Value

Name

WW

Name2

Result

Type

Value

Abc

50.5

Prod

Pass

A

1280

Xyz

47.2

Prod

Pass

Dr

Sound

Def

8.2

Test

Fail

Td

Wifi

Abc

51.3

Test

Fail

 

 

Xyz

6.2

Test1

Fail

Fr

Audio

Def

44.2

Prod2

Pass

Gf

Printer

Abc

451

Prod1

Pass

Cs

Audio

 

 

 

 

 

 

 

 

 

 

 

 

 

I would like to know is there any way I can show it as a table like above through query or through any changes in the XML

Labels (7)
0 Karma
1 Solution

to4kawa
Ultra Champion

It's better this way.

 

| makeresults
| eval _raw="Name,WW,Name2,Result,Type,Value
Abc,50.5,Prod,Pass,A,1280
Xyz,47.2,Prod,Pass,Dr,Sound
Abc,51.3,Test,Fail,,
Def,8.2,Test,Fail,Td,Wifi
Def,44.2,Prod2,Pass,Gf,Printer
Xyz,6.2,Test1,Fail,Fr,Audio
Abc,451,Prod1,Pass,Cs,Audio"
| multikv forceheader=1
| table Name,WW,Name2,Result,Type,Value
| fillnull Name,WW,Name2,Result,Type,Value value="NULL"
| sort Name WW
| streamstats dc(Name) as session
| streamstats count by session
| eval session=session.":".count
| xyseries session Name,WW,Name2,Result,Type,Value
| rename Name2:* as *:2:Name2,WW:* as *:1:WW, Result:* as *:3:Result, Type:* as *:4:Type, Value:* as *:5:Value
| eval session=mvindex(split(session,":"),-1)
| untable session fieldname value
| xyseries session fieldname value

 

View solution in original post

to4kawa
Ultra Champion

Run the pipes one by one and see the results. Up to the table is making the sample you presented.

0 Karma

to4kawa
Ultra Champion

It's better this way.

 

| makeresults
| eval _raw="Name,WW,Name2,Result,Type,Value
Abc,50.5,Prod,Pass,A,1280
Xyz,47.2,Prod,Pass,Dr,Sound
Abc,51.3,Test,Fail,,
Def,8.2,Test,Fail,Td,Wifi
Def,44.2,Prod2,Pass,Gf,Printer
Xyz,6.2,Test1,Fail,Fr,Audio
Abc,451,Prod1,Pass,Cs,Audio"
| multikv forceheader=1
| table Name,WW,Name2,Result,Type,Value
| fillnull Name,WW,Name2,Result,Type,Value value="NULL"
| sort Name WW
| streamstats dc(Name) as session
| streamstats count by session
| eval session=session.":".count
| xyseries session Name,WW,Name2,Result,Type,Value
| rename Name2:* as *:2:Name2,WW:* as *:1:WW, Result:* as *:3:Result, Type:* as *:4:Type, Value:* as *:5:Value
| eval session=mvindex(split(session,":"),-1)
| untable session fieldname value
| xyseries session fieldname value

 

View solution in original post

sdhawanx
Path Finder

This worked perfectly. Thanks a ton .

0 Karma

to4kawa
Ultra Champion

Splunk can't make same field names.

workaround:

| makeresults
| eval _raw="Name,WW,Name2,Result,Type,Value
Abc,50.5,Prod,Pass,A,1280
Xyz,47.2,Prod,Pass,Dr,Sound
Abc,51.3,Test,Fail,,
Def,8.2,Test,Fail,Td,Wifi
Def,44.2,Prod2,Pass,Gf,Printer
Xyz,6.2,Test1,Fail,Fr,Audio
Abc,451,Prod1,Pass,Cs,Audio"
| multikv forceheader=1
| table Name,WW,Name2,Result,Type,Value
| fillnull Name,WW,Name2,Result,Type,Value value="NULL"
| eval tmp=WW.",".Name2.",".Result.",".Type.",".Value
| table Name tmp
| stats values(tmp) as tmp by Name
| transpose 0 header_field=Name
| rename column as _column
| foreach * [ | rex field=<<FIELD>> max_match=0 "(?<<<FIELD>>_1_WW>\w+),(?<<<FIELD>>_2_Name2>\w+),(?<<<FIELD>>_3_Result>\w+),(?<<<FIELD>>_4_Type>\w+),(?<<<FIELD>>_5_Value>\w+)"
| fields - <<FIELD>>]

 

0 Karma

sdhawanx
Path Finder

i am new to Splunk. Sorry in advance if i miss something. But in the second line of this query it seems that you are using the search result values. Since the search result values in the table are dynamic, wont this mean that every time we would have to pass section manually. or is there something else that i am missing.

0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!