topic Re: Multisearch or union for this case in Splunk Search

Multisearch or union for this case- How to show for 5 user id's?

untitledman27 — Tue, 27 Sep 2022 14:13:47 GMT

i All

There are query splunk like this :

(index=Prod sourcetype=ProdApp (host=Prod01 OR Prod02) source="/prodlib/SPLID" "Response" ERR-12120) | rex "^(?:[^\[\n]*\[){6}(?P\w+)" | rex field=_raw "(?<my_json>\{.*)" | spath input=my_json output=customerName path=response.login.customerName | spath input=my_json output=responseCode path=response.responseHeader.responseContext.responseCode | dedup customerName | table customerName,responseCode | append [search index=Prod sourcetype=ProdApp (host=Prod01 OR Prod02) source="/prodlib/SPLID" "Request") | rex "^(?:[^\[\n]*\[){6}(?P\w+)" | rex field=_raw "(?<my_json>\{.*)" | spath input=my_json output=userId path=data.userId | dedup userId | table userId]

I will try to join both source from Request and Response, and result like below attachment :

My question is, how show for 5 user id's ? (in blue line)
Because i already try join both sources, the user id shown not related for the customer name (in black line)Picture

Re: Multisearch or union for this case

ITWhisperer — Tue, 27 Sep 2022 12:19:08 GMT

The table is displaying the events as separate rows - in order to align the userID rows with the customer Name/Response Code rows, you could do something like this

(index=Prod sourcetype=ProdApp (host=Prod01 OR Prod02) source="/prodlib/SPLID" "Response" ERR-12120) | rex "^(?:[^\[\n]*\[){6}(?P\w+)" | rex field=_raw "(?<my_json>\{.*)" | spath input=my_json output=customerName path=response.login.customerName | spath input=my_json output=responseCode path=response.responseHeader.responseContext.responseCode | dedup customerName | table customerName,responseCode | streamstats count as row | append [search index=Prod sourcetype=ProdApp (host=Prod01 OR Prod02) source="/prodlib/SPLID" "Request") | rex "^(?:[^\[\n]*\[){6}(?P\w+)" | rex field=_raw "(?<my_json>\{.*)" | spath input=my_json output=userId path=data.userId | dedup userId | table userId | streamstats count as row] | stats values(*) as * by row | fields - row

Re: Multisearch or union for this case

untitledman27 — Wed, 28 Sep 2022 03:42:40 GMT

(index=Prod sourcetype=ProdApp (host=Prod01 OR Prod02) source="/prodlib/SPLID" "Response" ERR-12120)
| rex "^(?:[^\[\n]*\[){6}(?P<u>\w+)"
| rex field=_raw "(?<my_json>\{.*)"
| spath input=my_json output=customerName path=response.login.customerName
| spath input=my_json output=responseCode path=response.responseHeader.responseContext.responseCode
| dedup customerName
| table customerName,responseCode
| streamstats count as row
| append [search index=Prod sourcetype=ProdApp (host=Prod01 OR Prod02) source="/prodlib/SPLID" "Request")
| rex "^(?:[^\[\n]*\[){6}(?P<u>\w+)"
| rex field=_raw "(?<my_json>\{.*)"
| spath input=my_json output=userId path=data.userId
| dedup userId
| table userId
| streamstats count as row]
| stats values(*) as * by row
| fields - row

Hi IT Whisper

I already try for above script. but the result like this

1. Why only two customer name and response code for this ? And there is wrong user id for above script (red line). Example for number 1, for Ikbal the username should be IKBAL110294, not DANIAAS086

And i hope for below snapshoot from my script, red line is filled all for user id and should be 29 rows for today

This is my script , pls help for filled all user id in above screenshoot(redline)

(index=prd-splid sourcetype=prd-splid-app (host=Prod11 OR host=Prod12 OR host=Prod011 OR host=Prod12) source="/prodlib/SPLID/logs/spl-message-*.log" "Response" "-/spl-banking/services/id/security/v1/login" SPL-PRD-99999) | rex "^(?:[^\[\n]*\[){6}(?P\w+)" | rex field=_raw "(?\{.*)" | spath input=my_json output=customerName path=response.login.customerName | spath input=my_json output=responseCode path=response.responseHeader.responseContext.responseCode | dedup customerName | table customerName,responseCode | append [search index=prd-splid sourcetype=prd-splid-app (host=Prod11 OR host=Prod12 OR host=Prod011 OR host=Prod012) source="/prodlib/SPLID/logs/spl-message-*.log" ("Request" "-/spl-banking/services/id/security/v1/login") | rex "^(?:[^\[\n]*\[){6}(?P\w+)" | rex field=_raw "(?\{.*)" | spath input=my_json output=userId path=data.userId | dedup userId | table userId]

