topic Re: chart over multiple fields in Splunk Search

chart over multiple fields

Jyothik — Wed, 06 Sep 2017 21:30:43 GMT

hello splunkers,

We are trying to get the chart over for multiple fields sample as below , we are not able to get it, kindly help us on how to query it.

Month Country  Sales count
01     A       10
02     B       30
03     C       20
04     D       10

Thanks in advance
Jyothi

Re: chart over multiple fields

woodcock — Wed, 06 Sep 2017 21:52:39 GMT

Like this:

... | stats count AS "Sales count" sum(count) AS "Sum count" BY date_month Country

Re: chart over multiple fields

Jyothik — Wed, 06 Sep 2017 21:59:15 GMT

it didn't help, we want exactly moth wise, country wise sales count. please help on query

Regards,
Jyothi

Re: chart over multiple fields

cmerriman — Wed, 06 Sep 2017 23:13:23 GMT

If I understand what you need, will something like this work:

|eval month_country=Month+"|"+Country
|chart count by month_country Sales
|rex field=month_country "(?<Month>\d+)\|(?<Country>.*)"
|fields - month_country

Re: chart over multiple fields

woodcock — Wed, 06 Sep 2017 23:52:36 GMT

Try this:

... | stats count AS "Sales count" sum(count) AS "Sum count" values(Country) BY date_month

BTW, your question is vague and you did not supply any usable sample data, nor an unambiguous mockup of the final goal. This means that it is very difficult for people to help and much time is likely to be wasted. Ask questions better to get better answers.

Re: chart over multiple fields

Jyothik — Thu, 07 Sep 2017 21:00:54 GMT

month and country are not same fields, month is different fiel, country is different field and sales count is different filed. looking to have on' x' axis month wise and on 'y' axis sales and country with different colors on bar chart. color Bar to represent each country.

Kindly help it to get me with query.

Regards,
Jyothi

Re: chart over multiple fields

cmerriman — Fri, 08 Sep 2017 11:49:51 GMT

try this syntax and let me know if the output is close what you're looking for :

|makeresults |eval data="month=1,country=A,salescount=10 month=2,country=B,salescount=30 month=3,country=C,salescount=20 month=4,country=D,salescount=10"| makemv data | mvexpand data | rename data as _raw | kv|eval{country}=salescount|fields - country salescount _time _raw|fields month *

if so, take your syntax and add |rename "Sales Count" as salescount|eval{Country}=salescount|fields - Country salescount|fields month * to it.

Re: chart over multiple fields

Sukisen1981 — Fri, 08 Sep 2017 16:14:06 GMT

try this -

| timechart span=1mon count by country | join _time [search | timechart span=1mon sum(sales)]

Re: chart over multiple fields

Jyothik — Fri, 08 Sep 2017 18:28:47 GMT

this helped me and gave some output but not the way totally i'm looking for 01.2017 calendar year to display as Jan 2017,
feb 2017,
instead that way it is displaying as 2012-03, my data doesn't contain 2012 at all which is as below.

_time Incident NULL Service Request
2012-03 3992 5 0
2012-04 0 0 0
2012-05 0 0 0
2012-06 0 0 0
2012-07 0 0 0
2012-08 0 0 0
2012-09 0 0 0
2012-10 0 0 0
2012-11

Re: chart over multiple fields

Jyothik — Fri, 08 Sep 2017 20:20:51 GMT

still getting the same result with no change , we have the below fields. 1.2017 is Jan 2017 and 2.2017 is feb 2017......month on x axis and no.of tickets on y axis .

Calendar Year_Month Country No.of Tickets
1.2017 USA 10
2.2017 MX 20
3.2017 UK 40

urgent to produce the dashboard, kindly help me. thanks in advance

Re: chart over multiple fields

Sukisen1981 — Sat, 09 Sep 2017 08:57:15 GMT

hmmm ... your data needs more explanation. Are your trying sales or ticket counts? can you povide a sample of your true raw data and what exactly you need? _time wont take your custom time field, but there is a way to make a time chart of your custom time field. You need to provide a more clear sample of your data. Trust me it is not as difficult as it looks, just need your data sample to actually look into the fields and formats your have and what you exactly need....

Re: chart over multiple fields

Jyothik — Mon, 11 Sep 2017 23:04:09 GMT

hello colleagues,

Below is the raw data , from the below i would like to know no.of tickets created in the month of january (01.2017) per country. thanks in advance.

Ticket ID Ticket Type Calendar Year /Month Country
500235 Service Request 01.2017 Berlin
500235 Service Request 01.2017 Berlin
400238 Service Request 01.2017 USA
500242 Service Request 01.2017 Mexico
50024 Service Request 01.2017 Japan
50024 Service Request 01.2017 Japan
40024 Service Request 01.2017 Japan
40024 Service Request 01.2017 India
50024 Service Request 03.2017 India
50024 Service Request 03.2017 India
50024 Service Request 01.2017 India
50024 Service Request 02.2017 Japan
40024 Service Request 02.2017 Japan
40024 Service Request 02.2017 Mexico
50024 Service Request 02.2017 Mexico

Re: chart over multiple fields

woodcock — Tue, 12 Sep 2017 00:20:10 GMT

Your field names are beyond awful (the values aren't too great either) but try this:

 ... | stats dc("Ticket ID") AS "Sales count" BY "Calendar Year /Month" Country

Re: chart over multiple fields

Jyothik — Tue, 12 Sep 2017 20:57:18 GMT

thanks to all , got the output