Solved: Re: Showing Specific Values in a dashboard

msarkaus

Hello,
I have this Splunk log that contains tons of quotes, commas, and other special characters. I’m trying to only pull the Latitude":77.0999, Longitude":-99.999 and from time to time there will be WarningMessages: This mail requires a number or Apartment number that I would like to capture in a dashboard.

StandardizedAddres SUCCEEDED - FROM: {"Address1":"123 NAANNA SAND RD","Address2":"","City":”GREEN","County":null,"State":"WY","ZipCode":"44444-9360","Latitude":null,"Longitude":null,"IsStandardized":true,"AddressStatus":1,"AddressStandardizationType":0} RESULT: 1 | {"AddressDetails":[{"AssociatedName":"","HouseNumber":"123","Predirection":"","StreetName":" NAANNA SAND RD ","Suffix":"RD","Postdirection":"","SuiteName":"","SuiteRange":"","City":" GREEN","CityAbbreviation":"GREEN","State":"WY","ZipCode":"44444","Zip4":"9360","County":"Warren","CountyFips":"27","CoastalCounty":0,"Latitude":77.0999,"Longitude":-99.999,"Fulladdress1":"123 NAANNA SAND RD ","Fulladdress2":"","HighRiseDefault":false}]," WarningMessages":["This mail requires a number or Apartment number."]:[],"ErrorMessages":[],"GeoErrorMessages":[],"Succeeded":true,"ErrorMessage":null}

I currently use the query below, but I’m not having any luck. This is past my skill set, please help….

index="cf" Environment="NA" msgTxt="API=/api-123BusOwnCommon/notis*"
| eval msgTxt=" API=/api-123BusOwnCommon/notis /WGR97304666665/05-08-2024 CalStatus=Success Controller=InsideApi_ notis Action= notis Duration=3 data*"
| rex "Duration=(?<Duration>\w+)"
| timechart span=1h avg(Duration) AS avg_response by msgTxt

I'd like to show the data like this in Splunk:

Latitude Longitude WarningMessages

2.351 42.23 Error in blah

4.10 88.235 Hello world

454.2 50.02 Blah blah blah blah...............

Thank you

bowesmana

It seems like things are moving under your feet - the syntax of your log message has changed from your original example, which had the text StandardizedAddressService, now it's StandardizedAddress.

Note that if you create a regex to extract the fields, and the message changes, it will break the extraction.

It would be useful, when you say you have errors - to show what you tried and what the result was, otherwise it's almost impossible to come up with some solution.

So, on these assumptions.

a) you have a JSON object after FROM: {}
b) another JSON object after RESULT: 1 | {} - is "1" a fixed value or variable?

Note that your example does NOT show valid JSON for the result. It is missing a comma after the Longitude value before the F - not sure if that is a typo or in your data.

97.999,"Longitude":-97.999"F

Assuming it is a typo then your search should be this

Your base data search goes here...

``` This line extracts the from and result JSON objects from your msgTxt field ```
| rex field=msgTxt "FROM:\s*(?<from>.*) RESULT:[^{]*(?<result>.*)"

``` This extracts the JSON from each of those objects ```
| spath input=from
| spath input=result
``` and this makes the field names a bit more sensible ```
| rename AddressDetails{}.* as Result.*, WarningMessages{} as Result.WarningMessages
| table Latitude Longitude *.Latitude *.Longitude Result.WarningMessages

If you reply to these, please post your code in code blocks, so that it's easy to read

View solution in original post

bowesmana

Your event is a heading, followed by a JSON object, so one approach is to simply create a field extraction to extract the JSON object and then you have access to all the fields directly.

This example shows what that would look like - the rex statement extracts the JSON inline, but you could do that as a calculated field. The spath parses the JSON

| makeresults
| eval _raw="StandardizedAddres SUCCEEDED - FROM: {\"StandardizedAddres\":\"SUCCEEDED\",\"FROM\":{\"Address1\":\"123 NAANNA SAND RD\",\"Address2\":\"\",\"City\":\"GREEN\",\"County\":null,\"State\":\"WY\",\"ZipCode\":\"44444-9360\",\"Latitude\":null,\"Longitude\":null,\"IsStandardized\":true,\"AddressStatus\":1,\"AddressStandardizationType\":0},\"RESULT\":1,\"AddressDetails\":[{\"AssociatedName\":\"\",\"HouseNumber\":\"123\",\"Predirection\":\"\",\"StreetName\":\"NAANNA SAND RD\",\"Suffix\":\"RD\",\"Postdirection\":\"\",\"SuiteName\":\"\",\"SuiteRange\":\"\",\"City\":\"GREEN\",\"CityAbbreviation\":\"GREEN\",\"State\":\"WY\",\"ZipCode\":\"44444\",\"Zip4\":\"9360\",\"County\":\"Warren\",\"CountyFips\":\"27\",\"CoastalCounty\":0,\"Latitude\":77.0999,\"Longitude\":-99.999,\"Fulladdress1\":\"123 NAANNA SAND RD\",\"Fulladdress2\":\"\",\"HighRiseDefault\":false}],\"WarningMessages\":[\"This mail requires a number or Apartment number.\"],\"ErrorMessages\":[],\"GeoErrorMessages\":[],\"Succeeded\":true,\"ErrorMessage\":null}"
| rex "StandardizedAddres SUCCEEDED - FROM: (?<event>.*)"
| spath input=event
| rename AddressDetails{}.* as *, WarningMessages{} as WarningMessages
| table Latitude Longitude WarningMessages

