topic Re: how to chart over multiple fields? in Splunk Search

how to chart over multiple fields?

pavanraghav — Tue, 03 Dec 2019 08:54:03 GMT

When I am using this :

chart count over Created_Month by Status
|table Created_Month,year,Relevant,Missing,Non_Relevant|addtotals

Then I am getting only data for Created_month , but year field is populating empty:

Created_Month   year    Relevant    Missing Non_Relevant    Total
November                       0          2               4        6

How can I retrieve year field data ??

Re: how to chart over multiple fields?

Sukisen1981 — Wed, 30 Sep 2020 03:13:57 GMT

hi - the way to read SPL is from left to right pipes. So when you use this -
chart count over Created_Month by Status
there is no year field all subsequent pipes after that will have no reference to year as the chart command output does not contain a year value.
What you can do is convert created_month to year by applying an eval ...something like this
|chart count over Created_Month by Status|eval Year=subtsr(Created_Month,x.y)
Now, your field Created_Month has to have some reference to year....something like 11-2019,12-2019,01-2020..and on
If it doesn't then you need to modify yhr Ceated_Month field AND then apply some sort of string extraction eval function on it

Re: how to chart over multiple fields?

DavidHourani — Wed, 30 Sep 2020 03:14:02 GMT

Hi @pavanraghav,

In case you didn't know, chart is a transforming command : https://docs.splunk.com/Splexicon:Transformingcommand

This means that after you run it you will only have the fields that are included in your command. In your case with :
chart count over Created_Month by Status
You will not have the year field.

If you wish to keep the year field make sure you include it with the month field before you make the chart. Make a field called Created_Month_Year that contains both. Then run your chart as follows :
chart count over Created_Month_Year by Status

After that all you have to do is extract and separate the month and year field from Created_Month_Year.

Let me know if that helps.

Cheers,
David

Re: how to chart over multiple fields?

pavanraghav — Wed, 30 Sep 2020 03:14:07 GMT

Hi David ,

i have followed the steps which you have suggested

Created_month_year Relevant Missing Non_Relevant Total
11/2019 0 2 4 6

But i need the result as :
Month year
November 2019

and also when i get this output , is there a way where my addtotals command should add only values in fields ( relevant , missing and non_relevant ) .

thanks in advance !

Re: how to chart over multiple fields?

DavidHourani — Wed, 30 Sep 2020 03:14:10 GMT

Cool, nice work @pavanraghav.

In order to get November 2019 the next step is to split Created_month_year into month and year. To do so you can either use the rex, the subtsr function of eval or the splitfunction. I would say the easiest would be to do it with rex as follows :

| rex field=Created_month_year "(?<month>\d+)\/(?<year>\d+)"

As for the addtotals there should be no issues if you have empty fields. if they are bugging you then you can use fillnull and replace null values with zeros.

Re: how to chart over multiple fields?

pavanraghav — Tue, 03 Dec 2019 11:03:25 GMT

ya sorted it out the same way

Anyways thanks for your support

Re: how to chart over multiple fields?

DavidHourani — Tue, 03 Dec 2019 11:05:03 GMT

great ! Please upvote and accept the answer if it was helpful !

Re: how to chart over multiple fields?

pavanraghav — Tue, 03 Dec 2019 11:24:47 GMT

regarding addtotals , the issue is not on the blank fields ,
but since the command sums up all the numeric values , it is considering year also as value .
we require only total of missing , relevant and non_relevant fields

Re: how to chart over multiple fields?

Sukisen1981 — Tue, 03 Dec 2019 11:28:36 GMT

why don't you just add an eval instead of using addtotals
|eval sum=missing+releavant+non_relevant etc etc?

Re: how to chart over multiple fields?

pavanraghav — Tue, 03 Dec 2019 11:39:28 GMT

ya i tried other way addtotals fieldname=Total Non_Relevant* Relevant* Missing*

it is working

I will try our suggestion too

Thanks

Re: how to chart over multiple fields?

pavanraghav — Wed, 30 Sep 2020 03:14:13 GMT

Hi everyone,

thanks a lot for suggestions

I need a help in understanding the below query in detail :

| eval e="$time_token.earliest$", l="$time_token.latest$"| eval e=case(match(e,"^\d+$"),e,e="" OR e="now" , "0" , true(), relative_time(now(),e)) | eval l=case(match(l,"^\d+$"),l,l="" OR l="now" , "2145916800", true(), relative_time(now(),l))| eval e=tonumber(e) , l=tonumber(l) | where order_date >= e AND order_date <= l

Can you guys help me out ??

Re: how to chart over multiple fields?

DavidHourani — Tue, 03 Dec 2019 11:51:08 GMT

Hi @pavanraghav,

This seems like another query than the original one of this post, go ahead and post a new question then link it here, we can work on it there.

If the answer below covers your initial question then please accept it so we can move on to the next 😉

Re: how to chart over multiple fields?

pavanraghav — Wed, 30 Sep 2020 03:14:18 GMT

ok
and regarding the above posted question

rex field=Created_month_year "(?.)/(?.)" |eval month=strftime(mon,"%B") |table month,year,Relevant,Missing,Non_Relevant | addtotals fieldname=Total Non_Relevant* Relevant* Missing*

this is working fine when im using in the search
but when im placing the same in the sourcecode of my dashboard it is throwing an error ,unexpected close tag for the line
rex field=Created_month_year "(?.)/(?.)"

Re: how to chart over multiple fields?

DavidHourani — Tue, 03 Dec 2019 11:59:28 GMT

could be the /causing an issue, try using this instead :

 | rex field=Created_month_year "(?<month>\d+)\/(?<year>\d+)"

Re: how to chart over multiple fields?

pavanraghav — Tue, 03 Dec 2019 12:09:44 GMT

tried not working

Re: how to chart over multiple fields?

DavidHourani — Tue, 03 Dec 2019 12:14:00 GMT

Try removing the tailing part of the command |eval month=strftime(mon,"%B") |table month,year,Relevant,Missing,Non_Relevant | addtotals fieldname=Total Non_Relevant* Relevant* Missing* and test with only ...| rex field=Created_month_year "(?<month>\d+)\/(?<year>\d+)"" to see if it works for u

Re: how to chart over multiple fields?

pavanraghav — Tue, 03 Dec 2019 12:16:40 GMT

nope still no progress

Re: how to chart over multiple fields?

DavidHourani — Tue, 03 Dec 2019 12:23:03 GMT

ummm, could you please paste the corresponding section of your code here ?

Re: how to chart over multiple fields?

to4kawa — Tue, 03 Dec 2019 12:49:44 GMT

| makeresults count=2
| streamstats count
| eval _time = if (count==2,relative_time(_time,"-3month@d"), relative_time(_time,"@d"))
| makecontinuous span=1d
| eval count=random() % 3 + 1
| eval status=mvindex(split("Relevant,Missing,Non_Relevant",","),count - 1)
 `comment("this is sample data")`
| bin span=1month _time
| chart count over _time by status
| addtotals
| eval Created_Month=strftime(_time,"%B") ,year=strftime(_time,"%Y")
| table Created_Month, year, Relevant, Missing, Non_Relevant, Total

I think you should use _time until the end.

Re: how to chart over multiple fields?

woodcock — Wed, 04 Dec 2019 10:41:24 GMT

Try this:

| eval Created_Month = Created_Month .  "/" . year
| chart count over Created_Month BY Status
| table Created_Month, Relevant, Missing, Non_Relevant
| addtotals