topic Re: 営業日・時間内のイベントのみカウント in Splunk Search

営業日・時間内のイベントのみカウント

satoshitonoike — Tue, 29 Sep 2020 12:23:13 GMT

現在、ヒストグラムにて業務の対応時間を集計しています。
実働時間の記載がないデータのため、２つの時間項目(受付日時　対応完了日時)を使用して対応時間を算出しております。
ですが、現状算出されるデータは受付日時と対応完了日時が土日以外の曜日で9-18の間にタイムスタンプが押されているイベントを取ってくるだけで、営業時間外の時間も対応工数として集計してしまっています。
できれば、該当イベントの営業時間内のみ対応工数として時間集計をして欲しいのです。
どのように設定すればよいでしょうか。
ご教示頂けますよう、よろしくお願いします。

searchは以下の様になっております。

Re: 営業日・時間内のイベントのみカウント

richgalloway — Mon, 16 Jan 2017 12:16:17 GMT

I believe your search has errors in it.

The first where clause is choosing events that happen on Saturday or Sunday instead of ignoring events on those days. It should be | where NOT (date_wday="saturday" OR date_wday="sunday").
The second where clause does not choose events within business hours. Try |where ( (myHour >= 9 AND myMinute >= 00) AND (myHour <= 18 AND myMinute <= 00) ).

Re: 営業日・時間内のイベントのみカウント

HiroshiSatoh — Tue, 17 Jan 2017 07:58:09 GMT

サーチ文はとりあえずおいといて、_time（タイムスタンプ）と受付日時、対応完了日時の関係と営業時間の定義を教えてもらえますか？

Re: 営業日・時間内のイベントのみカウント

satoshitonoike — Tue, 17 Jan 2017 09:04:50 GMT

Thank you for your replying to my question.
I fixed my search query as you proposed however, it still counting hours that are out of business time hour.
Regards. satoshi

Re: 営業日・時間内のイベントのみカウント

satoshitonoike — Tue, 17 Jan 2017 09:05:06 GMT

@HiroshiSatoh
　ご質問ありがとうございます。
　タイムスタンプに受付日時と対応完了日の両者を適応しております。この2項目の関係性としては、イベントの発生日時と終了日時です。
　営業時間の定義としては、毎日9:00-18:00（土日を除く）を定義しています。
　祝日はさすがに、入れようとすると工数がかかりすぎるため、省こうと考えています。
　よろしくお願いします。

Re: 営業日・時間内のイベントのみカウント

richgalloway — Tue, 17 Jan 2017 12:04:48 GMT

On closer inspection I see a logic error: myMinute can never be < 0. Try this

... | eval myTime = (myHour * 100) + myMinute
| where myTime >= 900 AND myTime <= 1800 
| ...

Re: 営業日・時間内のイベントのみカウント

satoshitonoike — Mon, 23 Jan 2017 00:32:41 GMT

Sorry for replying to you become late and, thank you for your answers.
However, it cannot work proper way which I want to see.
The search still collects the event where out of business hours.

Regards, satoshi.

Re: 営業日・時間内のイベントのみカウント

HiroshiSatoh — Mon, 23 Jan 2017 05:50:45 GMT

もう少し詳しく質問しますが、
①_timeにはどんな日時が設定されていますか？受付日時ですか？
②受付日と対応完了日で日をまたがることはありますか？
③毎日9:00-18:00（土日を除く）以外の受付日時は除外で良いですか？
④対応完了日時が18:00以降になった場合は18時までの時間を集計するでよいですか？

③がNGの場合
・9時前に受け付けて完了が9時以降のデータは対象にしますか？
・9時前に受け付けたデータは9時からの時間を集計しますか？

Re: 営業日・時間内のイベントのみカウント

satoshitonoike — Mon, 23 Jan 2017 06:27:24 GMT

再度ご質問、ありがとうございます。
①受付日時と対応完了日の両者をタイムスタンプフィールドに設定しているので、_timeはその２つのフィールドが設定されているものと思います（不要であれば、対応完了日を外します）。
②基本的に別日なので、日または月をまたぐことがあります。
③はい、おっしゃる通りです。
④これも、おっしゃる通りです。

追随して、欲を言ってしまうと、12時から13時の間（平日）も除外したいです。
ですが、これはできればという形なので、まずは上の状態で出るようにしたいです。
よろしくお願いします。

Re: 営業日・時間内のイベントのみカウント

HiroshiSatoh — Tue, 29 Sep 2020 12:29:41 GMT

①ですが、Splunkの_timeは１つです。１つのログにはどちらかしか設定できません。どちらですか？または全く違う時刻が設定されていますか？ログのイメージが提示できるのならお願いします。
※date_wdayは_timeの値で設定されます。

