I've been learning CrowdStrike's LogScale platform recently. To help myself learn the CrowdStrike Query Language (CQL) I figured I'd do some analysis of game data I had collected awhile ago. Help me learn and then create this post to perhaps help others who might be learning CQL as well.
That end, this post is a more written version of the live & somewhat interactive LogScale dashboard located: here.
I'm providing the data itself at the bottom of this post if you want to monkey around with it.
It isn't uncommon for users of tools like LogScale to not know where to start from a query perspective. Something unique with LogScale is being able to easily share dashboards outside of the tool itself. This is a pretty cool capability! The other place where I see individuals somewhat struggle is how to create a dashboard. Not mechanically as much as how to lay out data.
I should note the way I structured both my queries and the eventual dashboard should not be considered the 'right' way to do things. As with any language there are often multiple ways to achieve the same outcome. Start by making sure you are displaying the data you think you are displaying and then work toward efficiencies. As you gain experience that efficiency starts to be baked into your initial query creation.
A great CQL primer can be found: here
My original Splunk version of this post: here
Feel free to jump between the two posts to do a comparison of the query languages. That's fine as folks might have a Splunk frame of reference. I might even call out a few things here and there. What I'm not trying to do with this post is to say one tool is better than the other.
Data Background - there used to be this great little mobile game called Darkfire Heroes. One of the game modes was a 1v1 PvP match. They once did a 2 day PvP event to see which clans would get the most wins. Outside of the event itself, each clan could 'fill' a weekly rewards chest through any PvP win. Participation in the event then is a 2fer! As a side note, clans could hold up to 50 people. I should mention this data was manually collected and needed to be collected prior to the event's end. There are some gaps in terms of player names, all clan names, all clan participation, etc.
Analytic Goals - putting yourself in the shoes of a game admin or owner is not too much different that being a system admin (conceptually). In my case I'd want to do know things like
- What does participation overall look like?
- What clans are fully active and what does that drop off look like?
- Was the event enough of a driver to have clans fill their clan rewards chest?
- How full are clans?
- And then fun things like looking at the top 10 and comparing activity to the rest of the game dataset. That last part is beyond what I have of course.
Let's begin!
Oh I should note since I didn't ingest this data and LogScale doesn't currently have a way to simply pull up an uploaded CSV, I created a search with the raw data, saved it, and then call up that search as a function [ ie save the query as dfh_clan efforts then start a separate query with $dfh_clan_efforts() ].
1. Total Clans Participating
| select(@rawstring)
Creating events in LogScale feels a bit different to me than in Splunk. I'm not sure why TBH. I think because the entire event is captured in the command itself. In Splunk you use makeevent, add an eval for the strings, and then break the string up if you are creating multiple events. Either language, you probably wont be using the command much. Here I just need to call out there was 236 clans. Don't give me grief - I manually collected the data I do have!
2. Total Matches Played
| sum(wins, as=wins)
| games := wins * 2
| drop([wins])
Since the game showed just wins I needed to swag how many games were actually played. I was pretty generous and applied an across the board 50% win average (note line 3). This isn't quite a fermi problem but for us it moves the needle. Ironically when I was reviewing my Splunk post I realized I posted the wrong output number in the screenshot. Whoops.
I'll call out the assignment operator ( := ) as an interesting bit with CQL. While there is an eval command, this can serve as a substitute or shorthand of that function. I found myself using it a lot.
3. Clan Chests Filled
| wins > 1000
| count()
It actually took 1,186 PvP wins to fill a clan rewards chest but I collected my data a few hours before it ended so gave people the benefit of the doubt. Folks will note that you can simply drop filter statements as you go. You can also use boolean logic in the filter statements though given the small dimensionality of my data I don't do that in any of my queries. For example Diable2 and SpawnsofDiablo were separate clans but were related as a 'clan family.' If I wanted to pull them up together in this query I could have done
| wins > 1000 clan=*Diablo*
Note searching field values in CQL is case sensitive and luckily both clans used a capital D. Since I don't use any regex stuffs in my queries I'll bring up a few things here. I could have invoked the regex engine and told it to look for the string "diablo" in a case insensitive manner like this
| wins > 1000 clan=/diablo/i
Note the forward slashes and the lower case i at the end. If I wanted to pull up the Diablos and say, LegendsOnly I could do any of the following to include a regex OR.
| wins> 1000 clan=*Diablo* or clan=LegendsOnly
| in(field=clan, values=[*Diablo*, LegendsOnly])
| wins> 1000 clan=/(diablo|legendsonly)/i
What I don't know is if there is any value in doing (?:diablo|legendsonly) vs (diablo|legendsonly).
4. Clan Wins and Percent of Total Battles
| foo := "x"
| groupBy([foo], function=[sum(wins, as = totalWins), groupBy([clan], function=collect([wins]))])
| percOfTotalWins := (wins/totalWins)*100
| round("percOfTotalWins")
| table([clan, wins, totalWins, percOfTotalWins], sortby=percOfTotalWins)
Here is where I saw a delta between CQL and SPL - eventstats. I love me some eventstats! I'm not sure how frequently it is used by the average Splunker /shrug. It lets you create statistical evaluations across your dataset as a new field - like stats and eval had a baby. In CQL you can nest groupBy statements within groupBy statements. GroupBy is largely a SPL stats equivalent. TBH I'm still wrapping my head around the nesting bit.
The use case here is pretty simple - I want to have my query count the total number of wins so I can determine individual clan percent. Since I didn't have a secondary field to aggregate across all events, I created a new field that would be applied to all events (foo). Then within that same groupBy I simply pulled out the individual clan wins (collect ~ splunk stats values). There might be a better way to do what I did?
I'll call out then if you want your groupBy to do multiple functions or evaluations, you need to wrap your function in square brackets. Square brackets in CQL let the engine know it is dealing with an array. You will use this a good bit - for example look at the table command here.
5. Clan Wins as Percent of Total Battles - the Pie Version
| drop([finish_position, members])
Conceptually panel 4 and 5 are the same. I wanted to go through the gyrations in panel 4 to highlight how you can process the data. Here I'm simply calling up the data, keeping what I want, and by pivoting to a Pie chart letting the engine do the work.
6 a&b - Contributors Relative to Wins
| join(query={ $dfh_clan_efforts() | wins > 0 | groupBy([clan], function=count(as = contributors)) }, field=[clan], include=[contributors], mode=left)
I really liked how the Splunk version of this turned out as I'm effectively looking at 4 dimensions of data at once. Splunk is also 20 yrs old this year and has a ton of iterative development.
What I'll highlight here from a visualization standpoint is with LogScale, as of this time, in order to use a bar chart you will need to use a groupBy statement in your query. You can't simply take the same data in tabular form and use that visualization. I thought about trying a scatter plot but wanted to keep the posts relatively the same for the 1s and 1s of readers who find this let alone flip back and forth between the SPL and CQL version of this post.
I will call out how I brought the two datasets together via join. I didn't use the CQL match command as I needed to perform some calculations before merging the data. I'm also doing a left, or outer, join vs inner. The difference is inner joins (regardless of language) are used when you want to only show results where results are the same. Left joins are when you still want to see all of the data from the initial portion of your query.
7 & 8 - Clan Chest Filled Distribution
| full_chest := 1186
| perc_filled := (wins/full_chest)*100
| case{perc_filled >= 100 | perc_filled := 100; * | round(perc_filled)}
| case{ perc_filled >= 90 | foo := "01. 90-100"; perc_filled >=80 | foo := "02. 80-89"; perc_filled >= 70 | foo := "03. 70-79" ; perc_filled >= 60 | foo := "04. 60-69"; perc_filled >=50 | foo := "05. 50-59"; perc_filled >=40 | foo := "06. 40-49"; perc_filled >= 30 | foo := "07. 30-39"; perc_filled >=20 | foo := "08. 20-29" ; perc_filled > 10 | foo := "09. 10-19"; perc_filled >=0 | foo := "10. 0-9" ; * | foo := "fixme"}
| groupBy([foo], function=count(clan))
| sort(foo, order=asc)
Both of these panels are using the same query. I'm doing something here I like doing but haven't figured out a good way to explain. In effect find a value and then explore that value from a bucketized frequency perspective. Help? No? How about this
I first define how many wins fill a chest then figure out, from the 2 day PvP wins, how filled individual clan's chest would be as a percentage (perc_filled), Now I want to bucketize across the dataset the clan perc_filled (foo) to then aggregate on that field. In other words, how many clans fully filled their chest, how many filled it less than 10%, etc. I want to bucketize this as visually parsing 10 data points is often better than all data points. In my case that is only 236 but what if it were a million? I've arbitrability picked 10, it could have been 5 or 20 or whatever.
A sharp eye will note nuances of case statements: the use of curly brace, pipes, and semicolons. For those that don't otherwise know, when you use case statements an event is evaluated left to right. As soon as an argument evaluates as true it breaks out of the evaluation. This is why I can walk down numerically with my greater than symbols.
The reason the 'foo' buckets look goofy is I wanted to display the buckets I had created in a particular order. Many engines have sorting issues when you combine character, integer, and special characters. I prepended my order to help the sorting portion of the code.
I was somewhat surprised the pie chart would only display in descending % but that is what it is I guess. I also wanted to highlight here how you can open up the inner radius of the pie which is kinda neat if you are into that look.
9. Member Participation
| wins > 0
| groupBy([clan], function=[sum("wins", as=clan_wins), count(clan_member, distinct=true, as=contributing_members), avg(wins, as=avgMemberWins)])
| round("avgMemberWins")
| match(file="clan_leaderboard.csv", field="clan", strict=false)
| table([clan, finish_position, clan_wins, contributing_members, avgMemberWins], sortby=clan_wins, order=desc)
| default(value="null", field=[finish_position])
| case{ finish_position="null" | finish_position := 60; * | finish_position := finish_position }
In my match statement you will see strict=false. This is somewhat similar to a left join in that I want to keep my original dataset even if there isn't a match from the secondary csv I'm pulling in. On that note I did upload the actual csv into LogScale in the Files portion of my Repo's nav bar. The use of match can allow users to enrich data with a static dataset. You just can't pull up that dataset within a search on its own.
Because of how I collected the data there is a hole in the finish position for Valhalla. The last two lines of the query adjust the data for that. I haven't found a clean way to identify and work with nulls in LogScale yet other than this two stepper. In this case 'default' operates like Splunk's fillnull.
10 - Top 10 Hitters and Clan Representation
| sort(wins, order=asc, limit=20000)
| table([clan, clan_member, wins], sortby=wins, order=desc, limit=10) | join(query={$dfh_clan_efforts() | table([clan, clan_member, wins], sortby=wins, order=desc, limit=10)
| groupBy([clan], function=count(as="clan representation"))}, field=[clan], include="clan representation")
| table([clan, clan_member, "clan representation", wins])
The first part is straight forward enough - show me the top 10 individual finishers/contributors. The default for LogScale's sort function is only 200. An order of magnitude here is a bit high but I wanted to figure out what the max is was/currently is.
Now I want to see how many in the top 10 were in the same clan AND I wanted to display the data like I am. I tried multiple times with nested groupBy statements but nothing worked for me. That's fine, I'll use join. You'll see the joined query looks pretty similar to the first search - get the top 10 finishers, then do a count by clan. With joins you need to include an 'include' argument to tell LogScale what you are looking to bring over. Then its just a matter of using table to display the data in the filed order I want.
That's it!
Hopefully people find this valuable. If you have questions, comments, or alternate queries I'm all ears!
I suppose I haven't really talked about the dashboard layout itself. As a personal taste thing I like to start with high level aggregations at the top with both widget size and granularity increasing as you work down. For me that helps set the stage and as I start wondering about some of the granularities they start to become apparent. Separately I like telling folks that especially for singular dashboard panels try to think about what the audience's first question will be and work to answer that. My goto is something like failed auth. It's great that you can generate a list of people with the highest numbers of failed authentications. A security analyst will now want to know things like was that on one system or many, did it happen all at once or over time, is it still going on? Try to develop your singular panels or if you are using a full dashboard to answer those questions vs making the analyst go somewhere else to find them out. Saves time and hassle.
Raw data - copy/paste these into your LogScale instance, then save them as searches.
Individual efforts
No comments:
Post a Comment