Re: Multisearch or union for this case

ITWhisperer — Wed, 28 Sep 2022 07:02:39 GMT

How do you correlate the events with customerName to the events with userId?

There is nothing in your search to suggest that you wanted to do that!

Re: Multisearch or union for this case

untitledman27 — Wed, 28 Sep 2022 07:10:46 GMT

I will corellate customerName and user id with this source. For customerName with Response source, and for UserId with Request source, will joining Request and Response for got the both info

(index=prd-splid sourcetype=prd-splid-app (host=Prod11 OR host=Prod12 OR host=Prod011 OR host=Prod12) source="/prodlib/SPLID/logs/spl-message-*.log" "Response" "-/spl-banking/services/id/security/v1/login" SPL-PRD-99999)
| rex "^(?:[^\[\n]*\[){6}(?P\w+)"
| rex field=_raw "(?\{.*)"
| spath input=my_json output=customerName path=response.login.customerName
| spath input=my_json output=responseCode path=response.responseHeader.responseContext.responseCode
| dedup customerName | table customerName,responseCode
| append
[search index=prd-splid sourcetype=prd-splid-app (host=Prod11 OR host=Prod12 OR host=Prod011 OR host=Prod012) source="/prodlib/SPLID/logs/spl-message-*.log" ("Request" "-/spl-banking/services/id/security/v1/login")
| rex "^(?:[^\[\n]*\[){6}(?P\w+)"
| rex field=_raw "(?\{.*)"
| spath input=my_json output=userId path=data.userId
| dedup userId | table userId]

Re: Multisearch or union for this case

ITWhisperer — Wed, 28 Sep 2022 07:29:46 GMT

In your example "Ikbal" does not match "IKBAL110294" and therefore these fields cannot be used to correlate the events - unless you have some additional manipulations done on one or both of the fields until the values match.

Re: Multisearch or union for this case

untitledman27 — Wed, 28 Sep 2022 09:01:39 GMT

Each name or CIF have a different User ID.

For this example(Ikbal), he has the userid IKBAL110294 which he got from the Response login.
So I want to combine the customer name in the Request login(blueline) with the UserID in the Response login(redline)

Will join this

And this

and this

And my expexted result like this

And my exprected result like this

Re: Multisearch or union for this case

ITWhisperer — Wed, 28 Sep 2022 09:53:38 GMT

Why are you not extracting userId from here?

Re: Multisearch or union for this case

untitledman27 — Wed, 28 Sep 2022 09:59:49 GMT

