How to create a chart using multiple fields grouped by location

[ellenbytech]

I have 6 fields (Ones, Fives, ..., Hundreds). I want to view a chart of the number of bills of each type submitted over the course of the month. I want to be able to compare the frequency of each type of bill in relation to each other on one column graph. However, the fields I tagged only show a specific value rather than a range of them.

I created a tag for each bill (bill_type_ones, bill_type_fives, etc.) For example, my tag bill_type_twentiesonly counts values where the field Twenties= 0 so when I chart count by tag, it doesn't show the whole range of values. It actually just shows me the opposite of what I want. Is it possible to set up a tag or some other function that will count any number greater than 0?

From what I read on tags it looks like it is only possible to tag field-value pairs rather than field- value ranges.

[DalJeanis · Accepted solution]

You really should delete those tags and set them up correctly.

However, you probably don't actually want the number of events with Twenties, you want the number of Twenties, so the tags really aren't doing you any good.

Try this -

Your search that returns the relevant event records
| bin _time span=1d
| stats sum(Ones) as Ones, sum(Fives) as Fives , ... sum(Hundreds) as Hundreds by _time
| untable _time BillType BillCount
| timechart span=1d sum(BillCount) as BillCount by BillType

[ellenbytech]

Thank you so much! In the mean time I was searching by using NOT tag::*twenties and summing the count of non-zero events that way but that didn't let me have the data on each type of bill to be displayed on one chart. From here I think I'll be able to divide it by location instead of time myself.

[DalJeanis]

Absolutely!

There are a couple of different tricks if you wanted to use this kind of code with multiple levels of breakdown, like _time and Location. I'll just give you one that you can slot into the above method.

 Your search that returns the relevant event records
 | bin _time span=1d
 | stats sum(Ones) as Ones, sum(Fives) as Fives , ... sum(Hundreds) as Hundreds by _time Location
 | eval combo = "_time="._time." Location=".Location
 | untable combo BillType BillCount
 | rex field=combo "_time=(?<_time>\d+)"
 | rex field=combo "Location=(?<Location>[^$]+)"
 | fields - combo

You now have one record with the BillCount for each BillType at each Location and _time, that you can feed to whatever visualization you'd like.

For instance, you could do this if you wanted to look at only the Hundreds across _time by Location

| where BillType="Hundreds"
| timechart span=1d sum(BillCount) by Location

Or if you wanted to see the Hundreds and Twenties, you could create a synthetic series name like this...

| where BillType="Hundreds" OR BillType="Twenties"
| eval Location = Location." - ".BillType
| timechart span=1d sum(BillCount) by Location

[somesoni2]

Could you provide your current search, current output and corresponding expected output?