Splunk Search

How to simplify this search in order to give me a distinct count of servers by application?

singhh4
Path Finder

Hey guys.

I'm kind of new to Splunk and was wondering if there was a simpler way of writing this search.

index=server App1=1|stats dc(servers) as count1
|append [search index=server App2=1|stats dc(servers) as count2]
|append [search index=server App3=1|stats dc(servers) as count3]
|table count1 count2 count3

The idea is i need to count the number of servers per application
the data is set up something like this which is why i'm doing a distinct count instead of a normal count:

Server|App1|App2|App3
_____________________
serv1 |1   |0   |1
serv1 |1   |0   |1
serv2 |0   |1   |0
serv3 |1   |1   |1
serv3 |1   |1   |1
0 Karma
1 Solution

somesoni2
Revered Legend

Give this a try

index=server | fields server App* | untable server App count | eval temp=1
| chart dc(server) over temp by App | fields - temp

UPdated

 index=server | fields server "App1" "App2" "App3" | untable server App count | where count=1| eval temp=1 | chart dc(server) over temp by App | fields - temp

View solution in original post

woodcock
Esteemed Legend

Your description and your sample output are disjointed. You are not showing a "distinct count" in your output because this is abstracted away by your array. Your output is what is known as a "binary contingency chart" where "1" means "Yes" and "0" means "No". My answer does EXACTLY this but instead of binary, it gives you total event count, which is BONUS information for you.

0 Karma

somesoni2
Revered Legend

Give this a try

index=server | fields server App* | untable server App count | eval temp=1
| chart dc(server) over temp by App | fields - temp

UPdated

 index=server | fields server "App1" "App2" "App3" | untable server App count | where count=1| eval temp=1 | chart dc(server) over temp by App | fields - temp

View solution in original post

singhh4
Path Finder

this could work but the issue is that "App1" "App2" "and "App3" are actual application names and can't really use App*

0 Karma

somesoni2
Revered Legend

They use the full field name instead of App*. Rest all can stay the same.

0 Karma

somesoni2
Revered Legend

Like this

index=server | fields server "App1" "App2" "App3" | untable server App count | eval temp=1
| chart dc(server) over temp by App | fields - temp

0 Karma

singhh4
Path Finder

I got it but they all give me the same number

App1|App2|App3
1298 |1298|1298

when those aren't the actual numbers

0 Karma

somesoni2
Revered Legend

How about this?

index=server | fields server "App1" "App2" "App3" | untable server App count | where count=1| eval temp=1 | chart dc(server) over temp by App | fields - temp
0 Karma

somesoni2
Revered Legend

Did you get a chance to try above query?

0 Karma

woodcock
Esteemed Legend

Like this:

index=server (App1=1 OR App2=1 OR App3=1)
| eval APP=case(App1=1, "App1", App2=1, "App2", App3=1, "App3", true(), "BUG!")
| chart count OVER server BY App

singhh4
Path Finder

I am trying to follow this but am getting the following error:
Error in 'eval' command: The expression is malformed. Expected ).

Here is the exact search i am running:
index="linux_patch_summary" (120_365Days=1 OR Above365Days=1)
|eval days = case(120_365Days=1, "days1", Above365Days=1, "days2")
|chart count OVER hostname by days

where:
120_365Days = App1
Above365Days = App2
hostname = server

0 Karma

woodcock
Esteemed Legend

Does this work:

index="linux_patch_summary" (120_365Days=1 OR Above365Days=1)
|eval days = case(120_365Days=1, "days1", Above365Days=1, "days2", true(), "BUG!")
| contingency hostname days
0 Karma

cmerriman
Super Champion

try this:

index=server App1=1 OR App2=1 OR App3=1|eval App1Servers=if(App1=1,1,null())|eval App2Servers=if(App2=1,1,null())|eval App3Servers=if(App3=1,1,null())|stats sum(App1Servers) as count1 sum(App2Servers) as count2 sum(App3Servers) as count3

UPDATE:

index=server App1=1 OR App2=1 OR App3=1|stats dc(eval(match(App1,"1"))) as count1 dc(eval(match(App2,"1"))) as count2 dc(eval(match(App3,"1"))) as count3  by server

singhh4
Path Finder

Will this give me a distinct count of the servers?

0 Karma

cmerriman
Super Champion

try what I've updated. It'd be distinct, and if you have a server field, it'd be by server.

0 Karma

singhh4
Path Finder

i am following this but keep getting:

Error in 'stats' command: The eval expression for dynamic field 'eval(match(120_365Days,"%1%"))' is invalid. Error='The expression is malformed. Expected ).'

here is my exact search:

index="linux_patch_summary" 120_365Days=1 OR Above365Days=1
|stats dc(eval(match(120_365Days,"1"))) as "count1" by hostname

where:
120_365Days = App1
Above365Days = App2
hostname = server

0 Karma

cmerriman
Super Champion

are you trying to do a like or a match in the eval? in the error you pasted, you have like, but the search you posted you have match.

0 Karma

singhh4
Path Finder

I'm using a matchand getting the same error. i copied the error when i switched to a like. sorry about that

0 Karma

cmerriman
Super Champion

try this:

index="linux_patch_summary" 120_365Days=1 OR Above365Days=1
|stats dc(eval(match('120_365Days',"1"))) as "count1" by hostname

I think the underscore is causing the error. The single quotes should help, otherwise try renaming the field before the stats command.

0 Karma
.conf21 CFS Extended through 5/20!

Don't miss your chance
to share your Splunk
wisdom in-person or
virtually at .conf21!

Call for Speakers has
been extended through
Thursday, 5/20!