topic Re: change table view in Splunk Search

change table view

sarit_s — Mon, 30 Aug 2021 09:16:08 GMT

Hello
i have a table that looks like this :

and i want it to look like this:

so the type values will be the header
what should i do ?

thanks

Re: change table view

gcusello — Mon, 30 Aug 2021 09:28:08 GMT

Hi @sarit_s,

could you better describe your request?

because I don't see any relation between the first and the second table: the fields are all different and the fields of screen 2 aren't the values of type in screen 1!

Then, could you share the search you used for the first visualization?

Ciao.

Giuseppe

Re: change table view

sarit_s — Mon, 30 Aug 2021 10:31:55 GMT

Hey

i need the the type values (in screenshot 1) will be the headers of the table

this is my query :

sourcetype="kube:container:notificationsservice-workerservice" Message="Filtered channel context" "ContextData.ChannelName"=SalesforceEmail| top limit=500 "AdditionalData.Meta.NotificationType" |eval type="Priority " |append [search sourcetype="kube:container:notificationsservice-workerservice" Message="Filtered channel context" "ContextData.ChannelName"=SalesforcePriorityEmail| top limit=500 "AdditionalData.Meta.NotificationType" |eval type="Priority "] |append [search sourcetype="Silverpop-Transactional-*" Message="Message was successfully sent to *"| top limit=500 "AdditionalData.additionalData.AdditionalParameters.MailingID" |eval type="Silverpop "]

also, i have 2 parts the the type is "Priority" so i also want to sum them together so in the End i will have 1 column "Priority" and one column "SilverPop"

Re: change table view

gcusello — Mon, 30 Aug 2021 09:49:23 GMT

Hi @sarit_s,

ok, now is more clear, please try something like this:

(sourcetype="kube:container:notificationsservice-workerservice" Message="Filtered channel context" ("ContextData.ChannelName"=SalesforceEmail OR "ContextData.ChannelName"=SalesforcePriorityEmail)) OR (sourcetype="eToro-Silverpop-Transactional-*" Message="Message was successfully sent to *") | stats count(eval(sourcetype="kube:container:notificationsservice-workerservice")) AS Priority count(eval(sourcetype="eToro-Silverpop-Transactional-*")) AS Silverpop BY "AdditionalData.Meta.NotificationType"

In this way you haven't the limit of 50,000 results in subsearches.

Only one hint: use always the indication of the index, you'll have faster searches!

Ciao.

Giuseppe

Re: change table view

sarit_s — Mon, 30 Aug 2021 18:19:48 GMT

thanks but for some reason the results of "SilverPOP" are all 0 and its incorrect

Re: change table view

gcusello — Tue, 31 Aug 2021 07:51:46 GMT

Hi @sarit_s,

check the results of the last search

sourcetype="eToro-Silverpop-Transactional-*"

if it's correct.

Ciao.

Giuseppe

Re: change table view

sarit_s — Tue, 31 Aug 2021 12:01:13 GMT

yes, its correct

there are results

Re: change table view

gcusello — Tue, 31 Aug 2021 12:27:24 GMT

Hi @sarit_s,

ok, you have to modify your search:

(sourcetype="kube:container:notificationsservice-workerservice" Message="Filtered channel context" ("ContextData.ChannelName"=SalesforceEmail OR "ContextData.ChannelName"=SalesforcePriorityEmail)) OR (sourcetype="eToro-Silverpop-Transactional-*" Message="Message was successfully sent to *") | eval Type=if(sourcetype="kube:container:notificationsservice-workerservice","Priority","Silverpop") | stats count(eval(Type="Priority")) AS Priority count(eval(Type="Silverpop")) AS Silverpop BY "AdditionalData.Meta.NotificationType"

Ciao.

Giuseppe

Re: change table view

sarit_s — Tue, 31 Aug 2021 12:29:18 GMT

its still returns 0

Re: change table view

gcusello — Tue, 31 Aug 2021 12:43:51 GMT

Hi @sarit_s,

let me understand:

in the first search you use the top command for the field "AdditionalData.Meta.NotificationType"
in the second search you use the top command for the field "AdditionalData.AdditionalData.AdditionalParameters.Meta.MailingID"

Have they the same values?

if they have the same values, you have only to rename the second field as the first in the search:

(sourcetype="kube:container:notificationsservice-workerservice" Message="Filtered channel context" ("ContextData.ChannelName"=SalesforceEmail OR "ContextData.ChannelName"=SalesforcePriorityEmail)) OR (sourcetype="eToro-Silverpop-Transactional-*" Message="Message was successfully sent to *") | eval Type=if(sourcetype="kube:container:notificationsservice-workerservice","Priority","Silverpop") | rename "AdditionalData.AdditionalData.AdditionalParameters.Meta.NotificationType" AS "AdditionalData.Meta.NotificationType" | stats count(eval(Type="Priority")) AS Priority count(eval(Type="Silverpop")) AS Silverpop BY "AdditionalData.Meta.NotificationType"

Please check the names of the fields.

Ciao.

Giuseppe

Re: change table view

sarit_s — Tue, 31 Aug 2021 13:15:23 GMT

no, they don't have the same values.

the query you added returns no results at all 😞

Re: change table view

gcusello — Tue, 31 Aug 2021 13:21:34 GMT

Hi @sarit_s,

let me understand: if the values in fields "AdditionalData.Meta.NotificationType" and "AdditionalData.AdditionalData.AdditionalParameters.Meta.MailingID" are different, how can you think to group them and present in the same table?

You can have all results in one table but in some rows you'll have zero in the first column and in some others, you'll have zero in the other columns, in other words, you cannot group them.

Ciao.

Giuseppe

Re: change table view

sarit_s — Tue, 31 Aug 2021 13:43:15 GMT

i will try to make some order

i have 3 queries:

Silverpop: sourcetype="Silverpop-Transactional-*" Message="Message was successfully sent to *"| top limit=500 "AdditionalData.additionalData.AdditionalParameters.MailingID"

Salesforce channel: sourcetype="kube:container:notificationsservice-workerservice" Message="Filtered channel context" "ContextData.ChannelName"=SalesforceEmail| top limit=500 "AdditionalData.Meta.NotificationType"

Salesforce Priority channel: sourcetype="kube:container:notificationsservice-workerservice" Message="Filtered channel context" "ContextData.ChannelName"=SalesforcePriorityEmail| top limit=500 "AdditionalData.Meta.NotificationType"

i want to sum together the 2 priority queries and show the table in a way that the headers will be Priority and SilverPop as in the picture i added earlier

Re: change table view

gcusello — Tue, 31 Aug 2021 14:16:58 GMT

Hi @sarit_s,

ok this means that there isn't any correlation between the two searches and you want only to display results in one table, putting:

in the first column the values of fields "AdditionalData.Meta.NotificationType" and "AdditionalData.AdditionalData.AdditionalParameters.Meta.MailingID",
in the second column the count of Priority (the first two searches), present only in the rows where the first columns are the values of "AdditionalData.Meta.NotificationType",
in the third column the count of Silverpop (the third search), present only in the rows where the first columns are the values of "AdditionalData.AdditionalData.AdditionalParameters.Meta.MailingID".

in this case, please, try something like this:

sourcetype="kube:container:notificationsservice-workerservice" Message="Filtered channel context" ("ContextData.ChannelName"=SalesforceEmail OR "ContextData.ChannelName"=SalesforcePriorityEmail) | stats count AS Priority BY "AdditionalData.Meta.NotificationType" | rename "AdditionalData.Meta.NotificationType" As Column | append [ search sourcetype="Silverpop-Transactional-*" Message="Message was successfully sent to *" | stats count AS Silverpop BY "AdditionalData.additionalData.AdditionalParameters.MailingID" | rename "AdditionalData.additionalData.AdditionalParameters.MailingID" AS Column ]

I Called "Column" the first column, because it isn't possible to leave blank a column name.

Ciao.

Giuseppe

Re: change table view

sarit_s — Tue, 31 Aug 2021 17:11:29 GMT

Hey

the count is for the last 2 queries

and also in your query i see only 2 columns : "column" and "Priority" so i think something is missing there

Re: change table view

sarit_s — Wed, 01 Sep 2021 12:16:30 GMT

I fixed it by adding "|fields"

last question, is it possible to sort by the values in silverpop or priority so the highest will be first regardless if its silverpop or priority ?

Re: change table view

gcusello — Thu, 02 Sep 2021 12:24:27 GMT

Hi @sarit_s,

yes: put at the end of the search a sort command:

| sort Silverpop

If this answer solves your need, please accept it for the other people of Community.

Ciao and happy splunking.

Giuseppe

P.S.: Karma Points are appreciated 😉