②ですが、日をまたぐと計算も複雑になりますが、受付日時から完了日時までの9:00-18:00（土日を除く）間の時間を集計するでいいですか？

③ですが、土日に受け付けて完了日時が平日の場合でも対象外になりますがそれでいいですか？

Re: 営業日・時間内のイベントのみカウント

melonman — Mon, 23 Jan 2017 10:44:50 GMT

データ量にもよりますが、例えば以下のようなアプローチではどうでしょか。

1.何曜日の何時台に受付したか、何曜日の何時台に完了したかを分離して別々の情報とし、
2.上記の2つのデータを時間を軸に結合し、
3.どの問い合わせが何曜日の何時台に対応していたかを見つけ、
4.その時刻が営業時間かどうか判定して、
5.その後煮るなり焼くなりする

ちらっと勝手にデータ作ってやってみました。

業務id,受付日時,対応完了日時
id,started,completed
1,2017/1/22 18:00,2017/1/23 10:00
2,2017/1/22 15:00,2017/1/28 01:00
3,2017/1/23 09:00,2017/1/23 09:30
4,2017/1/23 15:00,2017/1/23 18:00
5,2017/1/23 15:00,2017/1/23 18:00
6,2017/1/27 15:00,2017/1/27 18:00
7,2017/1/27 17:00,2017/1/30 10:00

土日除く9am-6pmが営業だと、各id（問い合わせ）については：
id1が23日の9時から10時までの1時間だけ対応
id2が数日間に渡って対応はしているが、22日9時よりまえと27日の18時以降、その他の日の9時〜6時に入らない時間帯は除外
id3は30分で対応終了
とか、そんな感じでしょうか。（ここで理解が間違ってたら以下はおかしくなりますが参考までに書き続けます）

上記のcsvをlookupsディレクトリにtest.csvとして保存し、以下を実行すると、それっぽい表がでてきます。

| inputlookup test.csv
| eval _time=strptime('started',"%Y/%m/%d %H:%M"), data="started"
| fields _time id data
| append [
| inputlookup test.csv
| eval _time=strptime('completed',"%Y/%m/%d %H:%M"), data="completed"
| fields _time id data ]
| sort  _time
| timechart span=10m list(data) by id
| filldown
| foreach * [eval <<FIELD>>=if(match('<<FIELD>>', "started") AND mvcount('<<FIELD>>')=1,1,0)]
| untable _time id isWorking
| timechart span=1h max(isWorking) as isWorking by id
| eval H=strftime(_time,"%H"), W=strftime(_time,"%A")
| search NOT (W="Saturday" OR W="Sunday" OR H<9 OR H>=18)

おそらくこの時点で、どの問い合せ(id)が何時台に対応されているかが1と0で出てくると思います。
あとはidごとにsumを取れば（単純にaddtotalcolsをつけてもOK）、各問い合わせに業務時間内に何時間つかったか、がでてくるとおもいます。

ヒストグラムについては上記サーチの後に以下のようにすればでてくるかとおもいます。

| addcoltotals labelfield=H label="Total"
| where H="Total"
| fields - _time W H
| transpose 0
| rename "row 1" as H 
| bin H bins=10 
| stats count by H
| makecontinuous H
| fillnull count

ずれてるかもしれませんが、お役に立てれば幸いです。
サーチが無駄にながくなってしまってすみません、、

Re: 営業日・時間内のイベントのみカウント

satoshitonoike — Tue, 24 Jan 2017 01:14:15 GMT

①に関しては、受付日時のタイムスタンプになっていました。
　イメージ図に関しては、申し訳ありませんが、提示しかねます。

②はおっしゃる通りです。

③に関しては、対象外になって大丈夫です。

Re: 営業日・時間内のイベントのみカウント

satoshitonoike — Tue, 24 Jan 2017 01:50:38 GMT

回答ありがとうございます。

おっしゃっている形の表示で間違いではないのです。
しかし、私のサーチの構成が下手なせいか、上手く問合せIDに紐づいて集計されないです。

そこで、２点質問があり、お答え頂けるとありがたいです。

【質問】
・現在使用している元データ以外にlookupを作らないと仰っているような形で表示できないということでしょうか？

・| foreach * [eval <>=if(match('<>', "started") AND mvcount('<>')=1,1,0)]
| untable _time id isWorking
　上記検索文の使い方と、isWorkingとはどこから来たのか、もしくはどのようなフィールドを指定するといった意味合いで書かれたのでしょうか？

よろしくお願いします。

Re: 営業日・時間内のイベントのみカウント

melonman — Tue, 29 Sep 2020 12:35:00 GMT

