Incident - Business Service Relationship Query
would anyone be kind enough to help me build our custom SQL / Odata Query? In our environment, we started with pairing the Business Service Class to our Incident Records (as a relationship to the "affected services" section of an IR). We'd like to have this visualized through a query - chart widget (probably bars would be the best option), where it would separate the bars according to what Business Service was paired to the IR (displayname of the Business Service on the horizontal side).
E.g bar 1: Hardware , bar 2: Exchange , bar 3: Teams , etc. , bar xy: null if the relationship doesn't exist between an IR and a Business Service.
and the vertical side would just show the number of the IRs which have these relationships.
We'd also love to be able to filter the incidents by date in the same way as some of the other queries can be filtered out on the portal. This date would reflect when the incident was created. (today, yesterday, last 7 days, last 30 days, etc.). It'd be also handy if there was a possibility to order the bars in a way where the Incidents with the most services would start out from the leftmost bar and Incidents with the least amount of paired business services would end at the rightmost bar (or vice versa, doesn't really matter that much).
Also if I clicked on one of the bars we'd like to get a list of all these IR's with the following columns:
ID of the IR, Displayname of the IR, Created Date, Affected User, Status, Support Group, Assigned User.
Any chance someone over here could help us with this "mildly" complex query, we'd be very grateful! 😊