topic Re: Replace row value data and add new column with updated value in Splunk Search

Replace row value data and add new column with updated value

ravir_jbp — Tue, 06 Feb 2024 13:05:33 GMT

I am looking for specific query where I can alter the row values after the final output and create new column with new value.

For example,

I have written the below query :

index=csv sourcetype="miscprocess:csv" source="D:\\automation\\miscprocess\\output_acd.csv" | rex field=_raw "\"(?<filename>\d*\.\d*)\"\,\"(?<filesize>\d*\.\d*)\"\,\"(?<filelocation>\S*)\"" | search filename="*" filesize="*" filelocation IN ("*cl3*", "*cl1*") | table filename, filesize, filelocation

Which gives me the following output:

filename filesize filelocation

012624.1230	13253.10546875	E:\totalview\ftp\acd\cl1\backup_modified\012624.1230
012624.1230	2236.3291015625	E:\totalview\ftp\acd\cl3\backup\012624.1230
012624.1200	13338.828125	E:\totalview\ftp\acd\cl1\backup_modified\012624.1200
012624.1200	2172.1640625	E:\totalview\ftp\acd\cl3\backup\012624.1200
012624.1130	13292.32421875	E:\totalview\ftp\acd\cl1\backup_modified\012624.1130
012624.1130	2231.9658203125	E:\totalview\ftp\acd\cl3\backup\012624.1130
012624.1100	13438.65234375	E:\totalview\ftp\acd\cl1\backup_modified\012624.1100

BUT, I like the row values to be replaced by "ACD55" where the file location is cl1 and "ACD85" where the file location is cl3 under filelocation column. So the desire output should be:

filename filesize filelocation

012624.1230	13253.10546875	ACD55
012624.1230	2236.3291015625	ACD85
012624.1200	13338.828125	ACD55
012624.1200	2172.1640625	ACD85
012624.1130	13292.32421875	ACD55
012624.1130	2231.9658203125	ACD85
012624.1100	13438.65234375	ACD55

The raw events are like below:

"020424.0100","1164.953125","E:\totalview\ftp\acd\cl3\backup\020424.0100"

"020624.0130","1754.49609375","E:\totalview\ftp\acd\cl1\backup_modified\020624.0130"

please suggest :

Re: Replace row value data and add new column with updated value

ITWhisperer — Tue, 06 Feb 2024 12:20:13 GMT

| eval filelocation=if(like(filelocation,"%\cl1%"),"ACD55","ACD85")

Re: Replace row value data and add new column with updated value

ravir_jbp — Tue, 06 Feb 2024 12:40:56 GMT

@ITWhisperer great!! it worked Thank you for quick solution

Re: Replace row value data and add new column with updated value

ravir_jbp — Tue, 06 Feb 2024 12:44:25 GMT

Also have another doubt. I have written below query to get the specific output.

index=xyz sourcetype="automation:csv" source="D:\\Intradiem_automation\\ACD_FILETRACKER.csv"
| rex field=_raw "^(?P<ACD>\w+\.\d+),(?P<ATTEMPTS>[^,]+),(?P<FAIL_REASON>[^,]*),(?P<INTERVAL_FILE>[^,]+),(?P<STATUS>\w+),(?P<START>[^,]+),(?P<FINISH>[^,]+),(?P<INGEST_TIME>.+)"
| eval field_in_hhmmss=tostring(INGEST_TIME, "duration")
| rename field_in_hhmmss AS INGESTION_TIME_HH-MM-SS
| search ACD="*" ATTEMPTS="*" FAIL_REASON="*" INTERVAL_FILE="*" STATUS="*" START="*" FINISH="*" INGESTION_TIME_HH-MM-SS="*"
| table ACD, ATTEMPTS, FAIL_REASON, INTERVAL_FILE,INTERVAL_FILE1, STATUS, START, FINISH, INGESTION_TIME_HH-MM-SS
| dedup INTERVAL_FILE
| sort -START

I like to extract the filename "020624.0500" from Interval_file column and create another column name "Filename" beside the Interval_file column and before status column. Please help

ACD ATTEMPTS FAIL_REASON INTERVAL_FILE STATUS START FINISH INGESTION_TIME_HH-MM-SS

acd.55	1	NULL	C:\totalview\ftp\switches\customer1\55\020624.0500	PASS	2024-02-06 11:32:30.057 +00:00	2024-02-06 11:32:52.274 +00:00	00:00:22
acd.55	1	NULL	C:\totalview\ftp\switches\customer1\55\020624.0530	PASS	2024-02-06 12:02:30.028 +00:00	2024-02-06 12:02:54.151 +00:00	00:00:24
acd.85	1	NULL	C:\totalview\ftp\switches\customer1\85\020624.0500	PASS	2024-02-06 11:31:30.021 +00:00	2024-02-06 11:31:40.788 +00:00	00:00:10

