Hi,
I cannot found any similar thread on this issue, my aim is to display fields with different values between 2 row, so my problem will be...
my search...
statistics view from my search
product | color | product_id | description1 | description2 | description3 | description4 |
phone | blue | tag_1 | pass | pass | fail | |
phone | blue | tag_2 | fail | pass | pass | fail |
Desired_outcome
1)
product | color | product_id | description1 | description3 |
phone | blue | tag_1 | pass | |
phone | blue | tag_2 | fail | pass |
or
2) if option 1 not achievable, maybe this work as well.
product_id | description1 | description3 |
tag_1 | pass | |
tag_2 | fail | Pass |
Appreciate your help.
| makeresults
| eval _raw="product color product_id description1 description2 description3 description4
phone blue tag_1 pass pass fail
phone blue tag_2 fail pass pass fail"
| multikv forceheader=1
| fields - _* linecount
| transpose 0
| eval same=if('row 1'=='row 2',"same","different")
| where same="different"
| fields - same
| transpose 0 header_field=column
| fields - column
Adapting the solution provided by @ITWhisperer to provide Desired Outcome 1.
| makeresults
| eval _raw="product color product_id description1 description2 description3 description4
phone blue tag_1 pass pass fail
phone blue tag_2 fail pass pass fail"
| multikv forceheader=1
| fields - _* linecount
| eval pKey=product.":".color.":".product_id
| fields - product color product_id
| transpose 0
| eval same=if('row 1'=='row 2',"same","different")
| where same="different"
| fields - same
| transpose 0 header_field=column
| rex field=pKey "^(?<product>.*):(?<color>.*):(?<product_id>.*)"
| fields - column pKey
We have a similar question to this thread but in our case we're trying to compare 2 lookup files for user-IDs and if there's a match do a count of all the accounts that have a .admin in the name.
We're able to do a basic compare using "where isnull(userPrincipalName)" but not sure what's needed to compare if the user exist, then do a count .admin usernames.
Here's what we have so far:
| inputlookup UserID-File.csv | lookup UserID2-File userPrincipalName OUTPUTNEW userPrincipalName as userPrincipalName | where isnull(userPrincipalName)
Any thoughts? Thanks in advanced.
| regex userPrincipalName="\.admin"
| stats count
Thank you so much for your reply.
Since this is comparing 2 lookup files, the common field between the 2 files is the userPrincipalName. However we need to extract the sAMAccountName (which was exported from Active Directory) that contains a userID that have a .sa or .svc at the end of the name.
Any thoughts on how to achieve this?
To further clarify the 1st lookup file in the SPL is the file from AD and 2nd file is not. We've notice the 1st file referenced is where the results return from if it meets the search criteria.
| inputlookup ADUserID-File.csv | lookup ExDbUserID2-File userPrincipalName OUTPUTNEW userPrincipalName as userPrincipalName | where isnull(userPrincipalName)
I am not sure I understand. Is this is a third source that you want to lookup and compare or something that is already in one of the two sources you have used already?
@ITWhisperer wrote:I am not sure I understand. Is this is a third source that you want to lookup and compare or something that is already in one of the two sources you have used already?
Sorry for the confusion. These are same 2 sources.
Hi @yuming1127,
one little question: the pair product/color is unique in your results or not?
if yes, you can use stats to have your need.
your_search
| stats dc(product_id) AS dc_product_id values(product_id) AS product_id dc(description1) AS dc_description1 values(description1) AS description1 dc(description3) AS dc_description3 values(description3) AS description3 BY product color
| where dc_product_id>1 OR dc_description1>1 OR dc_description1>3
| table product color description1 description3
Ciao.
Giuseppe
great effort but I don't get desired outcome,
description1-description4 is just example, which the values are randomize, my real data have like 70 + description.
btw thanks for the reply
Sorry @gcusello, but this doesn't quite work if some of the values are null as they don't get included in the dc count. However, you could use fullnull to get around this. (Also, there is a typo in the where clause, it should be dc_description3>1.
| makeresults
| eval _raw="product color product_id description1 description2 description3 description4
phone blue tag_1 pass pass fail
phone blue tag_2 fail pass pass fail"
| multikv forceheader=1
| fields - _* linecount
| transpose 0
| eval same=if('row 1'=='row 2',"same","different")
| where same="different"
| fields - same
| transpose 0 header_field=column
| fields - column
Great one,
any idea to exclude product and color from the matching? or re-append product and color after the matching.
It's ok, i'm okay to edit the query
| eval same=if('row 1'=='row 2' AND 'row 1' != "phone" AND 'row 1' != "blue","same","different")
able to get expected outcome (1),
Thanks alot