Helpful Commands and Queries

this topic is a compilation of commands and queries that you can utilize with swimlane mongodb swimlane database once you’ve connected to mongodb’s swimlane database, you can use some of the queries listed here the queries are organized in this section by the collections targeted within them be sure to backup your swimlane data before using these commands update , remove , delete , deleteone , deletemany , or drop there is a file size limit of 500mb logs to find diagnostic information for the most recently encountered errors db logs find({level 'error'}) sort({date 1}) limit(30) to find diagnostic information for errors not strictly related to task execution db logs find({level 'error', taskname {$exists false}}) sort({date 1}) to find diagnostic information for errors strictly related to task execution, or for any recently executed tasks db logs find({level 'error', taskname {$exists true}}) sort({date 1}) to find recent execution history (start time, end time, outcome) db logs find({taskname 'gravityzone host details daily sync'}) sort({date 1}) limit(50) to 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) to find multiple instances of 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) to find log entries using regular expression searching db logs find({'message' {'$regex' '^failed to process the job an exception occurred job was automatically deleted'}, 'exception text' /emailimportretrievaljob/i, date {$gt\ new isodate('2017 06 06t17 25 00 000z')}}) sort({date 1}) limit(5) to 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) you can also 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}) to extract a large set of json documents to the console 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 mongodb limits the size of a record to 16mb to inspect an individual swimlane record’s properties and field values (after connecting to the swimlane database) `db records find({trackingfull 'case 6'})` to edit a field value in a swimlane record this 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" "not applicable" } } ) and db records update( { "trackingfull" "rc 1" }, { $set { "valuesdocument a5yckosvt6sxz" "not applicable" } } ) to find all records in given application with a malformation (incorrect null) in a certain field, and repair the affected 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}) to find all parent records in a given application that does not have any children db records find({applicationid '578aa3a50f1b8814c4556a7b', 'referencedrecordids' {$eq \[]}}) to find records with corrupted field values or other malformation db records find({"$text" {"$search" "jarray"}}) db records find({"$text" {"$search" "jtoken"}}) to find distinct values within a collection, e g to get the list of all values for a given record field in a given application db getcollection('records') distinct("valuesdocument ajs1o") to count records by application db records aggregate(\[{$group { id "$applicationid", count {$sum 1}}},{$sort {count 1}}]) to unlock all records db getcollection('records') update({locked true}, { $unset { locked 1, lockeddate 1, lockinguser 1}}) to determine 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() to 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 } } ]) to delete a record and its history this should only be used when record deletion through the swimlane ui has failed you can run this command after connecting to the swimlane database db records deleteone({trackingfull 'case 1'}) next, connect to the swimlanehistory database and run db records deletemany({'version trackingfull' 'case 1'}) see other swimlanehistory queries below settings to extend password expiration db settings update({}, {$set {'securityparameters passwordexpiration' 180}}) to disable active directory sync db settings update({}, {$set {'directory enabled' 'false'}}) reports to detect report filter corruption db reports find({"filters" {"$elemmatch" {"value v name t" "jvalue"}}}) count() to inspect report filter corruption db reports find({"filters" {"$elemmatch" {"value v name t" "jvalue"}}}) pretty() to purge report filter corruption db reports update({"filters" {"$elemmatch" {"value v name t" "jvalue"}}}, {$set {filters \[]}}) hangfire 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 to 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/}) to 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 to find all e mail ingest tasks relying on the sw\\ microsoft\\ exchange plugin db tasks find({'action descriptor name' 'exchange get email metadata'}) to remove ghost task menu entries (which result 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 to 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 } } ]); the next two queries list tasks that ran in the last 90 days for mongodb and with python for a 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"}}, ]) for a 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='admin', password='nowayjose', 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']) integration states to clear the state store (the data persisted by sw\\ context state ) for a given integration task db integrationstates deleteone( { " id" "integration task id"} ); aspnetusers to disable 2fa for a given user \#find user id first db getcollection('aspnetusers') find({}, {'username' 1}) pretty() #then use id to unset values db getcollection('aspnetusers') update({" id" "user id"}, {$unset { "onetimepasswordsecretbytes" 1, "isotpverified" 1, "isotpenforced" 1}}) gridfs (fs files and fs chunks) to detect orphaned plugin artifacts (often the result of failed plugin upgrades) ​db fs files find({filename /sw microsoft exchange/}) to find and/or delete the byte stream for an unnecessary 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 to detect large and/or orphaned files that were added to a swimlane record's 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 to find all of the fields in a given application 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 queries in this section are useful after connecting to mongodb’s swimlanehistory database records to retrieve every historic revision of a given record db records find({'version trackingfull' 'case 6'}) // alternate db records find({'referenceid' 'my record id'}) kubernetes to ascertain the mongodb connection string kubectl n $ns get secrets kubectl n $ns get secrets swimlane tasks o yaml echo "\[the value of data mongo swimlaneconnectionstring]” | base64 d to recover mongodb usernames and/or passwords from secrets kubectl n $ns get secrets # select desired secret (eg mongo admin in example) kubectl n $ns get secrets swimlane sw mongo admin o yaml echo "\[the value of user and/or password]” | base64 d python this section provides you with some easy methods to inspect json data returned from callees for a small amount of data, use json dumps in combination with print to inspect the data in the console from swimlane import swimlane swimlane = swimlane('https //host', 'user', 'password', verify ssl=false) app = swimlane apps get(name='doug sa change selection field') selection field def = app get field definition by name('selection') print(json dumps(selection field def, sort keys=true, indent=4, separators=(",", " "))) for a larger json object, write it out to a file and then inspect the text file with open('c \temp\inspect json txt', 'a') as f json dump(selection field def, f, sort keys=true, indent=4, separators=(",", " "))