Note that your AddressDetails is actually a JSON array, so in theory it could contain multiple results, so doing this with the JSON extraction will handle any possible case where you get more than one result in the address array.

msarkaus

I pleased to see your query is working; however, it's repeating the same values.

Sorry, I did not explain that there will be 1000's of logs, each with a different value.

bowesmana

Can you show your search, it seems that those numbers and warnings are the same as the example you gave - if that is what it is showing, then that is likely what the data contains. Can you show an example of a couple of messages and your search because the search will work - note that you should not include the eval _raw part, as that is just setting up example test data to show you how the rest of the search can work

msarkaus

index="ifi" appEnvrnNam="ANY" msgTxt="StandardizedAddress SUCCEEDED*"
| eval _raw="Standardizedss SUCCEEDED - FROM: {\"Standardizedss \":\"SUCCEEDED\",\"FROM\":{\"Address1\":\"123 NAANNA SAND RD\",\"Address2\":\"\",\"City\":\"GREEN\",\"County\":null,\"State\":\"WY\",\"ZipCode\":\"44444-9360\",\"Latitude\":null,\"Longitude\":null,\"IsStandardized\":true,\"AddressStandardization\":1,\"AddressStandardizationType\":0},\"RESULT\":1,\"AddressDetails\":[{\"AssociatedName\":\"\",\"HouseNumber\":\"123\",\"Predirection\":\"\",\"StreetName\":\"NAANNA SAND RD\",\"Suffix\":\"RD\",\"Postdirection\":\"\",\"SuiteName\":\"\",\"SuiteRange\":\"\",\"City\":\"GREEN\",\"CityAbbreviation\":\"GREEN\",\"State\":\"WY\",\"ZipCode\":\"44444\",\"Zip4\":\"9360\",\"County\":\"Warren\",\"CountyFips\":\"27\",\"CoastalCounty\":0,\"Latitude\":77.0999,\"Longitude\":-99.999,\"Fulladdress1\":\"123 NAANNA SAND RD\",\"Fulladdress2\":\"\",\"HighRiseDefault\":false}],\"WarningMessages\":[\"This mail requires a number or Apartment number.\"],\"ErrorMessages\":[],\"GeoErrorMessages\":[],\"Succeeded\":true,\"ErrorMessage\":null}"
| rex "StandardizedAddres SUCCEEDED - FROM: (?<event>.*)"
| spath input=event
| rename AddressDetails{}.* as *, WarningMessages{} as WarningMessages
| table Latitude Longitude WarningMessages

msarkaus

Scratch that first line..

Use this

index="ifi" appEnvrnNam="ANY" msgTxt="Standardizess SUCCEEDED - FROM:*"

bowesmana

The _raw=... line is OUR example based on your data to demonstrate the solution. You MUST not include that in YOUR search, as it makes every event equal to that _raw value.

msarkaus

So if I'm not to use

| eval _raw="StandardizedAddres SUCCEEDED - FROM: {\"StandardizedAddres.................."

Should I use

| eval msgTxt="StandardizedAddres SUCCEEDED - FROM: {\"StandardizedAddres\":\"SUCCEEDED\",\"FROM\":{\"Address1\":\"123 NAANNA SAND RD\",\"Address2\":\"\",\"City

And do I not include the /?

livehybrid

Hi @msarkaus

The following should hopefully work for you:

| rex "\"Latitude\"\s*:\s*(?<Latitude>-?\d+\.\d+)"
| rex "\"Longitude\"\s*:\s*(?<Longitude>-?\d+\.\d+)"
| rex "\"WarningMessages\"\s*:\s*\[\s*\"(?<WarningMessages>[^\"]*)"
| table _time Latitude Longitude WarningMessages

Here is a full working example for you to try with:

| windbag | head 1 
| eval _raw="StandardizedAddres SUCCEEDED - FROM: {\"StandardizedAddres\":\"SUCCEEDED\",\"FROM\":{\"Address1\":\"123 NAANNA SAND RD\",\"Address2\":\"\",\"City\":\"GREEN\",\"County\":null,\"State\":\"WY\",\"ZipCode\":\"44444-9360\",\"Latitude\":null,\"Longitude\":null,\"IsStandardized\":true,\"AddressStatus\":1,\"AddressStandardizationType\":0},\"RESULT\":1,\"AddressDetails\":[{\"AssociatedName\":\"\",\"HouseNumber\":\"123\",\"Predirection\":\"\",\"StreetName\":\"NAANNA SAND RD\",\"Suffix\":\"RD\",\"Postdirection\":\"\",\"SuiteName\":\"\",\"SuiteRange\":\"\",\"City\":\"GREEN\",\"CityAbbreviation\":\"GREEN\",\"State\":\"WY\",\"ZipCode\":\"44444\",\"Zip4\":\"9360\",\"County\":\"Warren\",\"CountyFips\":\"27\",\"CoastalCounty\":0,\"Latitude\":77.0999,\"Longitude\":-99.999,\"Fulladdress1\":\"123 NAANNA SAND RD\",\"Fulladdress2\":\"\",\"HighRiseDefault\":false}],\"WarningMessages\":[\"This mail requires a number or Apartment number.\"],\"ErrorMessages\":[],\"GeoErrorMessages\":[],\"Succeeded\":true,\"ErrorMessage\":null}"
| rex "\"Latitude\"\s*:\s*(?<Latitude>-?\d+\.\d+)"
| rex "\"Longitude\"\s*:\s*(?<Longitude>-?\d+\.\d+)"
| rex "\"WarningMessages\"\s*:\s*\[\s*\"(?<WarningMessages>[^\"]*)"
| table _time Latitude Longitude WarningMessages

🌟 Did this answer help you? If so, please consider:

Adding karma to show it was useful
Marking it as the solution if it resolved your issue
Commenting if you need any clarification

Your feedback encourages the volunteers in this community to continue contributing

msarkaus

Thank you it is working; however, it's repeating the same value. The search will be returning 1000's of logs each with a different value and some will not contain a warning message.

livehybrid

Hi @msarkaus

It looks like you have multiple events with the same content in then? If you have 1000s of events you should probably use something like stats to group them up:

| stats count by Latitude Longitude WarningMessages

🌟 Did this answer help you? If so, please consider:

Adding karma to show it was useful
Marking it as the solution if it resolved your issue
Commenting if you need any clarification

Your feedback encourages the volunteers in this community to continue contributing

msarkaus

I'm having issues getting this to work. I posted my search in a earlier post.

I was told not to use the eval _raw line, I've tried removing it and I have used | eval msgTxt="

and it is still not working. What am I doing wrong? Please help. thanks

bowesmana

Can you post your exact search

msarkaus

When I use | eval msgTxt= I get no results, but if I use | eval _raw it returns the requested data, but it repeats the same values.

index="cif" appEnvrnNam="Production" msgTxt="StandardizedAddressService SUCCEEDED*"

| eval msgTxt="StandardizedAddressService SUCCEEDED - FROM: {\"StandardizedAddressService\":\"SUCCEEDED\",\"FROM\":{\"Address1\":\"123 NAANNA SAND RD\",\"Address2\":\"\",\"City\":\"GREEN\",\"County\":null,\"State\":\"WY\",\"ZipCode\":\"44444-9360\",\"Latitude\":null,\"Longitude\":null,\"IsStandardized\":false,\"AddressStandardizationStatus\":1,\"AddressStandardizationType\":1},\"RESULT\":1,\"AddressDetails\":[{\"AssociatedName\":\"\",\"HouseNumber\":\"123\",\"Predirection\":\"\",\"StreetName\":\"NAANNA SAND RD\",\"Suffix\":\"RD\",\"Postdirection\":\"\",\"SuiteName\":\"\",\"SuiteRange\":\"\",\"City\":\"GREEN\",\"CityAbbreviation\":\"GREEN\",\"State\":\"WY\",\"ZipCode\":\"44444\",\"Zip4\":\"9360\",\"County\":\"Warren\",\"CountyFips\":\"27\",\"CoastalCounty\":0,\"Latitude\":77.0999,\"Longitude\":-99.999,\"Fulladdress1\":\"123 NAANNA SAND RD\",\"Fulladdress2\":\"\",\"HighRiseDefault\":false}],\"WarningMessages\":[],\"ErrorMessages\":[],\"GeoErrorMessages\":[],\"Succeeded\":true,\"ErrorMessage\":null}"

| rex "\"Latitude\"\s*:\s*(?<Latitude>-?\d+\.\d+)"

| rex "\"Longitude\"\s*:\s*(?<Longitude>-?\d+\.\d+)"

| rex "\"WarningMessages\"\s*:\s*\[\s*\"(?<WarningMessages>[^\"]*)"

