3

Using the StormEvents table on the Samples database on the help cluster:

StormEvents
| where State startswith "AL"
| where EventType has "Wind"
| where StartTime == "2007-01-02T02:16:00Z"
| project StartTime, State, EventType, InjuriesDirect, InjuriesIndirect, DeathsDirect, DeathsIndirect

I would like row-based output of the form:

desired un-pivoted output

I see the pivot() function, but it appears to only go the other direction, from rows to columns.

I've been trying various pack() ideas, but can't seem to get the required output.

Example:

StormEvents
| where State startswith "AL"
| where EventType has "Wind"
| where StartTime == "2007-01-02T02:16:00Z"
| project StartTime, State, EventType, InjuriesDirect, InjuriesIndirect, DeathsDirect, DeathsIndirect
| extend Packed =   pack(
                    "CasualtyType", "InjuriesDirect", "CasualtyCount", InjuriesDirect,
                    "CasualtyType", "InjuriesIndirect", "CasualtyCount", InjuriesIndirect,
                    "CasualtyType", "DeathsDirect", "CasualtyCount", DeathsDirect,
                    "CasualtyType", "DeathsIndirect", "CasualtyCount", DeathsIndirect
                )
| project-away InjuriesDirect, InjuriesIndirect, DeathsDirect, DeathsIndirect
| mv-expand Packed

This gives me too many rows, and it's not clear to me how to convert them to columns anyway.

What's a correct pattern to use for the required output?

sherifffruitfly
  • 415
  • 5
  • 14

1 Answers1

9

you could try something along the following lines:

let casualty_types = dynamic(["InjuriesDirect", "DeathsDirect", "InjuriesIndirect", "DeathsIndirect"]);
StormEvents
| where State startswith "AL"
| where EventType has "Wind"
| where StartTime == "2007-01-02T02:16:00Z"
| project StartTime, State, EventType, properties = pack_all()
| mv-apply casualty_type = casualty_types to typeof(string) on (
    project casualty_type, casualty_count = tolong(properties[casualty_type])
)
| project-away properties
Yoni L.
  • 22,627
  • 2
  • 29
  • 48
  • Thank you! I had just come up with essentially the same idea, but using a datatable + join. Yours looks less hokey than mine though. – sherifffruitfly Nov 12 '19 at 16:20
  • Question: in your ending, "properties[casualty_type]", how does kusto know what casualty_type is right there? To my eye that looks like it should be meaningless - there's no column in the properties json named that? – sherifffruitfly Nov 12 '19 at 17:40
  • this isn't the name of a property in the JSON payload, rather it's the name of an extended column (`casualty_types`) whose value is the name of the property in the JSON payload – Yoni L. Nov 12 '19 at 19:46
  • Thanks - I'll chew on that & mv-apply generally! – sherifffruitfly Nov 12 '19 at 21:14