table and lookup

[simo]

Hi All,

i have the following situation
a lookup with the following values

value

1

2

3

a table with name and value

name	value
a	1
b	2
a	1
b	2
b	2
a	2
b	1

I would like to be able to view a table as follows on a dashboard. must count how many times there is one of the values ​​in lookup for the name

name	1	2	3
a	2	1	0
b	0	4	0
c	0	0	0

if I add the value 4 in the lookup the table automatically becomes

name	1	2	3	4
a	2	1	0	0
b	0	4	0	0
c	0	0	0	0

thanks for any help

Simone

[ITWhisperer]

| makeresults 
| eval _raw="name	value
a	1
b	2
a	1
b	2
b	2
a	2
b	1"
| multikv forceheader=1
| fields - _* linecount
| stats count by name value
| append
    [| makeresults
    | eval value=split("1234","")
    | mvexpand value
    | eval count=0
    | eval name=""
    | table name value count]
| xyseries name value count
| fillnull value=0
| where name!=""

[simo]

Hi @ITWhisperer 
Thanks for the reply, but it doesn't seem to work 😞

if I add a value b 5

| makeresults
| eval _raw="name value
a 1
b 2
a 1
b 2
b 2
a 2
b 1
b 5"
| multikv forceheader=1
| fields - _* linecount
| stats count by name value
| append
[| makeresults
| eval value=split("1234","")
| mvexpand value
| eval count=0
| eval name=""
| table name value count]
| xyseries name value count
| fillnull value=0
| where name!=""

a new column with the value is added 5, but this must not happen because there must be only the columns in the lookup.

Simone

[ITWhisperer · Accepted solution]

| makeresults
| eval _raw="name,value
a,1
b,2
a,1
b,2
b,2
a,2
b,1
b,5"
| multikv forceheader=1
| fields - _* linecount
| stats count by name value
| append
[| makeresults
| eval value=split("1234","")
| mvexpand value
| eval keep="keep"
| eval count=0
| eval name=""
| table name value count keep]
| eventstats values(keep) as keep by value
| where keep="keep"
| xyseries name value count
| fillnull value=0
| where name!=""

[simo]

Hi @ITWhisperer 

ok so it goes, but if I have to add another field for example

name	surname	value
mario	rossi	1
marco	ferrari	2
mario	russo	4
marco	ferrari	5
marco	ferrari	1

always assuming the values ​​from 1 to 4, it's possible?

Simone

[ITWhisperer]

You would have to concatenate name and surname with a suitable delimiter into a single field before the xyseries, then split the field back into name and surname using the delimiter to separate them.

[kamlesh_vaghela]

@simo 

Try this .

YOUR_SEARCH 
| table name value 
| search 
    [| inputlookup YOUR_LOOKUP 
    | table value ] 
| chart count over name by value

Thanks
KV
▄︻̷̿┻̿═━一

If this reply helps you, an upvote would be appreciated.

[aasabatini]

Hi @simo 

did you think to use transponse command?

https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Transpose

search example

your search | table Name Value | transpose  header_field=Value column_name=Field_name