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
| 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!=""
| 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!=""
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
| 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!=""
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
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.
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.
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