how to chart over multiple fields?

[pavanraghav]

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 ??

[woodcock]

Try this:

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

[to4kawa]

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

[pavanraghav]

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 ??

[DavidHourani]

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 😉

[pavanraghav]

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 "(?.)/(?.)"

[DavidHourani]

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

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

[pavanraghav]

tried not working

[DavidHourani]

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

[pavanraghav]

nope still no progress

[DavidHourani]

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

[DavidHourani]

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

[pavanraghav]

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 !

[DavidHourani]

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.

[pavanraghav]

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

[Sukisen1981]

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

[pavanraghav]

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

it is working

I will try our suggestion too

Thanks

[pavanraghav]

ya sorted it out the same way

Anyways thanks for your support

[DavidHourani]

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

[Sukisen1981]

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