The log I provided was just a sample set to show what I am searching.   So, if I search for just "View Refresh" for a duration of 1 hour, I see 4 sets of events - i.e 4 entries of "start" and "end" of each. To underlying my commandments: Illustrate data input (in raw text, anonymize as needed), whether they are raw events or output from a search (SPL that volunteers here do not have to look at). Illustrate the desired output from illustrated data. If volunteers do not see actual data (4 sets of events), how can we tell why you do not get desired results (4 durations)? ... View more

Since I don't control the growth of data in the Contact DB,  I am trying to figure out a way to get an email alert if one of the groups exceeded 50k limit. That's exactly what my first suggestion does: Print a line if and only if one of them exceeded 50k (if you substitute 5000 with 50000).  All you need is add sendmail after that. ... View more

Can you explain @richgalloway 's main question: How can two events produce 4 transactions (durations)? Here is an emulation of the two events you illustrated, and the transaction command to follow   | makeresults format=csv data="_raw 2024-10-10T06:30:11.478-04:00 | INFO | 1 | | xxxxxxxxxxxxxxxxx : Start View Refresh (price_vw) !!! 2024-10-10T06:30:11.509-04:00 | INFO | 1 | | xxxxxxxxxxxxxxxxx : End View Refresh (price_vw) !!!" | eval _time = strptime(replace(_raw, "(\S+).*", "\1"), "%FT%T.%3N%z") | sort - _time ``` the above emulates index=* ("Start View Refresh (price_vw)" OR "End View Refresh (price_vw)") ``` | transaction endswith="End View Refresh" startswith="Start View Refresh"   The result is _raw _time closed_txn duration eventcount field_match_sum linecount 2024-10-10T06:30:11.478-04:00 | INFO | 1 | | xxxxxxxxxxxxxxxxx : Start View Refresh (price_vw) !!! 2024-10-10T06:30:11.509-04:00 | INFO | 1 | | xxxxxxxxxxxxxxxxx : End View Refresh (price_vw) !!! 2024-10-10 03:30:11.478 1 0.031 2 0 2 As richgalloway predicted, one duration. ... View more

I'm afraid I still don't understand what you are trying to do.  It makes absolutely no sense to join 50000 raw events.  In fact, it generally makes zero sense to join raw events to begin with. It is best to describe what the end goal is with zero SPL.  I posted my four commandments of asking an answerable question many times.  Here they are again. Illustrate data input (in raw text, anonymize as needed), whether they are raw events or output from a search that volunteers here do not have to look at. Illustrate the desired output from illustrated data. Explain the logic between illustrated data and desired output without SPL. If you also illustrate attempted SPL, illustrate actual output and compare with desired output, explain why they look different to you if that is not painfully obvious. In your case, simply avoid illustrating SPL.  Just illustrate what your data is,characteristics, etc., and what your result look like and why the illustrated data should lead to illustrated result, all without SPL.  There is a chance that some volunteers can understand if you do NOT show SPL. ... View more

If you have lots of events, performing lookup after stats will be more efficient. index=* | chart count by X | lookup my-lookup.csv Y AS X OUTPUT X_description This will add an extra field.  If you don't want to see X, just remove it with fields command. ... View more

Agree with @richgalloway.  To ask an answerable question about data analytics, you need to Illustrate data input (in raw text, anonymize as needed), whether they are raw events or output from a search that volunteers here do not have to look at. Illustrate the desired output from illustrated data. Explain the logic between illustrated data and desired output without SPL. If you also illustrate attempted SPL, illustrate actual output and compare with desired output, explain why they look different to you if that is not painfully obvious. One more suggestion, have you considered transaction command?  People here do not throw transaction into recommendations lightly because there are usually better alternatives.  But without context, transaction is the generic approach that fits your description. | transaction endswith="End View Refresh" startswith="Start View Refresh" ... View more

Looking for an output to give me results similar to what I provided below Printer Name      Location            Print Jobs                Pages Printed prnt_01                  main office       3                                   80 prnt_02                  front desk         1                                   15 prnt_03                  breakroom       0                                   25 prnt_04                  hallway              1                                   25 I have two separate queries for both respectively and having issues merging them together.  My individual queries are: Working query that gives me job count with sum of total jobs and total pages   ... Results Printer Name      Location            Print Jobs                Pages Printed prnt_01                  main office       3                                   80 prnt_02                  front desk         1                                   15 prnt_04                  hallway              1                                    25 Is this not confusing for others?  You already get what you wanted.  What is missing here? ... View more

Let me premise this by saying that it is rarely a good practice or necessary to have this many subsearches in one place. But without knowing the actual use case such as data involved in each, I am also not sure if I can understand what your desired results are.  If I disregard the pseudo code structure, and just interpret your words literally, all you need to do is something like search1 | stats count | where count > 5000 | eval this = "search 1" | append [search2 | stats count | where count > 5000 | eval this = "search 2"] | append [search3 | stats count | where count > 5000 | eval this = "search 3"] | append [search 4 | stats count | where count > 5000 | eval this = "search 4"] | stats values(this) as message | eval message = if(isnull(message), null(), mvjoin(message, ", and") . " exceed 5000")   ... View more

You mean something like this?   | eval date = strftime(_time, "%F") | stats min(_time) as start max(_time) as end by date | eval duration = round(end - start) | fields - start end   date duration 2024-10-04 61267 2024-10-05 8 Here is the emulation   | makeresults format=csv data="jobId, date, skip1, skip2, time Job1, 10/4/2024, 20241004, 10/4/2024, 0:38:27 Job1, 10/4/2024, 20241004, 10/4/2024, 0:38:41 Job 2, 10/4/2024, 20241004, 10/4/2024, 17:39:12 Job 2, 10/4/2024, 20241004, 10/4/2024, 17:39:24 Job 2, 10/4/2024, 20241004, 10/4/2024, 17:39:34 Job1, 10/5/2024, 20241004, 10/4/2024, 0:38:27 Job1, 10/5/2024, 20241004, 10/4/2024, 0:38:35" | eval _time = strptime(date . " " . time, "%m/%d/%Y %H:%M:%S") ``` data emulation above ```   ... View more

