About adikrhd

adikrhd · ‎03-26-2024

did not work, | makeresults | eval _raw = "{\"orderNum\":\"1234\",\"orderLocation\":\"demoLoc\",\"details\":{\"key1\":\"value1\",\"key2\":\"value2\"}}" | spath | spath input=_raw path=details output=hold | rex field=hold "\"(?<kvs>[^\"]*\"*[^\"]*\"*[^\"]*\"*)\"" max_match=0 | stats values(*) as * by kvs | rex field=kvs "(?<key>[^\"]*)\" : \"(?<value>[^\"]*)" max_match=0 | table orderNum key value orderLocation the value from the key-value pair can be an escaped JSON string. we also need to consider this while writing regex.

adikrhd · ‎03-19-2024

Hello @marnall, I appreciate your response, it did work for me after tuning it a bit | spath input=_raw path=details output=hold | rex field=hold mode=sed "s/({\s*|\s*}|\s*)//g" | makemv hold delim="," | mvexpand hold | rex field=hold "\"(?<key>[^\"]+)\":\"(?<value>[^\"]+)\"" | table orderNum key value orderLocation However, I have a follow-up on this. our admin has set some limitations for mvexpand command when I try to increase the search period for the last 3 days, I get this warning: output will be truncated at 6300 results due to excessive memory usage. Memory threshold of 500MB as configured in limits.conf / [mvexpand] / max_mem_usage_mb has been reached. Is there any alternate way to achieve the same results without using mvexpand, considering that on average there can be more than 50-60 key-value present under the "details" of a single event, and there can be 40K events per 30 days (assuming the retention period of the events is 30 days)?

adikrhd · ‎03-18-2024

Consider I have multiple such JSON events pushed to splunk. { "orderNum" : "1234", "orderLocation" : "demoLoc", "details":{ "key1" : "value1", "key2" : "value2" } } I am trying to figure out a spunk query that would give me the following output in a table orderNum key value orderLocation 1234 key1 value1 demoLoc 1234 key2 value2 demoLoc the value from the key-value pair can be an escaped JSON string. we also need to consider this while writing regex.

adikrhd · ‎07-18-2023

@yuanliu , I was able to achieve it through this; I missed the part where the value is a multivalued field. After using mvmap, it worked fine. | rex field=value mode=sed "s/.+=> *//" | eval value = mvmap(value, if(match(value, "#DEL|null"), value, "others")) | stats values(Unique_key) as Unique_key count by value | table Unique_key value count | rename value as "#DEL or null or others"

adikrhd · ‎07-18-2023

@yuanliu , I want something like this Unique_key #DEL or null or others count discrepancyDetails.record/ discrepancyDetails.recordD #DEL 2 discrepancyDetails.recordX . . others 1 discrepancyDetails.recordN discrepancyDetails.recordY null 2 | rex field=value mode=sed "s/.+=> *//" | eval value = if(match(value, "#DEL|null"), value, "others") | stats values(Unique_key) as Unique_key count by value | table Unique_key value count | rename value as "#DEL or null or others" I tried something like this but it doesn't seem to be working not sure why.

adikrhd · ‎07-17-2023

Thanks @yuanliu , In requirement 3 what if I want to group remaining keys under different group, so my result column would have header '#DEL or null or others' and all the other keys that do not fall under '#DEL' and 'null' category should be grouped under 'others' column. could you help me with this query is well?

adikrhd · ‎07-17-2023

@yuanliu this worked fine for requirement 3, just a follow up ask for requirement 3, I want to filter out all the fields that do not contain certain substring in the keys, I am not really able to figure our how can I filter out this. for instance, let us say I want to remove all the keys from the result that contain "Y", considering sample example "recordY" would be removed from the result.

adikrhd · ‎07-12-2023

@ITWhisperer please let me know if you are still not clear.

adikrhd · ‎07-10-2023

hey @ITWhisperer , The values are of two types: "#DEL": This indicates that the field is deleted. "<expected> => <actual>": This pattern represents the actual and expected values of the field. Therefore, we need to group all the fields that have "#DEL" and "null(<actual>)" values.

adikrhd · ‎07-10-2023

hey @yuanliu , yes you are right about mvexpand and {} use in the SPL, it turns out that the following query also returns the same result, please note that the transpose is important. index="demo1" sourcetype="demo2" | search discrepancyDetails AND Stats | spath "Stats.discrepancy" | search "Stats.discrepancy" > 0 | stats count(discrepancyDetails.*) as discrepancyDetails.* | transpose However, I appreciate your suggestion, but it seems that the provided answer is not yielding the desired results. It appears that the solution is renaming the "discrepancyDetails" fields to "bad," resulting in duplicated "Interested" fields with prefixes "discrepancyDetails." and "bad." in my Splunk fields section. Moreover, the numeric characters in the keys are not being replaced with an empty string. Could you please kindly assist me in finding a more effective solution? Thank you for your understanding. I have also added desired results for point 2 and 3 in the original post, please give it a try.

adikrhd · ‎07-10-2023

hey @ITWhisperer, I have updated the post with desired results, please give it a try now.

