Advanced queries: Group by with conditions. Summarizing values.

[splunkent2]

Hi,

I'm a novice to more advanced Splunk usage, but I understand that a lot is possible.

Here is an example of a log entry in our JSON based logs:

{
  "date": "2016-10-04T08:39:55.274Z",
  "gameId": "coolgame",
  "logLevel": "STAT",
  "levelId": "6001",
  "sessionId": "1456878-X1GTRER",
  "category": "stat",
  "type": "orientationChange",
  "message": [
    {
      "phase": "respawn",
      "orientation": "landscape",
      "timeSpentMs": 2000,
      "gameRound": 1
    }
  ],
  "serverData": {
    "clientIp": "10.72.14.250"
  },
  "timestamp": 1475570395274
}

Here is a description of the interesting parts:

• type is the type of log message, here only "orientationChange"
• message.phase could be respawn|ingame|paused
• message.orientation could be landscape|portrait
• message.gameRound is a positive integer
• message.timeSpentMs is a positive integer indicating nr of ms spent in landscape|portrait mode
• sessionId a string unique for that game session, it spans multiple gameRounds
• timestamp is an integer indicating the time when the log entry was created

A log entry like the one above will be created when any of these changes:

• message.phase
• message.orientation
• message.gameRound is a positive integer

An example

{ ... "sessionId": "1456878-X1GTRER", "message": [{"phase": "respawn","orientation": "landscape","timeSpentMs": 4000,"gameRound":  1}],  ... "timestamp": 1000000000000 }
{ ... "sessionId": "1456878-X1GTRER", "message": [{"phase": "ingame" ,"orientation": "landscape","timeSpentMs": 1000,"gameRound":  2}],  ... "timestamp": 1000000001000 }
{ ... "sessionId": "1456878-X1GTRER", "message": [{"phase": "ingame" ,"orientation": "portrait", "timeSpentMs": 20000,"gameRound": 2}],  ... "timestamp": 1000000021000 }
{ ... "sessionId": "1456878-X1GTRER", "message": [{"phase": "paused" ,"orientation": "portrait", "timeSpentMs": 60000,"gameRound": 2}],  ... "timestamp": 1000000081000 }

Here the user entered the game (respawn) in landscape mode. After four seconds the next game round was started (ingame). After one more second the user realized that he wanted to play the game in portrait mode and changed to that orientation. He then played the game for 20 seconds and then paused it for 1 minute.

Following are the two types of information I need and my rough ideas on how to get them:

1. See the total time spent in landscape/portrait mode for the three phases?
   • Only for the type "orientationChange"
   • Group by message.phase and summarize message.timeSpentMs
2. For landscape/portrait, see the number of gameRounds played completely in one orientation without any orientation change?
   • Only for the type "orientationChange"
   • Group by message.gameRound where all entries in the gameRound has stayed in one orientation
   • Count gameRounds in orientation portrait/landscape

Questions:

• Can 1 and 2 above be done in some easy way with Splunk? Any concrete suggestions on how would be greatly appreciated!
• Would it be recommended to change the log format somehow to make this easier? I can mostly change stuff in the message part.

[somesoni2 · Accepted solution]

For requirement 1 try like this

index=foo sourcetype=bar type="orientationChange"
| chart sum(message.timeSpentMs) over message.phase by message.orientation

For requirement 2, try this

index=foo sourcetype=bar type="orientationChange"
| chart count over message.gameRound over message.orientation

Json is Splunk recommended data format and since it's linear (not array), IMO it's efficient.

The only better way could be to convert JSON to linear keyvalue pair.