・現在使用している元データ以外にlookupを作らないと仰っているような形で表示できないということでしょうか？

元データだけでできるとおもいますよ。
私のテストデータでは、データをインデックスするのが手間だったので、
元データとしてcsvをつかっただけです。
ですので、| inputlookup test.csv あたりを index=問合せ対応_index として読み替えていただければと思います。
また、_timeでjoinをしている箇所がありますが、この部分でのポイントは、_timeをjoinの前の部分ではstartedの日時、joinの後の部分ではcompletedの日時にしている点です。

このjoinでおこなっているのは、以下の一つのイベントを：
受付ID=1, 受付時刻=2017/01/23 9:00, 完了時刻=2017/01/23 10:00

以下のような2つの情報に変換しています。

_time=2017/01/23 9:00, 受付ID=1, data=受付
_time=2017/01/23 10:00, 受付ID=1, data=完了

上記のようなデータに変換してから各種集計を行っています。

・| foreach * [eval <>=if(match('<>', "started") AND mvcount('<>')=1,1,0)]

各フィールドに対して処理や判断を書ける場合にforeachをつかいます。
今回は、各フィールドとなっている部分がtimechart の結果として出てきた各idで、
何時台に処理が開始したのか終了したのかを判定して、1か0に置き換えています。
具体的には、各idのデータを参照して、startedという文字列のみであれば1,
それ以外(データ無しもしくはcompletedもしくはstarted,completedの2つが入っている時間帯)は0とする、
としてます。
<>には、実際には各フィールド名がはいってきますので、今回の場合は、
1,2,3,4,5,6,7といったように順番にidごとに判定代入処理をおこなっていくことになります。

詳細はドキュメントのforeachコマンドの概要よんでいただいて、上記のサンプルデータとサンプルサーチを動かして見ていただければと思います。

・| untable _time id isWorking　isWorkingとはどこから来たのか

table形式になっているものを、分解する際にuntableというコマンドが使えます。
前段で各idがどの時間帯に対応されているかが1か0で表示されているところまででてきているかとおもいます。

_time 1 2 3 4
9:00 0 0 0 0
10:00 1 0 1 0
11:00 1 0 0 0
12:00 1 0 0 1

これを以下のように変換するのにuntableをつかってます。
isWorkingは上のテーブルのcellに当たる部分に名前をつけているイメージです。

_time,id,isWorking
9:00,1,0
9:00,2,0
9:00,3,0
9:00,4,0
10:00,1,1
10:00,2,0
10:00,3,1
10:00,4,0
11:00,1,1
11:00,2,0
11:00,3,0
11:00,4,0
12:00,1,1
12:00,2,0
12:00,3,0
12:00,4,1

test.csvを使ったサンプルでは、一旦10分毎に各idが対応されているかを判定してますが、
これを更に1時間集計（何時台）に再集計し直すさいにuntableを事前にやることでtimechart ... by id とできるようにしています。
もっと短くかけるとはおもいますが…(汗

Re: 営業日・時間内のイベントのみカウント

satoshitonoike — Tue, 24 Jan 2017 05:17:09 GMT

お早い返答ありがとうございます。

先程、いろいろな形で確かめてみましたが、データ量が多いせいなのか、私の認識が薄いせいかはわかりませんが、Hの数が1000-2000(H)と2000-3000(H)の集計になってしまうなど、きれいにヒストグラムになりません。

詳しくご回答くださったのに、使いこなせなくて申し訳ありません。。

Re: 営業日・時間内のイベントのみカウント

HiroshiSatoh — Tue, 24 Jan 2017 10:56:16 GMT

※melonmanさんのフィールド名を使わせて頂きました。

idの件数が多いとパフォーマンスの問題がでると思いますが、とりあえず計算するみたいです。結果はid毎の稼働時間の集計です。

･･･ date_wday!="saturday" AND date_wday!="sunday" AND date_hour>8 AND date_hour<18
|eval wk_started=strftime(strptime(started,"%Y/%m/%d%H:%M"),"%m/%d/%y:%H:%M:%S")
|eval wk_completed=strftime(strptime(completed,"%Y/%m/%d %H:%M"),"%m/%d/%y:%H:%M:%S")
|map search="|gentimes start=$wk_started$ end=$wk_completed$ increment=1h
|eval _time=starttime,id=$id$"
|eval wk_wday=strftime(_time,"%w"),wk_hour=strftime(_time,"%H")
|table id,_time,wk_wday,wk_hour
|where (wk_wday!=0 AND wk_wday!=6) AND (wk_hour>"08" AND wk_hour<"18" AND wk_hour!="12")
|stats count by id