adikrhd · ‎07-09-2023

Hello Community, I am fairly new to Splunk, and I am struggling with this. Here is my raw event: these are discrepancy events that show a reported discrepancy in the two JSONs (for the context of this problem, those JSONs are not necessary to be known). Assuming there are n events similar to what we have in the sample JSON. { "severity": "INFO", "time": "2023-07-09 18:53:53.930", "Stats": { "discrepancy" : 10 }, "discrepancyDetails": { "record/0": "#DEL", "record/1": "#DEL", "recordD": "#DEL", "recordX": "expected => actual", "recordY": "someExpectedVal => null", <-- actual value is null in this case "recordN": "someExpectedValN => null" } } Stats.discrepancy provides the total count, while discrepancyDetails provides the actual discrepancy. I want to fetch some statistics from this, which involve the following details: All the unique discrepancyDetails with their respective counts. Before finding the count, I want to remove all numerical characters from the key. For example, in the same JSON, we have two keys in the discrepancyDetails: "record/1" and "record/2". I want to treat these keys as "record/" and replace the numeric strings with an empty value. figure out all the keys with null actual (from sample json "expected => actual") values and "#DEL" (deleted) values I was able to obtain the unique count of all the keys using the following query. index="demo1" sourcetype="demo2" | search discrepancyDetails AND Stats | spath "Stats.discrepancy" | search "Stats.discrepancy" > 0 | stats count(discrepancyDetails.*) as discrepancyDetails.* | transpose I am unable to figure out points 2 and 3 from the above requirements. Desired output for requirement 2 considering above sample json: Unique_key count record/ 2 recordD 1 recordX 1 recordY 1 recordN 1 Desired output for requirement 3 considering above sample json: Unique_key null or #DEL count record/ recordD #DEL 2 recordY recordN null 2

adikrhd · ‎07-09-2023

Hey @GaetanVP I tried this same query using makeresult as suggested by @yuanliu , and it worked perfectly fine, I am not quite sure why it is not working with my production raw data. due to some security reasons I may not be able to provide you raw data screenshot but it looks something like this { "severity": "INFO", "time": "2023-07-09 18:53:53.930", "Stats": { "discrepancy" : 10 }, "discrepancyDetails": { "record1/0": "#DEL", "record2/1": "#DEL", "record3": "expected => actual", } } I want all unique discrepancyDetails and its count, anyway, I was able to get it through this query index="demo1" sourcetype="demo2" | search discrepancyDetails AND Stats | spath "Stats.discrepancy" | search "Stats.discrepancy" > 0 | stats count(discrepancyDetails.*) as discrepancyDetails.* | transpose I have some follow up queries on this. I may create a new thread for the same. Thanks for your prompt response, really appreciated!

adikrhd · ‎07-07-2023

Hello @GaetanVP , Appreciate your efforts here but it doesn't seem to be working, after untable _name value, it is not returning any visible stat results

adikrhd · ‎07-07-2023

Hello @richgalloway , Hope you are doing well. I have already tried using the json_keys() approach, but the problem I encountered is that it returns a list of multivalue events in the result, something like this: keys ----------- ["key1","key2","key3"] . . ["key1n","key2n","key3n"] But the ask here is that we want all unique keys in a single list. I am unable to figure out how to merge these two multivalues into a single unique list.

adikrhd · ‎07-07-2023

Hello Community, I stumbled across a scenario where I have events present in the JSON format as follows Event 1: { "severity": "INFO", "message": "msg", "details": { "key1": "val1", "key2": "val2", "key3": "val3" } } . . . Event n: { "severity": "INFOn", "message": "msgn", "details": { "key1n": "val1", "key2n": "val2", "key3n": "val3" } } I want to list all the unique keys present under the path "details." I tried querying it using mvexpand and json_keys, but nothing seems to be working. I would greatly appreciate some assistance. The expected output should be as follows: uniqueKeys: key1 key2 key3 . . . key1n key2n key3n Basically, I want to list down all the unique keys present under the "details" JSON path across all the events..

Posts	18
Solutions	1
Karma Given	9
Karma Received	1
Member Since	‎07-07-2023

Online Status	Offline
Date Last Visited	‎03-27-2024 01:22 AM

How do I extract key values from a json object?

Fetching statistics: Unique discrepancy details, r...

List all unique keys present under certain json pa...

Re: How do I extract key values from a json object...

Re: How do I extract key values from a json object...

How do I extract key values from a json object?

Re: Fetching statistics: Unique discrepancy detail...

Re: Fetching statistics: Unique discrepancy detail...

Re: Fetching statistics: Unique discrepancy detail...

Re: Fetching statistics: Unique discrepancy detail...

Re: Fetching statistics: Unique discrepancy detail...

Re: Fetching statistics: Unique discrepancy detail...

Re: Fetching statistics: Unique discrepancy detail...

Re: Fetching statistics: Unique discrepancy detail...

Fetching statistics: Unique discrepancy details, r...

Re: List all unique keys present under certain jso...

Re: List all unique keys present under certain jso...

Re: List all unique keys present under certain jso...

List all unique keys present under certain json pa...