@PickleRick is correct that you cannot "automate" this from outside of Splunk because the nature of an alert is not to carry all context. (Otherwise you wouldn't be asking this question.) Meanwhile, I will take a totally different approach from your stated approach.   Start from this dummy "alert" as an example. index=_internal log_level=ERROR earliest=-5m | stats count | where count > 50 | sendalert dummyaction If, say, every time this alert is triggered, you want a search to give you all raw events used in this alert to your email.  Schedule the following the same way the alert is scheduled: index=_internal log_level=error earliest=-5m | stats count | where count > 100 | map search search="search index=_internal log_level=error earliest=-5m" | sendemail to="elvis@splunk.com" sendresults=true There are many ways to refine and develop this idea, many different commands to choose from and many ways to customize according to what additional information you need for your investigation.  The bottom line is: You don't run a "script" (to respond to a triggered alert).  Just use the same filter to trigger an action that gives you the appropriate level of detail. ... View more

1.  index=abc source="/opt/src/datasource.tmp" | dedup _raw | table Servers | stats count(Servers) as Total 2.  index=abc source="/opt/src/datasource.tmp" | dedup _raw | table CompletedServers | stats count(CompletedServers) as Completed As @PickleRick points out, these searches you posted reveal potentially deeper problems that is your data.  If there is a need to dedup _raw, you should try clean up data first.  Also, there should never be two separate index searches using the same source.  PickleRick already illustrated a single search to get the same counts.   Let me further point out that most likely, the two searches produce the exact same count if Servers and CompletedServers appear in the same events. But back to your original table ServerName             UpgradeStatus ==========         ============= Server1                     Completed Server2                     Completed Server3                     Completed Server4                     Completed Server5                     Completed Server6                     Completed Server7                     Pending Server8                     Pending Server9                     Pending Server10                  Pending Obviously, neither of your searches will provide those "Pending" ones.  When asking a question in a public forum, it is really important to explain your input and output.  It is obvious that you did not think @sainag_splunk's and my previous answers did not give you the solution because you didn't even have the table. Because If you did, either of our searches will have given you the table you needed. So, I venture to guess that the real question is how to derive the first table from the index data you have.  Once this table is formed, either of our suggestions would have given you the display you wanted.  Is this correct? Back to the problem of UpgradeStatus.  When I point out that your searches do not produce Pending values, the big question is: What is in CompletedStatus?  Does it give "Completed" for some ServerName, and "Pending" for others?  And what is the field name that gives you ServerName?  Is it Servers used in your first search? If both are true, and that ServerName and CompletedStatus appear in the same events, the solution is as simple as index=abc source="/opt/src/datasource.tmp" | stats dc(Servers) as count by CompletedStatus | eventstats sum(count) as total | eval count = count . " (" . round(count / total * 100) . "%)" | fields - total | transpose header_field=CompletedStatus | fields - column In other words, all that change from my previous answer is field names that I guess from the two meaningless searches. Here are my four commandments of asking answerable data analytics questions: Illustrate data input (in raw text, anonymize as needed), whether they are raw events or output from a search that volunteers here do not have to look at. Illustrate the desired output from illustrated data. Explain the logic between illustrated data and desired output without SPL. If you also illustrate attempted SPL, illustrate actual output and compare with desired output, explain why they look different to you if that is not painfully obvious. Volunteers are not your mind readers.  It is unfair to ask unanswerable questions here. ... View more

The following is the emulation to produce the mock data.  Play with it and compare with real data.   | makeresults | fields - _* | eval _raw = "{ \"somekey\": [ { \"AmiLaunchIndex\": 0, \"ImageId\": \"ami-08435a7e5e61bc00b\", \"InstanceId\": \"i-0b307f45f965d4222\", \"InstanceType\": \"r5b.2xlarge\", \"KeyName\": \"gsus-prod-net-01-key\", \"LaunchTime\": \"2024-07-19T15:42:09+00:00\", \"Monitoring\": { \"State\": \"disabled\" }, \"Placement\": { \"AvailabilityZone\": \"us-east-1b\", \"GroupName\": \"\", \"Tenancy\": \"default\" }, \"Platform\": \"windows\", \"PrivateDnsName\": \"ip-172-23-43-196.ec2.internal\", \"PrivateIpAddress\": \"172.23.43.196\", \"ProductCodes\": [], \"PublicDnsName\": \"\", \"State\": { \"Code\": 16, \"Name\": \"running\" }, \"StateTransitionReason\": \"\", \"SubnetId\": \"subnet-03aa1e87392c5e63d\", \"VpcId\": \"vpc-0e8954f5da98fe98d\", \"Architecture\": \"x86_64\", \"BlockDeviceMappings\": [ { \"DeviceName\": \"/dev/sda1\", \"Ebs\": { \"AttachTime\": \"2024-07-19T15:41:21+00:00\", \"DeleteOnTermination\": false, \"Status\": \"attached\", \"VolumeId\": \"vol-0d39da483194fdffa\" } }, { \"DeviceName\": \"/dev/xvdf\", \"Ebs\": { \"AttachTime\": \"2023-07-06T19:30:04+00:00\", \"DeleteOnTermination\": false, \"Status\": \"attached\", \"VolumeId\": \"vol-07813f09191a4af20\" } }, { \"DeviceName\": \"/dev/xvdg\", \"Ebs\": { \"AttachTime\": \"2023-07-06T19:30:15+00:00\", \"DeleteOnTermination\": false, \"Status\": \"attached\", \"VolumeId\": \"vol-05d213e8588431029\" } }, { \"DeviceName\": \"/dev/xvdh\", \"Ebs\": { \"AttachTime\": \"2023-07-06T19:29:54+00:00\", \"DeleteOnTermination\": false, \"Status\": \"attached\", \"VolumeId\": \"vol-08849724290ff4ff0\" } }, { \"DeviceName\": \"/dev/xvdi\", \"Ebs\": { \"AttachTime\": \"2023-07-06T19:29:54+00:00\", \"DeleteOnTermination\": false, \"Status\": \"attached\", \"VolumeId\": \"vol-0711192d14ffdc4ff\" } }, { \"DeviceName\": \"/dev/xvdj\", \"Ebs\": { \"AttachTime\": \"2023-07-06T19:30:25+00:00\", \"DeleteOnTermination\": false, \"Status\": \"attached\", \"VolumeId\": \"vol-0d8db3494a8c7254c\" } }, { \"DeviceName\": \"/dev/xvdk\", \"Ebs\": { \"AttachTime\": \"2023-07-06T19:30:16+00:00\", \"DeleteOnTermination\": false, \"Status\": \"attached\", \"VolumeId\": \"vol-0cba7f29aec3d9a35\" } }, { \"DeviceName\": \"/dev/xvdl\", \"Ebs\": { \"AttachTime\": \"2023-07-06T19:30:15+00:00\", \"DeleteOnTermination\": false, \"Status\": \"attached\", \"VolumeId\": \"vol-0810a5ac78ecf0e11\" } }, { \"DeviceName\": \"/dev/xvdm\", \"Ebs\": { \"AttachTime\": \"2023-07-06T19:30:15+00:00\", \"DeleteOnTermination\": false, \"Status\": \"attached\", \"VolumeId\": \"vol-0e4ec2b5f1bf4d871\" } }, { \"DeviceName\": \"/dev/xvdn\", \"Ebs\": { \"AttachTime\": \"2023-07-06T19:29:54+00:00\", \"DeleteOnTermination\": false, \"Status\": \"attached\", \"VolumeId\": \"vol-092a40b78baeeac7f\" } }, { \"DeviceName\": \"/dev/xvdo\", \"Ebs\": { \"AttachTime\": \"2023-07-06T19:29:55+00:00\", \"DeleteOnTermination\": false, \"Status\": \"attached\", \"VolumeId\": \"vol-0b1e2d02d509f3f5e\" } }, { \"DeviceName\": \"/dev/xvdp\", \"Ebs\": { \"AttachTime\": \"2023-07-06T19:30:15+00:00\", \"DeleteOnTermination\": false, \"Status\": \"attached\", \"VolumeId\": \"vol-01cabf285c719295f\" } } ], \"ClientToken\": \"terraform-20230706192857135700000004\", \"EbsOptimized\": true, \"EnaSupport\": true, \"Hypervisor\": \"xen\", \"IamInstanceProfile\": { \"Arn\": \"arn:aws:iam::189682858917:instance-profile/hig/WindowsEC2Role\", \"Id\": \"AIPASYKP2XOSSBXPPW5LL\" }, \"NetworkInterfaces\": [ { \"Attachment\": { \"AttachTime\": \"2023-07-06T19:28:58+00:00\", \"AttachmentId\": \"eni-attach-09192f2a4fbf5a17d\", \"DeleteOnTermination\": false, \"DeviceIndex\": 0, \"Status\": \"attached\", \"NetworkCardIndex\": 0 }, \"Description\": \"\", \"Groups\": [ { \"GroupName\": \"gsus-prod-net-01-cmdb-sg-2022051216484634970000000a\", \"GroupId\": \"sg-04908349d0f791aec\" }, { \"GroupName\": \"gsus-prod-net-01-sql-sg-20220512164845082300000004\", \"GroupId\": \"sg-0b214ef19d0a4c0f7\" }, { \"GroupName\": \"gsus-prod-net-01-app-4587-v2sql-sg-20230707183929669400000001\", \"GroupId\": \"sg-0c2f333cbfb09b29f\" }, { \"GroupName\": \"gsus-prod-net-01-win-sg-20220512164844866600000001\", \"GroupId\": \"sg-0257bb3846318d5fd\" } ], \"Ipv6Addresses\": [], \"MacAddress\": \"12:90:ce:a5:ca:a1\", \"NetworkInterfaceId\": \"eni-0086b400faa99451d\", \"OwnerId\": \"189682858917\", \"PrivateDnsName\": \"ip-172-23-43-196.ec2.internal\", \"PrivateIpAddress\": \"172.23.43.196\", \"PrivateIpAddresses\": [ { \"Primary\": true, \"PrivateDnsName\": \"ip-172-23-43-196.ec2.internal\", \"PrivateIpAddress\": \"172.23.43.196\" }, { \"Primary\": false, \"PrivateDnsName\": \"ip-172-23-43-163.ec2.internal\", \"PrivateIpAddress\": \"172.23.43.163\" }, { \"Primary\": false, \"PrivateDnsName\": \"ip-172-23-43-141.ec2.internal\", \"PrivateIpAddress\": \"172.23.43.141\" } ], \"SourceDestCheck\": true, \"Status\": \"in-use\", \"SubnetId\": \"subnet-03aa1e87392c5e63d\", \"VpcId\": \"vpc-0e8954f5da98fe98d\", \"InterfaceType\": \"interface\" } ], \"RootDeviceName\": \"/dev/sda1\", \"RootDeviceType\": \"ebs\", \"SecurityGroups\": [ { \"GroupName\": \"gsus-prod-net-01-cmdb-sg-2022051216484634970000000a\", \"GroupId\": \"sg-04908349d0f791aec\" }, { \"GroupName\": \"gsus-prod-net-01-sql-sg-20220512164845082300000004\", \"GroupId\": \"sg-0b214ef19d0a4c0f7\" }, { \"GroupName\": \"gsus-prod-net-01-app-4587-v2sql-sg-20230707183929669400000001\", \"GroupId\": \"sg-0c2f333cbfb09b29f\" }, { \"GroupName\": \"gsus-prod-net-01-win-sg-20220512164844866600000001\", \"GroupId\": \"sg-0257bb3846318d5fd\" } ], \"SourceDestCheck\": true, \"Tags\": [ { \"Key\": \"Description\", \"Value\": \"\" }, { \"Key\": \"hig-owner\", \"Value\": \"mahesh.vidyasagar@thehartford.com\" }, { \"Key\": \"Billing\", \"Value\": \"APP-4587\" }, { \"Key\": \"Repository\", \"Value\": \"https://github.thehartford.com/HIG/gs_tfe_peoplesoft_app4587\" }, { \"Key\": \"Schedule\", \"Value\": \"None\" }, { \"Key\": \"mssql_adou\", \"Value\": \"OU=SQL-GS,OU=Windows,OU=Cloud,OU=Servers,DC=ad1,DC=prod\" }, { \"Key\": \"child_module_name\", \"Value\": \"terraform-aws-ec2-db-mssql\" }, { \"Key\": \"mssql_instancename\", \"Value\": \"MSSQLSERVER\" }, { \"Key\": \"module_name\", \"Value\": \"terraform-aws-ec2-instance\" }, { \"Key\": \"Pipeline\", \"Value\": \"https://jenkinsci.thehartford.com/iac-1/job/IAC/job/GS/job/PeopleSoft-4587/job/gsus_4587_peoplesoft_aws_gsprod/\" }, { \"Key\": \"IMDSv2\", \"Value\": \"Enabled\" }, { \"Key\": \"IMDSv2 Date\", \"Value\": \"07/06/2023, 19:30:24\" }, { \"Key\": \"PatchGroup\", \"Value\": \"Multiple DB Node AZ2\" }, { \"Key\": \"module_version\", \"Value\": \"v4.0.6\" }, { \"Key\": \"BackupDBGroup\", \"Value\": \"ec2-sqldb-prod-silver\" }, { \"Key\": \"mssql_version\", \"Value\": \"2019\" }, { \"Key\": \"AppOwner\", \"Value\": \"Mahesh Vidyasagar\" }, { \"Key\": \"hig-planit-appid\", \"Value\": \"APP-4587-PROD\" }, { \"Key\": \"ServiceTier\", \"Value\": \"Silver\" }, { \"Key\": \"DBNodes\", \"Value\": \"Secondary\" }, { \"Key\": \"Hostname\", \"Value\": \"a4587ue1pwsag06\" }, { \"Key\": \"mssql_datavolumes\", \"Value\": \"4\" }, { \"Key\": \"mssql_installtype\", \"Value\": \"SAG\" }, { \"Key\": \"hig-data-classification\", \"Value\": \"CompanyConfidential\" }, { \"Key\": \"PatchingFlag\", \"Value\": \"Tag Applied\" }, { \"Key\": \"Environment\", \"Value\": \"prod\" }, { \"Key\": \"Owner\", \"Value\": \"HIG\" }, { \"Key\": \"hig-billing\", \"Value\": \"APP-4587\" }, { \"Key\": \"mssql_environment\", \"Value\": \"prod\" }, { \"Key\": \"Domain\", \"Value\": \"ad1.prod\" }, { \"Key\": \"AppName\", \"Value\": \"PeopleSoft\" }, { \"Key\": \"ThirdParty\", \"Value\": \"No\" }, { \"Key\": \"child_module_version\", \"Value\": \"v2.2.1\" }, { \"Key\": \"Hosted Region\", \"Value\": \"US\" }, { \"Key\": \"Name\", \"Value\": \"PeopleSoft-4587-PROD-sag-06\" }, { \"Key\": \"BackupGroup\", \"Value\": \"ec2-nobackup\" }, { \"Key\": \"LOB\", \"Value\": \"Global Specialty\" }, { \"Key\": \"Patching Group\", \"Value\": \"Multiple DB Node AZ1\" }, { \"Key\": \"backup_s3bucket\", \"Value\": \"gs-gsus-prod-net-01-sqlbackup-us-east-1\" }, { \"Key\": \"DynatraceMonitoring\", \"Value\": \"true\" }, { \"Key\": \"mssql_collation\", \"Value\": \"Latin1_General_BIN\" }, { \"Key\": \"DataClassification\", \"Value\": \"CompanyConfidential\" }, { \"Key\": \"AppId\", \"Value\": \"APP-4587\" }, { \"Key\": \"AppTier\", \"Value\": \"DB\" }, { \"Key\": \"hig-environment-type\", \"Value\": \"PROD\" }, { \"Key\": \"backup_s3bucketkms\", \"Value\": \"arn:aws:kms:us-east-1:189682858917:key/e4b94c9a-82ad-4322-98ec-48d610f6548b\" }, { \"Key\": \"Comments\", \"Value\": \"\" } ], \"VirtualizationType\": \"hvm\", \"CpuOptions\": { \"CoreCount\": 4, \"ThreadsPerCore\": 1 }, \"CapacityReservationSpecification\": { \"CapacityReservationPreference\": \"open\" }, \"HibernationOptions\": { \"Configured\": false }, \"MetadataOptions\": { \"State\": \"applied\", \"HttpTokens\": \"required\", \"HttpPutResponseHopLimit\": 1, \"HttpEndpoint\": \"enabled\", \"HttpProtocolIpv6\": \"disabled\", \"InstanceMetadataTags\": \"disabled\" }, \"EnclaveOptions\": { \"Enabled\": false }, \"PlatformDetails\": \"Windows\", \"UsageOperation\": \"RunInstances:0002\", \"UsageOperationUpdateTime\": \"2023-07-06T19:28:58+00:00\", \"PrivateDnsNameOptions\": { \"HostnameType\": \"ip-name\", \"EnableResourceNameDnsARecord\": false, \"EnableResourceNameDnsAAAARecord\": false }, \"MaintenanceOptions\": { \"AutoRecovery\": \"default\" }, \"CurrentInstanceBootMode\": \"legacy-bios\", \"someOtherStuff\": \"othervalue\", \"c7n:MatchedFilters\": [ \"tag:AccountType\", \"tag:Attributes\", \"tag:EnvironmentType\", \"tag:ImageBuilder\" ] }, { \"AmiLaunchIndex\": 1, \"someOtherStuff\": \"othervalue\", \"c7n:MatchedFilters\": [ \"tag:ApplicationFailoverGroup\", \"tag:AppTier\", \"tag:Attributes\", \"tag:DBNodes\", \"tag:rk_aws_native_account_id\", \"tag:rk_cluster_id\", \"tag:rk_component\", \"tag:rk_instance_class\", \"tag:rk_job_id\", \"tag:rk_managed\", \"tag:rk_object\", \"tag:rk_restore_source_region\", \"tag:rk_restore_timestamp\", \"tag:rk_source_snapshot_native_id\", \"tag:rk_source_vm_native_id\", \"tag:rk_source_vm_native_name\", \"tag:rk_taskchain_id\", \"tag:rk_user\", \"tag:rk_version\" ] } ] }" | spath ``` data emulation above ```           ... View more

To ask an answerable data analytics question, follow these golden rules; nay, call them the four commandments: Illustrate data input (in raw text, anonymize as needed), whether they are raw events or output from a search that volunteers here do not have to look at. Illustrate the desired output from illustrated data. Explain the logic between illustrated data and desired output without SPL. If you also illustrate attempted SPL, illustrate actual output and compare with desired output, explain why they look different to you if that is not painfully obvious. Let's check. Your sample data are JSON fragments, not a complete JSON object.  Even the last update, no one can tell how deep that structure goes.  In other words, which exact JSON path leads to the subnode c7n:MatchedFilters.  I understand that your event size is enormous so you cannot show a complete sample.  But you should show the complete structure of the object. The phrase "show the count and tagname in tabular format" in the title seems to be an attempt to describe desired output.  But there are a thousand ways to show count and tagname in tabular format.  What is even a "count" in your text? No description of logic. N/A Without these, you are effectively asking volunteers to read your mind.  That is not a fair ask. Now, based on your last update, we can see that c7n:MatchedFilters is not a top node in your raw data.  It is a subnode in a very large array.  Is this large array a top node of your raw data?  If not, how many upper paths are there before we reach c7n:MatchedFilters?  Without an exact answer to this, there is no good answer to your question.  In the following, I will assume "yes" to this, i.e., I will assume that somekey[c7n:MatchedFilters] is the correct path. (Or, in SPL flattened notation, somekey{}.c7n:MatchedFilters{}. I will further assume that "show the count and tagname in tabular format" means to count events grouped by the string following "tag:" in each value of c7n:MatchedFilters.  In other words, I assume that this table is what you look for: tagName count AccountType 1 AppTier 1 ApplicationFailoverGroup 1 Attributes 2 DBNodes 1 EnvironmentType 1 ImageBuilder 1 rk_aws_native_account_id 1 rk_cluster_id 1 rk_component 1 rk_instance_class 1 rk_job_id 1 rk_managed 1 rk_object 1 rk_restore_source_region 1 rk_restore_timestamp 1 rk_source_snapshot_native_id 1 rk_source_vm_native_id 1 rk_source_vm_native_name 1 rk_taskchain_id 1 rk_user 1 rk_version 1 The above count is based on the fragments you pasted.  I constructed the following as a valid JSON object to mimic the structure.  If this is not the correct structure, you need to go back to the four commandments and start over.     { "somekey": [ { "AmiLaunchIndex": 0, "ImageId": "ami-08435a7e5e61bc00b", "InstanceId": "i-0b307f45f965d4222", "InstanceType": "r5b.2xlarge", "KeyName": "gsus-prod-net-01-key", "LaunchTime": "2024-07-19T15:42:09+00:00", "Monitoring": { "State": "disabled" }, "Placement": { "AvailabilityZone": "us-east-1b", "GroupName": "", "Tenancy": "default" }, "Platform": "windows", "PrivateDnsName": "ip-172-23-43-196.ec2.internal", "PrivateIpAddress": "172.23.43.196", "ProductCodes": [], "PublicDnsName": "", "State": { "Code": 16, "Name": "running" }, "StateTransitionReason": "", "SubnetId": "subnet-03aa1e87392c5e63d", "VpcId": "vpc-0e8954f5da98fe98d", "Architecture": "x86_64", "BlockDeviceMappings": [ { "DeviceName": "/dev/sda1", "Ebs": { "AttachTime": "2024-07-19T15:41:21+00:00", "DeleteOnTermination": false, "Status": "attached", "VolumeId": "vol-0d39da483194fdffa" } }, { "DeviceName": "/dev/xvdf", "Ebs": { "AttachTime": "2023-07-06T19:30:04+00:00", "DeleteOnTermination": false, "Status": "attached", "VolumeId": "vol-07813f09191a4af20" } }, { "DeviceName": "/dev/xvdg", "Ebs": { "AttachTime": "2023-07-06T19:30:15+00:00", "DeleteOnTermination": false, "Status": "attached", "VolumeId": "vol-05d213e8588431029" } }, { "DeviceName": "/dev/xvdh", "Ebs": { "AttachTime": "2023-07-06T19:29:54+00:00", "DeleteOnTermination": false, "Status": "attached", "VolumeId": "vol-08849724290ff4ff0" } }, { "DeviceName": "/dev/xvdi", "Ebs": { "AttachTime": "2023-07-06T19:29:54+00:00", "DeleteOnTermination": false, "Status": "attached", "VolumeId": "vol-0711192d14ffdc4ff" } }, { "DeviceName": "/dev/xvdj", "Ebs": { "AttachTime": "2023-07-06T19:30:25+00:00", "DeleteOnTermination": false, "Status": "attached", "VolumeId": "vol-0d8db3494a8c7254c" } }, { "DeviceName": "/dev/xvdk", "Ebs": { "AttachTime": "2023-07-06T19:30:16+00:00", "DeleteOnTermination": false, "Status": "attached", "VolumeId": "vol-0cba7f29aec3d9a35" } }, { "DeviceName": "/dev/xvdl", "Ebs": { "AttachTime": "2023-07-06T19:30:15+00:00", "DeleteOnTermination": false, "Status": "attached", "VolumeId": "vol-0810a5ac78ecf0e11" } }, { "DeviceName": "/dev/xvdm", "Ebs": { "AttachTime": "2023-07-06T19:30:15+00:00", "DeleteOnTermination": false, "Status": "attached", "VolumeId": "vol-0e4ec2b5f1bf4d871" } }, { "DeviceName": "/dev/xvdn", "Ebs": { "AttachTime": "2023-07-06T19:29:54+00:00", "DeleteOnTermination": false, "Status": "attached", "VolumeId": "vol-092a40b78baeeac7f" } }, { "DeviceName": "/dev/xvdo", "Ebs": { "AttachTime": "2023-07-06T19:29:55+00:00", "DeleteOnTermination": false, "Status": "attached", "VolumeId": "vol-0b1e2d02d509f3f5e" } }, { "DeviceName": "/dev/xvdp", "Ebs": { "AttachTime": "2023-07-06T19:30:15+00:00", "DeleteOnTermination": false, "Status": "attached", "VolumeId": "vol-01cabf285c719295f" } } ], "ClientToken": "terraform-20230706192857135700000004", "EbsOptimized": true, "EnaSupport": true, "Hypervisor": "xen", "IamInstanceProfile": { "Arn": "arn:aws:iam::189682858917:instance-profile/hig/WindowsEC2Role", "Id": "AIPASYKP2XOSSBXPPW5LL" }, "NetworkInterfaces": [ { "Attachment": { "AttachTime": "2023-07-06T19:28:58+00:00", "AttachmentId": "eni-attach-09192f2a4fbf5a17d", "DeleteOnTermination": false, "DeviceIndex": 0, "Status": "attached", "NetworkCardIndex": 0 }, "Description": "", "Groups": [ { "GroupName": "gsus-prod-net-01-cmdb-sg-2022051216484634970000000a", "GroupId": "sg-04908349d0f791aec" }, { "GroupName": "gsus-prod-net-01-sql-sg-20220512164845082300000004", "GroupId": "sg-0b214ef19d0a4c0f7" }, { "GroupName": "gsus-prod-net-01-app-4587-v2sql-sg-20230707183929669400000001", "GroupId": "sg-0c2f333cbfb09b29f" }, { "GroupName": "gsus-prod-net-01-win-sg-20220512164844866600000001", "GroupId": "sg-0257bb3846318d5fd" } ], "Ipv6Addresses": [], "MacAddress": "12:90:ce:a5:ca:a1", "NetworkInterfaceId": "eni-0086b400faa99451d", "OwnerId": "189682858917", "PrivateDnsName": "ip-172-23-43-196.ec2.internal", "PrivateIpAddress": "172.23.43.196", "PrivateIpAddresses": [ { "Primary": true, "PrivateDnsName": "ip-172-23-43-196.ec2.internal", "PrivateIpAddress": "172.23.43.196" }, { "Primary": false, "PrivateDnsName": "ip-172-23-43-163.ec2.internal", "PrivateIpAddress": "172.23.43.163" }, { "Primary": false, "PrivateDnsName": "ip-172-23-43-141.ec2.internal", "PrivateIpAddress": "172.23.43.141" } ], "SourceDestCheck": true, "Status": "in-use", "SubnetId": "subnet-03aa1e87392c5e63d", "VpcId": "vpc-0e8954f5da98fe98d", "InterfaceType": "interface" } ], "RootDeviceName": "/dev/sda1", "RootDeviceType": "ebs", "SecurityGroups": [ { "GroupName": "gsus-prod-net-01-cmdb-sg-2022051216484634970000000a", "GroupId": "sg-04908349d0f791aec" }, { "GroupName": "gsus-prod-net-01-sql-sg-20220512164845082300000004", "GroupId": "sg-0b214ef19d0a4c0f7" }, { "GroupName": "gsus-prod-net-01-app-4587-v2sql-sg-20230707183929669400000001", "GroupId": "sg-0c2f333cbfb09b29f" }, { "GroupName": "gsus-prod-net-01-win-sg-20220512164844866600000001", "GroupId": "sg-0257bb3846318d5fd" } ], "SourceDestCheck": true, "Tags": [ { "Key": "Description", "Value": "" }, { "Key": "hig-owner", "Value": "mahesh.vidyasagar@thehartford.com" }, { "Key": "Billing", "Value": "APP-4587" }, { "Key": "Repository", "Value": "https://github.thehartford.com/HIG/gs_tfe_peoplesoft_app4587" }, { "Key": "Schedule", "Value": "None" }, { "Key": "mssql_adou", "Value": "OU=SQL-GS,OU=Windows,OU=Cloud,OU=Servers,DC=ad1,DC=prod" }, { "Key": "child_module_name", "Value": "terraform-aws-ec2-db-mssql" }, { "Key": "mssql_instancename", "Value": "MSSQLSERVER" }, { "Key": "module_name", "Value": "terraform-aws-ec2-instance" }, { "Key": "Pipeline", "Value": "https://jenkinsci.thehartford.com/iac-1/job/IAC/job/GS/job/PeopleSoft-4587/job/gsus_4587_peoplesoft_aws_gsprod/" }, { "Key": "IMDSv2", "Value": "Enabled" }, { "Key": "IMDSv2 Date", "Value": "07/06/2023, 19:30:24" }, { "Key": "PatchGroup", "Value": "Multiple DB Node AZ2" }, { "Key": "module_version", "Value": "v4.0.6" }, { "Key": "BackupDBGroup", "Value": "ec2-sqldb-prod-silver" }, { "Key": "mssql_version", "Value": "2019" }, { "Key": "AppOwner", "Value": "Mahesh Vidyasagar" }, { "Key": "hig-planit-appid", "Value": "APP-4587-PROD" }, { "Key": "ServiceTier", "Value": "Silver" }, { "Key": "DBNodes", "Value": "Secondary" }, { "Key": "Hostname", "Value": "a4587ue1pwsag06" }, { "Key": "mssql_datavolumes", "Value": "4" }, { "Key": "mssql_installtype", "Value": "SAG" }, { "Key": "hig-data-classification", "Value": "CompanyConfidential" }, { "Key": "PatchingFlag", "Value": "Tag Applied" }, { "Key": "Environment", "Value": "prod" }, { "Key": "Owner", "Value": "HIG" }, { "Key": "hig-billing", "Value": "APP-4587" }, { "Key": "mssql_environment", "Value": "prod" }, { "Key": "Domain", "Value": "ad1.prod" }, { "Key": "AppName", "Value": "PeopleSoft" }, { "Key": "ThirdParty", "Value": "No" }, { "Key": "child_module_version", "Value": "v2.2.1" }, { "Key": "Hosted Region", "Value": "US" }, { "Key": "Name", "Value": "PeopleSoft-4587-PROD-sag-06" }, { "Key": "BackupGroup", "Value": "ec2-nobackup" }, { "Key": "LOB", "Value": "Global Specialty" }, { "Key": "Patching Group", "Value": "Multiple DB Node AZ1" }, { "Key": "backup_s3bucket", "Value": "gs-gsus-prod-net-01-sqlbackup-us-east-1" }, { "Key": "DynatraceMonitoring", "Value": "true" }, { "Key": "mssql_collation", "Value": "Latin1_General_BIN" }, { "Key": "DataClassification", "Value": "CompanyConfidential" }, { "Key": "AppId", "Value": "APP-4587" }, { "Key": "AppTier", "Value": "DB" }, { "Key": "hig-environment-type", "Value": "PROD" }, { "Key": "backup_s3bucketkms", "Value": "arn:aws:kms:us-east-1:189682858917:key/e4b94c9a-82ad-4322-98ec-48d610f6548b" }, { "Key": "Comments", "Value": "" } ], "VirtualizationType": "hvm", "CpuOptions": { "CoreCount": 4, "ThreadsPerCore": 1 }, "CapacityReservationSpecification": { "CapacityReservationPreference": "open" }, "HibernationOptions": { "Configured": false }, "MetadataOptions": { "State": "applied", "HttpTokens": "required", "HttpPutResponseHopLimit": 1, "HttpEndpoint": "enabled", "HttpProtocolIpv6": "disabled", "InstanceMetadataTags": "disabled" }, "EnclaveOptions": { "Enabled": false }, "PlatformDetails": "Windows", "UsageOperation": "RunInstances:0002", "UsageOperationUpdateTime": "2023-07-06T19:28:58+00:00", "PrivateDnsNameOptions": { "HostnameType": "ip-name", "EnableResourceNameDnsARecord": false, "EnableResourceNameDnsAAAARecord": false }, "MaintenanceOptions": { "AutoRecovery": "default" }, "CurrentInstanceBootMode": "legacy-bios", "someOtherStuff": "othervalue", "c7n:MatchedFilters": [ "tag:AccountType", "tag:Attributes", "tag:EnvironmentType", "tag:ImageBuilder" ] }, { "AmiLaunchIndex": 1, "someOtherStuff": "othervalue", "c7n:MatchedFilters": [ "tag:ApplicationFailoverGroup", "tag:AppTier", "tag:Attributes", "tag:DBNodes", "tag:rk_aws_native_account_id", "tag:rk_cluster_id", "tag:rk_component", "tag:rk_instance_class", "tag:rk_job_id", "tag:rk_managed", "tag:rk_object", "tag:rk_restore_source_region", "tag:rk_restore_timestamp", "tag:rk_source_snapshot_native_id", "tag:rk_source_vm_native_id", "tag:rk_source_vm_native_name", "tag:rk_taskchain_id", "tag:rk_user", "tag:rk_version" ] } ] }   Basically, I took the valid element from your latest sample, and added the fragment containing the c7n:MatchedFilters key as a second element of top level key somekey. So, the second element does not have all the other keys as they are not of interest to this particular question. After this construction, I think I get a handle to why the basic technique doesn't work.  But let me explain the basic technique first.     This is assuming that Splunk search head already extracted all fields from raw data, which is the norm.  But your raw events are so huge, even that single valid array element you posted is >15KB.  Splunk has this default in limits.conf   [spath] # Number of characters to read from an XML or JSON event when # auto extracting. extraction_cutoff = 5000   You can increase this to be larger than the maximum size of your events.  But that may require a lot of RAM. Without touching extraction_cutoff, you can directly reach c7n:MatchedFilters with spath command.  Like this:   | spath path=somekey{} | spath input=somekey{} path=c7n:MatchedFilters{} output=tagName | stats count by tagName | eval tagName = replace(tagName, "tag:", "")     ... View more

when we combined these lookups as shown in the query you shared, the results only reflected matches from the second lookup, meaning only the IP addresses were being compared. Additionally,  My mistake again.  When using the same output name, the second lookup overrides the first.  Use outputnew in the second. index=A sourcetype="Any" | fields "IP address" Hostname OS | dedup "IP address" Hostname OS | eval Hostname = lower(Hostname) | lookup inventory.csv Reporting_Host as Hostname output Reporting_Host as match | lookup inventory.csv Reporting_Host as "IP address" OUTPUTNEW Reporting_Host as match | eval match = if(isnull(match), "missing", "ok") | table Hostname "IP address" OS match   ... View more

You can still use token in that where clause.  In fact, where in an inputlookup uses the same syntax as search term, unlike the where command that requires an eval expression. ... View more

I think my understanding fits your description.  The idea behind my suggested search is: Search between the two hours.  Find all records that has pv_number. (You can restrict pv_number to a given value but my search assumes that you want to group by pv_number, which is stated in the OP.) Look for the earliest value of state, and the latest. Compare earliest value and latest value. Only print those where the two equal. Have you tried my search?  Also play with my emulation (that should run in any instance), and examine output with and without that where filter.  As my code indicates, I use thread_name to fake pv_number, date_minute to fake state.  They may have different values from your real data, but the principle is the same. ... View more

You said crash several times.  This makes me think that your server may not have enough memory for that giant eventstats. (Again, this all depends on how many unique host name and IP addresses are in those tens of thousands of events.  If that number is unusually large, it could exceed system RAM.  But it also makes me suspect that your search heads might be under provisioned.) If you are willing to use inventory lookup, things can improve.  Given that only one field from index B is useful in your logic, inventory should come from this index. (You also said index B is smaller.) Here is my suggested setup for inventory.csv. index = B sourcetype="foo" | dedup Reporting_Host | table Reporting_Host | outputlookup inventory.csv After this, index=A sourcetype="Any" | fields "IP address" Hostname OS | dedup "IP address" Hostname OS | eval Hostname = lower(Hostname) | lookup inventory.csv Reporting_Host as Hostname output Reporting_Host as match | lookup inventory.csv Reporting_Host as "IP address" output Reporting_Host as match | eval match = if(isnull(match), "missing", "ok") | table Hostname "IP address" OS match ... View more

No difference with inputlookup. fields is usually preferred if working with an index search that fetches actual events. ... View more

This is somewhat confusing.  Do you mean to say that you have a multiselect token that evaluates into the search expression shown in the first code box, or is that one of multiselect values?  If the former, I strongly suggest that you rethink the strategy because a user may well end up composing a token that evaluates into |table source index="myindex" sourcetype="pinginfo" source="C:\\a\\b\\c\\d\\e\\f f\\g\\h\\ı-i-j\\porty*" |dedup source This is probably not what the user wanted. Regardless, if your really, really want double backslash - I really can't conjure up a good reason for that even in a Microsoft world, you need something like index="myindex" sourcetype="pinginfo" source="C:\\\\a\\\\b\\\\c\\\\d\\\\e\\\\f f\\\\g\\\\h\\\\ı-i-j\\\\porty*" |table source |dedup source   ... View more

You need to better explain the desired results by illustrating them in table or elaborate on what "compute stats on totalItems" will do.  Do not force volunteers to read your mind. If I must try mind reading, I speculate that you want a sum of totalItems.  This can be achieved with   | rename message as _raw | kv | stats sum(totalItems) as totalItems by someType   Here is an expansion of your mock data to make total meaningful _raw {"cluster_id":"cluster","message":"Excel someType=MY_TYPE totalItems=1 errors=\"ABC, XYZ\" status=success","source":"some_data"} {"cluster_id":"cluster","message":"Excel someType=YOUR_TYPE totalItems=2 errors=\"ABC, XYZ\" status=failure","source":"some_data"} {"cluster_id":"cluster","message":"Excel someType=MY_TYPE totalItems=3 errors=\"AAA, XYZ\" status=questionable","source":"some_data"} Running the above search gives someType totalItems MY_TYPE 4 YOUR_TYPE 2 Is this table about right? You are correct to call out total errors needing to be a separate search. (I mean, you can have them combined if you want to group top error by someType, too.)  To do this, however, I have to assume that your developers are nice to you and placed quotes around values of errors. (See the difference between my mock data and yours.)   | rename message as _raw | kv | stats count by errors | sort count | tail 1 | rename errors as topErrors   The same expanded, "nicified" mock data would give topErrors count ABC, XYZ 2 This is the emulation to produce the mock data that you can play with and compare with real data   | makeresults format=json data="[ {\"cluster_id\":\"cluster\", \"message\":\"Excel someType=MY_TYPE totalItems=1 errors=\\\"ABC, XYZ\\\" status=success\",\"source\":\"some_data\"}, {\"cluster_id\":\"cluster\", \"message\":\"Excel someType=YOUR_TYPE totalItems=2 errors=\\\"ABC, XYZ\\\" status=failure\",\"source\":\"some_data\"}, {\"cluster_id\":\"cluster\", \"message\":\"Excel someType=MY_TYPE totalItems=3 errors=\\\"AAA, XYZ\\\" status=questionable\",\"source\":\"some_data\"} ]" ``` data emulation above ```   ... View more

JSON is a structure that does not require any specific order of key.  If your downstream application has this requirement, they are noncompliant to the standard.  You don't have to make any change.  Demand that your downstream developer make change. ... View more