Representing data as cumulative bar chart

Question

Representing data as cumulative bar chart

Gueorguiev, Orlin /Extern 21

My data looks as following:
{
"data" : [{
"salesforceEntity": "Entity 1",
"state": "RUNNING",
"sumOfAllEntities": 299
},
{
"salesforceEntity": "Entity 2",
"state": "RUNNING",
"sumOfAllEntities": 103
},
{
"salesforceEntity": "Entity 2",
"state": "OPEN",
"sumOfAllEntities": 22
}]
}

What I would like to achieve is to represent data in a timeseries cumulative bar chart by state, as follows:
at <TimeGenerated>
salesforceEntity = "Entity 1"
state="OPEN"
sumOfAllEntities=22
state="RUNNING",
sumOfAllEntities=299

salesforceEntity="Entity 2"
state="RUNNING",
sumOfAllEntities=103

How can I do that?

Answer accepted by question author

1 additional answer

Your answer

Answer 1

This is a example (so you will need to tweak it ). You need from the mv-expand line onwards, pasted into your query.

let ParsedMessageLog= dynamic(  
 {  
 "data" : [{  
 "salesforceEntity": "Entity 1",  
 "state": "RUNNING",  
 "sumOfAllEntities": 299  
 },  
 {  
 "salesforceEntity": "Entity 2",  
 "state": "RUNNING",  
 "sumOfAllEntities": 103  
 },  
 {  
 "salesforceEntity": "Entity 2",  
 "state": "OPEN",  
 "sumOfAllEntities": 22  
 }]  
 }  
);  
print a=ParsedMessageLog.data  
| mv-expand a  
| extend salesforceEntity_ = tostring(a.salesforceEntity)  
| extend state_ = tostring(a.state)  
| extend sumOfAllEntities_ = toint(a.sumOfAllEntities)  
| project-away a  
| summarize sum(sumOfAllEntities_) by salesforceEntity_

https://portal.azure.com#@d8ee6e2f-3e07-4558-b301-610427e94492/blade/Microsoft_Azure_Monitoring_Logs/DemoLogsBlade/aiDemo/true/resourceId/%2FDemo/source/LogsBlade.AnalyticsShareLinkToQuery/q/H4sIAAAAAAAAA52QTUvDQBCG7%252Fsrhj0l0BYbT63k4KGIoGkRPInINJmGlXyxO62NH%252F%252FdneZStlioh4X5eOadd6cihhVaR8UjOYclPbRlCkXfYG3ySMGXAl0go4Y5vEjisCK3aW1Oi4YN93oOeohgqkcCMDJJ9ek5y%252B6zu6G4rZeb26o6kIac7yezmYKf0bDijGpyker06vo%252FqsvVIvvTaOIVX%252F1T8Y3qrGkYMA1vNpEjqW%252Bod2Pad9gUIBntmXwY%252BniDFLh17LXKCCdhOz6aFIMhLrVjJrA84N6msEFPxjrbvlPOY%252FzA%252FuDSQzVa80kSRSdyMaz70y%252F8AvYeZ7Y5AgAA/timespan/P1D

Answer 2

Clive Watson 7,946 MVP Volunteer Moderator

Do you mean this?

datatable (TimeGenerated:datetime, salesforceEntity:string, state:string,sumOfAllEntities:int )   
[   
 "2022-12-11","Entity 1","RUNNING",299,  
 "2022-12-12","Entity 2", "RUNNING",103,  
 "2022-12-12","Entity 2", "RUNNING",2  
]  
| summarize sum(sumOfAllEntities) by salesforceEntity, TimeGenerated  
| render columnchart

Please "accept" if this helps

Gueorguiev, Orlin /Extern 21

Hi @Clive Watson ,

thanks for the response. I am not sure as to how to add this to my query. I simply added it to the bottom:

ContainerLog   
| where LogEntry has "org.mule.extension.jsonlogger.JsonLogger"  
| parse LogEntry with LogLevel " " DateTime " [[MuleRuntime].uber." uberId ": [" ApplicationId "]." errorHandler "] [processor: " processorId " event: " eventId "] org.mule.extension.jsonlogger.JsonLogger: " LogMessageJson  
| extend ParsedLogMessage = parse_json(LogMessageJson)  
| project TimeGenerated, ParsedLogMessage.content.data  
| summarize sum(sumOfAllEntities) by salesforceEntity, TimeGenerated  
| render columnchart

however I get 'summarize' operator: Failed to resolve scalar expression named 'salesforceEntity'...

I do believe that I am doing something very wrong. Like how should I exactly tell to summarize to use the data from a specific object (in this case ParsedLogMessage.content.data)?

Clive Watson 7,946 Reputation points MVP Volunteer Moderator

2022-12-13T09:15:07.037+00:00

You did the right thing, the datatable I supplied tried to emulate the data you mentioned, that you said had names like "salesforceEntity".

I assume salesforceEntity or a entry you will name as that is within the JSON you return with ParsedLogMessage? Without seeing the data its hard to work out what you are missing. Even one line of ParsedLogMessage would help (changing any confidentail names). Does the line 5 work ok?

If you added a limit 1 to get one random row that would help us see the data structure

| project TimeGenerated, ParsedLogMessage.content.data
| limit 1

Gueorguiev, Orlin /Extern 21

Hi,
the content of ParsedLogMessage.content is the example which I supplied in the original question:

{  
"data" : [{  
"salesforceEntity": "Entity 1",  
"state": "RUNNING",  
"sumOfAllEntities": 299  
},  
{  
"salesforceEntity": "Entity 2",  
"state": "RUNNING",  
"sumOfAllEntities": 103  
},  
{  
"salesforceEntity": "Entity 2",  
"state": "OPEN",  
"sumOfAllEntities": 22  
}]  
}

Share via

Representing data as cumulative bar chart

1 additional answer

Your answer