Dashboards & Visualizations

table and lookup

simo
Path Finder

Hi All,

i have the following situation
a lookup with the following values

value
1
2
3

 

a table with name and value

namevalue
a1
b2
a1
b2
b2
a2
b1

 

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

name123
a210
b040
c000

 

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

name1234
a2100
b0400
c0000

 

thanks for any help

Simone

Labels (1)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust
| 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!=""

View solution in original post

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
| 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!=""
0 Karma

simo
Path Finder

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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
| 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!=""
0 Karma

simo
Path Finder

Hi @ITWhisperer 

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

name surnamevalue
mario rossi1
marco ferrari 2
mario russo4
marco ferrari 5
marco ferrari 1

 

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

Simone

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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.

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

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

0 Karma

aasabatini
Motivator

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

 

“The answer is out there, Neo, and it’s looking for you, and it will find you if you want it to.”
0 Karma