Re: Replace row value data and add new column with updated value

ravir_jbp — Tue, 06 Feb 2024 13:22:38 GMT

@ITWhisperer seems its working partially. I can see only data with cl1 is getting replaced. I also have data with cl3 which needs to be replaced by ACD85.

cl1=ACD55

cl3=ACD85

Am I missing any thing here.

Re: Replace row value data and add new column with updated value

ITWhisperer — Tue, 06 Feb 2024 13:29:38 GMT

| eval Filename=mvindex(split(INTERVAL_FILE,"\\"),-1)

Re: Replace row value data and add new column with updated value

ITWhisperer — Tue, 06 Feb 2024 16:23:52 GMT

That's hard to tell - please can you share your full search?

Re: Replace row value data and add new column with updated value

yuanliu — Wed, 07 Feb 2024 08:23:21 GMT

only data with cl1 is getting replaced. I also have data with cl3 which needs to be replaced by ACD85.

There is no possibility @ITWhisperer's search should give this half of replacement. But first, your search is very inefficient: The third line starting with search should be accomplished in the first line so fewer events are computed. Secondly, using regex on rigidly formatted data (CSV) is a waste and prone to errors. This is what I suggest, using exactly what @ITWhisperer proposed.

index=csv sourcetype="miscprocess:csv" source="D:\\automation\\miscprocess\\output_acd.csv" ("\cl3\" OR "\cl1\") | eval filename = split(_raw, ",") | eval filesize = mvindex(filename, 1), filelocation = mvindex(filename, 2) | eval filename = mvindex(filename, 0) | eval filelocation=if(like(filelocation,"%\cl1%"),"ACD55","ACD85")

Also important: Play with the following emulation and compare with your real data:

| makeresults | fields - _* | eval data=split("012624.1230,13253.10546875,E:\totalview\ftp\acd\cl1\backup_modified\012624.1230 012624.1230,2236.3291015625,E:\totalview\ftp\acd\cl3\backup\012624.1230 012624.1200,13338.828125,E:\totalview\ftp\acd\cl1\backup_modified\012624.1200 012624.1200,2172.1640625,E:\totalview\ftp\acd\cl3\backup\012624.1200 012624.1130,13292.32421875,E:\totalview\ftp\acd\cl1\backup_modified\012624.1130 012624.1130,2231.9658203125,E:\totalview\ftp\acd\cl3\backup\012624.1130 012624.1100,13438.65234375,E:\totalview\ftp\acd\cl1\backup_modified\012624.1100", " ") | mvexpand data | rename data AS _raw | search (\\cl1\\ OR \\cl3\\) ``` the above emulates index=csv sourcetype="miscprocess:csv" source="D:\\automation\\miscprocess\\output_acd.csv" ("\cl3\" OR "\cl1\") ``` | eval filename = split(_raw, ",") | eval filesize = mvindex(filename, 1), filelocation = mvindex(filename, 2) | eval filename = mvindex(filename, 0) | eval filelocation=if(like(filelocation,"%\cl1%"),"ACD55","ACD85")

The output is

_raw	filelocation	filename	filesize
012624.1230,13253.10546875,E:\totalview\ftp\acd\cl1\backup_modified\012624.1230	ACD55	012624.1230	13253.10546875
012624.1230,2236.3291015625,E:\totalview\ftp\acd\cl3\backup\012624.1230	ACD85	012624.1230	2236.3291015625
012624.1200,13338.828125,E:\totalview\ftp\acd\cl1\backup_modified\012624.1200	ACD55	012624.1200	13338.828125
012624.1200,2172.1640625,E:\totalview\ftp\acd\cl3\backup\012624.1200	ACD85	012624.1200	2172.1640625
012624.1130,13292.32421875,E:\totalview\ftp\acd\cl1\backup_modified\012624.1130	ACD55	012624.1130	13292.32421875
012624.1130,2231.9658203125,E:\totalview\ftp\acd\cl3\backup\012624.1130	ACD85	012624.1130	2231.9658203125
012624.1100,13438.65234375,E:\totalview\ftp\acd\cl1\backup_modified\012624.1100	ACD55	012624.1100	13438.65234375

As you see, there is no such "partial replacement". You will need to illustrate and explain any discrepancy between real data and this mock data if you don't get the same results.