| table _time Latitude Longitude WarningMessages

bowesmana

Do NOT use eval for anything to set any value to "StandardizedAddressService bla bla bla"

That is data that I understand is coming from your event - when you make that eval statement you are setting a field called msgTxt (in your example) to the value you give it.

What is the name of your field that contains that phrase in your index=... search

You should extract the "event" field using the rex statement specifying the field you want to extract from, e.g

| rex field=msgTxt blablabla

and then just use the original SPL I posted at the beginning

| spath input=event
| rename AddressDetails{}.* as *, WarningMessages{} as WarningMessages
| table Latitude Longitude WarningMessages

after that.

If you get something unexpected, add some more fields to the table statement at the end to show what those fields are

msarkaus

I have used the rex field=msgTxt but I keep getting errors. I'm sorry but I've worked on this for hours, and nothing seems to work. I'm still pretty new to Splunk and this is not in my skill-set.

Maybe I should start over..

However, the results I'm looking for have slightly changed. The field or log that contains my results are located in msgTxt

and I would like to pull both Latitude/Longitude values and the WarningMessages. The field has Latitude and Longitude listed twice. Most of the time the first set will return 0's and the log will always be in this format.

The log looks like this:

StandardizedAddressService SUCCEEDED - FROM: {"Address1":"63 Somewhere NW ST","Address2":null,"City":"OKLAND CITY","County":null,"State":"OK","ZipCode":"99999-1111","Latitude":97.999,"Longitude":-97.999,"IsStandardized":false,"AddressStandardizationStatus":0,"AddressStandardizationType":0} RESULT: 1 | {"AddressDetails":[{"AssociatedName":"","HouseNumber":"63","Predirection":"NW","StreetName":"Somewhere","Suffix":"ST","Postdirection":"","SuiteName":"","SuiteRange":"","City":"OKLAND CITY","CityAbbreviation":"OKLAND CITY","State":"OK","ZipCode":"99999","Zip4":"1111","County":"Oklahoma","CountyFips":"40109","CoastalCounty":0,"Latitude":97.999,"Longitude":-97.999"Fulladdress1":"63 Somewhere NW ST","Fulladdress2":"","HighRiseDefault":false}],"WarningMessages":[],"ErrorMessages":[],"GeoErrorMessages":[],"Succeeded":true,"ErrorMessage":null}

I'm hoping to see the following results:

Latitude Longitude Latitude Longitude WarningMessages

99.2541 -25.214 99.254 -25.214 NULL

00.0000 -00.000 99.254 -21.218 NULL

00.0000 -00.000 00.000 -00.000 Error message with something

The results for all of the phrases will be different and I will be searching through1000's of logs.

If it's too much work to show both set of the Latitude/Longitude values, then the second set would work.

Your help is greatly appreciated..

Thanks

bowesmana

It seems like things are moving under your feet - the syntax of your log message has changed from your original example, which had the text StandardizedAddressService, now it's StandardizedAddress.

Note that if you create a regex to extract the fields, and the message changes, it will break the extraction.

It would be useful, when you say you have errors - to show what you tried and what the result was, otherwise it's almost impossible to come up with some solution.

So, on these assumptions.

a) you have a JSON object after FROM: {}
b) another JSON object after RESULT: 1 | {} - is "1" a fixed value or variable?

Note that your example does NOT show valid JSON for the result. It is missing a comma after the Longitude value before the F - not sure if that is a typo or in your data.

97.999,"Longitude":-97.999"F

Assuming it is a typo then your search should be this

Your base data search goes here...

``` This line extracts the from and result JSON objects from your msgTxt field ```
| rex field=msgTxt "FROM:\s*(?<from>.*) RESULT:[^{]*(?<result>.*)"

``` This extracts the JSON from each of those objects ```
| spath input=from
| spath input=result
``` and this makes the field names a bit more sensible ```
| rename AddressDetails{}.* as Result.*, WarningMessages{} as Result.WarningMessages
| table Latitude Longitude *.Latitude *.Longitude Result.WarningMessages

If you reply to these, please post your code in code blocks, so that it's easy to read

msarkaus

Thank you the solution you provided worked.. I appreciate your patience and willingness to help me.

Showing Specific Values in a dashboard

eval

rex

See your relevant APM services, dashboards, and alerts in one place with the updated ...

Cultivate Your Career Growth with Fresh Splunk Training

Introducing a Smarter Way to Discover Apps on Splunkbase

Are you a member of the Splunk Community?

Showing Specific Values in a dashboard

eval

rex

See your relevant APM services, dashboards, and alerts in one place with the updated ...

Cultivate Your Career Growth with Fresh Splunk Training

Introducing a Smarter Way to Discover Apps on Splunkbase