I don't know how to get the userid(blueline) from here, because it's outside of the Response or Request source(before -/spl..., i don't know how to get the data)

Re: Multisearch or union for this case

ITWhisperer — Wed, 28 Sep 2022 10:07:47 GMT

| rex "^.+(\[[^\]]+\].*){4}\[(?<userid>[^\]]+)\]"

Does this work?

If not, please can you share the log events in a code block </> rather than a screenshot?

Re: Multisearch or union for this case

untitledman27 — Thu, 29 Sep 2022 04:24:16 GMT

Not able to work

I already input like this from your script, but still error

(index=prd-splid sourcetype=prd-splid-app (host=Prod11 OR host=Prod12 OR host=Prod011 OR host=Prod012) source="/prodlib/SPLID/logs/spl-message-*.log" "Response" "-/spl-banking/services/id/security/v1/login" SPL-PRD-99999)
| rex "^(?:[^\[\n]*\[){6}(?P\w+)"
| rex field=_raw "(?<my_json>\{.*)"
| spath input=my_json output=customerName path=response.login.customerName
| spath input=my_json output=responseCode path=response.responseHeader.responseContext.responseCode
| dedup customerName
| table customerName,responseCode
| streamstats count as row
| append [search index=prd-splid sourcetype=prd-splid-app (host=Prod11 OR host=Prod12 OR host=Prod011 OR host=Prod012) source="/prodlib/SPLID/logs/spl-message-*.log" ("Request" "-/spl-banking/services/id/security/v1/login")
| rex "^(?:[^\[\n]*\[){6}(?P\w+)"
| rex field=_raw "(?<my_json>\{.*)"
| spath input=my_json output=userId path=data.userId
| dedup userId
| table userId
| streamstats count as row]
| stats values(*) as * by row
| fields - row

This is my script

(index=prd-splid sourcetype=prd-splid-app (host=LXSPLPIDV11 OR host=LXSPLPIDV12 OR host=LXSPLPIDV011 OR host=LXSPLPIDV012) source="/prodlib/SPLID/logs/spl-message-*.log" "Response" "-/spl-banking/services/id/security/v1/login" SPL-PRD-99999)
| rex "^(?:[^\[\n]*\[){6}(?P\w+)"
| rex field=_raw "(?<my_json>\{.*)"
| spath input=my_json output=customerName path=response.login.customerName
| spath input=my_json output=responseCode path=response.responseHeader.responseContext.responseCode
| dedup customerName
| table customerName,responseCode
| append [search index=prd-splid sourcetype=prd-splid-app (host=Prod11 OR host=Prod12 OR host=Prod011 OR host=Prod012) source="/prodlib/SPLID/logs/spl-message-*.log" ("Request" "-/spl-banking/services/id/security/v1/login")
| rex "^.+(\[[^\]]+\].*){4}\[(?<userid>[^\]]+)\]"
| rex field=_raw "(?<my_json>\{.*)"
| spath input=my_json output=userId path=data.userId
| dedup userId
| table userId]

Re: Multisearch or union for this case

untitledman27 — Fri, 30 Sep 2022 03:50:18 GMT

Hi IT Whisper

Can you help me or give some clue for this ?

Re: Multisearch or union for this case

ITWhisperer — Fri, 30 Sep 2022 07:10:40 GMT

(index=prd-splid sourcetype=prd-splid-app (host=LXSPLPIDV11 OR host=LXSPLPIDV12 OR host=LXSPLPIDV011 OR host=LXSPLPIDV012) source="/prodlib/SPLID/logs/spl-message-*.log" "Response" "-/spl-banking/services/id/security/v1/login" SPL-PRD-99999) | rex "^(?:[^\[\n]*\[){6}(?P\w+)" | rex "^.+(\[[^\]]+\].*){4}\[(?<userid>[^\]]+)\]" | rex field=_raw "(?<my_json>\{.*)" | spath input=my_json output=customerName path=response.login.customerName | spath input=my_json output=responseCode path=response.responseHeader.responseContext.responseCode | dedup customerName | table userid,customerName,responseCode

Re: Multisearch or union for this case

untitledman27 — Fri, 30 Sep 2022 09:31:34 GMT

There this error

Re: Multisearch or union for this case

ITWhisperer — Fri, 30 Sep 2022 12:02:06 GMT

Please paste your sample events into a code block </> rather than a graphic so we can test the solution more easily.

Re: Multisearch or union for this case

untitledman27 — Mon, 03 Oct 2022 02:41:39 GMT

Like this ?

(index=prd-splid sourcetype=prd-splid-app (host=LXSPLPIDV11 OR host=LXSPLPIDV12 OR host=LXSPLPIDV011 OR host=LXSPLPIDV012) source="/prodlib/SPLID/logs/spl-message-*.log" "Response" "-/spl-banking/services/id/security/v1/login" SPL-PRD-99999)

Re: Multisearch or union for this case

ITWhisperer — Mon, 03 Oct 2022 07:06:10 GMT

No, please paste the events not the SPL using the code block </>

Re: Multisearch or union for this case

untitledman27 — Tue, 04 Oct 2022 01:53:15 GMT

Like this ?

command: regex="^.+(\[[^\]]+\].*){4}\[(?<userid>[^\]]+)\]"

[LXSPKPIDV01] Streamed search execute failed because: Error in 'rex' command: regex="^.+(\[[^\]]+\].*){4}\[(?<userid>[^\]]+)\]" has exceeded configured match_limit, consider raising the value in limits.conf.

Re: Multisearch or union for this case

ITWhisperer — Tue, 04 Oct 2022 07:24:20 GMT

No, more like this

2022-09-28 10:11:26.484 [default ...

so we can include in our test of a solution for you

Re: Multisearch or union for this case

untitledman27 — Tue, 04 Oct 2022 09:27:21 GMT

<>
2022-10-04 11:23:11.160 [default task-45] INFO [SPLMessage][TWiUqanHFodo0UrDyrdkN3TXJD-5nry3r9N49A4N][11][Android-SPL][VIVIARVIANY01][ID][3.0][1852ms][] -/spl-banking/services/id/security/v1/login - Response - {"response":{"responseHeader":{"responseContext":{"responseCode":"SPL-PRD-99999","responseDescription":"Kami tidak dapat melanjutkan permintaanmu. Coba kembali ya.","serviceResponseTimeInGMT":"Tue Oct 04 04:23:10 GMT 2022","referenceNumber":"20221004042311158","systemDate":1664857391159,"challenge":"cZtpwwmGW6Q=","pubKey":"30820122300d06092a864886f70d01010105000382010f003082010a0282010100ac92dacc038f81d974e9134ea89bcf8a506962c494d7a8ca7a2931b5f9bdf7fb9b7b5d333b170cd427e3a3a062a7ca319d4817b8a7628a15ace5cfc468a78bcb26b7f69f0155406f4e47cfed6b80d4ead331da0607ce940e00b560878634fce7268d67749c45560c10a14a11808bad0f53b27d4a0037bedb3d885b2259925c813230a0a2438d7c3e87294cf3cfee62b4f635c607dc053ff0636d7251b6ed15665dc7ee498b2318db44c07104ddbc8824d7b8b639eaba8b09e115000f6e450a03aaa9f7da0b800bd6396303a14b8dd9b8e3cf78f1d78d84df8940b9d4fcb9825549903a658551a55b6eb99b4cc3c2a463f881586f09b0d26c379b985f9c900d990203010001","pubKeyIndex":"1"},"requesterContext":{"unityFlag":false,"sessionId":"0zCMYGYrcstrZZRkCodgvfkxOSJWaCtMO4HdB0rq"}},"login":{"pfmAvailable":true,"insightsAvailable":true,"verifyPin":true,"userHashKey":"8+nvMI569nIpkVfAPZ5SWso8VW+lbcdDkuQBeyP7Ges=","softTokenSerialNumber":"VKD2AD363E9A","firstPartyAccess":"Y","activationTime":0,"lastLoginTime":"04 Oct 2022 11:22 AM ","segment":"P","softTokenState":"ACTIVE","twoFAMode":"S","forceChangeUserIdentity":false,"forceChangePassword":false,"concurrentSessions":1,"stt":0,"maskedMobileNumber":"********8187","maskedEmail":"dondalop****@Anonymous.com","customerName":"VIVI ARVIANY ","securityInformation":{"assignmentDate2":"","tokenStatus":"","preferredLanguage":"E","lastLoginTime":"04 Oct 2022 11:22 AM ","softTokenFlag":true,"softTokenSerialNumber":"VKD2AD363E9A","softTokenTfaDown":false,"softTokenState":"ACTIVE","firstPartyAccess":"Y","concurrentSessions":1,"firstLoginDate":"28092022","firstLoginTime":"085655","ssoSessionToken":"70001_PUsXPbHvIlUFtKLIckbQjSimsaR_LUGkUZEm2KqgTMgd707Nu_bMoluUcNn6XWUPojGhlOgoUwO0QPShh8-9brWokMuiJ_hBXhVtokxXFw-KoEf-GuxE5FbiiPpTLeD3IK1zOtLAFDu_pxYeBwJKjLNhP0EAmvEK-GbK0hzW5EnMpl0jZkd-S69nPqoRopKZmTveYEXn5-AWghl-B-fG1NqcjTepH-8GjwDbe-dGv2aIbLS1ZUQJ_jo1LUfmRFYWXzICFNPK3mzLV5TWas8JmQeuYKoNFg20nMxBMGqbe38LyZejg6KFrDE4b3EJKVEXbqkNcxZXClu_nQFj_yF7J_LKTWiqpXcmHNr9hVviKO-UCQ1hlULk-8_y2b2Kj_oN-R8TLHuMW2htD57IQ","userLoginTime":"1664857389552","timeToSTActivation":0,"twoFAStatus":false,"blackOutTime":"30","activationTime":0,"assignmentDate":"","twoFAMode":"S","tokenActivated":false,"forceChangeUserIdentity":"N","forceChangePassword":"N","lastLoginTimeStamp":"2022-10-04 11:22:51.0"}},"dbcx":false,"timeout":false,"partialTimeout":false,"hasEcomProduct":false,"bundle":false,"twoFAEnabled":false,"twoFaEnabled":false}}
</>