Query Optimization: How best to consolidate fields across multiple records into a single record.
I am working on a query to allow me to see what connection properties a client software is using to connect.
The Log is in an XML format and I already have the parser working. now that i have records parsed i am trying to build a query that will take/join/merge fields of multiple records into a single record by session. (1 session >= 5 records). I am not a query guru so i have been stuck with how to approach this.
As you can see below there is not a single Record that contains ALL of the fields i am hoping to capture, but the group (sessionid) does.
After applying the parser and transactionizing on sessionID my results look like this.
The intended output would look like this.
I am not sure how best to structure the query (use join or subquery?) to get my intended output. Any input would be greatly appreciated.
fields= sfs_session_id,sfs_acct_login,srcIPaddr,kexAlg,dhGroup,dhBits,hostKeyAlg,encCipherIn,encCipherOut,macAlgIn,macAlgOut
Note: The screenshots are obviously not from sumologic, i used excel to convey the desired structure visually.
-
Official comment
hi Austin,
the values operator will likely save the day here as it will merge all possible values into one field using one more aggregate fields. If there are only two options: null or a value A you will just get A as the final value. It's often a easy alternative to transactionize for a use case like this.
so you could do something like
_sourcecategory=Loga or _sourcecategory=logB| parse ... as sessionid
| parse ... as A nodrop
| parse ... as B nodrop
| values (A) as A, values(B) as B by sessionid
you can have more than one field in the aggregation too if they always have a value say
| count as events,values (A) as A, values(B) as B by sessionid,field1,field2,field3.. etc
Comment actions
Please sign in to leave a comment.
Comments
1 comment