Helpful Commands and Queries

mongodb swimlane database once you’ve connected to mongodb’s swimlane database, here are some of the many queries that may be useful these are grouped by the collections targeted in the queries note before employing any of the following commands that make use of update , remove , delete , deleteone , deletemany , or drop be sure to take a sound backup of swimlane data logs find diagnostic info for the most recently encountered errors db logs find({level 'error'}) sort({date 1}) limit(30) find diagnostic info for errors not strictly related to task execution db logs find({level 'error', taskname {$exists false}}) sort({date 1}) find diagnostic info for errors strictly related to task execution, for any recently executed task(s) db logs find({level 'error', taskname {$exists true}}) sort({date 1}) find recent execution history (start time, end time, outcome) db logs find({taskname 'gravityzone host details daily sync'}) sort({date 1}) limit(50) find instances of specific error messages or print statement diagnostic messages db logs find({message /update existing soc record/i}) sort({date 1}) limit(50) find instances of multiple specific error messages // separately db getcollection('logs') find({message /create task from alerts/i}) sort({date 1}) limit(10) db getcollection('logs') find({message /validationexception/i}) count() // together db getcollection('logs') find({$and \[{message /validationexception/i}, {message /create task from alert/i}]}) sort({date 1}) limit(20) // find one but exclude the other db getcollection('logs') find({$and \[{message /validationexception/i}, {message {$not /create task from alert/i}}]}) sort({date 1}) limit(20) find log entries using regular expression searching db logs find({'message' {'$regex' '^failed to process the job an exception occured job was automatically deleted'}, 'exception text' /emailimportretrievaljob/i, date {$gt\ new isodate('2017 06 06t17 25 00 000z')}}) sort({date 1}) limit(5) find all log entries within a certain span of time db logs find({date {$gte isodate('2017 11 28t14 00 00 000z'), $lte isodate('2017 11 28t15 00 00 000z')}}) count() db logs find({level 'error', date {$gte isodate('2018 09 11t22 00 00 000z'), $lte isodate('2018 09 18t17 20 00 000z')}}) sort({date 1}) limit(100) combine several of the above needs (time span inclusion, specific message exclusion) db getcollection('logs') aggregate( \[ { $project { date {$and \[{$gte isodate('2017 11 20t21 00 00 000z')}, {$lte isodate('2017 11 20t21 07 00 000z')}]}, message {$and \[{$not /email asset/i}, {$not /getnewmail/i}]} } } ] ) sort({date 1}) extract a large set of json documents to the console records = \[]; var cursor = db logs find({date {$gte isodate('2017 09 18t00 00 00 000z')}}) sort({date 1}); while(cursor hasnext()) { records push(cursor next()) } print(tojson(records)); // this can be done for any collection logs, records, tasks, etc records inspect an individual swimlane record’s properties and field values (after connecting to the swimlane database) db records find({trackingfull 'case 6'}) edit a field value in a swimlane record (which requires two commands because every field value is stored in two places within the underlying record) db records update( { "trackingfull" "rc 1", "values k" 'a5yckosvt6sxz' }, { $set { "values $ v v v" "not applicable" } } ) db records update( { "trackingfull" "rc 1" }, { $set { "valuesdocument a5yckosvt6sxz" "not applicable" } } ) find all records in given app with a malformation (incorrect null) in a certain field, and repair the effected records // find effected records db records find({applicationid '578aa3a50f1b8814c4556a7b', 'values k' '578aa43c1b095222d1ad4d49', 'values v' null}) // fix one db records update({applicationid '578aa3a50f1b8814c4556a7b', trackingfull 'alrt xxxxx', 'values k' '578aa43c1b095222d1ad4d49', 'values v' null}, {$pull {'values' {'k' '578aa43c1b095222d1ad4d49'}}}, {$unset {'valuesdocument 578aa43c1b095222d1ad4d49' ''}}) // fix all db records update({applicationid '578aa3a50f1b8814c4556a7b', 'values k' '578aa43c1b095222d1ad4d49', 'values v' null}, {$pull {'values' {'k' '578aa43c1b095222d1ad4d49'}}}, {$unset {'valuesdocument 578aa43c1b095222d1ad4d49' ''}}, {multi\ true}) find all parent records in a given app who do not currently have any children db records find({applicationid '578aa3a50f1b8814c4556a7b', 'referencedrecordids' {$eq \[]}}) find records with corrupted field values or other malformation db records find({"$text" {"$search" "jarray"}}) db records find({"$text" {"$search" "jtoken"}}) find distinct values within a collection, e g get the list of all values for a given record field in a given app db getcollection('records') distinct("valuesdocument ajs1o") count records by application db records aggregate(\[{$group { id "$applicationid", count {$sum 1}}},{$sort {count 1}}]) unlock all records db getcollection('records') update({locked true}, { $unset { locked 1, lockeddate 1, lockinguser 1}}) ascertain the count of records that are restricted or not yet restricted db getcollection('records') find( { 'applicationid' {$eq 'applicaion id here'}, 'allowed id' {$eq \[]} // 'allowed id' {$eq 'user id here'} can also check for records that have specific user/group id restrictions } ) count() find records with text and/or numeric list fields whose values were improperly created via the restful api // find records whose list field values were not given any identifiers db getcollection('records') find({ "values" { $elemmatch { $and \[ { "v t" { $regex "core models record listitem " } }, { $or \[ { "v v" { $elemmatch { " id" { $exists false } } } }, { "v v" { $elemmatch { " id" null } } } ] } ] }} }) // find records whose list field values have identifiers, but these are identifiers are not unique within a given field in a given record db records aggregate(\[ {$unwind "$values"}, {$unwind "$values v v"}, {$group { id {valueid "$values v v id", recordid "$ id", fieldid "$values k", type "$values v t"}, count {$sum 1}} }, {$match { " id type" { $regex /^core models record listitem / }, " id valueid" {$exists true}, " id valueid" {$ne null}, count {$gt 1} } }, {$project { " id valueid" 1, " id recordid" 1, " id fieldid" 1, "count" 1 } } ]) delete a record and its history should only be used when record deletion through the swimlane ui has failed run the following command after connecting to the swimlane database db records deleteone({trackingfull 'case 1'}) then connect to the swimlanehistory database and run the following command db records deletemany({'version trackingfull' 'case 1'}) see other swimlanehistory queries below settings disable active directory sync db settings update({}, {$set {'directory enabled' 'false'}}) reports detect report filter corruption db reports find({"filters" {"$elemmatch" {"value v name t" "jvalue"}}}) count() inspect report filter corruption db reports find({"filters" {"$elemmatch" {"value v name t" "jvalue"}}}) pretty() purge report filter corruption db reports update({"filters" {"$elemmatch" {"value v name t" "jvalue"}}}, {$set {filters \[]}}) hangfire how to purge the hangfire job queue // unfortunately, there is no method for clearing only specifically chosen tasks // delete all task execution data db hangfire jobgraph drop() // delete all task execution data (more thorough) db hangfire job remove({}); db hangfire jobqueue drop(); db hangfire aggregatedcounter drop(); db hangfire jobgraph drop(); tasks find all e mail ingest tasks of type built in imap import db tasks find({'action descriptor name' /email import/}) db tasks find({'trigger type' /email/}) find all integration tasks that rely on a specific asset db getcollection('tasks') find({'action assetid' 'atdgodcw1mtxx'}) // for most tasks do this db getcollection('tasks') find({'triggers assetid' 'atdgodcw1mtxx'}) // for built in tasks do this find all e mail ingest tasks relying on the sw microsoft exchange plugin db tasks find({'action descriptor name' 'exchange get email metadata'}) remove “ghost” task menu entries (resulting from creating a task with the same name as a task that was previously deleted by application deletion // option 1 // swimlane ui navigate to swimlane > collections > menu items find by task name > delete // option 2 db getcollection('menuitems') find({name 'ars submit v3 vector string' }) // option 3 (as a python script to find and delete all ghost entries) from pymongo import mongoclient import ssl mongo connection string = 'mongodb //swimlane\ ur4swimlane!@192 168 56 121 27017/swimlane' client = mongoclient(mongo connection string, ssl=true, ssl cert reqs=ssl cert none) db = client\['swimlane'] tasks = db\['tasks'] menu = db\['menuitems'] menu items = menu find({'type' 2}) for item in menu items task id = item\['taskid'] try task = tasks find one({' id' task id}) if not task menu delete one({' id' item\[' id']}) except pass find all tasks where the script includes the value ‘pendulum’ db getcollection('tasks') aggregate(\[ { $project { name 1, id 1, match { $gt \[{ $indexofbytes \[ "$action script", "pendulum"] }, 1] } } }, { $match { match true } } ]); list tasks that ran in the last 90 days mongodb query db hangfire jobgraph aggregate(\[{ "$match" {"parameters task name" {"$exists" true, "$ne" "null"}, "createdat" { "$exists" true, "$lte" new date(isodate() gettime() 90 24 60 60000)}}}, {"$group" {" id" "$parameters task name"}}, ]) python script \# list tasks that ran in the last 90 days import datetime from pymongo import mongoclient import ssl client = mongoclient(host='localhost', port=27017, username='insert username', password='example password here', authsource='admin', authmechanism='scram sha 1', ssl=true, ssl cert reqs=ssl cert none) swimlane \# \<class 'pymongo collection collection'> tasksexecuted = client get collection("hangfire jobgraph") \# \<class 'pymongo command cursor commandcursor'> tasks executed docs = tasksexecuted aggregate(\[{ "$match" {"parameters task name" {"$exists" true, "$ne" "null"}, "createdat" { "$exists" true, "$lte" datetime datetime today() + datetime timedelta( 90)}}}, {"$group" {" id" "$parameters task name"}}, ]) \# \<class 'list'> tasks executed = list(tasks executed docs) for taskid in tasks executed taskid\['task name'] = taskid pop(' id') print(taskid\['task name']) integrationstates clear the state store (the data persisted by sw context state ) for a given integration task db integrationstates deleteone( { " id" "integration task id"} ); gridfs (fs files and fs chunks) detect orphaned plugin artifacts (often the result of failed plugin upgrades) db fs files find({filename /sw microsoft exchange/}) find and/or delete the byte stream for superfluous plugin db getcollection('fs chunks') find({'files id' 'uploaded file id'}) // the uploaded file id is the id value from the spurious entry(ies) resulting from the above query, if present detect large and/or orphaned files that are/were added to swimlane record attachments fields db fs chunks aggregate(\[{$group { id "$files id", chunks {$sum 1}}}, {$sort {chunks 1}}]) foreach(function(f){ if (f chunks > 15) { print(f id + "\t" + f chunks) } }) applications find all of the fields in a given app of a given type db applications find( { fields { $elemmatch {fieldtype 8, targetid null } } }, {'fields $ name' 1}) // field types are 1 text, 2 numeric, 3 selection, 4 date, 5 user/group, 6 attachments 7 tracking id, 8 reference, 10 comments, 11 history, 12 list // there is no 9 signalrmessages setting a timeout on signalr (web socket) messages db getcollection('signalrmessages') createindex({ "createddate" 1 }, { expireafterseconds 300 } ) mongodb swimlanehistory database the following queries are useful after connecting to mongodb’s swimlanehistory database records retrieve every historic revision of a given record db records find({'version trackingfull' 'case 6'}) // alternate db records find({'referenceid' 'my record id'}) enjoy using these commands!