Working days - how to find

Elastic Stack Kibana
1

Hi,
I have a problem.
I'm getting data from the api which lists the rooms that users have booked.
Each room has set working days and hours. I need to make a table where the Average real resource usage will be calculated (the formula is ((100*sum(realDuration))/(max(resource.workingMinutesInDay)*unique_count(<Count Of Working day by timerange>)))

count of working days in week is saved in resource.countOfWorkingDayOneWeek

But I don't know how to find out how many days were working days by timerange (some resources have working days set to Monday-Friday, some to Monday-Sunday, some to Wednesday-Friday)

So I was wondering if anyone has encountered this before.

2

Hi @TheyCallMeTrinity,

Can you share your index mapping? It's difficult to tell without knowing the data structure, but it sounds like you want to do an aggregation based on a date range.

3 
{
  "dapi_events": {
    "mappings": {
      "properties": {
        "action": {
          "properties": {
            "action": {
              "type": "text",
              "fields": {
                "keyword": {
                  "type": "keyword",
                  "ignore_above": 256
                }
              }
            },
            "created": {
              "type": "date"
            },
            "doneBy": {
              "type": "text",
              "fields": {
                "keyword": {
                  "type": "keyword",
                  "ignore_above": 256
                }
              }
            },
            "id": {
              "type": "text",
              "fields": {
                "keyword": {
                  "type": "keyword",
                  "ignore_above": 256
                }
              }
            }
          }
        },
        "area": {
          "properties": {
            "id": {
              "type": "long"
            },
            "name": {
              "type": "text",
              "fields": {
                "keyword": {
                  "type": "keyword",
                  "ignore_above": 256
                }
              }
            },
            "parentAreas": {
              "type": "text",
              "fields": {
                "keyword": {
                  "type": "keyword",
                  "ignore_above": 256
                }
              }
            }
          }
        },
        "attendees": {
          "properties": {
            "email": {
              "type": "text",
              "fields": {
                "keyword": {
                  "type": "keyword",
                  "ignore_above": 256
                }
              }
            },
            "id": {
              "type": "text",
              "fields": {
                "keyword": {
                  "type": "keyword",
                  "ignore_above": 256
                }
              }
            },
            "name": {
              "type": "text",
              "fields": {
                "keyword": {
                  "type": "keyword",
                  "ignore_above": 256
                }
              }
            },
            "responseStatus": {
              "type": "text",
              "fields": {
                "keyword": {
                  "type": "keyword",
                  "ignore_above": 256
                }
              }
            }
          }
        },
        "createdDateTime": {
          "type": "date"
        },
        "eventDifferences": {
          "properties": {
            "key": {
              "type": "text",
              "fields": {
                "keyword": {
                  "type": "keyword",
                  "ignore_above": 256
                }
              }
            },
            "value": {
              "type": "text",
              "fields": {
                "keyword": {
                  "type": "keyword",
                  "ignore_above": 256
                }
              }
            }
          }
        },
        "finishedAsPlanned": {
          "type": "boolean"
        },
        "iCalUId": {
          "type": "text",
          "fields": {
            "keyword": {
              "type": "keyword",
              "ignore_above": 256
            }
          }
        },
        "id": {
          "type": "text",
          "fields": {
            "keyword": {
              "type": "keyword",
              "ignore_above": 256
            }
          }
        },
        "importance": {
          "type": "text",
          "fields": {
            "keyword": {
              "type": "keyword",
              "ignore_above": 256
            }
          }
        },
        "isAllDay": {
          "type": "boolean"
        },
        "isCancelled": {
          "type": "boolean"
        },
        "lastModifiedDateTime": {
          "type": "date"
        },
        "onlineMeetingProvider": {
          "type": "text",
          "fields": {
            "keyword": {
              "type": "keyword",
              "ignore_above": 256
            }
          }
        },
        "organizer": {
          "properties": {
            "email": {
              "type": "text",
              "fields": {
                "keyword": {
                  "type": "keyword",
                  "ignore_above": 256
                }
              }
            },
            "name": {
              "type": "text",
              "fields": {
                "keyword": {
                  "type": "keyword",
                  "ignore_above": 256
                }
              }
            },
            "responseStatus": {
              "type": "text",
              "fields": {
                "keyword": {
                  "type": "keyword",
                  "ignore_above": 256
                }
              }
            }
          }
        },
        "plannedDuration": {
          "type": "long"
        },
        "plannedEnd": {
          "type": "date"
        },
        "plannedStart": {
          "type": "date"
        },
        "realDuration": {
          "type": "long"
        },
        "realEnd": {
          "type": "date"
        },
        "realStart": {
          "type": "date"
        },
        "resource": {
          "properties": {
            "baseUtcOffset": {
              "type": "long"
            },
            "capacity": {
              "type": "long"
            },
            "daysOfWeek": {
              "type": "text",
              "fields": {
                "keyword": {
                  "type": "keyword",
                  "ignore_above": 256
                }
              }
            },
            "displayName": {
              "type": "text",
              "fields": {
                "keyword": {
                  "type": "keyword",
                  "ignore_above": 256
                }
              }
            },
            "email": {
              "type": "text",
              "fields": {
                "keyword": {
                  "type": "keyword",
                  "ignore_above": 256
                }
              }
            },
            "endTime": {
              "type": "text",
              "fields": {
                "keyword": {
                  "type": "keyword",
                  "ignore_above": 256
                }
              }
            },
            "id": {
              "type": "long"
            },
            "resourceTypeId": {
              "type": "long"
            },
            "resourceTypeName": {
              "type": "text",
              "fields": {
                "keyword": {
                  "type": "keyword",
                  "ignore_above": 256
                }
              }
            },
            "serverTypeId": {
              "type": "long"
            },
            "startTime": {
              "type": "text",
              "fields": {
                "keyword": {
                  "type": "keyword",
                  "ignore_above": 256
                }
              }
            },
            "supportsDaylightSavingTime": {
              "type": "boolean"
            },
            "timeZoneInfo": {
              "type": "text",
              "fields": {
                "keyword": {
                  "type": "keyword",
                  "ignore_above": 256
                }
              }
            },
            "username": {
              "type": "text",
              "fields": {
                "keyword": {
                  "type": "keyword",
                  "ignore_above": 256
                }
              }
            },
            "workingMinutesInDay": {
              "type": "long"
            }
          }
        },
        "resourceProperties": {
          "properties": {
            "dataTypeId": {
              "type": "long"
            },
            "dataTypeName": {
              "type": "text",
              "fields": {
                "keyword": {
                  "type": "keyword",
                  "ignore_above": 256
                }
              }
            },
            "defaultValueBoolean": {
              "type": "boolean"
            },
            "defaultValueNumber": {
              "type": "long"
            },
            "description": {
              "type": "text",
              "fields": {
                "keyword": {
                  "type": "keyword",
                  "ignore_above": 256
                }
              }
            },
            "id": {
              "type": "text",
              "fields": {
                "keyword": {
                  "type": "keyword",
                  "ignore_above": 256
                }
              }
            },
            "matchAll": {
              "type": "boolean"
            },
            "name": {
              "type": "text",
              "fields": {
                "keyword": {
                  "type": "keyword",
                  "ignore_above": 256
                }
              }
            },
            "resourcePropertyItems": {
              "properties": {
                "defaultValue": {
                  "type": "boolean"
                },
                "id": {
                  "type": "text",
                  "fields": {
                    "keyword": {
                      "type": "keyword",
                      "ignore_above": 256
                    }
                  }
                },
                "name": {
                  "type": "text",
                  "fields": {
                    "keyword": {
                      "type": "keyword",
                      "ignore_above": 256
                    }
                  }
                },
                "resourceValue": {
                  "type": "boolean"
                }
              }
            },
            "resourceTypeIds": {
              "type": "long"
            },
            "resourceValueBoolean": {
              "type": "boolean"
            },
            "resourceValueNumber": {
              "type": "long"
            }
          }
        },
        "responseStatus": {
          "type": "text",
          "fields": {
            "keyword": {
              "type": "keyword",
              "ignore_above": 256
            }
          }
        },
        "sensitivity": {
          "type": "text",
          "fields": {
            "keyword": {
              "type": "keyword",
              "ignore_above": 256
            }
          }
        },
        "seriesMasterId": {
          "type": "text",
          "fields": {
            "keyword": {
              "type": "keyword",
              "ignore_above": 256
            }
          }
        },
        "serviceAccount": {
          "properties": {
            "email": {
              "type": "text",
              "fields": {
                "keyword": {
                  "type": "keyword",
                  "ignore_above": 256
                }
              }
            },
            "eventId": {
              "type": "text",
              "fields": {
                "keyword": {
                  "type": "keyword",
                  "ignore_above": 256
                }
              }
            },
            "name": {
              "type": "text",
              "fields": {
                "keyword": {
                  "type": "keyword",
                  "ignore_above": 256
                }
              }
            },
            "responseStatus": {
              "type": "text",
              "fields": {
                "keyword": {
                  "type": "keyword",
                  "ignore_above": 256
                }
              }
            }
          }
        },
        "showAs": {
          "type": "text",
          "fields": {
            "keyword": {
              "type": "keyword",
              "ignore_above": 256
            }
          }
        },
        "subject": {
          "type": "text",
          "fields": {
            "keyword": {
              "type": "keyword",
              "ignore_above": 256
            }
          }
        }
      }
    }
  }
}
4

and i need table like this

output
output604×532 34.8 KB

5

That's not in your mapping?

Also you are using a lot of this type of multi-field, which is probably a bit of a waste and you might want to clean it up.

6

In "daysOfWeek" I have saved the days Monday, Tuesday, Wednesday,.... And then via the scripted field I have the length of the array. And I need to find out if the user enters, for example, a time range of 90 days, how many of those days were working

7

I still don't have a solution. Is there anyone here who has solved a similar thing please? Alternatively, does anyone have experience with any paid advice?

8

I've not done anything similar to this but I'll give it a try. I'm assuming you're looking for some help on how to calculate the number of days based on the list of days from the daysOfWeek field?

I think you need to do a rather involved script, similar to this example for C# where you calculate the number of each of those days, making use of the Painless DaysOfWeek functionality similar to the java.time package mirrored in Painless.

Personally it's probably something I would calculate in a script, or even making use of a calendar capability to make sure I covered bank holidays, which I'm not sure from your mapping above you have access to in your document set.

Hopefully that gives you an idea of where to start!

9

I have the number of working days available for each resource. And I need to calculate the Average resource usage based on the selected timerange (30 days, a week, 3 months, whichever the user chooses) - the formula should look something like this ((100*sum(realDuration))/(max(resource.workingMinutesInDay)*unique_count(TotalCountOfWorkingDay)))
but I have no idea how to get to the TotalCountOfWorkingDay. What is the way to take something like params._interval in lens in TBSV and then pull only working days from that params._interval

image
image1599×680 56.1 KB

10

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.