■ はじめに
Sumo LogicにインポートできるALLIGATEのログ検索用クエリーを提供します。
以下のクエリーを提供します。
※クエリーをインポートするときに使用するJSONを提供します。
■ 現在出勤している社員リスト
照合ログから現在出勤している社員リストを表示するクエリーです。
クエリー名：List of employees currently working
JSON：
{
"type": "SavedSearchWithScheduleSyncDefinition",
"name": "List of employees currently working",
"search": {
"queryText": "_sourceCategory=\"alligate/accesslog\" and _collector=\"File Uploads\"\n| csv _raw extract 1 as timestamp,2 as userId,3 as userName,4 as furigana,5 as gateId, 6 as gateName,7 as deviceId,8 as deviceType,9 as logId,10 as logLabel,11 as sequenceNo,12 as voltage,13 as errorId\n| now() as nowTimestamp | formatDate(nowTimestamp, \"yyyy/MM/dd\") as nowDate\n| where !isBlank(userid) and longTimestamp >= parseDate(concat(nowDate,\" 00:00:00\"), \"yyyy/MM/dd HH:mm:ss\", \"Asia/Tokyo\") and longTimestamp <= parseDate(concat(nowDate,\" 23:59:59\"), \"yyyy/MM/dd HH:mm:ss\", \"Asia/Tokyo\")\n| count(userid),first(timestamp),last(timestamp) group by userid, username\n| where _count > 0 | sort by username asc",
"defaultTimeRange": "today",
"byReceiptTime": true,
"viewName": "",
"viewStartTime": "1970-01-01T00:00:00Z",
"queryParameters": [],
"parsingMode": "Manual"
},
"searchSchedule": null,
"description": ""
}
表示イメージ：
■ 出勤日数リスト
照合ログから今月の出勤日数を社員ごとに表示するクエリーです。
クエリー名：List of days the employee worked
JSON：
{
"type": "SavedSearchWithScheduleSyncDefinition",
"name": "List of days the employee worked",
"search": {
"queryText": "_sourceCategory=\"alligate/accesslog\" and _collector=\"File Uploads\"\n| csv _raw extract 1 as timestamp,2 as userId,3 as userName,4 as furigana,5 as gateId, 6 as gateName,7 as deviceId,8 as deviceType,9 as logId,10 as logLabel,11 as sequenceNo,12 as voltage,13 as errorId \n|parseDate(timestamp, \"yyyy/MM/dd HH:mm:ss\", \"Asia/Tokyo\") as longTimestamp\n|formatDate(longTimestamp, \"yyyy/MM\") as yserMonth\n|formatDate(longTimestamp, \"yyyy/MM/dd\") as yserMonthDay\n|sort by longTimestamp asc\n|now() as nowTimestamp | formatDate(nowTimestamp, \"yyyy/MM\") as nowMonth\n|where yserMonth = nowMonth\n|first(userid) as firstUser by yserMonth,yserMonthDay,gateid,userid,username\n|where !isBlank(userid)\n|first(gateid) as firstGate by yserMonth,yserMonthDay,userid,username\n|count(userid) as daysWorked by yserMonth,userId,userName|sort by userid asc",
"defaultTimeRange": "this month",
"byReceiptTime": true,
"viewName": "",
"viewStartTime": "1970-01-01T00:00:00Z",
"queryParameters": [],
"parsingMode": "Manual"
},
"searchSchedule": null,
"description": ""
}
表示イメージ：
■ ２ヶ月出勤していない社員リスト
照合ログから２ヶ月出勤していない社員を表示するクエリーです。
２ヶ月分のユーザーリストと３か月分のユーザーリストを比較して、２ヶ月分のユーザーリストに存在しない社員を表示します。
クエリー名：List of employees who have not worked for 2 months
JSON：
{
"type": "SavedSearchWithScheduleSyncDefinition",
"name": "List of employees who have not worked for 2 months",
"search": {
"queryText": "_sourceCategory=alligate/accesslog\n| csv _raw extract 1 as timestamp,2 as userId,3 as userName,4 as furigana,5 as deviceId, 6 as deviceType,7 as gateId,8 as gateName,9 as logId,10 as logLabel,11 as sequenceNo,12 as voltage,13 as errorId | parseDate(timestamp, \"yyyy/MM/dd HH:mm:ss\", \"Asia/Tokyo\") as longTimestamp | sort by longTimestamp asc\n| 3 as lastMonth\n| formatDate(now(), \"yyyy/MM/dd\") as nowDate\n| parseDate(nowDate, \"yyyy/MM/dd\", \"Asia/Tokyo\") as nowTimestamp\n| toLong(24*60*60*1000) as oneDay\n| nowTimestamp - toLong(oneDay*lastMonth*30) as targetTimestamp\n| toLong(targetTimestamp) as targetTimestamp\n| formatDate(targetTimestamp, \"yyyy/MM/dd\") as targetDate\n| where longTimestamp >= targetTimestamp and longTimestamp <= now()\n| where !isBlank(userid) | first(userid) as userid, last(timestamp) as lastDayOfWork group by userid, username\n| if([subquery:_sourceCategory=alligate/accesslog\n| csv _raw extract 1 as timestamp,2 as userId\n| parseDate(timestamp, \"yyyy/MM/dd HH:mm:ss\", \"Asia/Tokyo\") as longTimestamp | sort by longTimestamp asc\n| 2 as lastMonth\n| formatDate(now(), \"yyyy/MM/dd\") as nowDate\n| parseDate(nowDate, \"yyyy/MM/dd\", \"Asia/Tokyo\") as nowTimestamp\n| toLong(24*60*60*1000) as oneDay\n| nowTimestamp - toLong(oneDay*lastMonth*30) as targetTimestamp\n| toLong(targetTimestamp) as targetTimestamp\n| formatDate(targetTimestamp, \"yyyy/MM/dd\") as targetDate\n| where !isBlank(userid)\n| where longTimestamp >= targetTimestamp and longTimestamp <= now() | compose userid], true, false) as goingToWork\n| where !goingToWork | sort by userid asc",
"defaultTimeRange": "last 3 month",
"byReceiptTime": false,
"viewName": "",
"viewStartTime": "1970-01-01T00:00:00Z",
"queryParameters": [],
"parsingMode": "Manual"
},
"searchSchedule": null,
"description": ""
}
表示イメージ：
■ 照合エラーリスト
照合ログから本日の照合エラー一覧を表示するクエリーです。
クエリー名：List of access errors
JSON：
{
"type": "SavedSearchWithScheduleSyncDefinition",
"name": "List of access errors",
"search": {
"queryText": "_sourceCategory=alligate/accesslog | csv _raw extract 1 as timestamp,2 as userId,3 as userName,4 as furigana,5 as gateId, 6 as gateName,7 as deviceId,8 as deviceType,9 as logId,10 as logLabel,11 as sequenceNo,12 as voltage,13 as errorId\n| parseDate(timestamp, \"yyyy/MM/dd HH:mm:ss\", \"Asia/Tokyo\") as longTimestamp\n| now() as nowTimestamp | formatDate(nowTimestamp, \"yyyy/MM/dd\") as nowDate\n| where longTimestamp >= parseDate(concat(nowDate,\" 00:00:00\"), \"yyyy/MM/dd HH:mm:ss\", \"Asia/Tokyo\") and longTimestamp <= parseDate(concat(nowDate,\" 23:59:59\"), \"yyyy/MM/dd HH:mm:ss\", \"Asia/Tokyo\")\n| where logId = \"100\" or logId = \"101\" or logId = \"102\" or logId = \"103\" or logId = \"104\" or logId = \"105\" or logId = \"106\" or logId = \"107\" or logId = \"108\" or logId = \"110\" or logId = \"111\" or logId = \"112\" or logId = \"113\"\n| count(userid) by timestamp, userid, username, logId, logLabel, deviceId, deviceType\n| sort by timestamp desc",
"defaultTimeRange": "today",
"byReceiptTime": true,
"viewName": "",
"viewStartTime": "1970-01-01T00:00:00Z",
"queryParameters": [],
"parsingMode": "Manual"
},
"searchSchedule": null,
"description": ""
}
表示イメージ：
■ 電池残量アラート
電池を使用している製品（CylinderLock、HandleLockなど）をご利用の場合に、電池残量が少ないゲートの一覧を表示するクエリーです。
下記の条件に該当したゲートの一覧を表示します。
条件１：直近６ヶ月の間 voltage="3" or voltage="2.94" or voltage="2.8"の合計回数 ≧ 10,000回
条件２：直近３ヶ月の間 voltage="2.8"の回数 ≧ 4,000回
条件３：直近１ヶ月の間 voltage="2.76"の回数 ≧ 1回
※お客様の利用状況により、電池の消耗が異なり、精度が低い場合がございます。ご了承ください。
クエリー名：List of low battery gates
JSON：
{
"type": "SavedSearchWithScheduleSyncDefinition",
"name": "List of low battery gates",
"search": {
"queryText": "_sourceCategory=\"alligate/accesslog\" and _collector=\"File Uploads\"\n| csv _raw extract 1 as timestamp,5 as gateId,6 as gateName,12 as voltage\n| parseDate(timestamp, \"yyyy/MM/dd HH:mm:ss\", \"Asia/Tokyo\") as longTimestamp | sort by longTimestamp asc\n| 6 as lastMonth\n| formatDate(now(), \"yyyy/MM/dd\") as nowDate\n| parseDate(nowDate, \"yyyy/MM/dd\", \"Asia/Tokyo\") as nowTimestamp\n| toLong(24*60*60*1000) as oneDay\n| nowTimestamp - toLong(oneDay*lastMonth*30) as targetTimestamp\n| toLong(targetTimestamp) as targetTimestamp\n| formatDate(targetTimestamp, \"yyyy/MM/dd\") as targetDate\n| sort by longTimestamp asc \n| where longTimestamp >= targetTimestamp and longTimestamp <= now()\n| where [subquery:_sourceCategory=\"alligate/accesslog\" and _collector=\"File Uploads\"\n| csv _raw extract 1 as timestamp, 5 as gateId,12 as voltage\n| parseDate(timestamp, \"yyyy/MM/dd HH:mm:ss\", \"Asia/Tokyo\") as longTimestamp | sort by longTimestamp asc\n| 6 as lastMonth\n| formatDate(now(), \"yyyy/MM/dd\") as nowDate\n| parseDate(nowDate, \"yyyy/MM/dd\", \"Asia/Tokyo\") as nowTimestamp\n| toLong(24*60*60*1000) as oneDay\n| nowTimestamp - toLong(oneDay*lastMonth*30) as targetTimestamp\n| toLong(targetTimestamp) as targetTimestamp\n| formatDate(targetTimestamp, \"yyyy/MM/dd\") as targetDate\n| sort by longTimestamp asc \n| where longTimestamp >= targetTimestamp and longTimestamp <= now()\n| where voltage=\"2.8\" or voltage=\"3\" or voltage=\"2.94\" | count by gateid \n| where _count >= 10000 | compose gateid] or [subquery:_sourceCategory=\"alligate/accesslog\" and _collector=\"File Uploads\"\n| csv _raw extract 1 as timestamp, 5 as gateId,12 as voltage\n| parseDate(timestamp, \"yyyy/MM/dd HH:mm:ss\", \"Asia/Tokyo\") as longTimestamp | sort by longTimestamp asc\n| 3 as lastMonth\n| formatDate(now(), \"yyyy/MM/dd\") as nowDate\n| parseDate(nowDate, \"yyyy/MM/dd\", \"Asia/Tokyo\") as nowTimestamp\n| toLong(24*60*60*1000) as oneDay\n| nowTimestamp - toLong(oneDay*lastMonth*30) as targetTimestamp\n| toLong(targetTimestamp) as targetTimestamp\n| formatDate(targetTimestamp, \"yyyy/MM/dd\") as targetDate\n| sort by longTimestamp asc \n| where longTimestamp >= targetTimestamp and longTimestamp <= now()\n| where voltage=\"2.8\" | count by gateid \n| where _count >= 4000 | compose gateid] or [subquery:_sourceCategory=\"alligate/accesslog\" and _collector=\"File Uploads\"\n| csv _raw extract 1 as timestamp, 5 as gateId,12 as voltage\n| parseDate(timestamp, \"yyyy/MM/dd HH:mm:ss\", \"Asia/Tokyo\") as longTimestamp | sort by longTimestamp asc\n| 1 as lastMonth\n| formatDate(now(), \"yyyy/MM/dd\") as nowDate\n| parseDate(nowDate, \"yyyy/MM/dd\", \"Asia/Tokyo\") as nowTimestamp\n| toLong(24*60*60*1000) as oneDay\n| nowTimestamp - toLong(oneDay*lastMonth*30) as targetTimestamp\n| toLong(targetTimestamp) as targetTimestamp\n| formatDate(targetTimestamp, \"yyyy/MM/dd\") as targetDate\n| sort by longTimestamp asc \n| where longTimestamp >= targetTimestamp and longTimestamp <= now()\n| where voltage=\"2.76\" | count by gateid | where _count > 0 | compose gateid] | where voltage!=0 | parseDate(timestamp, \"yyyy/MM/dd HH:mm:ss\", \"Asia/Tokyo\") as longTimestamp |sort by longTimestamp| num(voltage) as voltage| count(gateid) as countGate,min(voltage) as minVoltage,first(voltage) as lastVoltage,first(timestamp) as lastTimestamp by gateid, gatename\n| where minVoltage <= lastVoltage",
"defaultTimeRange": "last 6 month",
"byReceiptTime": false,
"viewName": "",
"viewStartTime": "1970-01-01T00:00:00Z",
"queryParameters": [],
"parsingMode": "Manual"
},
"searchSchedule": null,
"description": ""
}
表示イメージ：
■ 通行権限に関係する操作を行ったリスト
操作ログから通行権限に関係する操作をしたログを表示するクエリーです。
クエリー名：List of operations related to gate permission
JSON：
{
"type": "SavedSearchWithScheduleSyncDefinition",
"name": "List of operations related to gate permission",
"search": {
"queryText": "_sourceCategory=\"alligate/ownerlog\"\n| csv _raw extract 1 as timestamp, 2 as userid, 3 as name, 4 as furigana, 5 as logtype, 6 as logmessage\n| parseDate(timestamp, \"yyyy/MM/dd HH:mm:ss\", \"Asia/Tokyo\") as longTimestamp\n| concat(logmessage, \":\") as logmessage\n| concat(\"msg=\", replace(logmessage, \"：\", \":\")) as logmessage\n| parse field=logmessage \"msg=*:\" as logmessage\n| now() as nowTimestamp | formatDate(nowTimestamp, \"yyyy/MM/dd\") as nowDate\n| where longTimestamp >= parseDate(concat(nowDate,\" 00:00:00\"), \"yyyy/MM/dd HH:mm:ss\", \"Asia/Tokyo\") and longTimestamp <= parseDate(concat(nowDate,\" 23:59:59\"), \"yyyy/MM/dd HH:mm:ss\", \"Asia/Tokyo\")\n| where logtype=\"014\" or logtype=\"015\" or logtype=\"016\" or logtype=\"017\" or logtype=\"018\" or logtype=\"024\" or logtype=\"025\" or logtype=\"026\" or logtype=\"027\" or logtype=\"032\" or logtype=\"033\" or logtype=\"041\" or logtype=\"042\" or logtype=\"043\" or logtype=\"070\"\n| count(logtype) by timestamp, userid, name, logtype, logmessage\n| sort by timestamp desc",
"defaultTimeRange": "today",
"byReceiptTime": true,
"viewName": "",
"viewStartTime": "1970-01-01T00:00:00Z",
"queryParameters": [],
"parsingMode": "Manual"
},
"searchSchedule": null,
"description": ""
}
表示イメージ：
