XQL Timeseries Chart

[Samuel_Mino]

I'm trying to build a timeseries chart that counts alert volume per day and that fills in zero values for days with no data. I have the following XQL that populates days with data but I'm unable to fill in a zero for all other days between now and the last event.

dataset = alerts | bin _time span = 1D timeshift = 1736879866 timezone = "America/New_York" | comp count() as numEvents by _time
| sort asc _time
| view graph type = line header = "Accessing bash history file using bash commands" xaxis = _time yaxis = numEvents

How do I do this?

[aspatil]

Hello @Samuel_Mino ,

Thanks for reaching out on LiveCommunity!

Below is the sample query for Incidents, please have a look and modify your query as per your requirements:
dataset = incidents | bin _time span = 1h | comp count (incident_id) as event_count by _time | sort asc _time | union ( dataset = xdr_data | filter event_type = ENUM.AGENT_STATUS | bin _time span=30m // Make sure this is smaller than your original time buckets | comp count() as empty_bucket_count by _time | alter empty_bucket_count = 0 ) | bin _time span=1h | comp sum(event_count) as event_count, sum(empty_bucket_count) as empty_bucket_count by _time | alter event_count = coalesce(event_count, empty_bucket_count)

If you feel this has answered your query, please let us know by clicking on "mark this as a Solution". Thank you.